Results 1 to 6 of 6
  1. #1
    skourta is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    3

    Auto fill one field based on another field in the same table

    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!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    If EH II always means Pre-Mycenaean and Bronze Age than you do not need the other fields. Those values simply need to be in the EH II Lookup table as additional fields.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    skourta is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    3
    I see what you mean, thanks very much for the reply. The thing about this option that worried me was that there are more than a hundred chronological values in the first field. So if I were to query the table containing the artifact description for all artifacts dated to Bronze Age, for example, I would have to enter the names of every single value from my first field that fits under "Bronze Age", right? If I'm getting this wrong or missing an easier alternative please let me know. These types of queries will be used the most so I'm trying to eliminate as much time as I can. Thanks again!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Nope! Using a Join in a Query, all of the fields from both tables are available to examine. It would be as if they were all in the same table. MAGIC! That is what an RDBMS is all about. The real power is in the Relationships and Joins.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    skourta is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    3
    Thank you so much! It all makes sense now

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Are you ready to use the Thread Tools and mark this thread as Solved yet?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto fill a field from multiple lookup
    By shahemaan in forum Forms
    Replies: 4
    Last Post: 06-13-2014, 04:00 PM
  2. Auto fill field upon New Record selected
    By NOTLguy in forum Programming
    Replies: 27
    Last Post: 12-05-2010, 05:12 PM
  3. Selecting a corresponding table field based on text field.
    By michaeljohnh in forum Programming
    Replies: 5
    Last Post: 10-08-2010, 10:33 AM
  4. Replies: 2
    Last Post: 09-20-2010, 09:02 PM
  5. Auto-fill field not saving to table
    By aaid in forum Forms
    Replies: 1
    Last Post: 12-18-2009, 05:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums