Record Not Showing in Query When One Field Contains No Value

[Zacko]

I have a query based on three other queries. There are four fields in this query. These are the fields:

CUSTOMER ID joins all three queries on which the query is based. The customer ID.

OVER30 displays the total outstanding amount that hasn't been paid by a customer in over 30 days.

CURRENT displays the total current outstanding for each customer for orders made within the current month.

TOTAL displays the ultimate total of OVER30 and CURRENT so it's a calculated field that simply adds the values from the two previous fields.

My problem is certain customers have no value/record for OVER30 because they're good customers. I still expect to see a record of them in the query because they do have a value/record in CURRENT (record displayed in the query from which I draw this field). However, there is no record of them in the query. Records only exist for customers with values/records in both OVER30 and CURRENT.

Keeping in mind that the values in OVER30 and CURRENT are results of two other queries, what would be the solution to this problem?

Here is the query in SQL:

SELECT [Q ACCOUNT ITEMS].[CUSTOMER ID], [Q ACCSTAT11].OVER30, [Q ACCSTAT22].CURRENT, [OVER30]+[CURRENT] AS TOTAL
FROM [Q ACCSTAT11] INNER JOIN ([Q ACCSTAT22] INNER JOIN [Q ACCOUNT ITEMS] ON [Q ACCSTAT22].[CUSTOMER ID] = [Q ACCOUNT ITEMS].[CUSTOMER ID]) ON [Q ACCSTAT11].[CUSTOMER ID] = [Q ACCOUNT ITEMS].[CUSTOMER ID]
GROUP BY [Q ACCOUNT ITEMS].[CUSTOMER ID], [Q ACCSTAT11].OVER30, [Q ACCSTAT22].CURRENT, [OVER30]+[CURRENT];
 

[MikeTheBike]

Hi

You need to use an outer join which would be either a Left Join or RIGHT JOIN, depending on the order of the tables in the query.
In this case it would suggest something like this

SELECT [Q ACCOUNT ITEMS].[CUSTOMER ID], Nz([Q ACCSTAT11].[OVER30],0) As OVER30, [Q ACCSTAT22].[CURRENT], Nz([Q ACCSTAT11].[OVER30],0)+[Q ACCSTAT22].[CURRENT] AS TOTAL
FROM [Q ACCSTAT11] RIGHT JOIN ([Q ACCSTAT22] INNER JOIN [Q ACCOUNT ITEMS] ON [Q ACCSTAT22].[CUSTOMER ID] = [Q ACCOUNT ITEMS].[CUSTOMER ID]) ON [Q ACCSTAT11].[CUSTOMER ID] = [Q ACCOUNT ITEMS].[CUSTOMER ID]
GROUP BY [Q ACCOUNT ITEMS].[CUSTOMER ID], [Q ACCSTAT11].[OVER30], [Q ACCSTAT22].[CURRENT], Nz([Q ACCSTAT11].[OVER30],0)+[Q ACCSTAT22].[CURRENT];
 

This will return all the common records from [Q ACCOUNT ITEMS] and [Q ACCSTAT22] and the relevant records from [Q ACCSTAT11]. Where there is no record in [Q ACCSTAT11] a null value will be returned, this is changed to a zero by the Nz() function.
HTH

MTB

[Zacko]

Thanks a lot, Mike. It worked perfectly. I now have what I want and have used the query to make a subform and will be making a subreport in the same way. Thank you!