Re: Getting Data from 2 Queries into 1 Report

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Alison,

I won't be giving you the help you hoped to receive. I'm going to
make some critical observations and pass on some information. Don't
take any of it as a personal attack and please do take it all as
earnestly intended advice.

The reason people are often asked to provide the details of their
table design is that the table design is key to all that follows in
the implementation of your application. Even though I don't know the
specifics of what you're doing with your application I can see that
your over-all table design is incorrect.

"How come", you ask? Because you should be relating the Invoice and
the Payment in a one-to-many relationship. [ By the way, since you've
named your tables ...Log I infer that this is a parallel application
to something else that generates your actual invoice and that you may
be entering your payments received manually or ?? ] You create a
one-to-many relationship between two tables by including a copy of the
Primary Key of the main/parent/One table in the secondary/child/many
table as a Foreign Key. Assuming each table will have an autonumber
Primary Key then the Foreign key field in the 'many' table is simply a
field storing a Long Integer number and having the same field name as
the Primary Key of the 'one' table. It has the sense of "I am the
child of the record in the 'one' side table with the value of ...".
Just to confuse the issue a bit more, there is no special datatype for
a Foreign Key. It is simply a meaning assigned to a field (or
possibly several fields if you're using a natural Primary Key in the
'one' table. It is generally sufficient if you simply enter the
words "Foreign Key in the description of the field in your table
design. By the way, if you are using Autonumber surrogate primary
keys, it is common to give them the table name with the two letters
"ID" tacked on the end.

How does it work? Once your tables are designed, go to the
Relationships window and Add the two tables under consideration. Draw
a line between the Primary Key in the 'one' table to the Foreign Key
in the 'many' table. Enforce Referential Integrity and enable
Cascading Deletes.

One more note about "how it works"; The parent does not maintain a
list of its children. The children identify themselves as being the
child of the parent whose Primary Key value is contained in their
Foreign Key field(s).

Oops! I've been giving you suggestions assuming that you use a
standard naming convention. It is recommended that you do so. The
Reddick naming convention is widely adopted. It's very useful to have
done so if you ever come back to your code sometime in the future.

I usually name tables "tbl" plus the singular of the name of the thing
its records describe. tblInvoice and tblPayment would cover the two
tables you've described so far.

While we're talking about your tables, tblCustomer would participate
in a one-to-many relationship with tblInvoice. The same PK & FK
relationships apply so you should be sure to include that in your
re-design and in the Relationships window referenced above: You'll
have three tables in a hierarchy.

When you re-design your tables, don't include any Lookup Fields!
They are an abomination and, while they may be very helpful in an
application that will remain forever primitive, they will lead to
troubles later (particularly noticed as queries presenting unexpected
data).

Notice I didn't say lookup tables. Lookup tables are a good thing.
They are simply regular tables that get the "lookup" name from their
intended use: to hold a list of things that other parts of your
application can lookup and have inserted into the current record, thus
avoiding typographic errors and miss-spellings. PaymentType might be
such a list.

There are several other Access related newsgroups, among them:
tablesdesign, forms, formscoding, queries, reports, gettingstarted.
There are lots of books on Access. Check them out before you buy.
There is also an excellent site filled with Access lore:
www.mvps.org/access

HTH
--
-Larry-
--

"Alison Downing via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxx> wrote
in message news:409922d95cd04523a918a73134837130@xxxxxxxxxxxxxxxxxxxx
> Hi Larry,
>
> I suppose I should've guessed I'd leave something out. OK - here's
the
> table designs;
>
> Table: InvoiceLog
> [IDNo]
> [Date] ...this is the date the invoice was raised
> [Customer]
> [InvoiceNo]
> [NetPrice]
> [DatePaid] ... the date the invoice was paid
>
> Table: PaymentsLog
> [IDNo]
> [Date]
> [Type] ... this is a lookup field that records different types of
payments
> e.g. cash, sundry, fuel
> [Details] ... text field
> [TotalCost]... the total payment made including tax
> [VATApplicable] ... yes/no field for indicating whether tax is
applicable
> (because some things you buy aren't subject to tax)
>
> I also have two forms;
>
> Form: InvoiceLog
> This has all the fields in the Invoice Log table plus two calculated
fields;
> [InvVATCalc]
> [InvTotalCalc]
> Both fields are derived from a query based on Table:PaymentsLog
using
> expressions;
> "InvVATCalc: [Net Price]*0.175"
> "InvTotalCalc: [Net Price]+[InvVATCalc]"
>
> Form: PaymentLog
> Again, this has all the fields in the Payment Log table plus two
calculated
> fields;
> [NetCalc]
> [VATCalc]
> Again, both fields are derived from a query based on
Table:PaymentsLog
> using expressions;
> "NetCalc: IIf([VAT Applicable],[Total Cost]/117.5*100,[Total Cost])"
> "VATCalc: IIf([VAT Applicable],[Total Cost]-[NetCalc],0)"
>
> I have no relationships set up for any of the tables or forms
because, when
> reporting the figures, Invoice VAT is completely different from
Payment VAT
> and I must total them separately.
> The date of payment [PaymentsLog.Date] and the date the invoice was
paid
> [InvoiceLog.DatePaid] are relevant only for retrieving the data
within a
> selected time period.
>
> I've entered three months data onto the forms and everything is
working
> beautifully, including the queries that pull the records I need for
the
> report.
>
> Hope this helps. Just please let me know if you need any more info.
> Thanks
> Alison
>
> --
> Message posted via http://www.accessmonster.com


.

Relevant Pages

  • Re: Business objects, subset of collection
    ... with amount due, payment total, etc. ... for the selected vendor ... I think one way to express my point is that the filter is the WHERE clause and one specifies the attributeto be checked in it. ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... from invoice i join payment p on i.invoiceid=p.invoiceid ... locating an invoice. ... composite key based on the sum of all of the attributes and methods at ... A SQL database is very good at doing thing kind of decisions. ...
    (comp.object)
  • Re: credit card reciept response
    ... transaction is submitted for processing. ... to the consumer or a POST string to a site designated by the merchant. ... Here you accept the POST data, validate it, and mark the invoice ... DO NOT send any order details back to the payment ...
    (comp.lang.php)
  • Re: Business objects, subset of collection
    ... from invoice i join payment p on i.invoiceid=p.invoiceid ... locating an invoice. ... composite key based on the sum of all of the attributes and methods at ... A SQL database is very good at doing thing kind of decisions. ...
    (comp.object)
  • RE: Append Queries - Seek Old Invoices
    ... There are a lot of Customers to be entered in it, Okay, ... so not only payment details will be entered on ... CilentCode is the common field and therefore must be the primary key in the ...
    (microsoft.public.access.queries)