You have one "*" of a mess here.
Working on the concept that you added the text "Water Lines" (otherwise, why not use that?) and what you actually have is the numeric values to work with then looking at your example data, it appears that:
"10301 Water lines" has the most in common with the remaining members.
IFF this holds true in that there are simular commonalities then you have a hope of an easy solution in that you can use the replace function to remove the dashes and then use the right function to return the last five digits.
However, life is rarely that nice.
One way I might do this is to have a table with the unique names like "water," "pen," etc.. then a table with the outside codes, who from, the unique name, the location code. Then either use a linking table, or even in the second table have the relationship to the first.
On first blush it would start out along these lines:
tbl_db_locationcode:
[db_locationcode_pk] autonumber; primary key.
[db_locationcode_name] text(50); required, no duplicate
(this will become your databases internal standardized location code)
tbl_os_source:
[os_source_pk] autonumber; primary key.
[os_source_name] text(50); required, no duplicate
(Use this table to help standardize the source names such as NCCI and Payroll etc...)
tbl_os_locationcode:
[os_locationcode_pk] autonumber; primary key.
[os_locationcode_fk_os_source] 1 to many on tbl_os_source PK
[os_locationcode_fk_db_locationcode] 1 to many on tbl_os_source PK
[os_locationcode_code] text(50); required; no duplicates.
{{Should Add compound key on
[os_locationcode_fk_os_source]
[os_locationcode_code]
- needs to be unique to prevent adding the same source and location codes more that once}}
(Now this table will have one entry for each source's location code... so you get a table with the information much like you listed in your post)
tbl_metricdata:
[metricdata_pk] autonumber; primary key.
[metricdata_fk_DB_locationcode] 1 to many on tbl_db_locationcode
[metricdata_(...)]
(( or maybe
tbl_metricdata:
[metricdata_pk] autonumber; primary key.
[metricdata_fk_OS_locationcode] 1 to many on tbl_OS_locationcode
[metricdata_(...)]
))
Setting up tbl_os_locationcode:
Now in your query, build a query on tbl_os_locationcode pulling in all of the fields (do not use the star).
You will setup the lookup control for the fields [os_locationcode_fk_os_source] and [os_locationcode_fk_db_locationcode] based on their tables... this way you can use the human readable "Name" fields.
You can build a form that is bound to this query, if you use the wizard it should setup comboboxes for the two lookups if not, then you can do this by hand.
Setting up tbl_metricdata
Depending on which one you use, either query based on the db_locationcode or,
Very simular in that you would use a query, build a lookup field against your source and location fields (concatenate the source and location code) and enter your other data. Same thing within your forms.
Here's the deal, you can now pull the outside and internal location codes by source (payroll, ncci, etc...) or by outside codes, or the internal db code etc... and by relationships query the metrics table
It should be noted that this is just an initial thought without a formal analysis of the database design that I would normally do to construct something like this... basically, I've left out some of the details; however, I hope you can see what I'm doing here
(
> Database Normalization and Table Structures. )
10
