Re: Return Query Result when no data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <NOSPAMrobpparker@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ePDAaoAfIHA.4140@xxxxxxxxxxxxxxxxxxxxxxx
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
data***. 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" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OEekka7eIHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
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" <NOSPAMrobpparker@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:eMBcYd5eIHA.748@xxxxxxxxxxxxxxxxxxxxxxx
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" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23bNvbj3eIHA.4140@xxxxxxxxxxxxxxxxxxxxxxx
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







.

Quantcast