Results 1 to 11 of 11
  1. #1
    Phil-AND is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    10

    Automatic Reference ID Numbers in a Query

    Hello!

    I am fairly new to access and sql as a whole. I have been messing with it for a while writing up Queries and trying to figure out how to create a Query based on two tables that will compare the information, and spit out an automatically generated reference number in a generated query or table. Here is my dilemma in an example:

    I have a Master Table that has the columns "State", "City" and "Highway".
    I then have 4 other tables that are titled based on issues with a highway at the specific City/State. Those tables are: "Pavement", "Signs", "Paint", "Drainage". Below are examples of what the Tables look like:


    Master Table:
    ___STATE CITY _ HIGHWAYS___
    MISSOURI ST. LOUIS I-70


    MISSOURI ST. LOUIS I-64
    MISSOURI ST. LOUIS I-40

    Pavement Table:

    HIGHWAYS ISSUE RECOMMENDED ACTION RESOLVED

    I-70 POT-HOLE HIRE CREW TO PATCH NO
    I-70 CRACK HIRE CREW TO REPAIR YES


    SIGNS Table:

    HIGHWAYS ISSUE RECOMMENDED ACTION RESOLVED

    I-70 MISSING SIGN PURCHASE NEW SIGN YES
    I-64 INCORRECT DIRECTION PURCHASE NEW SIGN YES


    And the Paint and Drainage Tables have the same design. What I want in my Query is, If an issue exists in the pavement table, I want the query to populate the columns "STATE", "CITY", HIGHWAY", and "REFERENCE ID". The Reference ID Column would be auto created only if an issue exists in one of the issue tables based on the master table. The reference ID would not just be a number but an example of a reference ID that refers to the pavement is "PAV_I-70_1". So the first three digits in the reference ID refer to the table, then a reference to the highway, and if there are multiple instances, a number following that auto increments.

    If this didn't make sense please ask me to clarify something but any input on how to get a query to auto generate this reference number would greatly be appreciated!

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is not a normalized structure. Why do you have 4 tables? 1 tables with an additional field for "Pavement", "Signs", "Paint", "Drainage" would be easier to manage.

    Is the Reference ID stored in a table? If so, which one?

  3. #3
    Phil-AND is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    10
    Hey ssanfu thanks for your reply!

    Each of the 4 tables is kind of like a detailed account of each type of issue with different columns than each other and a single key column linking them to the Master Table. The Reference ID is complicated.... I don't even know if what I want done is possible, but I want Access to create a reference ID for me. Basically it would be, If TableB.ColumnB = TableA.ColumnA, Then in the query, Create a column for the Reference ID based on the table it came from, the Highway it applies to, and an incremental number for the instances where that issue persists more than once.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Your example shows same fields for the two issue tables. There are other fields not showing in post?

    The ReferenceID calculation in query makes no sense to me.

    Can't a highway have related record(s) in each of the 4 tables?

    Need to save PK of master table into the issue tables. Use form/subforms arrangement for data entry.

    Main form bound to master table and 4 subforms bound to the issue tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Phil-AND is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    10
    Sorry I did not want to include all columns in the issues table because there are a lot of them and they arent all pertinent in creating a relationship. Yeah I am sorry its hard for me to explain what we are looking for with the ReferenceID because we are moving from Excel to Access to try to better handle our data. We basically create a report for someone to look at this data we found. The Reference ID is just there to create the basic report so it doesnt look too cluttered, then if they want to look into the specific Issue denoted by the reference ID, they will take the reference ID we created and look through the list to find it on the more detailed table.

    I like the forms idea for data entry Thank you.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The forms can also be interface for users to just view records.

    A report object would also use the same arrangement (report/subreports).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Phil-AND is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    10
    Awesome. Yeah I think we will do that, but do you know of any way to basically do an autonumber for the referenceID (It can be in a query, table or wherever) Where the format would be "Table1 Title"_"Table1.Column2"_"Sequential Number"?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Code can accomplish anything. Generating custom unique identifier is a common topic in forum. Do search. I just do not understand the need in this situation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Phil-AND is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    10
    Awesome I will do that. I have a difficult time finding the words to use in my search to get what I am looking for. Thank You.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Generate custom unique identifier

    as key words in the forum search and restrict to my username will return 10 threads

    Less key words and no username will return even more.

    https://www.accessforums.net/forms/a...ing-23329.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Phil-AND is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    10
    Perfect! Thank you for your help.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-20-2014, 04:15 PM
  2. Automatic Query Updates
    By zmbaker in forum Access
    Replies: 1
    Last Post: 06-02-2014, 10:03 AM
  3. problem: automatic send email on query
    By ihere in forum Import/Export Data
    Replies: 12
    Last Post: 05-18-2014, 03:38 PM
  4. generating automatic numbers in a database
    By bonbon68 in forum Access
    Replies: 3
    Last Post: 03-16-2011, 10:09 AM
  5. Automatic Serial Numbers
    By Mitch87 in forum Access
    Replies: 9
    Last Post: 02-18-2010, 12:57 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