Query Critera Question

[RudeRam]

I am trying to create a query from a table of 95 fields. I only need the query to come back with only those fields that have a 1 or more in it. I have tried in the criteria box using >=1 but I get no returns from my query. If I take the criteria away then I get all the fields regardless if any value is in the field.

Once I get the query correct I will then create my report from that.

Thanks

 

[Norie]

create a query from a table of 95 fields

Can I ask why you have so many fields?

Are these fields holding similar data?

I have tried in the criteria box using >=1

What exactly did you try?

 

[RudeRam]

I have 95 training events in whcih to track , when ever one is completed they will inout the number of time it is done with 1 being the least and XX being the most. I do not want to see those events which do not have a 1 or more in them

 

[Andrew Fergus]

Hi, following on from Norie's question, it appears you have used a data structure that is going to make this very difficult for you - not just for this one query but for other queries and reports in future.

Have you considered setting up a training event table and linking it to whoever or whatever is doing the training? This way your training table would have the 95 rows (as oppposed to 95 columns) and to exclude the training events that have not been started will be a lot easier (in the query).

Also, with your existing data structures, what are you going to do if you want to add another training event? If you add another field (or column) to your table then this is going to have a major impact on any queries and reports you have.

This might not be want you want to hear but, in my opinion, I think you should change your data structures to better support what you are trying to do and to make full use of the "relational" bits of the database. No offence intended by it but I can see significant problems in future with your existing set up.

HTH, Andrew.

 

[RudeRam]

Andrew,

If I am following you correctly, you are suggesting that I make a seperate table with the events and link it to say my roster table.

I am trying to make this an easier on my co workers

Thanks
Rude

 

[Norie]

Rude

What is your current data structure?

What fields do you have, what are they called etc?

 

[RudeRam]

Norie,

They way the table is set up is across the top are my training events. I have it set up so when data is input from the input form I will get a indivdual record.

The first field is Name the second field is Date of flight with each remaining field being a training event i.e. SAT SR44, Day landing LD01, etc for a total of 95 fields.

HTH

Rude

 

[Norie]

Well that should be able to be cut down to just 4 fields:

Name
Date
Training Event - contains SAT SR44, Day Landing LD01 etc
Completed - no of times completed

You might not even need the last field. Can you give an overview of what this data actually represents?

 

[RudeRam]

Norie,

How would I make one field contain the training events. I have some experience in Access butnot that much.

I track training for pilots in the USAF. Each one of the events they are required to do so instead of having them do them on paper I want to build a data base where the input for entry.

HTH
Rude