Summary:
I have a table that has a uique primary key identifying each record. I also have three more fields that can identify a record:
- Category
- CategoryNumber
- DuplicateNumber
When I add a new record and choose the Category, how can I get CategoryNumber to increment correctly based on whether it is a duplicate or not.
Note that this is one big table. This is merely an upgrade from excel to access.
tldr:
Yesterday I asked about how to deal with multiple keys in Access. May be I did not ask the right question because my problem is even more puzzling today.
Here is what I am trying to do and where I am getting stuck.
I am creating a database to keep track of my film photos taken on a variety of different films and formats with different cameras (a total of 18 different independent pieces of information for each photo) that span back almost 15 years. Years ago I kept the data in MS Excel and programed the input form to do what I am about to explain.
I wanted to keep track of the photos in two ways. The first was a master photo number that would let me know how many total photos I had in my files. It was a sequentail number that I never intended to ever delete something in that sequence. It would be a number that would uniquely identify each photo. I see this as my primary key for the whole database.
But I also wanted to file the slides, negatives, and transparencies according to general groups or categories so that I could quickly pull them if I needed to make a photo submission or I was looking for something specific. So I also added a category identifer (e.g. Landscapes had a 'LS' name attached as well or Sunsets as 'SS' etc). I also wanted to keep a running sequential number for each slide that was in each category. So e.g. one of my first photos in my data base has PhotoID = 3, its Category = LS and its CategoryID = 1. Further down I have PhotoID = 14, Category = LS and CategoryID = 2.
Now in addition to these, I further have another number that I need to keep track of and that was if I made duplicate exposures of the same scene. To work this I just added a DuplicateNumber to each photo. If it had no duplicates then DuplicatNumber was 0, if it had one dupe than DuplicateNumber was 1, 2 dupes, DuplicateNumber was 2 etc.
So I had two ways for finding any photo in my files. Either by the PhotoID which was sequential in the Database but not in my physical files, or by the combination of Category, CategoryID and DuplicateNumber, which are how I physically file the photos.
Now in Excel what I did was when I opened my form, I first had to choose what category to put the photo in via radio buttons on the form. I would then fill in all the pertinent data for the photo. When I executed the form, it would find the last number in the master sheet, increment it and add all the data to a new record. It would then activate the category sheet for the category I chose, determine the last number in that sheet, increment it and add in the new data there as well.
An important note is that DuplicateNumber is always 0 by defalut unless I made it 1, 2 etc. It was never computed, but I entered it.
When I created slide lables (the "report") I would include the main PhotoID, The CategoryName, CategoryID and DuplicateNumber in this format (1LS1.0, 14LS45.0, 15LS45.1) and in addtion the location and Date taken.
But now I don't want to use Excel anymore and have tried to migrate the database to MS Access. So I have one main table called PhotoDetails.
PhotoID is the primary key in this table. It has a PhotoCategory field that is a number that has a relation to another table called Category. Its Primary key is PhotoCategoryID and it is also a foreign key in PhotoDetails. PhotoCategoryID is a number that identifies each category. In the list of all my categories, LS is number 11. But here is where I get stuck.
I have CategoryNumber, which is the number identifying the photo in each category, and DuplicateNumber, identifying the number of duplicates, in the PhotoDetal table. What I can't figure out is when I add a new photo, how do I increment the CategoryNumber? The form I have right now allows me to enter in all the data, and when I click to enter the next it will autonubmer the PhotoID key, but what about CategoryNumber? I can't remember what the last number is for each Category so as for me to enter that number in myself. Unless I mimic what I did in Excel by having all the data stored twice, once in the PhotoDetails (aka Master Sheet in Excel) and again in multiple Category tables. I was hoping Access could provide me a workaround.
Currently the relationships I have are the Category table linked to PhotoDetails in a 1-M relation. I also have several other tables all linked in a 1-M relation (e.g. table Film stores the different films I have used and the 1-M relation lets me link what film to each slide, and most of the other data is stored in that way).
I have 31 different Categories. Each photo has a Category and a CategoryID number, as well as a Duplicate number. I will choose the Category on data entry and the DuplicateNumber, but I want the CategoryID number as well as PhotoID to be incremented automatically for me. What is the correct way to proceed with this where I only have to keep one PhotoDetail table that is not duplicated for each category as I did in Excel.
I really appreciate any help anyone can give me. I am at my wits end.