Query is just not quite right

[ed333]

I have a query which, for the purpose of generating our company's bills, creates an enourmous recordset. Most of the data comes from the Charges table, but there are 3 other tables involved here. The problem is, I want to have every charge show up in my query, even if some information is missing for that particular record. Normally everything is ok, but we've noticed charges missing from the bill that should be there, and we need a way to find them easily. I figured that if they showed up in this query, we could see where the missing information needs to be added.

Here is my SQL for generating these Pre-Bills:

SELECT tblCharges.RESPATTY, tblCharges.CHGATTY, tblClients.CLNTNO, tblCharges.FILENO, tblClients.CLNAME, tblFiles.TITLEFULL, tblCharges.DISB, tblCharges.DATE, tblCharges.UNITS, tblCharges.DESC1, tblCharges.CHARGE, tblCharges.HOLDFLAG, tblClients.STREET, tblClients.CITYSZ, tblClients.TELEA, tblClients.TELLB, tblCharges.RecordNumber, tblCharges.DESCMEMO, tblCharges.BILLCODE, tblCharges.RATE, tblCharges.BILLFLAG, tblCharges.WRITEOFF
FROM tblClients INNER JOIN ((tblBILLCODE INNER JOIN tblCharges ON tblBILLCODE.CODE = tblCharges.BILLCODE) INNER JOIN tblFiles ON tblCharges.FILENO = tblFiles.FILENO) ON tblClients.CLNTNO = tblFiles.CLNTNO
WHERE (((tblCharges.RESPATTY)=[Which Attorney?]) AND ((tblCharges.DATE) Between [Start Date?] And [Stop Date?]) AND ((tblCharges.BILLFLAG) Is Null) AND ((tblCharges.WRITEOFF) Is Null))
ORDER BY tblClients.CLNTNO, tblCharges.FILENO, tblCharges.DISB, tblCharges.DATE, tblCharges.RecordNumber;

If DISB or DATE is null, the record is omitted.
If CLNTNO or FILENO are null or mismatched, the record is omitted.

Any help as to how I can get these to show would be greatly apprecited.

Ed

 

[GumbyD]

You need to use outer joins and then add "or is null" to the criteria strings. Let me know if you need a bit more explaination.

Good luck!

GumbyD

 

[Mile-O]

INNER JOINS will only match data, however, when the joined tables have related data.

If you aren't getting the data you need then look up LEFT and RIGHT joins.

 

[ed333]

How do you use outer joins? I created this query in design view because I really don't know much about SQL. I can post a picture of my relationships if it will help

Thanks

Ed

 

[Mile-O]

In the QBE double click on any relationship between tables. Study the join types.