Creating Access Tables From Excel Spreadsheet (1 Viewer)

Packy427

Registered User.
Local time
Yesterday, 20:21
Joined
Jun 16, 2015
Messages
11
Hi all,

I've been given a task to take an existing Excel spreadsheet and transfer it to Access. I'm a novice in Access, and I'm having trouble designing a way to lay out the data in tables that would make sense. I know there will be plenty of headaches down the road if I don't have a good starting point.

The Excel Spreadsheet is set up as follows, where there's 5 units, 8 locations to measure at, as well as a range and average for each location across all units. Like so...


Unit# | x x x x x | Range Ave
Loc 1 | * * * * * | * *
Loc 2 | * * * * * | * *
Loc 3 | * * * * * | * *
.....

I have tried to have all measurement data in a table (loc1_unit1, loc1_unit2, etc.) and left out calculated fields, but I'm at the point where I have to take averages across fields, and I'm just hoping there's a better way. I originally did it that way because the person I got the project from wants to still be able to enter all five units at once (make the form look like the spreadsheet).

Any thoughts? Thanks in advance.

-Pat
 
Last edited:

Minty

AWF VIP
Local time
Today, 00:21
Joined
Jul 26, 2013
Messages
10,355
You need to normalise the data.
In very simple terms if anything in the field name (Column) looks like data it shouldn't be there. eg Measure1, measure2 etc = wrong.

Just because they are used to seeing it stored in a spreadsheet doesn't mean it's the way to do it. Imagine suddenly needing to add another measurement - all you carefully worked out forms reports and queries would need rewriting!

Post up exactly the current layout and each fields purpose.

Also don't store the averages - as a rule of thumb - if you can calculate it from other stored data you don't need to store it. If you go back and amend a measurement your calculation will take care of it - if you stored the avg it would need re calculating and restoring.
 

JHB

Have been here a while
Local time
Today, 01:21
Joined
Jun 17, 2012
Messages
7,732
I would say it so:
If it should looks like Excel, then keep it in Excel, Excel it the best application for that!
Sorry it sounds very negative, but ...
I Excel you store the data horizontally, in MS-Access store the vertically - it is one of the main difference between the two application!
 

Packy427

Registered User.
Local time
Yesterday, 20:21
Joined
Jun 16, 2015
Messages
11
Attached is the form that populates the table as it's currently designed. No calculated values are stored in it, I was going to evaluate those in a query later down the road.

Where I'm stuck is that they take measurements 5 units at a time and then require a report to show those five units with the calculated fields. It 'seems' logical to keep them grouped together in a record, but I'm sure it's actually not with a relational database.

If you have any ideas I'd love to hear them
 

Attachments

  • Capture.PNG
    Capture.PNG
    26.1 KB · Views: 174

Packy427

Registered User.
Local time
Yesterday, 20:21
Joined
Jun 16, 2015
Messages
11
@JHB Thanks for your feedback. In the current design Excel is definitely the best suited for the job. I'm hoping there's some way to change/modify the design of the data to make it work with Access. Because I know retrofitting the spreadsheet definitely won't be pretty or fun.

The reason for looking at the switch is the data is collected every day, with a single spreadsheet for each day. So the files pile up fast, and they're looking to consolidate.
 
Last edited:

JHB

Have been here a while
Local time
Today, 01:21
Joined
Jun 17, 2012
Messages
7,732
Looking at your picture, I would use a form with a subform, and do the input vertically, it isn't more difficult as do it horizontally, and it is more flexible.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:21
Joined
Feb 28, 2001
Messages
27,001
Looks to me like you have at least two tables required for sample generic description plus the actual raw data.

You have 8 locations - there is a location table waiting to be evoked. You have a location key (code) and one record with details for each location - but ONLY location data, not unit data or sample data.

You have 5 units - there is a unit table waiting to be evoked. You have a unit key (code) and one record with details for each unit - but ONLY unit data, not location data or sample data.

Then there is the actual sample table. In it you would have a location code, a unit code, a sample date, and the actual sample value(s).

Now... to determine range and average, you would be able to use a QUERY to compute these things. Look up the DMin, DMax, and DAvg (or is it spelled out as DAverage? I never use it in my databases...) domain aggregate functions. The criteria clauses will be ugly but once you get the pattern right, they will all be the same.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:21
Joined
Feb 28, 2001
Messages
27,001
I apologize for having to leave this apparently dangling, but our site rule is that we do our side forums only when we aren't having a fire drill (figurative or literal). I was in the middle of the above when we had a figurative fire drill.

Having laid the groundwork above, I now need to ask for Packy to give us just a few more details about the spreadsheet, because there is an easy way to do this, it just takes a little bit of query work. That image-capture is confusing in that I don't see something that CAN be averaged in the numbers you reported. However, here is what I do (sometimes).

I have a spreadsheet with "stuff" in it. For alignment and such, some rows have titles, but others have blanks. So I do an IMPORT of the spreadsheet to a temporary table and tell it NO PRIMARY KEY and a few other things that make the rows/columns come in as raw text, raw numbers, or raw blanks. The rows that are headers have fixed text that can be identified. The rows that have detail data have things in specific columns that can be identified. The rest is window-dressing. With me so far?

So write queries that purge the import table of rows that are neither headers nor detail lines. What is left is either non-blank headers or detail lines. E.g. you have rows that begin with "unit #" and some data, but you have other rows that do NOT contain the unit # headers. You can remove the rows from the spreadsheet that are fixed in nature.

You can add an R somewhere on those lines that relate to your right-side stuff and then do INSERT INTO queries (one at a time) to extract those rows that are left-side (no R) and those that are right-side (has an R). In each case, you can remove the rows that you just imported to your REAL detail tables. When you are done, what you have is an empty import table and you have done row adds for the things you wanted to keep.

Sounds torturous? It is - the first time. But once you get it set up, it is "set and forget" and you just run a Macro that does the queries in the right order.
 

Packy427

Registered User.
Local time
Yesterday, 20:21
Joined
Jun 16, 2015
Messages
11
Hi all,

First off thanks for all of your support!

I think I have a solution that will work. Since all five units are measured in the same day, I set up the table where each record has the date, unit#, and all eight measurement locations. Then, to keep the measurements grouped together I use my query to pull all measurements from just a single day, resulting in those five units.

In my form, I took JHB's advice and used a subform that is bound to the table and had one subform for each unit.

And to The_Doc_Man, I didn't mention it in my initial question, but I was looking to set up a table and then a form so all of the data entry/storage was done in Access and cut out the use of the spreadsheet entirely. However, I really like your idea and am definitely bookmarking it for if I ever need it down the road!

Thanks again, you guys are awesome. :D
 

Users who are viewing this thread

Top Bottom