Access 2000 query criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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;
 
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
 
Back
Top