Brain Cramp Db design

[BABs]

I am building a new db from excel spreadsheets. One spreadsheet has gauge
information: (each row/record is a different gauge)
gauge serial number - calibration date - load range - location
The second spreadsheet ahs gauge readings by date: (each row/record is a
different date containg data from all gauges)
date - gauge1 - gauge2 - gauge3 - gauge4 - etc
How can I set up the db so I can query for results by date or by gauge number?
I'm drawing a blank on how to link a row/record to a column......
TIA

 

[Jeff Boyce]

see comments in-line below...

I am building a new db from excel spreadsheets.

Risky. Spreadsheets are rarely well-normalized, and Access is optimized for
relational/well-normalized data.

One spreadsheet has gauge
information: (each row/record is a different gauge)
gauge serial number - calibration date - load range - location
The second spreadsheet ahs gauge readings by date: (each row/record is a
different date containg data from all gauges)
date - gauge1 - gauge2 - gauge3 - gauge4 - etc
How can I set up the db so I can query for results by date or by gauge
number?

Why not leave it in Excel and use the filters there?

I'm drawing a blank on how to link a row/record to a column......

Before you try to get Access to do this, consider brushing up on relational
database design. Access is not a spreadsheet on steroids, and much of what
you know about spreadsheets will lead you the wrong way with Access.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I am building a new db from excel spreadsheets. One spreadsheet has gauge
information: (each row/record is a different gauge)
gauge serial number - calibration date - load range - location
The second spreadsheet ahs gauge readings by date: (each row/record is a
different date containg data from all gauges)
date - gauge1 - gauge2 - gauge3 - gauge4 - etc
How can I set up the db so I can query for results by date or by gauge number?
I'm drawing a blank on how to link a row/record to a column......
TIA

As Jeff says... Excel and Access are different. Good spreadsheet design can be
wretchedly bad table design. This is a case in point!

Spreadsheets are "wide and flat", tables are "tall and thin". A normalized
structure would have a one to many relationship with two tables:

Guages
(your existing first spreadsheet looks fine)

Readings
ReadingID <primary key>
ReadingDate <date/time>
GuageID <link to Guages>
Result

You cannot link a value in one table to a fieldname in another table without
all sorts of contrortions...

 

[Roger Carlson]

If you're talking about importing your spreadsheet data into Access, you
need to normalize your second table.

If you're unfamiliar with normalization, see my tutorials page here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238, especially
"What Is Normalization?" and "Entity-Relationship Diagramming".

Then you can use the method in my sample:NormalizeDenormalize.mdb (
intermediate ) to normalize your gauge readings table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Daryl S]

BABs -

Your guage spreadsheet sounds normalized - one record per guage, all data
dealing with the guage. That should convert easily to a table. You should
be able to import it pretty much as is, selecting the guageID as the primary
key.

Your guage readings datae is not normalized. You will want your new table
to hold the GuageID, ReadingDate, and GuageReading, with the GuageID and
ReadingDate together as the primary key. You can make a copy of your guage
reading spreadsheet and make changes there to allow you to copy/paste this
data into your new table. Insert a column between your date and guage 1
columns, and put the guageID (maybe this is "guage 1"?) down the whole
column. Then the first three columns of your spreadsheet match the format
of the guage readings table. Then you can change column B to contain "guage
2" and delete column C (the reading for guage 1 that you already pasted into
your table). Now the first three columns have the data you need for the
second guage. Copy/Paste these into your guage readings table. Continue
on for all the guages.

Hope that helps!

 

[Steve]

I would like to offer to help you. I provide fee-based basic help with
Access, Excel and Word applications. Let me design and create your database
for you. My fee would be very modest. If interested, contact me.

Steve
(e-mail address removed)

 

[John... Visio MVP]

I would like to offer to help you. I provide fee-based basic help with
Access, Excel and Word applications. Let me design and create your database
for you. My fee would be very modest. If interested, contact me.

Steve
(e-mail address removed)

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

 

[Tony Toews [MVP]]

I would like to offer to help you. I provide fee-based basic help with
Access, Excel and Word applications. Let me design and create your database
for you. My fee would be very modest. If interested, contact me.

Go away. We don't want you here.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/