By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,218 Members | 1,498 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,218 IT Pros & Developers. It's quick & easy.

Trouble With Left Join

P: n/a
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.
Dec 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Dec 19 '05 #2

P: n/a
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.
Dec 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.