Brain Cramp Db design
in [Tables]
|
From: BABs on 1 Dec 2009 11:27 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
From: Jeff Boyce on 1 Dec 2009 11:57 see comments in-line below... "BABs" <BABs(a)discussions.microsoft.com> wrote in message news:E8468617-2BA8-449A-B45F-2A71E1779958(a)microsoft.com... >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. > TIA
From: John W. Vinson on 1 Dec 2009 14:30 On Tue, 1 Dec 2009 08:27:02 -0800, BABs <BABs(a)discussions.microsoft.com> wrote: >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... -- John W. Vinson [MVP]
From: Roger Carlson on 1 Dec 2009 14:31 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 "BABs" <BABs(a)discussions.microsoft.com> wrote in message news:E8468617-2BA8-449A-B45F-2A71E1779958(a)microsoft.com... >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
From: Daryl S on 1 Dec 2009 14:34 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! -- Daryl S "BABs" wrote: > 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
|
Next
|
Last
Pages: 1 2 Prev: New to the whole DB world Next: Brain Cramp Db design - stevie pimps again |