Trouble With Left Join

[New Guy]

I created this query in Access with the Design View.

SELECT Fiscal_Period.FiscalKey, Invoice.type, Invoice.accid,
Sum(Invoice.Total) AS Amount
FROM Fiscal_Period LEFT JOIN Invoice ON Fiscal_Period.FiscalKey =
Invoice.FiscalKey
WHERE (((Invoice.accid)=29))
GROUP BY Fiscal_Period.FiscalKey, Invoice.type, Invoice.accid
HAVING (((Invoice.type)=0))
ORDER BY Invoice.accid, Fiscal_Period.FiscalKey, Invoice.type;

I have 37 fiscal periods in the table from 200301 until 200601. I am
expecting to get 37 records, one for each record, but I am only
getting records for the ones in the Invoice table.

How do I get a record for the months in which there are no records in
the Invoice table.

In the query builder, I clicked on the join option thay says "Include
ALL records from Fiscal_Period and only those records from Invoice
where the joined fields are equal.

Thnks.

[Mike Preston]

On Mon, 19 Dec 2005 22:36:04 GMT, New Guy <ne****@yahoo.com> wrote:

I created this query in Access with the Design View.

SELECT Fiscal_Period.FiscalKey, Invoice.type, Invoice.accid,
Sum(Invoice.Total) AS Amount
FROM Fiscal_Period LEFT JOIN Invoice ON Fiscal_Period.FiscalKey =
Invoice.FiscalKey
WHERE (((Invoice.accid)=29))
GROUP BY Fiscal_Period.FiscalKey, Invoice.type, Invoice.accid
HAVING (((Invoice.type)=0))
ORDER BY Invoice.accid, Fiscal_Period.FiscalKey, Invoice.type;

I have 37 fiscal periods in the table from 200301 until 200601. I am
expecting to get 37 records, one for each record, but I am only
getting records for the ones in the Invoice table.

How do I get a record for the months in which there are no records in
the Invoice table.

In the query builder, I clicked on the join option thay says "Include
ALL records from Fiscal_Period and only those records from Invoice
where the joined fields are equal.
I *think* that your Having clause is restricting the resultset to
those rows where Invoice.Type =0. With that said, if you have a row
that is built from a Fiscal_Period that has no matching Invoice, that
row will first be included (in the Left Join) and then, since it
doesn't "have" an Invoice, no longer be included.

I think the same thing goes for the Where clause.

So I think you will need to "OR" both of those with something that
will keep your Fiscal_Period rows where there are no Invoices.

Something like:
WHERE (((Invoice.accid)=29) OR ISNULL(Invoice.accid))
and
HAVING (((Invoice.type)=0) OR ISNULL(Invoice.accid))

I'm not sure the ISNULL will work in both clauses (I know it will work
in the WHERE clause).

Try it and see what happens.

mike

[New Guy]

On Mon, 19 Dec 2005 23:34:25 GMT, mb******@pacbell.net.invalid (Mike
Preston) wrote:

On Mon, 19 Dec 2005 22:36:04 GMT, New Guy <ne****@yahoo.com> wrote:
I *think* that your Having clause is restricting the resultset to
those rows where Invoice.Type =0. With that said, if you have a row
that is built from a Fiscal_Period that has no matching Invoice, that
row will first be included (in the Left Join) and then, since it
doesn't "have" an Invoice, no longer be included.

I think the same thing goes for the Where clause.

Yes, that did it.

Thanks a lot.