Solved Linking form table and subform table from imported excel spreadsheet data (1 Viewer)

[rexcision]

I have two separate excel spreadsheets. One contains some overview information about a project, whereas the other excel spreadsheet has project specific details that extend multiple rows. Each row in this specific info spreadsheet has an alpha-numeric identifier that can be used to link it to one of the rows in the overview excel spreadsheet.

My goal is to have a button that the user can push to import these two files, and the information gets append onto existing tables. So far I have it set-up that the spreadsheets are imported into their own tables, and then I'm using SQL queries to append the data I need into the correct spots, as the layouts of the existing and imported tables do not match. I currently have it working for the overview project information, but not yet for the specific project information.

I am unsure how to use the identifier from the spreadsheets to maintain that link between the specific and overview project info when importing and appending into the Access tables. The way its currently set-up is that the overview table simply has an autonumber field and that autonumber field is used as the link between the overview and specific project info tables. So given the excel spreadsheet identifier is much different than the autonumber format, is there some sort of SELECT statement I can use to store that autonumber identifier and then input it into the INSERT statement when I'm importing the specific project info? Or is there a different way i should approach this altogether?

I'm pretty new to SQL/Access so let me know if you need anymore info as I am unsure whether or not that's enough.

Edit: Additional Info Below

Here is what the overview spreadsheet is like

Project Number	Date	Submission ID	Description
2048237.794.4589	01/01/21	LHJFSDHJL-34894	This is a description of the project
2784783.483.2578	02/23/21	BSRDOUIOP-87472	This is a separate description of a different project

Here is what the project specifics spreadsheet is like

Specific Detail	Time	Submission ID	Something else	Data
Something Occurred	09:00	LHJFSDHJL-34894	78578456	jknfgkjn
Something Else occurred	12:00	LHJFSDHJL-34894	gvbherfvuhbrv	gfgdfhs
Something Occurred	10:00	BSRDOUIOP-87472	iuheiuiojbiugjb	8975740

So each file can have information on multiple projects but the submission ID "links" what specific info belongs to what project in the spreadsheets.

Now my existing tables in the database look like this:

Project Overview Table

Autonumber	Project	Date	Description
1	28794389.457.243843	03/04/21	Some description
2	27878443.436.894897	03/05/21	Some other description

Project Specific Table

Autonumber	Project Autonumber	Detail	Time
1	1	Some detail	09:00
2	1	another detail	10:30
3	1	a new detail	12:30
4	2	Different project detail	08:00

So wondering how I can use the Submission ID from the spreadsheets to maintain the relationship and make sure that the project overview and project specific details are inserted into the database with the correct project autonumber between them.

 

[arnelgp]

if the "linked" is already working on your msA tables, then access will maitain
that link when you import your excel files.

 

[rexcision]

if the "linked" is already working on your msA tables, then access will maitain
that link when you import your excel files.

I have added some more info in my post about what the spreadsheets and tables look like. The spreadsheets have different fields then the tables so I need to import each spreadsheet as a new table and then query them to insert/append the details I need into the existing tables. I have it working fine so far for inserting the project overview info. It is just I am not sure how I go about querying the project specific details to make sure that when inserting them into the existing tables, they'll have the proper project autonumber to "link" with the project overview table.

 

[Pat Hartman]

1. I would link to the spreadsheets rather than import them to avoid database bloat.
2. I would use my own autonumbers as PKs for both tables as you have done. I'm not sure why you didn't include the Submission ID but you need to and I would create a unique index for the SubmissionID to prevent duplicates. If this field is not permanently unique, then we need to change the procedure a little so post back if that is the case.
3. Append the data from the overview linked spreadsheet.
4. For the details, in the append query, link the SubmissionID in the detail table to the permanent table. Then use the PK of the permanent table as the FK for the details table.

 

[rexcision]

1. I would link to the spreadsheets rather than import them to avoid database bloat.
2. I would use my own autonumbers as PKs for both tables as you have done. I'm not sure why you didn't include the Submission ID but you need to and I would create a unique index for the SubmissionID to prevent duplicates. If this field is not permanently unique, then we need to change the procedure a little so post back if that is the case.
3. Append the data from the overview linked spreadsheet.
4. For the details, in the append query, link the SubmissionID in the detail table to the permanent table. Then use the PK of the permanent table as the FK for the details table.

Currently the database is filled through forms from the users where they have to physically be in the office, but we are trying to use some new software that users can use in the field and then export their results. So what I am trying to do is import these results properly into the existing tables. So this is why there is no submission id field on the existing table as this whole process is brand new.

But for your response,:
1. The spreadsheets are pretty small, and I have it set to delete the tables once the data is transferred as I had some linking issues initially, but that was when i was first testing this out so i may have messed it up so I'll try again.
2. I believe the submission ID is unique however it comes from the software so I cannot guarantee that, but it probably is.

