A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Design assistance requested



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2010, 11:18 PM posted to microsoft.public.access.tablesdbdesign
CB
external usenet poster
 
Posts: 184
Default Design assistance requested

I apologize for the length of this post. I’m trying to give as much info the
first time to help you understand what I need. I haven't done any design in
some time so I feel weak on normalization.

I am contemplating moving from Excel workbooks to an Access database for our
equipment comparison data. I **think** it is worthwhile and need
assistance/guidance with the best method for creating my tables. My questions
are towards the end of this post.

Our process to date … we have one regional standard (RS - consists of two
sensors) and many travelling standards (TS – one sensor). On a regular basis,
we compare the travelling standards against the regional standard and take
numerous readings. Currently, there is one workbook per travelling standard.
Within each workbook, there is one spreadsheet for each comparison and each
comparison has many readings.

All the info that is CURRENTLY recorded on a spreadsheet for a given
comparison, if I were to like it to a database table would be:

tblComparison
TSSerNum (actually not on each spreadsheet. It’s in the filename and header
of each worksheet)
CompDate
InstallTime
TechName
RdgTime1 to RdgTimeX (for first order normalization – just one field, i.e.,
RdgTime)
RS1Rdg1 to RS1RdgX (for first order normalization – just one field, i.e.,
RS1Rdg)
RS2Rdg1 to RS2RdgX (for first order normalization – just one field, i.e.,
RS2Rdg)
TSRdg1 to TSRdgX (for first order normalization – just one field, i.e., TSRdg)
Remarks

Additional info that I think would be necessary would be:

CompID - PK
TSModel
RSModel
RSSerNum

If I were to normalize some more I suppose I could have the following three
tables with their PKs as FKs in the comparison table.

tblTechs
TechID - PK
TechName

tblRS
RSID - PK
RSModel
RSSerNum

tblTS
TSID - PK
TSModel
TSSerNum

However, I’m hoping to add this to our existing calibration database where
there already exists a techs table and equipment table. Hmm, I suppose having
both the RS and TS listed in the equipment table will complicate things.

Anyway, I’m not sure what the best way is to go from here but I have a
feeling that I need some kind of Readings table. Should I have a separate
Readings table for each travelling standard? I’d appreciate any guidance you
could provide.

Regards,

Chris

  #2  
Old February 12th, 2010, 12:11 AM posted to microsoft.public.access.tablesdbdesign
CB
external usenet poster
 
Posts: 184
Default Design assistance requested

Hi again,

I forgot to add that there are situations where more than one TS is being
compared against the RS at one time. For example, today I compared three
units against the RS so I had three work books open. Consequently, the data
for the following fields was identical in all three books:

CompDate
InstallTime
TechName
RdgTime
RS1Rdg
RS2Rdg

as well as RSModel and RSSerNum were they to be added.

Unique data would be TSRdgs for each different TS, as well as the remakrs.

I'm sure this complicates things.

Thanks again!

Chris

"CB" wrote:

I apologize for the length of this post. I’m trying to give as much info the
first time to help you understand what I need. I haven't done any design in
some time so I feel weak on normalization.

I am contemplating moving from Excel workbooks to an Access database for our
equipment comparison data. I **think** it is worthwhile and need
assistance/guidance with the best method for creating my tables. My questions
are towards the end of this post.

Our process to date … we have one regional standard (RS - consists of two
sensors) and many travelling standards (TS – one sensor). On a regular basis,
we compare the travelling standards against the regional standard and take
numerous readings. Currently, there is one workbook per travelling standard.
Within each workbook, there is one spreadsheet for each comparison and each
comparison has many readings.

All the info that is CURRENTLY recorded on a spreadsheet for a given
comparison, if I were to like it to a database table would be:

tblComparison
TSSerNum (actually not on each spreadsheet. It’s in the filename and header
of each worksheet)
CompDate
InstallTime
TechName
RdgTime1 to RdgTimeX (for first order normalization – just one field, i.e.,
RdgTime)
RS1Rdg1 to RS1RdgX (for first order normalization – just one field, i.e.,
RS1Rdg)
RS2Rdg1 to RS2RdgX (for first order normalization – just one field, i.e.,
RS2Rdg)
TSRdg1 to TSRdgX (for first order normalization – just one field, i.e., TSRdg)
Remarks

