Don't even know what to ask...

[Guest]

I am new to Access and I am not even sure how to ask this question, but here goes...I have two tables. One is a list of patient information and one is a list of class information. I tried to make a relationship between the two based on classid (linking the patient with a particular class). The problem is my user would like to type class information in the table, then in the patient table, type in the classid and have all the subsequent class information pulled over automatically. So if they entered a class id of ABC123, it would pull over instructor name, class location, and other fields related to that record in the class information table

I think I have done this with FileMaker before, but I've found that trying to transfer knowledge from FileMaker to Access hardly ever works. Can anyone please help before I pull my hair out?

Thanks!

 

[Chris Nebinger]

You dont' want to save the information in the patient
table. Just create a query between the two, and view the
data that way.

Chris Nebinger

-----Original Message-----
I am new to Access and I am not even sure how to ask this

question, but here goes...I have two tables. One is a
list of patient information and one is a list of class
information. I tried to make a relationship between the
two based on classid (linking the patient with a
particular class). The problem is my user would like to
type class information in the table, then in the patient
table, type in the classid and have all the subsequent
class information pulled over automatically. So if they
entered a class id of ABC123, it would pull over
instructor name, class location, and other fields related
to that record in the class information table.

I think I have done this with FileMaker before, but I've

found that trying to transfer knowledge from FileMaker to
Access hardly ever works. Can anyone please help before I
pull my hair out?j

 

[John Vinson]

I am new to Access and I am not even sure how to ask this question, but here goes...I have two tables. One is a list of patient information and one is a list of class information. I tried to make a relationship between the two based on classid (linking the patient with a particular class). The problem is my user would like to type class information in the table, then in the patient table, type in the classid and have all the subsequent class information pulled over automatically. So if they entered a class id of ABC123, it would pull over instructor name, class location, and other fields related to that record in the class information table.

I think I have done this with FileMaker before, but I've found that trying to transfer knowledge from FileMaker to Access hardly ever works. Can anyone please help before I pull my hair out?j

Thanks!

Yep. Access is NOT FileMaker; they're both databases but that's close
to being the entire resemblance!

Your problem is a paradigm difference: in FileMaker a "table" can
contain data from other tables. In Access it cannot (or at least
should not).

The solution is to use a Query. Store Class data (and only Class data,
nothing about Patients) in the Classes table; store Patient data (and
only Patient data) in the Patients table. If a Patient can only be in
one class, there would be a ClassID field (a "foreign key" it's
called) in the Patient table.

You can then create a Query by adding both tables to the query design
grid, joining them by ClassID. This Query will let you display the
fields from both tables. You can base a Form, Report, or even another
query on this Query. Functionally it's very much like a table.