Multipe keys in Access?

Question

Years ago, I created a data base in MS Excel to keep track of my photographs. The way I designed it was as follows. I had a master sheet that had a primary key with a sequenctial number ID for every photo. Then I had several sheets in the workbook that contained specific photos of the same subject, e.g. landscapes (LS), seascapes (SC), sunsets (SS) etc. Each of these sheets had a primary key as well that sequenced the photo ID in that subject. I programmed a form to open when I needed to enter in a new photo. When the form opened it would determine the last record in the master sheet and increment the primary key to the next number in the sequence. Then I would pick what sheet (phto type - LS, SC, SS etc) and it would determine the last record number on that particular sheet in increment to the next number and enter that into the form as well. Then I could enter in the specifics about that photo like exposure settings, location, date taken, key words, etc. It would then write all this information in both the master sheet and the specific subject sheet. Not the most elegant, but it worked for me.

I want to duplicate this in MS Access but I am having trouble in figuring out how to increment on both the master photo number and the subject photo number at the same time when I open the master form.

In MS Access I created a master table with all the fields that existed in the Master sheet in the Excel workbook. The only difference between the master and subject sheets is that the master has two additional columns, one for the Primary key (the master photo number) and one for the subject of the particular photo. The next column contains the specific subject photo number. So for example, two sequential records in the master sheet might be as follows slideID = 1245 subject = LS subjectID = 245, then slideID = 1246 subject = SS subjectID = 127. In the subject sheets subjectID is the only unique identifer. All other pertinent information about the photo is duplicated in both the master and subject sheets.

So in MS Access, I have this one Master table. I have a form that I use to enter in the photo information. How can I get it to increment both the Master photo ID number, currently the Primary key of the table and also to increment the subject ID number once I choose what the subject is from the form?

I hope this is clear.

Answer 1

Have you looked into the different types of relationships you can form? For example, a one to many relationship. What relationships do you have set up currently in Access?

Answer 2

As you are the only person entering data, it is safe to use DMax, because no-one else can grab an identical number. You can even use DMax in the Default Value of the textboxes, which means that the number will be suggested every time you create a new record, if the record is created the suggested number becomes an actual number.

=DMax("[slideID]","TableNameHere")+1

If it is your intention to learn database design, there is more that needs to be done, because your table needs to be normalized, you may wish to read Fundamentals of Relational Database Design, Paul Litwin, 2003

Answer 3

Your master photo table should have all the information you need for any given photo. If you wanted to just see 'Landscapes', just create a query that filters the master table by subject. This eliminates the duplicate entry. Any fields/data about the photo in the subject tables, could be moved to the master table.

If you want your photos to have more than one subject, you need a Photo_Subject table. The only fields it needs are: Photo_Subject_ID (Auto number), PhotoID (from master table), SubjectID (or just put the name of subject). This way, you can enter the same photo as many times as you want and assiciate it with as many subjects as you want. You could add an index on the PhotoID & SubjectID that avoids duplicate entry. (Yes, I suggest avoiding a Compound Primary Key).