Select Query using multiple Field criteria

[trebor3900]

I am trying to create a query that gives me a stock number relating to an item of clothing dependant on the sizes of the person concerned. When working i will make it into an update query but the criterea's seem to have a problem with each other.
I Got around this to an extent by using IIF statements in the query.
ie
IIf([tblClothing]![CollarSize]=0,[tblClothing]![CollarSize],[tblOfficers]![CollarSize])

so that if the sizes requiring the query were for trousers, the collar size query would not affect it as they would be zero anyway.

My problem is when i query for Minimum and Maximum Chest sizes or any other size that does not have an absolute value, but a range.
ie
IIf([tblClothing]![ChestMin]=0,[tblClothing]![ChestMin],[tblOfficers]![ChestSize] Between [tblClothing]![ChestMin] And [tblClothing]![ChestMax])------This returns no records

>[tblOfficers]![ChestSize]-------This works but affects the other queries. Cannot seem to put it successfully into an IIF statement

 

[The_Doc_Man]

Your biggest problem is that you treat multiple different items as the same item, which they are not. Your database is not normalized. One sure way to recognize this is the need for too many IIF statements when a simple SELECT query should ALWAYS be what you want.

If your differently-sized items have the same internal stock number, you have done yourself no favors. Even if the manufacturer's stock number is the same for the different sizes. (This would also be true if the color could vary but the stock number does not.)

If the primary record key is the item's maker's stock number and size, you would be able to write queries by size and stock number, which is what you seemed to be trying to do. The catch is, of course, narrowing down the search criteria to minimize the various returns you get from that query.

This is a design flaw in your business model which you need to rectify. The flaw is exposed by your inability to perform the query you really wanted to perform in a simple manner because the data you need for the query is not well organized. (Sorry if that sounds harsh; it really wasn't meant to be excessively critical. But I don't mince words, either.)

 

[trebor3900]

No. As i am starting out, the harsher the words the better. I was hoping that i was making a basic error as they are easier to rectify. That not being the case i will have to look again at the structure of the database. The Stock numbers of each item is different, including sizes

thanks again for the reply