Query Problems

[P.McFarlane]

Hi

I have two tables. The first, 'JobData', has in it the following fields:
Date, JobNo, Charge, Cost
The second, 'InvoiceData' has in it the following fields:
Date, JobNo, InvoicedAmount

I have been trying to create a query that will return the following:
JobNo, SumOfCharge, SumOfCost, SumOfInvoicedAmount between two dates

However table InvoiceData does not necessarily have an InvoicedAmount for
all JobNo within the date range.
So far I have not been able to create a suitable query.
Any suggestions would be very welcome.

 

[MGFoster]

Hi

I have two tables. The first, 'JobData', has in it the following fields:
Date, JobNo, Charge, Cost
The second, 'InvoiceData' has in it the following fields:
Date, JobNo, InvoicedAmount

I have been trying to create a query that will return the following:
JobNo, SumOfCharge, SumOfCost, SumOfInvoicedAmount between two dates

However table InvoiceData does not necessarily have an InvoicedAmount for
all JobNo within the date range.
So far I have not been able to create a suitable query.
Any suggestions would be very welcome.

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

Since you did not include your query I'll guess that you are using INNER
JOINs between the tables. Use LEFT JOINs when you know that there will
not be any related data in a joined table, e.g.:

SELECT J.JobNo, SUM(J.Charge) As SumOfCharge, SUM(J.Cost) As SumOfCost,
SUM(I.InvoicedAmount) As SumOfInvoicedAmount
FROM JobData As J LEFT JOIN InvoiceData As I ON J.JobNo = I.JobNo
GROUP BY J.JobNo

Since there is always data in JobData that table will be on the Left,
which means all the data in JobData will be displayed (depending on the
filtering criteria [the WHERE ]). Any JobData row (record) that doesn't
have related data in InvoiceData will show a ZERO sum for
SumOfInvoicedAmount.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSNrtMIechKqOuFEgEQKuEQCcC3q6SKmaml6lBN55lr7vq6eamNMAoIlC
MFYlTBf4N+zfzk+KCTmLUVks
=d9ID
-----END PGP SIGNATURE-----