Combining tables, but including all records.

[Guest]

I adopted several budget tracking databases. These databases use a query
from an Invoice Table and a Budget Table to show the amount of funds expended
YTD, and the balance the budget left.

Because it is pulling from the Invoice Table, unless an invoice already
exists, the query doesn't work. Because of this, each year a set of fake
invoices amounting to $0 are entered into and Invoice Table for every
department expenditure. This is very tedious, and time consuming.

What I'm looking for is some way to show all accounts regardless of whether
there is an invoice applied to it, as well as the sum of the funds for the
accounts that have been invoiced.

Can it be done?

Thanks,
Liz

 

[John Vinson]

What I'm looking for is some way to show all accounts regardless of whether
there is an invoice applied to it, as well as the sum of the funds for the
accounts that have been invoiced.

Can it be done?

Certainly. You need an "Outer Join".

Your query probably has the Accounts table joined to the Invoices
table. Select that join line by clicking on the line itself, in query
design view. Choose Option 2 (or maybe 3): "Show all records in
Accounts and matching records in Invoices".


John W. Vinson[MVP]

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT B.Account, I.Account, ... other columns ...
FROM Budget As B LEFT JOIN Invoice AS I
ON B.Account = I.Account
WHERE ... criteria ...

LEFT JOINs get all records in left table. To show null right table
columns you have to include a left table column in the SELECT clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkNwfIechKqOuFEgEQKBPACcDpiVUTn6eT/CEDHo1xG+YuhvglsAoKHV
rEgppd/VllZmcydJsmxalavo
=z4/6
-----END PGP SIGNATURE-----

 

[Guest]

Thanks for the help. Worked great!
Liz

Certainly. You need an "Outer Join".

Your query probably has the Accounts table joined to the Invoices
table. Select that join line by clicking on the line itself, in query
design view. Choose Option 2 (or maybe 3): "Show all records in
Accounts and matching records in Invoices".


John W. Vinson[MVP]

 

[Guest]

I appreciate the help.

Thanks,
Liz

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT B.Account, I.Account, ... other columns ...
FROM Budget As B LEFT JOIN Invoice AS I
ON B.Account = I.Account
WHERE ... criteria ...

LEFT JOINs get all records in left table. To show null right table
columns you have to include a left table column in the SELECT clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkNwfIechKqOuFEgEQKBPACcDpiVUTn6eT/CEDHo1xG+YuhvglsAoKHV
rEgppd/VllZmcydJsmxalavo
=z4/6
-----END PGP SIGNATURE-----