I have two tables, one with the Businesses and its individual ID for each business along with some information for each such as addresses. In the other table, I have reports for each business but no ID, I want to link each report so it updates with the ID of the first table using information such as the address. How can I accomplish this?
I could barely parse that. Some sample data from both tables could probably clear things up.
However, I think I heard that you have 2 tables that you want to JOIN, however you haven't explicitly defined a way to JOIN them. So, take computers out of the question for a minute--when you look at the data in those 2 tables how do you know that some rows in 1 table go with some rows in the 2nd table? If a person had only pen and paper how could they deduce the connection between the data?
On second thought, we definitely need sample data so you can illustrate how you know the data in one table belongs to data in the second.
I believe these tables might make it clear, so in the reports list, there are multiple reports per business and I want the ID field to automatically match the ID field from the business list using the business name, street #, and street name. However, all three of these fields are never inputted exactly the same but should be similar.
I have two tables, one with the Businesses and its individual ID for each business along with some information for each such as addresses. In the other table, I have reports for each business but no ID, I want to link each report so it updates with the ID of the first table using information such as the address. How can I accomplish this?
You should have 2 tables with the following structure so that you can set a Relationship
between the Business and the Address.
We would link the Parent PKBusinessID from tblBusiness to the BusinessID FKBusinessIDin tbleAddress
I believe these tables might make it clear, so in the reports list, there are multiple reports per business and I want the ID field to automatically match the ID field from the business list using the business name, street #, and street name. However, all three of these fields are never inputted exactly the same but should be similar.
Your Reports List structure is wrong.
Yo would not store the actual Business Name. If you set the Business table as the Main Parent Form for Data Input
then you would have a Subform for the Address's with a FK of BusinessID
I believe these tables might make it clear, so in the reports list, there are multiple reports per business and I want the ID field to automatically match the ID field from the business list using the business name, street #, and street name. However, all three of these fields are never inputted exactly the same but should be similar.
To answer the direct question, you can write a query to attempt this update. This could be defined on one line but I broke it up for clarity.
Code:
UPDATE [Reports List] As R INNER JOIN [Business List] As B
ON R.[Business Name] = B.[Business Name]
SET R.ID = B.ID ;
However, this would be a one-time thing and DOES DEPEND in the business names being spelled correctly in each record of each table. After you ran that query, you would then have to check for the Reports List to still have blanks. Those blanks would imply a name in the Reports list that doesn't match any names in the Business list. Whether it is a spelling error or an error of omission in the Business list would be for you to decide. Keep that query around until you can fix all the names that don't match up correctly, because it is a safely repeatable query. I.e. if you ran it twice, the worst it would do the second time is overwrite the Reports List ID with the same thing that had been loaded previously.
Now, as a side note... The Reports List table is a dependent table in this context whereas the Business List table is an independent table. Therefore the Reports List ID is a foreign key to the (at least potential) primary key represented by the Business List ID. It is considered bad form to just call fields "ID" - particularly when they might appear in more than one table. For instance, you might call the Business List ID "BusID" and in the Reports list call the corresponding field "BusFK" - which becomes a name full of meaning - a foreign key to the business list.
To answer the direct question, you can write a query to attempt this update. This could be defined on one line but I broke it up for clarity.
Code:
UPDATE [Reports List] As R INNER JOIN [Business List] As B
ON R.[Business Name] = B.[Business Name]
SET R.ID = B.ID ;
However, this would be a one-time thing and DOES DEPEND in the business names being spelled correctly in each record of each table. After you ran that query, you would then have to check for the Reports List to still have blanks. Those blanks would imply a name in the Reports list that doesn't match any names in the Business list. Whether it is a spelling error or an error of omission in the Business list would be for you to decide. Keep that query around until you can fix all the names that don't match up correctly, because it is a safely repeatable query. I.e. if you ran it twice, the worst it would do the second time is overwrite the Reports List ID with the same thing that had been loaded previously.
Now, as a side note... The Reports List table is a dependent table in this context whereas the Business List table is an independent table. Therefore the Reports List ID is a foreign key to the (at least potential) primary key represented by the Business List ID. It is considered bad form to just call fields "ID" - particularly when they might appear in more than one table. For instance, you might call the Business List ID "BusID" and in the Reports list call the corresponding field "BusFK" - which becomes a name full of meaning - a foreign key to the business list.
I believe this could also be done by having the query find business reports with similar names and addresses to then assign it automatically to the corresponding business ID?
Describe this process please. Specifically how is the data input?
Are users directly inputting this data via an Access form into the Reports List table? If so, you have designed that form incorrectly. If not, please explain how the Report List is populated.
Editing the original sample I made, some of the reports made originally were never made consistently, this is one reason I would like them to have the same ID so it can be easier to access all the reports from a specific business even if the data itself was not entered correctly.
Editing the original sample I made, some of the reports made originally were never made consistently, this is one reason I would like them to have the same ID so it can be easier to access all the reports from a specific business even if the data itself was not entered correctly.
Plog asked how was the data entered into this table?
Was it entered directly into the table or was a Data Input Form used.
It looks like you have data for 3 Records which should be for the same Business Name, but due to bad data input we have 3 corrupt records.
Sorry, it seems as if they have been just inputting it manually into the table.
I think the best way to input such reports is in the business list, having the option to open up a report form which will then have all the basic business information always saved and be consistent.
Tables are where we store Data
Reports is a means of displaying Data from 1 or more Tables.
What you would normally have is a Form for Data Input into Related Tables.
In your process would you want to be able to Select a Business Name and then enter
1 or More details (Records) related to that Business Name?
If this is the case then you need a Main Form based on Business Names
With a Subform based on Business Reports (I think this name does not reflect your actual business)
Tables are where we store Data
Reports is a means of displaying Data from 1 or more Tables.
What you would normally have is a Form for Data Input into Related Tables.
In your process would you want to be able to Select a Business Name and then enter
1 or More details (Records) related to that Business Name?
If this is the case then you need a Main Form based on Business Names
With a Subform based on Business Reports (I think this name does not reflect your actual busines
Having a form that will have a business list subform where I would be able to pick individual businesses and have the ability to open a form for each business where I would be able to input a 'business report'
Having a form that will have a business list subform where I would be able to pick individual businesses and have the ability to open a form for each business where I would be able to input a 'business report'
Stez,
Can you step back and tell us using simple, plain English about your business at the 30,000 ft level, and the what, when and why of the various reports?
Consider this hypothetical: Often there is a list of Customers who may Order many Products and/or Services from your Organization. Management wants reports at quarter end, year end etc to determine "How's the business doing".
Readers need something concrete to work with and advise/suggest accordingly.
