Hi,
I am building a database to store records that were retrieved during an archaeological survey. One table contains information related to individual sherds of pottery, including their date. The table as it is now has the following fields:
- ID (autonumber)
- Unit_ID (this refers to a specific collection unit number from which the sherd came; it is also the primary key).
- Artifact_Category (eg. Ceramic, glass, metal…etc)
- Chronology (this is selected by a combo box, the source of which is another table that lists different chronological values; this table is named DD_Chronology)
- 10 more fields, irrelevant here
What I would like to do is add four more fields whose value will depend on what is entered in the “Chronology” column. For esoteric reasons that I will not burden you with, we archaeologists have annoyingly come up with different ways of categorizing time periods and, depending on what questions are being asked, it becomes more or less useful to use certain categories over others. This is why I need the additional fields, as it will save a lot of time in the long run. I could enter these fields manually, but having them fill in automatically would be a huge time save given I have a couple thousand artifacts to enter. I’m having a hard time deciding how to go about this. Here is a simplified example of these chronological categories and values:
If a sherd is EH II it also = Pre-Mycenaean and = Bronze Age
if it's MH II = Pre-Mycenaean = Bronze Age
if it's LH I = Mycenaean = Bronze Age
if it's PG = Post-Mycenaean = Iron Age...etc
Here "EH II/MH II/LH I/PG would each be values in the "Chronology" field, Pre-Mycenaean/Mycenaean/Post-Mycenaean would be in the "Category 1" field, and Bronze Age and Iron Age would be "Category 2"
Basically, if I enter “EH II”, I would like Categories 1 and 2 to automatically fill in. Should I create different tables for each chronological category or should I include all possible chronological values within the same table (DD_Chronology) and somehow “ask” Access to single out which values correspond to what?
Any help would be very much appreciated!