Select records

[Ricoy_Chicago]

I work for a small business school. I have set up the
a "back-end" database (Masters.mdb) that can be accessed
by all (7) admissions Reps. The database contains about
1600 records (Contacts interested in attending the
school). However, out of those 1600 records only about 100
records are actually needed because they represent the
students who register to start in the oncoming quarter.

I have linked the table "Contacts" (in the Masters.mdb)to
the "Enrolled.mdb" (front-end) but it links all 1600
records. I have a "yes/no" field that allows me to query
the 100 records needed. i know I cannot link to a query.

The problem is once the "contact" is enrolled additional
fields are needed, fields that are nonexisting on
the "Contacts" table. If add the fields to the "Contacts"
table it is going to have way too many fields (so far, it
already has 55)

Is there anything I can do?

Thank yo for your help.

 

[Dirk Goldgar]

I work for a small business school. I have set up the
a "back-end" database (Masters.mdb) that can be accessed
by all (7) admissions Reps. The database contains about
1600 records (Contacts interested in attending the
school). However, out of those 1600 records only about 100
records are actually needed because they represent the
students who register to start in the oncoming quarter.

I have linked the table "Contacts" (in the Masters.mdb)to
the "Enrolled.mdb" (front-end) but it links all 1600
records. I have a "yes/no" field that allows me to query
the 100 records needed. i know I cannot link to a query.

What makes you say that? You can certainly base a form on a query, and
a query can certainly join tables to other queries and pull fields from
both tables and queries. If you mean you can't create a linked table in
your front-end that is linked to a query in the back-end, that is true,
but you don't really need to. You can just create a linked table in the
front-end that is linked to the back-end Contacts table, create a query
in the front-end that selects only the enrolled records, and base all
forms and other operations on that query.

The problem is once the "contact" is enrolled additional
fields are needed, fields that are nonexisting on
the "Contacts" table. If add the fields to the "Contacts"
table it is going to have way too many fields (so far, it
already has 55)

Is there anything I can do?

It sounds like you may -- just may -- need an Enrollments table that is
related one-to-one with the Contacts table. If you had that, you
wouldn't need the check-box field, because the presence of a record in
this table would indicate that the Contact is enrolled. Now, it isn't
clear to me whether a Contact might have multiple enrollments, in which
case the Enrollments table would actually be related many-to-one with
contacts, a more normal arrangement. it could work either way.

 

[Guest]

-----Original Message-----


What makes you say that? You can certainly base a form on a query, and
a query can certainly join tables to other queries and pull fields from
both tables and queries. If you mean you can't create a linked table in
your front-end that is linked to a query in the back-end, that is true,
but you don't really need to. You can just create a linked table in the
front-end that is linked to the back-end Contacts table, create a query
in the front-end that selects only the enrolled records, and base all
forms and other operations on that query.


It sounds like you may -- just may -- need an Enrollments table that is
related one-to-one with the Contacts table. If you had that, you
wouldn't need the check-box field, because the presence of a record in
this table would indicate that the Contact is enrolled. Now, it isn't
clear to me whether a Contact might have multiple enrollments, in which
case the Enrollments table would actually be related many- to-one with
contacts, a more normal arrangement. it could work either way.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Dirk,
I have done the work on the query already and it works.
Yes, a contact may have several enrollments, so it has to
be many-to-one relationship from the "Enrollments" table
to the "Contacts" table. Now, how do I create this
enrollment table if I already have 1600 records in
the "Contacts" table?
Once I have the "Enrollments" table I can use that as my
linked table and I can get rid of my query in the front-
end.
Thank you for all your help.

 

[Dirk Goldgar]

Dirk,
I have done the work on the query already and it works.
Yes, a contact may have several enrollments, so it has to
be many-to-one relationship from the "Enrollments" table
to the "Contacts" table. Now, how do I create this
enrollment table if I already have 1600 records in
the "Contacts" table?
Once I have the "Enrollments" table I can use that as my
linked table and I can get rid of my query in the front-
end.
Thank you for all your help.

You say you have a check-box field in the Contacts table that you can
currently use to identify those who are enrolled. So you can create a
SELECT query in the query designer that selects these records, and just
those fields from them that ought to go into an Enrollments table
(including the ContactID field or whatever the primary key of Contacts
is). Then change that into a make-table query, with "Enrollments" as
the name of the new table to create, and run it to create and load the
table.

Once you've created the Enrollments table this way, you'll want to open
it in design view and set indexes on the appropriate fields, either
choosing some combination of existing fields as the primary key or
creating a new field for that purpose. Possibly you'll have a primary
key for Enrollments consisting of ContactID, SchoolYear, and Quarter --
I don't know enough to say.

You've been talking about front-end and back-end, and I don't know
whether you're working in the front-end or the back-end. In order to
enforce referential integrity between Contacts and Enrollments, you'll
need to have the Enrollments table in the back-end; that is, in the
same database file with Contacts.

 

[Guest]

-----Original Message-----


You say you have a check-box field in the Contacts table that you can
currently use to identify those who are enrolled. So you can create a
SELECT query in the query designer that selects these records, and just
those fields from them that ought to go into an Enrollments table
(including the ContactID field or whatever the primary key of Contacts
is). Then change that into a make-table query, with "Enrollments" as
the name of the new table to create, and run it to create and load the
table.

Once you've created the Enrollments table this way, you'll want to open
it in design view and set indexes on the appropriate fields, either
choosing some combination of existing fields as the primary key or
creating a new field for that purpose. Possibly you'll have a primary
key for Enrollments consisting of ContactID, SchoolYear, and Quarter --
I don't know enough to say.

You've been talking about front-end and back-end, and I don't know
whether you're working in the front-end or the back-end. In order to
enforce referential integrity between Contacts and Enrollments, you'll
need to have the Enrollments table in the back-end; that is, in the
same database file with Contacts.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

The back-end file is the one containing the "contacts"
table and that is where i will put the "Enrollment" table.
The front-end files (those used by the admissions Reps)
contain queries, forms, reports, etc.

Thank you for your input, i will try the select query to
build the "enrollments" table. I am assuming that this new
table will be updated as new records are added
to "Contacts".

Once again, tahnk you very much for your help.

 

[Dirk Goldgar]

The back-end file is the one containing the "contacts"
table and that is where i will put the "Enrollment" table.
The front-end files (those used by the admissions Reps)
contain queries, forms, reports, etc.
Good.

Thank you for your input, i will try the select query to
build the "enrollments" table. I am assuming that this new
table will be updated as new records are added
to "Contacts".

I'm not sure whether this statement represents a misunderstanding or
not. Records won't automatically appear in Enrollments as records are
added to Contacts. There won't be a record in Enrollments for every
record in Contacts; rather, there will be an Enrollments record only
for those Contacts who enroll. After you initially load the table with
your make-table query, you'll need some mechanism to enroll contacts.
I'd recommend using a subform on the Contacts form. The subform would
be based on the Enrollments table, and would be linked to the main form
by ContactID.

 

[Guest]

-----Original Message-----


I'm not sure whether this statement represents a misunderstanding or
not. Records won't automatically appear in Enrollments as records are
added to Contacts. There won't be a record in Enrollments for every
record in Contacts; rather, there will be an Enrollments record only
for those Contacts who enroll. After you initially load the table with
your make-table query, you'll need some mechanism to enroll contacts.
I'd recommend using a subform on the Contacts form. The subform would
be based on the Enrollments table, and would be linked to the main form
by ContactID.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

I guess I have to create the query first, understand how
it works and then try the subform. Thanx.
however, it's TGIF and quitting time is around the corner.
See ya on Monday!