Table Design...Did I make a mistake?

[Colby]

I have build a database that contains 3 main tables. Though each table has
data that refers to one element. They are for physician information and are
set up as follows:
The first table is contact information
The second is volume type info
and the third is outcome type info.

All fields are linked to one physician, however, each physician has several
quarters of data. I am running into a problem that in my form, when i add a
new record for a new quarter of values it is overwriting my previous data.

Should i have set my tables up differently?

 

[Jeff Boyce]

How are the tables related to each other? Are you saying that each table
holds a "PhysicianID"?

So how many Outcome records can you have for each physician (depends on your
primary key)? If you can only have one, there's the reason new data is
overwriting the old data ... you can only have one record per physician?!

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Colby]

Yes, each table holds a physicianID. Each physician will have 50 or so
idicators to track each quarter. Does this help?

 

[Jeff Boyce]

Colby

If you are saying that each physician can only have one record in the
Outcomes table, that is your problem.

If the relationship between physicians and outcomes is "one-to-many", you
need a table in which you can store one row/record for EACH
physician/outcome combination, not one for ALL (that would be ... a
spreadsheet!).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP