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
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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] |
#6
|
|||
|
|||
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] |
#7
|
|||
|
|||
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] |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|