Query Count True and False from multiple values

[Geekmomz]

Greetings, all! I have a question regarding an issue I'm having today. Here's the situation: I am counting number of units of each type shipped within a given date range in a query. That is working fine.

The problem comes in when I try to separate repaired units from scrapped units. Repaired units have a numeric or alphanumeric code (always starting with a number) in a field where scrapped units have the text "scrapped." Each repaired shipment, however, has a different code.

I have tried setting this field in the query to Group By with criteria rows for 'Like "scrap*"' and 'Not Like "scrap*"', but it still groups on each value for the field. What I'd like to do (strongly prefer to keep this within one query) is to group on a calculated field (Scrap: Iif([TrackCode] Like "scrap*",TRUE,FALSE)), but I can't seem to figure out how to do that without having to have the TrackCode field in the query as a Group By field.

I know I could use this as is and send it to a report which would allow me to group on the calculated field mentioned above and then calculate totals in a group footer, but as this data needs to be sent to Excel, it seems kind of silly to route it to a report first if it's not necessary.

Am I crazy, or is there a way to do this in my one (otherwise simple) query?
Thanks for your help!
Geekmomz

 

[POKY]

hi Geekmomz

I’m not sure if I understood your Q. so see and decide ::

Add a Yes/No field to your table (in my ex. “ysnSelected”)
Check it for “repaired”
Then make a query like this:

SQL = “SELECT YourTbl.YourField, YourTbl.ysnSelected
FROM YourTbl WHERE (((YourTbl.ysnSelected)=True));”

This query will give u all the checked parts for “repaired”

Not that if you change to “False” u will get the not checked : for u it is the “scrapped units”

Hope helped in someway !

POKY

 

[Geekmomz]

Poky,
Thanks for your suggestion. Unfortunately, what I need is a means of doing this in the query, not in the table. I have been considering adding a Scrapped T/F field to the table, but due to various reasons that is not an option in time for this query to be completed.
Geekmomz