Relationships with multiple-index primary key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm not sure if this is even the correct approach, so a brief explanation:

I receive a 1900 row excel spreadsheet with 32 columns containing client
information weekly and I dump that into Access (tblClients). Unfortunately,
some of the data in the spreadsheet sporadically changes from week to week
(like stage level, comments, etc.), so I need to import it every week without
overwriting the previous import to maintain some sort of history. Therefore
the table has lots of duplicate rows (I know, I know it's really poor db
design, but this is what I'm stuck with for now).

Here's the rub: each client is assigned one particular representative that
isn't contained in the original imported spreadsheet and I would rather not
have to go into the table each time to enter the names of those reps to each
client assigned for the spreadsheet I just imported into the table (each
Client has only one rep, but one rep can have multiple clients). I tried
creating another table with a one-to-many, so that when I pull client A, I
see that rep 1 is assigned, but I'm not sure how to relate the two tables,
since a client would have more than 1 primary key number (yes, yes, chastise
me for total non-normalization; I deserve it). I tried messing around with
multi-index primary key (like Client Name, Client Address, Client Deal as the
primary key), but was unsuccessful in getting it to work properly (I put each
field of the multi-index primary key of tblClients into tblReps).

The only reason I don't just leave it in excel and tack the new info to the
bottom is that my supervisor sometimes just wants to see the most current
info and other times needs to see the last few updates at once. Plus Access
forms give you a little more control over how data shows up than using
filters in excel.

Is there anything that I might be able to use as a work-around besides
linking to the excel files as opposed to importing them? Thanks in advance
for your time and any help you might be able to lend.
 
Rich,
You could build a junction table between tblClients and tblReps. This table
would contain only two field ClientID and RepID (or whatever the primary key
is for each table). It would link between the other two table in your
relationships. This would make for a many-to-many relationship between
clients and reps (which is not what you need), but you wouldn't have to
update the tblClient records each time you import them.

hth,
James Deckert
 
Back
Top