2
SELECT f.card_serial_num, count(1) AS CardRxCnt
FROM [Excel 8.0;HDR=Yes;Database=C:\Users\Mike\Desktop\er.xls].[er$] AS f
WHERE f.location_name not like 'PREPACK' 
  and f.card_type not in ('PRN','sequential') 
  and (f.card_due_date = #9/15/2014# or f.card_due_date = #10/1/2014#)
GROUP BY f.card_serial_num
HAVING CardRxCnt >2

I have a problem with HAVING CardRxCnt >2. If I take out, I get my query returned. But if I have it in, it somehow prompts for an input and so when I just put 1 it returns nothing. Actually without CardRxCnt, sometimes the query prompts for an input at which I enter 1 and the query executes. Yet other times it would just go through. So my question is two part:

  1. what could be causing this random behavior of "asking"? I know to prompt for a user input, I have to surround a value with []. Could it be the Excel part?

  2. CardRxCnt (in SELECT and HAVING): I don't see what's wrong with it but when I add this, query does not work.

Please help. And I can't do VBA/macro here so if you're gonna say why don't you query with VBA it's not a solution for me.

Barranka
  • 20,547
  • 13
  • 65
  • 83
mcchung52
  • 31
  • 1
  • 4
  • 2
    Try changing your `HAVING` clause to `HAVING count(*) > 2` -- http://stackoverflow.com/questions/2068682/why-cant-i-use-alias-in-a-count-column-and-reference-it-in-a-having-clause – sgeddes Oct 12 '14 at 00:43
  • `count(1)`? Not a good way to do things. Use `count(*)` or `count(f.card_serial_num)` – Barranka Oct 12 '14 at 04:00
  • WOW! count(*) instead of referencing it with CardRxCnt worked! Thanks. I guess the query kinda knows what count I'm referencing to. I'm still kind of new so I thought I had to name what I want to reference all the time. BTW I read somewhere that having * brings up all the fields so using 1 was more efficient if i'm just counting. So I just used. Can you explain why * is a better way of doing things? – mcchung52 Oct 12 '14 at 06:36
  • Also, on the same query that worked a minute ago I get a syntax error but everything is exactly the same and it's driving me nuts. Is there a better tool to test out your query? I feel like it's the Microsoft Access (2003 version). Same query I copy and paste into a new query and it works! WTH???? Really confused. – mcchung52 Oct 12 '14 at 07:20

0 Answers0