Setting Criteria in Multiple Fields in Access Query

[MPXJohn]

Hey all, Thanks for helping. What I have is an Access Query which has multiple date fields (RejectionDate1, RejectionDate2, etc) and multiple Builder ID fields (BuilderID1, BuilderID2, etc). What I am trying to do is have the Query return data based on a date range that is entered for a specific Builder ID. So if I enter, say, 10/1/15-10/31/15 as the date range and 110 as the Builder ID, I need it to 1st look at all the Date fields and then return any data in that range that has the specific ID in any of the BuilderID fields. I am using [code]Between [Enter Beginning Date] And [Enter Ending Date][code] to enter the date, but I only can enter the date once, and the same type code fort the BuilderID. I have the codes on the same line which makes them an AND statement, and it works if there is only one Date field and one BuilderID field, but I am having trouble making it work with the multiple fields.

[JoeM]

What you have here is an database structure that is not normalized. You should not have similar repeating fields in your table like that.
Instead of having multiple RejectionDate fields, you should have just one, like this:
RejectionNumber
RejectionDate
So if you had multiple Rejection Dates, you would have multiple records, not multiple fields within the same record.
The same holds true for BuilderID.

If you do this, then you can easily do what you want, as your Criteria only has to pertain to one field.

Here is an article on the Rules of Data Normalization: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf
As you are experiencing first hand, if your database is not normalized, tasks which should be fairly easy become much more difficult than they need to be.

[MPXJohn]

Thanks Joe. From reading other posts I figured that might be the answer. Thanks for the link. Guess I have some reading to do.