469,331 Members | 5,740 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

GROUP BY ALL type query needed

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Customer.Refer, Count(*) AS Orders
  2. FROM Customer
  3. WHERE (((Customer.Date) Between #1/1/2007# And ((#1/8/2007#)+1)) 
  4. AND ((Customer.Notes) Like '*<Orders>*1*</Orders>*' 
  5. Or (Customer.Notes) Like '*<Orders>*2*</Orders>*' 
  6. Or (Customer.Notes) Like '*<Orders>*3*</Orders>*' 
  7. Or (Customer.Notes) Like '*<Orders>*4*</Orders>*' 
  8. Or (Customer.Notes) Like '*<Orders>*5*</Orders>*' 
  9. Or (Customer.Notes) Like '*<Orders>*6*</Orders>*' 
  10. Or (Customer.Notes) Like '*<Orders>*7*</Orders>*' 
  11. Or (Customer.Notes) Like '*<Orders>*8*</Orders>*' 
  12. Or (Customer.Notes) Like '*<Orders>*9*</Orders>*' 
  13. Or (Customer.Notes) Like '*<Orders>*0*</Orders>*'))
  14. GROUP BY  Customer.Refer

Expand|Select|Wrap|Line Numbers
  2. <NULL> | 500
However I want all other 'refer' sources to also be listed e.g.

Expand|Select|Wrap|Line Numbers
  2. <NULL> | 500
  3. REFA | 0
  4. REFB | 0
  5. 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).
Jun 21 '07 #1
4 1935
32,182 Expert Mod 16PB
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.
Jun 24 '07 #2
32,182 Expert Mod 16PB
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.
Jun 24 '07 #3
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.
Jul 6 '07 #4
32,182 Expert Mod 16PB
No problem.
I'm glad you managed to resolve your issue :)
Jul 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Joshua Moore-Oliva | last post: by
3 posts views Thread by Abhi | last post: by
1 post views Thread by David Horowitz | last post: by
6 posts views Thread by cppnow | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.