Re: Using a fieldname as parameter value
- From: "Jeff Boyce" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 31 Jan 2006 05:24:13 -0800
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>
"lmclanman" <lmclanman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:45B2F0CC-7346-4A72-9E7B-8926E398EF89@xxxxxxxxxxxxxxxx
> 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" wrote:
>
> > 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.
> >
> > --
> > Regards
> >
> > Jeff Boyce
> > <Office/Access MVP>
> >
> > "lmclanman" <lmclanman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:BB55061E-39CC-4B39-8B81-1FE95CAF92BB@xxxxxxxxxxxxxxxx
> > > 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?
> >
> >
.
- References:
- Re: Using a fieldname as parameter value
- From: Jeff Boyce
- Re: Using a fieldname as parameter value
- Prev by Date: Re: Calcuations in queries
- Next by Date: Re: Query does not show all data
- Previous by thread: Re: Using a fieldname as parameter value
- Next by thread: Complex issue with queries
- Index(es):
Relevant Pages
|
