Assigning an ID based on similar field of a different table (1 Viewer)

[Stez]

Business reports would be done yearly to see how the business is doing. In the table of business reports, records are added chronologically so for every business, there would be different business reports but at different dates.

My plan at the moment is to have the business list table which contains fields such as ID, names, addresses, and contact information all have a specific ID each. Through a form with the business list, I can add a business report to the specific business without having to fill out names, addresses, and contact information.

This is all for after I figure out how to add an ID for the already-made business reports corresponding to the current business list.

 

[Mike Krailo]

This is all for after I figure out how to add an ID for the already-made business reports corresponding to the current business list.

Something is very wrong with your posted tables or views in post #3 earlier. You have what appears to be a Reports List table that shows the FK of the business (23) and then proceeds to show the business name and street address???? That is highly un normalized data. The business name and address of the business should only be in the Business List table or whatever you called that table. You do have the foreign key 23 already in there so that's good. I do not see a primary key in the Reports List table??? Are you just not showing it or is the primary key actually missing?

The Reports List table should look similar to this in design view:

Please confirm this is how you have it setup or not. Each table should have a primary key and preferably it should be named more descriptively like RepID and BusID so it is clear which key is which. Try not to use spaces or special characters in your table field names.

I created a sample for you to look at and see if it works like you want it to. This example is just to show you how to get the table structure right so that you have a simple one to many relationship represented by a main form of businesses and a subform of business reports.

Post Thought:

Maybe your data is currently not normalized and you are trying to get it into a normalized state. If that is the issue, please confirm or deny. That can be a tedious process to have to go through to fix all of your data problems. It can be done, but it's a process. I'm starting to think that this is what your struggling with. If this is the case, it would be best to upload your un normalized data or database if the tables are already imported into the database and we can make better recommendations on how to proceed. It's really hard to do without having at least a good size chunck of data to work with.

 

[The_Doc_Man]

I'm going to break from the specifics and step away to the higher altitude.

As you presented those tables, they are not normalized. This is a common failing with newer ACCESS users. It is easily correctable.

As a result of what is an ineffective design, you have non-consistent data. With proper design, YOU would enter the business data for each business, with name, address, ID as a prime key to identify the business by a unique number, and other things that directly relate ONLY to the business. Your data entry forms would make it anywhere from unlikely to impossible for incorrect data entry to occur in any of the business details. Then your Reports List table would have NOTHING specific to the business except the business ID as a foreign key (on which you then can build relationships to support JOIN queries. Once proper relationships are in place, the query builders can make use of those relationships whenever you have to use two tables together for data referencing.

You need to do a little bit of reading. I respectfully recommend that you search for topics of "database normalization." If you search this forum, since it IS a database site, you don't need to specify "database" - but if you do a general web search, you must include "database" with "normalization" because other disciplines also use "normalization" and you would get confusing results. Until you understand normalization and what it does (and WHY it does it) you will have ineffective (verging on sometimes nearly unusable) designs.

 

[GaP42]

Stez - timing is key to the process if you are trying to migrate from the un-normalised data structures to a one where the data for business name and address are in the business table only and the report table does not have these.
Take a copy of your tables into another db with the data you currently have
Modify the structure of your Report table to include a FK to Business ID - initially unpopulated.
Assess the quality of the data for business name:
- are all business names in the business table "good", does this list include all business names required.
- if so develop a series of queries to progressively update the Business NAME in Report table where it does not match a business name in the Business table - eg by removing spaces. This will be a tedious process, but you must get standardised names for the businesses in the Report table that will match to the Business names in the Business Table.
- each of these queries can be saved with a name including a number for the sequence of execution.
If your business table Business Addresses are "good" then there is no need to change (update) the addresses in the Reporet table as eventually you will be using the "good" address from the business table.

Having got this far and happy with the data cleaning step, you will then need another query to update the Business ID in the Report Tabel with the Business ID from the Business table where business name matches in each (after all your cleaning)
Clearly test it out for the results.
Report with a new copy of the tables from the production" database. Check whether there are any new businesses, any left overs you need to deal with.
Prepare a query that removes the Business name and address fields from the Report table
Prepare the forms that users will use now to access the business record and add/edit reports

Ok - are you ready:
At a prearranged time, when users do not have access to the database (take the production db offline)
Backup the production database
Execute the changes to structure - add Business ID to Report Table
Import the queries and forms
Run the update queries sequentially as prepared
Check the data - any orphan records in Report table with no matching BusinessName in the Business table
(what will you do with these?)
All OK - make changes to the structure of the Report table to remove Business Name and Address.
Check your new forms work
Remove the queries you added.
Notify users of the change - and operation

This is a simple general outline (obviously) - you will need to work out the specifics and determine the timing, planing, the user training etc.
This is a step not included above: after you develop this change and happy with it, get some users to check the quality of the data and the forms/reports/ useability (a user acceptance test), before you do it on the real production db.
Do it as a once-only change.

 

[GaP42]

Also I forgot to suggest that for convenience it is a very good idea to add a "matchStatus" field to the Report table temporarily - at the stage before matching - and set its value to "U" - unmatched. Then as you carry out the matching process those records updated as matched are set to "M" -matched. Allows you to easily track how many are left that need your attention and to inspect them to see what processing rule you need to write to update them. It can be more sophisticated to tell you which query did the update if you want.
On completion of the process, and checking, the column should be removed when the other columns in the same table are removed.