Building a union query gathering information from three tables

[odrap]

In a combination of two tables : tblInvoices and tblPaymentInvoices i gather all information re payments of invoices by clients. The table tblInvoicepayments is used to record the partial as well as whole payment of each invoice. Here we the invoicenumber, the paydate, the amount paid .
Apart of the these tables I also use a table tblCreditnotes.
These three tables must give me the possiblity to create a report
for each client that gives here/him a oversight of all the invoices that are booked on here/his name with a detailed information of what invoices, over a given timeperiod , still has to be paid, are already paid or partial paid ,with the date and the amount that was paid. In the same report the creditnotes concerning this client must be shown for the same period , together with what of these creditnotes has been paid and when and what creditnotes still has to be paid back.
Before asking to open the report, it must be possible to define the client for which the report has to be made as well as the period over which the information has to be shown .
To combine the information in the tables tblInvoices and tblInvoicepayments i know i can use a union query. But once this query is made up, how can i combine the information with these in the table tblCreditnotes, and how and where can i put the where clause , so that the client as well the period can be choosen that counts for the whole data

 

[apr pillai]

To bring the raw data to the report level we may put them through several processes with the help of different type of Queries like SELECT or MAKE-TABLE, UPDATE, APPEND Queries and automate with macro. Don't attempt to do everything with one or two Queries. Create intermediate tables with initial source queries and work with that data for further processing.

Union Queries are used only to combine data from two or more tables for which you have to remember certain rules to avoid problems. Have a look at the following Post for clues:

http://www.msaccesstips.com/2008/02/union-query/

 

[boblarson]

What field in creditnotes links up to the other tables?

In order for you to return the memo field intact (over 255 characters) you will need to limit the union query to the information you want and then link on the appropriate fields using that union query in another query and linked to the creditnotes table via any common keys.

Also, if not every record in the union query would have a record in the creditnotes table, you will need to use an outer join so that you also retain the records that have no note (and you can't use criteria other than is null or is not null on an outer joined table/query).