So if I assume it is unique, if I add another field in both the permanent overview and permanent specific tables to save the submissionID from these imported tables, I should be able to then just append the imported data where it belongs, and then insert the Project Autonumber from the Overview Table into the Specific Table, using the submission ID as the WHERE clause? I can't test that at the moment but from your knowledge would something like below work?

INSERT [Project Overview Table].[Autonumber] INTO [Project Specific Table].[Project Autonumber] WHERE [Project Specific Table].[SubmissionID] = [Project Overview Table].[SubmissionID] AND [Project Specific Table].[SubmissionID] IS NOT NULL

 

[Pat Hartman]

Do you actually have a field named "autonumber"? It is really best to not use reserved words as column names although this one probably won't cause an issue. The issues arise when you use property names such as "Name" or functions such as "Month" as column names.

Append queries append rows, not columns so the query you posted would create a new row with just the "autonumber" field so I don't think that is what you want. I attached a database. Pretend the table named Linked_spreadsheet is linked to a spreadsheet instead of being in the database. Look at the append query to see how the FK is derived.

Access cannot recover space from deleted tables/rows except by doing a C&R and C&R on close is not recommended. It doesn't matter whether you create new tables and delete them or append rows to existing tables and delete the rrows. The result i the same. Dead space that makes the database grow, sometimes at an alarming rate. The best solution is you want to import rather than link is to create a second BE to hold the imported data. Create the BE with the necessary tables but no data. Compact and save the template in a folder on the server. When you start the import proces, the first step is to delete the old template from the local directory and copy the empty template from the server to the local drive where your FE is. Assuming you have done this ONCE manually and linked the tables, the links will not be broken so you don't have to relink. Doing the delete at the beginning rather than at the end, makes testing easier since you will always have the last import data available if you need to verify that it imported correctly.

 

[rexcision]

Do you actually have a field named "autonumber"? It is really best to not use reserved words as column names although this one probably won't cause an issue. The issues arise when you use property names such as "Name" or functions such as "Month" as column names.

Append queries append rows, not columns so the query you posted would create a new row with just the "autonumber" field so I don't think that is what you want. I attached a database. Pretend the table named Linked_spreadsheet is linked to a spreadsheet instead of being in the database. Look at the append query to see how the FK is derived.

Access cannot recover space from deleted tables/rows except by doing a C&R and C&R on close is not recommended. It doesn't matter whether you create new tables and delete them or append rows to existing tables and delete the rrows. The result i the same. Dead space that makes the database grow, sometimes at an alarming rate. The best solution is you want to import rather than link is to create a second BE to hold the imported data. Create the BE with the necessary tables but no data. Compact and save the template in a folder on the server. When you start the import proces, the first step is to delete the old template from the local directory and copy the empty template from the server to the local drive where your FE is. Assuming you have done this ONCE manually and linked the tables, the links will not be broken so you don't have to relink. Doing the delete at the beginning rather than at the end, makes testing easier since you will always have the last import data available if you need to verify that it imported correctly.

No I was just trying to describe the fields, the actual field name is some sort of RecordID name like that but it just populates through Autonumber.

I have quickly reviewed the database you sent me and upon first glance it does seem like it should do what I need. I will hopefully have time in a few days to really run through it and test it out. I am not too familiar with any of the Join commands so I'll also do some brushing up on that. Appreciate the time you took on this Pat, it has been a big help!

Ahh right, I keep forgetting Access is like that. I will try and link when I get a chance, should be able to, but if not I'll try and follow the rest of what you described.

 

[Pat Hartman]

Good Luck

 

[rexcision]

Do you actually have a field named "autonumber"? It is really best to not use reserved words as column names although this one probably won't cause an issue. The issues arise when you use property names such as "Name" or functions such as "Month" as column names.

Append queries append rows, not columns so the query you posted would create a new row with just the "autonumber" field so I don't think that is what you want. I attached a database. Pretend the table named Linked_spreadsheet is linked to a spreadsheet instead of being in the database. Look at the append query to see how the FK is derived.

Access cannot recover space from deleted tables/rows except by doing a C&R and C&R on close is not recommended. It doesn't matter whether you create new tables and delete them or append rows to existing tables and delete the rrows. The result i the same. Dead space that makes the database grow, sometimes at an alarming rate. The best solution is you want to import rather than link is to create a second BE to hold the imported data. Create the BE with the necessary tables but no data. Compact and save the template in a folder on the server. When you start the import proces, the first step is to delete the old template from the local directory and copy the empty template from the server to the local drive where your FE is. Assuming you have done this ONCE manually and linked the tables, the links will not be broken so you don't have to relink. Doing the delete at the beginning rather than at the end, makes testing easier since you will always have the last import data available if you need to verify that it imported correctly.

Finally had time today to come back to this issue and this worked perfectly! I knew there was some sort of join query that could do it, i just didnt know how haha. Thanks so much for putting together that demo, I have definitely learned from this so appreciate it!

 

[Pat Hartman]

It's easier to do than to explain You're welcome.