Querie help needed

[Jman883]

I need to build a Querie that will filter about 50 fields for "Trainer", but all i need to know is if I have more than 2 for each field. I am drawing a blank. Any Ideas on how to do this?

Thanks

 

[statsman]

In the criteria of your query under the field you wish to look more more than 2, enter the following

>2

 

[Jman883]

I have Trainer in the criteria box if i put >2 behind trainer this works. As I add to other fields me search is getting smaller. doi need to build a querie for every field?

 

[ajetrumpet]

Jman,

It sounds like you are using the word "field" to mean something other than what it really is. Plus, in order to get a helpful answer, you should probably elaborate a bit more. Your description of the problem seems vague....

 

[Jman883]

Ok,
I have about 50 fields which are machine operations and we set the operators training level at Novice, Trainee , Certified, and Trainer. For auditing purposes. I wanted to run a querie to show how many operations (Fields) have <>2 operators at the trainer level. I was hoping that I could get something that said:

op 10 op 20
22 44

The lower # 22 and 44 would be how many operators are at the traioner level.
I am sure there is a way but I am not working with #'s.

 

[danabides]

sounds like you might want to run a crosstab query. your row heading should be 'training level' and your value would be machine operators. group by 'count'. that will tell you how many operators are at each training level.

 

[Jman883]

I think this will work, i will have to spend some time with it.

Thanks

 

[Jman883]

This looks great for a single field or lookng at a total for 3 fields, but I have 50. Is there another way to do this?

 

[neileg]

You are running into a problem that is cause by bad design. Your 50 fields clearly suggest that your data is not normalised. Instead of 50 fields in a table, you should have 50 records in a related table. This way, you would simply be able to group and count the records.

The only way to do this task with the current structure is to create 50 queries and then join them together in a way that resembles how the table should have been built in the first place.

Do some research into normalis(z)ation.

 

[Jman883]

Your right, I have inherited this database and the data table is to big.

Thank you all for your support.