Need help with number ranges

[Elisa]

I need to set up a table in Access that has a field for
different age bands. For example, the first age band
would be 000-000, for newborns, the second age band would
be 001-001, the third would be 002-003, and they would
continue to 065-999. There will also be fields attached
to the age band specifying gender and dollar amounts. I
don't know if I can do this without formatting the field
as text. I will be querying this field by asking for
information on people with different ages.
Any help will be greatly appreciated.

 

[John Vinson]

I need to set up a table in Access that has a field for
different age bands. For example, the first age band
would be 000-000, for newborns, the second age band would
be 001-001, the third would be 002-003, and they would
continue to 065-999. There will also be fields attached
to the age band specifying gender and dollar amounts. I
don't know if I can do this without formatting the field
as text. I will be querying this field by asking for
information on people with different ages.
Any help will be greatly appreciated.

You can do this with a "non equi join". Create a table with three
fields: Low (Integer), High (Integer), and Band (Text). Fill it with
rows like:

0 0 Newborn
1 1 Infant
2 3 Baby
3 5 Toddler
....
65 999 Esteemed Elder

You can then create a Query joining this table to a query wherein you
calculate the age in years; you'll need to go into SQL view and edit
the Join clause to something like

INNER JOIN Bands ON Bands.Low <= query.Age AND Bands.High >= query.Age