Need help with linked SQL table and Access form

[Jacqueline]

I hope I can explain the problem well enough for you to
understand...

I have an Access 2000 database with all the forms and
queries in it...and have linked to SQL Server tables for
the data.
There is a User_ID field in every table as the unique key.

WEB side: There is a medical school online application
form
SQL Server houses the tables and data from the web
application.
Access database is for Med School Administrators use.

Problem: A student may submit the names of more than 1
Educational Institute that they attended...in the web app
this is set up dynamically to show 1 or to show as many
school names as they list.

In the Access database I have a form (which looks like
the page on the web app) but cannot dynamically list all
the schools. (using subforms with a new record for each
defeats the purpose of this form)

The EdInstitute table has 1 field for listing
EdInstitutes (there aren't individual fields like
EdInst1...EdInst2...etc)...so 1 User_ID could be there
several times.

The Access Display form that shows what has been
submitted by a student for acceptance looks like this...

1. Fee payment label...a checkbox...date paid textbox

2. Reference names...need to be able to list 2 reference
names...checkbox and date submitted textbox

3. Transcripts from EdInst...need to be able to list all
that the student entered on the web form here...plus the
dates submitted.

4. MCAT Scores...same thing...need to be able to show all
MCAT written dates dynamically.

Advice greatly appreciated!

 

[Albert D. Kallal]

Problem: A student may submit the names of more than 1
Educational Institute that they attended...in the web app
this is set up dynamically to show 1 or to show as many
school names as they list.

In the Access database I have a form (which looks like
the page on the web app) but cannot dynamically list all
the schools. (using subforms with a new record for each
defeats the purpose of this form)

Using a subform with a new record for each Institute they attended makes
perfect sense. I mean using a sub-form is the standard and recommend way to
list the "many" side of a relation. Hence, for the "many" institutes, why
would you not use a sub-form? One has to assume the actual database contains
a new record for each Institute...right? (I mean, how can one student have
"many" institutes in one record? Not very possible is it?).

3. Transcripts from EdInst...need to be able to list all
that the student entered on the web form here...plus the
dates submitted.

Yes, and how are those many Institutes listed in the database? Can we not
assume one record for each Institute the ONE student enters? (again, we are
taking a one to many relationship here, and thus a sub-form seems perfect
for this. You would wind up with a nice grid/table view of all the
Institutes for the one student...is not this what we want?).

4. MCAT Scores...same thing...need to be able to show all
MCAT written dates dynamically.

Again, without know the actual table designs is it hard to suggest. However,
assuming the people who set this up knew what they were doing, then again,
another sub form to display the "many" values makes perfect sense again.