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
Here is what the project specifics spreadsheet is like
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
Project Specific Table
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.
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.
Last edited: