Queries

[Duane]

Typically you use a variable for criteria in a query, but can you use a
variable for a field?

Lets say there are multiple fields in my table, i.e. field1, field2, field3
and so on. At design time I do not know which field to query because the
criteria changes every day. One day I might need to query field1, the next
day I might need to query field46. Is it possible to use a variable as a
field in the SELECT statement?

SELECT tblMyTable.Group, MyVariable, tblMyTable.StartDate WHERE
tblMyTable.Group = -1;

Thanks in advance.

 

[Allen Browne]

No. You cannot use a parameter in a query to determine which field to apply
criteria to.

From the way you describe your query, it sounds like you have many fields
with similiar data. This kind of thing is very common in spreadsheets, but
it is not the way you create a relational database. You need a related table
where there can be many *records* for these things, instead of many fields
in this table.

This concept is absolutely foundational to relational database design, and
it is one of the most common mistake people make when starting out with
Access.

It will take some reading to understand how to do this, but it's worth the
effort. Here's a starting point:
http://allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_03.pdf
and lots of other information about this crucial topic called
"normalization":
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Duane]

Thank you very much for your response Mr. Browne. I read your stuff often
and am a frequent visitor to your website. I am new to Access, but I am
somewhat familiar with database normalization. I emphasize the somewhat.

Maybe you can give me a little further direction. I made a post a few days
ago concerning a table that has several fields in it. Group, Description,
ScheduleType, and MOD0 through MOD195. I didn't not design this database,
although I have been given it to look at it and possibly convert it from
Paradox to Access. I am new but love the challenge.

The table in question is used as a lookup to know which Work Group is off on
a particular day (RDO). The MOD0 - MOD196 fields are all logical fields.
True fields representing Off Days (RDO's). There are only 7 rows in the
table, as there are only 7 RDO groups. The work schedule is a rotating
schedule. One week you would have a Monday/Tuesday off. The next week you
would have Tuesday/Wednesday off, and so on. I can only assume there are
196 MOD fields because there are 7 RDO groups multiplied by 2 groups off
each day, totaling 14 X 14 for a total of 196. One complete cycle.

Example: Row 1 represents RDO Group1. Following the rotating schedule for
Group1, each MOD field which is TRUE represents an RDO.
(MOD0,MOD1,MOD8,MOD9,MOD16,MOD17, and so on until MOD195.).

The text box on the main form contains the date (Date()+1. It's a projected
date which is used to generate reports so the supervisor knows who to call
in for overtime on that day, based on the RDO group that is off that day.
Not needing to know which group is off, the date is converted to a
LngInteger, i.e. 02/21/2008 = 39499. Then using the MOD 196 Function the
remainder is 103. Using the table above I know that group 1/2 are both off
on that day. If the date is 02/22/2008, then I know group 2/3 is off on
that day. There are no tables hard coded with dates and they don't have to
figure out which group is off.

I know which group is off on MOD102 because in the table Group1, field
MOD102 is TRUE, but how can I use that information without having to view
the table to know.

I sure appreciate your time. Thanks in advance.

 

[Allen Browne]

So you work on a 14 day cycle, and staff are rostered to one of 7 groups.

That suggests a table with 2 fields:
CycleNum Number value from 1 to 14 for the day of the cycle
GroupOff Number value from 1 to 7, for the group off on this day
The combination of the 2 fields together would be primary key (so a group
can't be entered twice for the same cycle number.)

Now you can populate this table from your existing one by executing 196
append queries, selecting one column at a time, and only the fields where
the column is True.

Now you have a normalized table, with just two query.