GROUP BY ALL type query needed

[pkerr2003]

I want to list all groups within my data regardless of whether they meet a certain condition - if they do not meet the condition I want to display a count of 0 for the relevant groups.

Access Query Question:

Using Access 2003 SP2.

I wanted to use the 'GROUP BY ALL' command but apparently this cannot be used with remote tables and Microsoft no longer support the 'ALL' command with future releases.

I want a list of all referral sources for all new customers regardless of whether they have placed an order or not - if a group has no orders placed I want the count to be 0.

e.g. code:

SELECT Customer.Refer, Count(*) AS Orders
FROM Customer
WHERE (((Customer.Date) Between #1/1/2007# And ((#1/8/2007#)+1)) 
AND ((Customer.Notes) Like '*<Orders>*1*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*2*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*3*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*4*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*5*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*6*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*7*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*8*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*9*</Orders>*' 
Or (Customer.Notes) Like '*<Orders>*0*</Orders>*'))
GROUP BY  Customer.Refer

Result:

REFER | ORDERS
<NULL> | 500

However I want all other 'refer' sources to also be listed e.g.

REFER | ORDERS
<NULL> | 500
REFA | 0
REFB | 0
REFC | 0

The GROUP BY ALL does not work - I get the following error:

"Syntax Error (missing operator) in query expression 'ALL customer.refer'"

Note: On a separate point my 'Where' clause looks complicated because I only know if an order has been placed by looking at a 'memo' field searching for the following pattern "<Orders>####<Orders/>" (where #### can be any number combination - I have not been able to find an easier way to search for a string pattern (any guidance would be greatly appreciated).

[NeoPa]

I would state confidently, that the SQL you posted does NOT match the error message posted.
Please check and post (using Copy/Paste) the matching pair.

[NeoPa]

It is also difficult to work without the actual data (this is however necessary most times as data is hard to post sensibly), but would your <Orders> information not be detectable simply with the "Like '*<Orders>*</Orders>'" check. Not if you have all non-numeric characters in any order number, but do you have that? I'll leave that with you.

[pkerr2003]

Thank you for your help. I have resolved the problem by using three separate queries - getting the data into the exact format that I require.

You are right the error message reported did not match the data shown - the message was right it was just the table reference that was different.

Thanks again for replying - much appreciated.

[NeoPa]

No problem.
I'm glad you managed to resolve your issue :)