If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Brain Cramp Db design
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 |
#2
|
|||
|
|||
Brain Cramp Db design
see comments in-line below...
"BABs" wrote in message ... 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 |
#3
|
|||
|
|||
Brain Cramp Db design
On Tue, 1 Dec 2009 08:27:02 -0800, 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 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] |
#4
|
|||
|
|||
Brain Cramp Db design
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 he http://www.rogersaccesslibrary.com/f...ts.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/...UBED1=ACCESS-L "BABs" wrote in message ... 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 |
#5
|
|||
|
|||
Brain Cramp Db design
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 |
#6
|
|||
|
|||
Brain Cramp Db design
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 "BABs" wrote in message ... 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 |
#7
|
|||
|
|||
Brain Cramp Db design - stevie pimps again
"Steve" wrote in message
... 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 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 |
#8
|
|||
|
|||
Brain Cramp Db design
"Steve" schreef in bericht ... 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. You would offer the same crappy help as in 'Setting up Observation Database' ?? Nevertheless... Get lost $teve. Go away... far away.... Again... Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... This newsgroup is meant for FREE help.. No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... we have passed 10.500 pageloads... it's a shame !!) Arno R |
#9
|
|||
|
|||
Brain Cramp Db design
"Steve" wrote:
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/ |
#10
|
|||
|
|||
Brain Cramp Db design - stevie pimps again
"Steve" wrote in message
... 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 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 |
Thread Tools | |
Display Modes | |
|
|