Linking Table Fields???

[Guest]

As always, much thanks in advance!!!

I have a table that lists payments from clients. One column of that table
lists visits numbers and diagnosis that are connected to that clients. For
example, one client (#12345) might have two visits (1 and 2) and have two
different diagnosis for each visit (Hypertension and Diabetes). Remember,
this is a payments table, so most of the data comes from other tables
(clientID table, diagnosis lookup table, visits table). Here is the
question: the Diagnosis table has three columns (pkDiagNum, Diagnosis,
Diagnosis Code). I need to allow my client (my wife, so this is important
) to be able to choose a Diagnosis and have the appropriate Diagnosis Code
which is connected to that Diagnosis appear in another column in the table.
I suppose I could do this in the Visits table, but I still cannot figure out
how to do it. I have Diagnosis as a drop down (lookup) column, and this
works well. The next column needs to connect that Diagnosis to the connected
Diagnosis Code.

Would a query be appropriate here? I was thinking so, but I cannot figure
out what to do with that query. Do I have it query BOTH Diagnosis and
Diagnosis Code? Then have both Payment Table Columns have this query as
their lookup source? Does the query "fire" for each row of a table
separately-in that, if one selects "Hypertension" in the first field, would
the second field "know" that the first field was "Hypertension"?

Any help would be deeply appreciated.

Dave

 

[Jeff Boyce]

Dave

I'm having trouble visualizing the data structure. How you extract
information will depend on how you have it structured.

Your description seems to imply that you can have one record with more than
one diagnosis and more than one visit. If you have a field for every
possible visit and diagnosis, you have ... a spreadsheet! In a relational
database, you'd have one-to-many relationships between tables.

If you have a DiagnosisID (?pkDianNum) that you've recorded in your Payments
table, use a query to "get" the Diagnosis Code. Join the Diagnosis table to
the Payment table on the key field.

Your description may suggest that you're using a "lookup" data type field in
a table. If so, this may be adding to your confusion. Check the
tablesdbdesign newsgroups and mvps.org/access for some very good reasons NOT
to do this. Lookup tables, good ... lookup fields, bad <g>

Good luck!

Jeff Boyce
Microsoft Office/Access MVP