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!