Additional info that I think would be necessary would be:

CompID - PK
TSModel
RSModel
RSSerNum

If I were to normalize some more I suppose I could have the following three
tables with their PKs as FKs in the comparison table.

tblTechs
TechID - PK
TechName

tblRS
RSID - PK
RSModel
RSSerNum

tblTS
TSID - PK
TSModel
TSSerNum

However, I’m hoping to add this to our existing calibration database where
there already exists a techs table and equipment table. Hmm, I suppose having
both the RS and TS listed in the equipment table will complicate things.

Anyway, I’m not sure what the best way is to go from here but I have a
feeling that I need some kind of Readings table. Should I have a separate
Readings table for each travelling standard? I’d appreciate any guidance you
could provide.

Regards,

Chris

  #3  
Old February 12th, 2010, 01:24 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Design assistance requested

Should I have a separate Readings table for each travelling standard?
Just one for readings but with FK field for the 'many' side of relationsip
with TSID - PK.
tblRS_TS_Read --
RS_TS_ID PK
TechID - FK
RSID - FK
TSID - FK
ReadDate - DateTime
RdNUM - if you need it
Reading - each reading a separate record
RMKS

Set one-to-many relationships between the tables and tblRS_TS_Read.

I do not know what InstallTime is for. Does it have a bearing on the
reading? ReadDate will store date and time so no need for RdgTime.


--
Build a little, test a little.


"CB" wrote:

Hi again,

I forgot to add that there are situations where more than one TS is being
compared against the RS at one time. For example, today I compared three
units against the RS so I had three work books open. Consequently, the data
for the following fields was identical in all three books:

CompDate
InstallTime
TechName
RdgTime
RS1Rdg
RS2Rdg

as well as RSModel and RSSerNum were they to be added.

Unique data would be TSRdgs for each different TS, as well as the remakrs.

I'm sure this complicates things.

Thanks again!

Chris

"CB" wrote:

I apologize for the length of this post. I’m trying to give as much info the
first time to help you understand what I need. I haven't done any design in
some time so I feel weak on normalization.

I am contemplating moving from Excel workbooks to an Access database for our
equipment comparison data. I **think** it is worthwhile and need
assistance/guidance with the best method for creating my tables. My questions
are towards the end of this post.

Our process to date … we have one regional standard (RS - consists of two
sensors) and many travelling standards (TS – one sensor). On a regular basis,
we compare the travelling standards against the regional standard and take
numerous readings. Currently, there is one workbook per travelling standard.
Within each workbook, there is one spreadsheet for each comparison and each
comparison has many readings.

All the info that is CURRENTLY recorded on a spreadsheet for a given
comparison, if I were to like it to a database table would be:

tblComparison
TSSerNum (actually not on each spreadsheet. It’s in the filename and header
of each worksheet)
CompDate
InstallTime
TechName
RdgTime1 to RdgTimeX (for first order normalization – just one field, i.e.,
RdgTime)
RS1Rdg1 to RS1RdgX (for first order normalization – just one field, i.e.,
RS1Rdg)
RS2Rdg1 to RS2RdgX (for first order normalization – just one field, i.e.,
RS2Rdg)
TSRdg1 to TSRdgX (for first order normalization – just one field, i.e., TSRdg)
Remarks

Additional info that I think would be necessary would be:

CompID - PK
TSModel
RSModel
RSSerNum

If I were to normalize some more I suppose I could have the following three
tables with their PKs as FKs in the comparison table.

tblTechs
TechID - PK
TechName

tblRS
RSID - PK
RSModel
RSSerNum

tblTS
TSID - PK
TSModel
TSSerNum

However, I’m hoping to add this to our existing calibration database where
there already exists a techs table and equipment table. Hmm, I suppose having
both the RS and TS listed in the equipment table will complicate things.

