Need to figure out how to use a repetitive list as primary key

[mhockey88]

Hi, I'm new to Access (literally brand new, my only form of training has been the microsoft training videos) and I'm trying to figure out the correct way to use a list that has repeats as a primary key or figure out the proper way to go about doing something like this. Essentially I have a list of issues and the corresponding actions being taken to deal with said issues, and the ultimate goal is going to be to create a form/report that shows the Issue with the Actions underneath. I have an excel sheet that looks like this:
Issue Action


Issue 1 Action 1
Issue 1 Action 2
Issue 1 Action 3
Issue 2 Action 1
Issue 2 Action 2
etc.

The problem I'm having is that I obviously want Actions 1, 2 and 3 to be linked to Issue 1, but I can't use the Issue 1 field as the primary key since the Issue # repeats for each line that has an action for that issue. I'm sure this has probably been answered elsewhere, but I wasn't even really sure what to search so if someone could point me to the appropriate thread or answer this question simply the help would be very much appreciated!

[John_G]

Hi -

Your problem is one of "normalization". (There is a wealth of information on normalizing database data; just do an internet search)

In you case, you need two tables. The first will will describe Issues, one record for each, and can have Issue number as its PK.

The second table is for Actions, and it is the one you show above actually. The difference is that the only data the Actions table will have relating to Issues is the Issue number Issue number; all the other fields will relate to that specific action.

The primary key for the Actions table is a compound key consisting of the Issue Number AND the Action Number. The DB purists don't like compound primary keys (though there is nothing wrong with them IMO), so if you want to use an Autonumber field as the PK in Actions you can do that, too.

But the main thing you need is the two tables.

John

[rpeare]

As an addendum to John_G's post I would suggest a third table, a junction table.

Assuming you have a 'standard' list of issues and actions you could have a third table similar to this:

tblIssues
Issue_ID  Issue_Text ---> other issue specific fields
1         Issue 1
2         Issue 2
3         Issue 3

tblActions
Action_ID  Action_Text ---> other action specific fields
1          Action 1
2          Action 2
3          Action 3

tblJobs
Job_ID  JobDate ---> other job related fields
1       1/1/2014
2       1/7/2014

tblIssueActions
IA_ID  Job_ID  Issue_ID  Action_I
1      1       1         2
2      1       1         3 
3      1       2         1
4      1       2         2

assuming that you don't follow every action with every issue, in other words you want to pick which actions you are going to perform for any given issue that arises.

[mhockey88]

Thanks so much, I think you've definitely got me on the right track here!

[orange]

hockey,

I agree with John and rpeare.What they have identified will address your current problem. However, seeing as you're new to ACCESS and fresh from some training, my guess is you're full of Access terms and solutions. Don't take this the wrong way, but you need to focus on your business and the design of your database - Not Access.

What are you really trying to do in plain English?
Quite often with Problem/Solution type databases,
1 Problem may be associated with 1 or many Solutions.
1 Solution may relate to 1 or Many Problems.

As was mentioned, you will use a junction/linking table to resolve a potential Many to Many Relationship.

Also, a Compound primary key is possible. I prefer a single field PK (autonumber), and I would suggest a composite, unique index on IssueID and ActionID to prevent duplicates.

Here's a link to free Junction table video
Good luck.

[mhockey88]

So based on your guy's advice this is what I have so far: The ultimate goal is create a report that looks something like this excel mockup I made: Do I appear to be on the right track? Thanks so much, your guy's help is saving me countless hours on Youtube!