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

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

--
Wayne
Manchester, England.



"MaryLou" wrote:

> 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.

I don't know how to sql.
Can you help me out with this?
(E-Mail Removed)

"Wayne-I-M" wrote:

> Hi
>
> Can you the sql - you should get a better answer it you do
>
> --
> Wayne
> Manchester, England.
>
>
>
> "MaryLou" wrote:
>
> > 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


--
Wayne
Manchester, England.



"MaryLou" wrote:

> I don't know how to sql.
> Can you help me out with this?
> (E-Mail Removed)
>
> "Wayne-I-M" wrote:
>
> > Hi
> >
> > Can you the sql - you should get a better answer it you do
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "MaryLou" wrote:
> >
> > > 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.

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;

"Wayne-I-M" wrote:

> 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
>
>
> --
> Wayne
> Manchester, England.
>
>
>
> "MaryLou" wrote:
>
> > I don't know how to sql.
> > Can you help me out with this?
> > (E-Mail Removed)
> >
> > "Wayne-I-M" wrote:
> >
> > > Hi
> > >
> > > Can you the sql - you should get a better answer it you do
> > >
> > > --
> > > Wayne
> > > Manchester, England.
> > >
> > >
> > >
> > > "MaryLou" wrote:
> > >
> > > > 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.

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

Thank you so much, it works great.
Mary Lou

"(E-Mail Removed)" wrote:

> 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
>
>