Anyway, I’m not sure what the best way is to go from here but I have a
feeling that I need some kind of Readings table. Should I have a separate
Readings table for each travelling standard? I’d appreciate any guidance you
could provide.

Regards,

Chris

  #4  
Old February 12th, 2010, 04:59 PM posted to microsoft.public.access.tablesdbdesign
CB
external usenet poster
 
Posts: 184
Default Design assistance requested

Hi Karl,

Thanks for responding so quickly. I'm on my way out the door so can't
saymuch at the moment but I will respond further later - if not this
afternoon (almost 10 a.m. in Manitoba right now) then on Monday.

We have be recording installation time as well as reading times since the
equipment must be allowed time to settle before comparisons are made. If the
tech starts taking readings too soon, the initial readings may be
questionable.

Thanks again!

Chris

"KARL DEWEY" wrote:

Should I have a separate Readings table for each travelling standard?

Just one for readings but with FK field for the 'many' side of relationsip
with TSID - PK.
tblRS_TS_Read --
RS_TS_ID PK
TechID - FK
RSID - FK
TSID - FK
ReadDate - DateTime
RdNUM - if you need it
Reading - each reading a separate record
RMKS

Set one-to-many relationships between the tables and tblRS_TS_Read.

I do not know what InstallTime is for. Does it have a bearing on the
reading? ReadDate will store date and time so no need for RdgTime.


--
Build a little, test a little.


"CB" wrote:

Hi again,

I forgot to add that there are situations where more than one TS is being
compared against the RS at one time. For example, today I compared three
units against the RS so I had three work books open. Consequently, the data
for the following fields was identical in all three books:

CompDate
InstallTime
TechName
RdgTime
RS1Rdg
RS2Rdg

as well as RSModel and RSSerNum were they to be added.

Unique data would be TSRdgs for each different TS, as well as the remakrs.

I'm sure this complicates things.

Thanks again!

Chris

"CB" wrote:

I apologize for the length of this post. I’m trying to give as much info the
first time to help you understand what I need. I haven't done any design in
some time so I feel weak on normalization.

I am contemplating moving from Excel workbooks to an Access database for our
equipment comparison data. I **think** it is worthwhile and need
assistance/guidance with the best method for creating my tables. My questions
are towards the end of this post.

Our process to date … we have one regional standard (RS - consists of two
sensors) and many travelling standards (TS – one sensor). On a regular basis,
we compare the travelling standards against the regional standard and take
numerous readings. Currently, there is one workbook per travelling standard.
Within each workbook, there is one spreadsheet for each comparison and each
comparison has many readings.

All the info that is CURRENTLY recorded on a spreadsheet for a given
comparison, if I were to like it to a database table would be:

tblComparison
TSSerNum (actually not on each spreadsheet. It’s in the filename and header
of each worksheet)
CompDate
InstallTime
TechName
RdgTime1 to RdgTimeX (for first order normalization – just one field, i.e.,
RdgTime)
RS1Rdg1 to RS1RdgX (for first order normalization – just one field, i.e.,
RS1Rdg)
RS2Rdg1 to RS2RdgX (for first order normalization – just one field, i.e.,
RS2Rdg)
TSRdg1 to TSRdgX (for first order normalization – just one field, i.e., TSRdg)
Remarks

Additional info that I think would be necessary would be:

CompID - PK
TSModel
RSModel
RSSerNum

If I were to normalize some more I suppose I could have the following three
tables with their PKs as FKs in the comparison table.

tblTechs
TechID - PK
TechName

tblRS
RSID - PK
RSModel
RSSerNum

tblTS
TSID - PK
TSModel
TSSerNum

However, I’m hoping to add this to our existing calibration database where
there already exists a techs table and equipment table. Hmm, I suppose having
both the RS and TS listed in the equipment table will complicate things.

Anyway, I’m not sure what the best way is to go from here but I have a
feeling that I need some kind of Readings table. Should I have a separate
Readings table for each travelling standard? I’d appreciate any guidance you
could provide.

Regards,

Chris

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.