Using a fieldname as parameter value

[Guest]

I have a problem similar to the one Debbie posted.
My database has 23 fieldnames, ie., 20212 20217 etc.
I am trying to avoid having to copy and modify 23 queries, as the contents
of each field is either a Y or a null value.

I want to be able to specify the field and have all records returned that
have a Y in the field. In a database class, the students were able to
populate a list box with the names of queries by specifying a value for the
query objects in their macro code. I was wondering if there is a way to do
the same thing for field names?

 

[Jeff Boyce]

Having fieldnames like "20212", "20217", etc. sounds a lot like repeating
fields, in which you've embedded data in the fieldname.

This happens a lot, in spreadsheets.

If you provide a bit more information about the kind of data you are storing
in these fields, the 'group's readers may be able to offer alternate
approaches.

 

[Guest]

The data is stored in an Access 2003 database in Access 2000 format. The data
in each field is a "text" value, either a "Y" or a null. The "Y" value
indicates which students are enrolled in each class, which is represented by
the 5 character fieldname "20212". The original data, in an Oracle database,
is gathered by a series of Access macros. I then run several queries in order
to get the fields in a particular order and copied into several tables. The
rearranged data is then exported to the actual database, and it is in this
final database that all the work is actually done.

 

[Jeff Boyce]

I believe, based on your description, that your table structure would
benefit from further normalization. I still have the impression that you
are describing what would be done if you had to work in a spreadsheet.
Using field names to indicate classes, or months, or ... is not
well-normalized, and will give both you and Access some serious headaches.

An alternate table structure for showing which students are enrolled in
which classes might be:

tblEnrollment
ClassID (this could be your class "number", or an ID from another
table listing classes)
StudentID (this is an ID from a table listing students)
EnrollmentDate

This design means you'd have one row per student enrolled in a specific
class.

To find all the students enrolled in a specific class (say "20212") you'd
create a simple query that returns all StudentIDs where ClassID corresponded
to the ID for class = 20212.

Good luck
Jeff Boyce
<Office/Access MVP>

 

[Guest]

A student can be enrolled in more than one class, which would mean under your
schema, multiple records for a student taking more than one class. The
approach taken by the Oracle (which is where all the data originally resides)
guru provides one record per student. I have a "hard-coded" query for each
class. I was looking for a "one query fits all" approach. Thank you for your
assistance in this matter.