One Excel Spreadsheet to Multiple Access Tables

[Frustrated888]

Dear All,

Please be kind to a newbie.

I am importing an Excel spreadsheet into Access and am having trouble thinking outside the box.

My spreadsheet currently has multiple entries for 160 clients. These include referral information and date, screener results, assessment status, assessed barriers, various types of client contacts during a four-month period, treatment status, discharge status, and reason for discharge. Currently there are about 125 fields in all.

I can easily divide the fields into logical tables but the only primary key that makes sense to me for each table is a combination of the client's social security number and date of screener.

I currently have one "main table" that has the client's name, social security number and screening data along with some other information related to the referral. I have all other tables linked to this main table and all have referential integrity enforced and allow for cascading updates.

My problem concerns adding new clients to the main table. I need to have new records added to all other tables so that I don't need to input the social security number into each table. My current solution doesn't produce this, I only appear capable of automatically adding records to a second table if that table is the only table linked to my main table.

Can someone please point me in the right direction?

Thanks in advance for any help.

Frustrated888

 

[Darrylles]

Hi,

Sounds like you have everything set up ok.
Your main table has a primary key of CSS# & DOS.

You are having problems due to keys being matched against this multiple field key - this is where 'natural' pk's become a problem.

My personal advice (and you may be inundated with alternative advice - because some people are adamant with sticking with past 'Codd's law'), is to add a new field in your 'MAIN' table called perhaps Main_pk of type autonumber.

Add a field to each related record of type 'long' called 'MAIN_FK' - and create a relationship on those fields.
Autonumbers are created automatically, the 'MAIN_FK' will have to be populated using your logic.

Remove your old relationship.

From then on - you work on this realtionship....

MAIN_PK 1 <------->> MAIN_FK

Regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk