Please help me in choosing an SQL statement (inner, outer or left join)

[wassimdaccache]

Dear Sirs;


I'm trying to write SQL statement and it is not executing what I need.

tables are: customers(customerID, customerName)
Invoices(invoiceID,customerid,invoicedate,invoicea mount)
Receipt (receiptID,customerid,receiptdate,receiptamount)

using inner outer an left join is always making a combination on the query.

what i need is just to add to queries to each other for example:

invoiceID customerID invoicedate receiptamount receiptdate receiptID

22--------------171-----------1/1/2006-------------- 0 --------------0 -------------- 0
0----------------171--------------0-------------- -----120-----------1/2/2006---------21
0----------------171--------------0--------------------360-----------1/3/2006---------22
23---------------171----------1/4/2006--------------0 --------------- 0 ----------------0

The idea is to sort this query by date.

My relationship is connected table customers by invoices and customers by receipt (1 to infinity customerid to customerid(invoices and receipt))

I don't want any duplication in data.

does my relationship map me to this kind of query ?
Is there any feature that report in Access provide to such as queries?

Any help guys please please I read this articles many times
http://bytes.com/forum/thread608296.html and i don't think a single sql statement will help me

I WOULD BE MORE THAN HAPPY TO HEAR FROM YOU.




WASSIM S DACCACHE

[Delerna]

try making a query for each of the tables.Each query will that take the fields that are in its table and adds fields that are not in its table. So each table will have that same fields that are in your result.

Then you can use a union query to join the results of the three queries into 1 recordset and sort them by date.

I can provide an example if you have trouble doing that.

[wassimdaccache]

Man;


Still have duplication in values.

Kindly, provide me more details or example.

Appreciate your help


WASSIM S DACCACHE
CCE

[Delerna]

actually you only need the two tables

 
select invoiceID,
        customerID,
        invoicedate,
        InvoiceAmount as Amount,
        0 as receiptdate,
        0 as receiptID
from Invoices
union all
select 0 as invoiceID,
        customerID,
        0 as invoicedate,
        receiptamount as Amount,
        receiptdate,
        receiptID
from Receipt
 

By the way
The reason you are getting duplicates with the joins is that there are 2 invoices and 2 receipts for a single customer which are probly for two seperate orders.
You have nothing to distinguish the two invoices from one another in your join, such as an order number. Ditto for the invoices.
so when you join the invoices to the receipts.
For each invoice there are 2 matching receipts, therefore the 2 invoices return 4 records.
I hope that I explained that clearly enough.

[wassimdaccache]

Dear Delerna,



I understood your logic. I appreciate you it works with me.


Thank you again for all your feedback and your aid.


Best regards;



WASSIM S DACCACHE
CCE
<< Link removed in Technical Thread >>

[NeoPa]

Wassim,

FYI The rules prohibit inclusion of links in the technical posts (except where they're part of the question).

I've removed the link and I'm sure it was done in innocense so no real problem.