Return Query Result when no data

[Rob]

Using Access 2000, I have two tables, one is Debts, the other Sales. Sales
table has 3 fields: AccNo, Week and Turnover, Debts table also has 3 fields:
AccNo, Week and Outstanding debt.

I want a query to show all accounts in the Debts table for a given week
where there is an outstanding balance and where there's a Turnover from the
Sales table, I want to show this. However, the bit that does work for me is
that where there isn't a record in the Sales table (probably because the
customer has a debt but no sales), the debt record isn't showing because we
don't record nil sales in the Sales table.

Any ideas would be welcome. Thanks, Rob

 

[Rob Parker]

Hi Rob,

I don't quite understand what you mean when you say " ... the bit that does
work for me is ...". Do you mean this is the bit that does NOT work for
you. If so, it's likely to be due to using an inner join on AccNo and Week
(I'm assuming that you are joining the tables on both these fields), rather
than an outer (Left or Right - depending on how you establish them) join.
To change the join type, click on the line(s) joining the fields from the
two tables in the query design grid, and select the appropriate "Show all
records from ... " option.

If this doesn't solve your problem, please post the SQL view of your current
query, and a better description of your problem.

HTH,

Rob

 

[Rob]

Rob,

Thanks for the advise, and yes this was the part that did not work - sorry
about that. Using you suggestions I have achieved partly what I was looking
for. The below SQL returns the 1484 records, the number of records in
Debtmain however, Salemain has 1176 records of which I know 3 don't match
Debtmain as they don't have a debt for the date. I'd like to include these
3 records and show a total of 1487 records.

Any ideas or is this impossible. Thanks, Rob

SELECT DEBTMAIN.ACCNO, DEBTMAIN.WKEND, SALEMAIN.WKEND, DEBTMAIN.CURRBAL,
SALEMAIN.TURNOVER
FROM DEBTMAIN LEFT JOIN SALEMAIN ON (DEBTMAIN.ACCNO = SALEMAIN.ACCNO) AND
(DEBTMAIN.WKEND = SALEMAIN.WKEND)
WHERE (((DEBTMAIN.WKEND)=#2/23/2008#));

 

[Rob Parker]

Hi again Rob,

It's not impossible. But since your wanting records from the other table,
which are not appearing in your current query, you need a separate query to
retrieve them, and then a Union query to combine both recordsets.

This should do it (Note: I've removed one of the WKEND fields from your
original query - it's superfluous):

SELECT DEBTMAIN.ACCNO, DEBTMAIN.WKEND, DEBTMAIN.CURRBAL, SALEMAIN.TURNOVER
FROM DEBTMAIN LEFT JOIN SALEMAIN ON (DEBTMAIN.WKEND = SALEMAIN.WKEND) AND
(DEBTMAIN.ACCNO = SALEMAIN.ACCNO)
WHERE (((DEBTMAIN.WKEND)=#2/23/2008#))
UNION
SELECT SALEMAIN.ACCNO, SALEMAIN.WKEND, DEBTMAIN.CURRBAL, SALEMAIN.TURNOVER
FROM DEBTMAIN RIGHT JOIN SALEMAIN ON (DEBTMAIN.WKEND = SALEMAIN.WKEND) AND
(DEBTMAIN.ACCNO = SALEMAIN.ACCNO)
WHERE (((DEBTMAIN.WKEND) Is Null) AND ((SALEMAIN.WKEND)=#2/23/2008#));

If you're not familiar with Union queries, you'll find that you can't view
them in the query design grid; you can only view as SQL or as the result
datasheet. If you need to build one, the easiest way is to build each part
in the query design grid, then switch each to SQL view, and cut/paste each
part into the final union query, adding the UNION keyword between each.
Also note that if either part of the union query can return duplicate
records (I'm assuming that in this case the combination of ACCNO and WKEND
will not give duplicates), then you would need to use UNION ALL to prevent
duplicates from being eliminated in the union query.

HTH,

Rob

 

[Rob]

Thank you Rob, have used this to build upon, just having issues with Is
Null, Union doesn't seem to like it.

Regards, Robert

 

[Rob Parker]

Hi Rob,

The SQL I posted works fine - I built two test tables and checked. What
exactly do you mean by "Union doesn't seem to like it"? What is the SQL
that you've got now? Do the two separate Select parts of your union query
each work, and return the correct data (and have the same number of fields)?

Rob

 

[Rob]

Rob,

I have achieved what I was looking for, worth the effort as I'll use in
other queries. I have posted a separate query about an error I'm getting
using CINT in the query so hopefully someone will be able to help here as
well.

Thanks for your response. Rob