set criteria for several fields

[Guest]

I would like to select records of dependents born prior to Jan 1 1986. My
fields are Dep1, Dep1DofB, Dep2, Dep2DofB, Dep3, Dep3DofB etc. I need all
dependents born prior to Jan. 1, 1986. How can I ensure that I get anyone
from Dep1 and Dep2 and Dep3 etc. that are born before Jan 1, 1986. If I set
the criteria <01/01/1986 for each field it produces no records even though
their are dependents with birthdates before Jan 1, 1986 in the records.

 

[Rick B]

You could use an "or" statement to test each DOB field, but...
Generally you should not have your table set this way. This is a
one-to-many relationship. A person might have one dependant or 10
dependents. You should store these in a separate table with three fields...

EmployeeNum (or some key to tie it to your main table)
DependentName
DependentDOB

This will allow you to search the entire table for anyone born before your
date with one statement. You could also pull the related data from the
primary table for any selected records in this secondary table.

Note - If it were me, I'd probably have first, middle, and last name fields
in this table.

 

[Jeff Boyce]

Pat

Based on your description, your table is ... a spreadsheet! Use of
repeating fields (one set for each dependant) is pretty much required in a
spreadsheet design, but creates all kinds of headaches (as you've found) in
a relational database.

What are you going to do when you run into the family with 12 dependants?
Add that many more columns to your table? Modify your queries? Revise your
reports and forms? Re-do your code?

Instead, consider using a relational design, employing a one-to-many
relationship. Perhaps something like:

tblFamily
FamilyID
FamilyName

tblDependant
DependantID
FamilyID (identifies which family this dependant is associated with)
DependantFirstName
...

You could take the normalization even further by using a "Person" table to
hold all person information, and only inserting the PersonID in the
tblDependant.

 

[Guest]

How would I use an "or statement" to pull from each DOB field?

 

[Rick B]

In your query just put your identical criteria under each field on a
separate row in the grid. Notice in the left side of the grid that placing
criteria on separate rows creates an "OR" statement. Placing it on the same
row would create an "AND" statement.

 

[Guest]

I found when I did this, before posting for help, that it returned zero
records. I believe this method was looking for matching records in all
fields before producing the results and there are no employees who have
multiple dependents (in my table I have space for 5 dependents) born before
1986

 

[Rick B]

put the criteria on SEPARATE ROWS in the grid. That produces an OR.

 

[Rick B]

Again, you should move these to a separate related table. You are not using
a normalized design.

 

[Guest]

I am a bit apprehensive of doing this. This is our sole means of employee
tracking and if I mess around and lose or invalidate data.....I am not and
Access expert (as you probably guessed!) am basically self taught and the
sole person responsible for the functioning of our database.

 

[Rick Brandt]

I am a bit apprehensive of doing this. This is our sole means of employee
tracking and if I mess around and lose or invalidate data.....I am not and
Access expert (as you probably guessed!) am basically self taught and the
sole person responsible for the functioning of our database.

So work on a copy and don't abandon the current file until the new copy is
tested and working properly. Either that or hire somebody who can set it up for
you.