moving up to complex queries

[JaneInMA]

I feel I have a good grip of the basics of SQL and I am trying to grip the more complex. I just wrote a query that starts with one query which grabs the selection criteria from a form. I then bring that into a second query which using totals counts the number of time a field occurs. I then bring that into a third query to count how many instances I have where the field occurs once, twice etc.
This works but has limitations.
My question is should I have been able to write this as one query instead of three and is there be a simple way to loop through several selection criteria (I am looking to loop through the whole set of data seperating it by Right([field),2)
Thanks Jane

 

[JoeMiller]

Three times seems right to me, as for the looping through, I'm not sure that I understand what you are trying to do. Could you explain a little more?

Joe Miller
joe.miller@flotech.net

 

[JaneInMA]

I am taking a table with 5000 rows all which could be categorised into 15 or so categories based on the primary key categorisation ie all starting with QC, all starting with QA and would like to in one report get how many in each category have been changed (a category we capture in a date field) how many times. My current trio of query can do this if the user types in QC or QA but I would like to see all the categories captured in one report.
ie for all QAs 100 have been changed once
15 changed twice and so on
then move to the next category and say for QC 120 have been changed once, 20 twice etc
Any hints or thoughts on my current strategy .
(I also wondered if using three queries is just being a Access wuss and I had to start to learn to build bigger and better SQL.)

 

[JoeMiller]

Ok, this may be possible in one query, but I need to know how many times an item could be changed. Are we talking 3 or 4 times max? or could it be like 250 times on 1 item? And what is the thing that's deciding that the record has been changed? or is the table a record of changes so each record is a change?

I need some more db structure, what your doing, etc... I understand the goal, but don't know what things have to be looked at to achieve the goal.

Joe Miller
joe.miller@flotech.net

 

[JaneInMA]

OK, we have a table in which we create a new record for each time the item is changed. Therefore we can query that table and one record means one change for a specific item on a specific date. The number of changes theoretically is infinite, but in practice most items get changed once in the time period (say one year), while one or two get changed up to 5-6 times.
The db tracks how the items (SOPs) are controlled, with the changes being just one part of the picture. However keeping track of how each category changes allows supervisors to segregate work. Therefore the more information I can provide them on one report the better.
Thanks for keeping an eye on this topic.

 

[JoeMiller]

Could you possibly email me a db with just that changes table in it so I could have a look see and give you a better answer? I'm pretty sure we can do this in one query using a subquery, but I want to be sure.

Joe Miller
joe.miller@flotech.net