Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-18-2015, 06:05 AM   #1
Packy427
Newly Registered User
 
Join Date: Jun 2015
Location: Central NJ
Posts: 11
Thanks: 10
Thanked 0 Times in 0 Posts
Packy427 is on a distinguished road
Creating Access Tables From Excel Spreadsheet

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 by Packy427; 06-18-2015 at 06:10 AM. Reason: Edit example table
Packy427 is offline   Reply With Quote
Old 06-18-2015, 06:23 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,737 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Creating Access Tables From Excel Spreadsheet

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Packy427 (06-18-2015)
Old 06-18-2015, 06:34 AM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Creating Access Tables From Excel Spreadsheet

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!

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 06-18-2015, 06:38 AM   #4
Packy427
Newly Registered User
 
Join Date: Jun 2015
Location: Central NJ
Posts: 11
Thanks: 10
Thanked 0 Times in 0 Posts
Packy427 is on a distinguished road
Re: Creating Access Tables From Excel Spreadsheet

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
Attached Images
File Type: png Capture.PNG (26.1 KB, 96 views)
Packy427 is offline   Reply With Quote
Old 06-18-2015, 06:43 AM   #5
Packy427
Newly Registered User
 
Join Date: Jun 2015
Location: Central NJ
Posts: 11
Thanks: 10
Thanked 0 Times in 0 Posts
Packy427 is on a distinguished road
Re: Creating Access Tables From Excel Spreadsheet

@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 by Packy427; 06-18-2015 at 06:56 AM. Reason: Additional note
Packy427 is offline   Reply With Quote
Old 06-18-2015, 07:09 AM   #6
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Creating Access Tables From Excel Spreadsheet

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.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Packy427 (06-19-2015)
Old 06-18-2015, 08:20 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,554
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Creating Access Tables From Excel Spreadsheet

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Packy427 (06-19-2015)
Old 06-18-2015, 12:10 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,554
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Creating Access Tables From Excel Spreadsheet

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Packy427 (06-19-2015)
Old 06-19-2015, 07:02 AM   #9
Packy427
Newly Registered User
 
Join Date: Jun 2015
Location: Central NJ
Posts: 11
Thanks: 10
Thanked 0 Times in 0 Posts
Packy427 is on a distinguished road
Re: Creating Access Tables From Excel Spreadsheet

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.
Packy427 is offline   Reply With Quote
Old 06-19-2015, 07:14 AM   #10
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,311
Thanks: 537
Thanked 939 Times in 890 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
You might find it useful and informative to read the thread here:- http://www.access-programmers.co.uk/...d.php?p=671226

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
Packy427 (06-19-2015)
Reply

Tags
data structures , excel & access , normalisation , normalize , spreadsheet

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add VB Code to Excel Spreadsheet from within Access JonMulder Modules & VBA 2 05-01-2014 12:20 PM
Question Excel Spreadsheet in Access DrZac General 6 10-12-2012 12:16 AM
How do i extract tables from MS Access into a single spreadsheet in excel? xwnoob General 3 02-02-2012 05:13 AM
Formatting Excel spreadsheet in Access fredalina Modules & VBA 5 11-24-2009 04:37 PM
Include excel macro when creating spreadsheet presuming_ed General 5 05-13-2003 02:47 AM




All times are GMT -8. The time now is 05:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World