Access 2000 query criteria

[MaryLou]

I am using a query I made in Jobboss and I need HELP.

I am working with 3 tables

I have invoices that I can sort and pull up fine (could be 20 items) but
when I add a date and payment I get only the the items paid.

I am sorting job and ship date and I want to include invoice paid and unpaid
and their dates.

 

[Wayne-I-M]

Hi

Can you the sql - you should get a better answer it you do

 

[MaryLou]

I don't know how to sql.
Can you help me out with this?
(e-mail address removed)

 

[Wayne-I-M]

Hi

Open the query in design view and select View at the top of the screen -
select sql and copy the code and paste it so others on the forum can see it.

I have to go out now but someone else will look at it and it will let them
know what your query is doing and (maybe) why it's going wrong

 

[MaryLou]

As requested:

SELECT Invoice_Detail.Part_Number, Invoice_Detail.Job,
Invoice_Detail.Ship_Date, Invoice_Detail.Packlist, Invoice_Detail.Document,
Invoice_Detail.Quantity, Invoice_Header.Paid_Date,
Invoice_Receipt.Amount_Applied, Invoice_Receipt.Last_Updated
FROM (Invoice_Header INNER JOIN Invoice_Receipt ON Invoice_Header.Document =
Invoice_Receipt.Invoice) INNER JOIN Invoice_Detail ON Invoice_Header.Document
= Invoice_Detail.Document
WHERE (((Invoice_Detail.Job)="14117B-A") AND ((Invoice_Detail.Ship_Date)
Between #1/1/2007# And #3/30/2007#))
ORDER BY Invoice_Detail.Ship_Date;

 

[andrewsmitchell]

I'm guessing that you need an 'outer' (right or left) join so that
'all' records will be shown.

To accomplish this, in the 'query designer':

(preliminary: you might want to arrange your tables - left to right:
invoice_detail, invoice_header, invoice_receipt)

1) right click on the 'join' (in the upper window - the 'line' between
the tables

2) select 'join properties'

3) click the option that says 'include all records from
'invoice_detail' and only...

4) do the same for the 2nd join - between the tables Invoice_Header
and Invoice_Receipt (selecting all records from Invoice_Header)


SELECT Invoice_Detail.Part_Number, Invoice_Detail.Job,
Invoice_Detail.Ship_Date, Invoice_Detail.Packlist,
Invoice_Detail.Document, Invoice_Detail.Quantity,
Invoice_Header.Paid_Date, Invoice_Receipt.Amount_Applied,
Invoice_Receipt.Last_Updated
FROM (Invoice_Header LEFT JOIN Invoice_Receipt ON
Invoice_Header.Document = Invoice_Receipt.Invoice) RIGHT JOIN
Invoice_Detail ON Invoice_Header.Document = Invoice_Detail.Document
WHERE (((Invoice_Detail.Job)="14117B-A") AND
((Invoice_Detail.Ship_Date) Between #1/1/2007# And #3/30/2007#))
ORDER BY Invoice_Detail.Job, Invoice_Detail.Ship_Date;



Andy

 

[MaryLou]

Thank you so much, it works great.
Mary Lou