Multiple criteria in one query

[cormack15]

I am trying to interrogate multiple fields using one query with two x criteria statements for each field. When I type in the criteria statemnts it all looks fine. When I close the database and go back to the query design there are multiple criteria, the more I add the more complicated it gets to the extent that I quickly run out of memory. It would appear, to me (and I am a real novice) that access is taking my simple criteria and having to make it complex to deal with the complex relationships it uis creating.
Does that make sense?
Is the answer to have multiple nested queries?

 

[Galaxiom]

Access reconstructs complex queries to suit itself and it isn't always what you would expect.

Odds are if you query is that complicated you have a problem with the way your data is structured.

Tell us more about your table structure and hopefully we can help you optimise it for the queries you are wanting to run.

 

[cormack15]

Hi Galaxiom -
I have one table which has 58 fields. The fields are a variety of different types (text, numbrs and dates). There are nearly half a million records altogether, not all records have an entry in each field.
I want to run a query by form and have a form capturing variables entered by a user to drive the query. I want the query to be able to run on ca. 30 of those fields, so a user can specify a variety of values (including max/min values) and only return those that meet all their requirements.

 

[cormack15]

btw - yes, I think that is exactly what is happening - it is reconstructing the query and I expect you are right, the problem no doubt lies in the way I have structured things!
All help gratefully recieved.

 

[Galaxiom]

An alternative is to store the values in one field and have another field to store the aspect of the measurement that is currently designating the separate fields.

Usually the designation would be held in another lookup table with fields for a numeric code and the name of the measurement. The code is used in the main table to indicate the measurement type.

This way the search is performed on one field but can also be restricted to any particular measurement type by placing criteria on the other field.

 

[cormack15]

Thank you Glaxiom - I'll play around with that - your help very much appreciated