[Question] Creating multiple tables out of a single spreadsheet

[russdwright]

I have been told that I need to take information from a single spreadsheet and then turn it into a single database, which of course requires the creation of multiple tables for normalization purposes.



I've done a lot of reading and seen that this is possible, but I've not found a good straight-forward explanation.

Based on what I have read, the process to do this is as follows:

1. Create a link between the spreadsheet and Access as its own table.
2. Create new tables using queries to pull (append) the data from the linked spreadsheet to the applicable table(s).
3. Create links to the tables using applicable foreign keys.

Please advise if I am understanding this correctly, or if there are steps that I am missing in the process. Thanks!

[JoeM]

Do you want this to stay as linked to your Excel workbook?
If not, I would just import or link the original Excel file to your Access database, and use a series of Append Queries to write the data from this table to your permanent tables in the Access database.

I am not sure what you are trying to accomplish with this last step:

3. Create links to the tables using applicable foreign keys.

Create links to where, precisely? You "could" set up relationships between your tables, but that isn't really necessary. I seldom do. I just set up the joins directly in my queries.

[Bulzie]

That sounds right, get all your tables set with keys, foreign keys, fields, datatypes, Relationships, etc. Then as you said, link the sheet, then run append queries as needed to move data to the tables. Is this a one time process or will it be daily data appends?

If you provide more details on your data (with samples of data), fine folks on here will help you get started with the design as well if needed.

Edit: If the data backend is Access as well, Relationships using referential integrity can be useful to keep data updated or avoid orphan records.

[russdwright]

Do you want this to stay as linked to your Excel workbook?
If not, I would just import or link the original Excel file to your Access database, and use a series of Append Queries to write the data from this table to your permanent tables in the Access database.

The idea is that as data is updated in the spreadsheet the database would be updated, as well.

I am not sure what you are trying to accomplish with this last step:

Create links to where, precisely? You "could" set up relationships between your tables, but that isn't really necessary. I seldom do. I just set up the joins directly in my queries.

I was referring to setting up relationships between the tables. I am also going to create a front end for the purpose of easily viewing the information, and I may be creating some reports, as well.

My SQL is a bit rough, so I don't know if I could effectively achieve the joins you are referring to.

[russdwright]

If you provide more details on your data (with samples of data), fine folks on here will help you get started with the design as well if needed.

I work for a financial institution so I am leery of providing a data sample; however, I can give you a breakdown of what we are doing in general terms:

The team I am a part of is part of a project to centralize provisioning (access) for a large number of applications. As part of this process, we have sent out questionnaires requesting information about these applications. We then have meetings setup to get more information, which is being compiled into the spreadsheet for record-keeping purposes and to provide metrics/reporting on the project.

The attached table shows the fields in the spreadsheet at this time. I've already spoke with the creator of the spreadsheet and advised that we may need to rename and/or split some of these fields to make the data easier to work with.

One of the big things that I know I need to deal with is that some of these fields have specific sets of answers, all of which will require separate tables with relationships established accordingly.

He has already told me that any updates we do on this spreadsheet are NOT to be pushed back to the spreadsheet; I believe he intends to update the spreadsheet manually using the information that is added to this sheet (don't ask me why).

[russdwright]

Forgot the attachment!headings for spreadsheet.zip

[JoeM]

The idea is that as data is updated in the spreadsheet the database would be updated, as well.

I am not sure how that work work in your model, where you are using a linked table, and then using Append Queries to write the information to your "normalized" tables.
Seems to me that if you wanted to take this approach, you would need to delete the data from your normalized tables, and then re-run your Append Queries each time to re-build the data on the tables.
I do not know of anyway to make a linked table update other tables automatically. That is really not the way that databases work, the tables are not dependent on other tables (that would kind of violate the rules of normalization!).

The only way I can think of to make things "dynamic" is to take your linked table, and instead of creating other normalized tables, create queries that resemble what normalized tables look like, and use those queries instead of tables. Then any changes to your data in the linked table would automatically flow through. Of course, without knowing the complexity of what you are doing, I am not sure what kind of problems that could present (performance, etc).

[russdwright]

I am not sure how that work work in your model, where you are using a linked table, and then using Append Queries to write the information to your "normalized" tables.
Seems to me that if you wanted to take this approach, you would need to delete the data from your normalized tables, and then re-run your Append Queries each time to re-build the data on the tables.
I do not know of anyway to make a linked table update other tables automatically. That is really not the way that databases work, the tables are not dependent on other tables (that would kind of violate the rules of normalization!).

The only way I can think of to make things "dynamic" is to take your linked table, and instead of creating other normalized tables, create queries that resemble what normalized tables look like, and use those queries instead of tables. Then any changes to your data in the linked table would automatically flow through. Of course, without knowing the complexity of what you are doing, I am not sure what kind of problems that could present (performance, etc).

I've read something about using macros to achieve this result, but maybe I misread.

[JoeM]

Sure, but it could get a little tricky. You can do something like that if you have some unique identifier in your Excel file.
If you do, then you do the following:

Compare your Excel linked table to each created table (based on the unique identifier), and create the three following Action Queries:
1. Where you find a new unique indentifier, run an Append Query to add the new record to your table
2. Where you find a match on the unique identifier, run an Update Query to update each field in that table to catch any changes (you can try to look for changes, but then you would need to write criteria for each and every field that might change)
3. Check to see if any unique identifiers dropped off (that is, they appear in your created Access table, but are no longer on the Excel file). You would then need to run a Delete Query to remove them.

You would need to create these 4 Action Queries for every Access table you built off of the Excel workbook. So if you built 10 tables, you would need to create 30 queries that you would need to run each time.

If you are really clever and are able to add a Date/Time stamp field to your Excel file, you may be able to make use of that to zero on the records that need updating (mainly talking about the Update query here). That doesn't reduce the never of queries that you need through, just helps you identity which existing records need updating.

Once you have created all these queries, you can put them in a single Macro that you run each time (which just a single click). As you can see, there is a bit of upfront work to do with this method.
And if you do not have a unique identifying record in your Excel workbook, I don't think you are going to be able to use this method.

[russdwright]

I have a unique identifier, so I can definitely do that. Fortunately, I remember just enough SQL to pull that off, but I'm not afraid to ask for help when I get to that point

[Bulzie]

Just for clarification, when we say sample of data, if it is confidential, just supply test data in the main fields for each table. Most times easier to help figure what people are wanting to do by looking at sample data and what output they want.

[kd2017]

It might be helpful to go through that spreadsheet you linked and explain exactly what it is and what data you expect in each column. Also, what's the significance of the different background colors?

[ssanfu]

You will have to come up with field names that short, but long enough to be self descriptive.

"Do you perform Password Resets? (Password Resets should be a Helpdesk Function)"
or
"# Provisioning requests per week (Adds, Changes, Deletes)"
would be horrendously bad field names for many reasons!!


Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.
Do not use reserved words as object names. (examples: "Date", "Description", "Desc") See list of reserved words