Multiple tables?

[Prayder]

I have a hard copy sheet that has hourly readings taken on it and I need to turn that into a table. The sheet has boxes for each hour from 5am one day to 4 am the next. A new sheet is done each day beginning at 5am. I need to be able to record each hour so I am not sure if I would have each hour as a field or have one field called DailyHour and then have each hour as a row in that field?

 

[RuralGuy]

In my timesheet table I have a separate record for each hour of each day.

 

[Prayder]

So are you saying have one field named (DailyHour) or whatever and within that field have many records with each record holding a different hour?

 

[RuralGuy]

My table has several fields but one of them is a DateTime field and in your case it would have 24 records each day with all of the hours.

 

[Galaxiom]

So are you saying have one field named (DailyHour) or whatever and within that field have many records with each record holding a different hour?

Yes. Otherwise you would have the same kind of data in differnt fields which is against the principles of normalization.

The denormalized structure would make aggregating the data much more complex.

BTW All days will go into the same table too. The date and time could be combined into one field or kept separate. The choice depends how you are working with the data.

The fact that the "day" starts at 5AM needs to be carefully considered in the decision.

 

[RuralGuy]

I think I would just have 24 records each day starting with 01:00. A query could pick out the hours in which you were interested.

 

[Prayder]

I am creating a table based off a hard copy sheet that is currently being used in which each "day" starts at 5am.

Would it be possible to have one field that is the date (logdate) and one field that held the hour(dailyHour)?

I mean couldn't I feasibly have a table with multiple records where each record was a date field, hour field, and then all the other fields I need?

 

[RuralGuy]

All we are saying is the Date and Time can be in one field since it is a DateTime field. http://support.microsoft.com/kb/q130514/en-us

 

[Galaxiom]

Sometimes combining Date and Time is not the best solution.

For example in this case it may be better to nominate a "day" as a date for the all the records coming from one sheet even though those after midnight are technically on the following day.

Then store the Time periods as integers, 1 to 24. They are discrete repeating times and as such don't really need to be held as time at all. They can easily be displayed as a real time if required.

The advantage of splitting the date and time becomes apparent if data from this table is displayed as a subform. The main form will have a day field (a date) allowing the MasterLinkFields to connect to the day field (also a date) in the subform.

With the date and time combined a complex calculation would be required to derive the ChildLinkField in the subform. Moreover this calculation would be required on every record in the table before the subform could display.

In the separated fields the "day" field in the subform recordsouce can be indexed making the process literally hundreds of times faster.

 

[Prayder]

Sometimes combining Date and Time is not the best solution.

For example in this case it may be better to nominate a "day" as a date for the all the records coming from one sheet even though those after midnight are technically on the following day.

Then store the Time periods as integers, 1 to 24. They are discrete repeating times and as such don't really need to be held as time at all. They can easily be displayed as a real time if required.

The advantage of splitting the date and time becomes apparent if data from this table is displayed as a subform. The main form will have a day field (a date) allowing the MasterLinkFields to connect to the day field (also a date) in the subform.

With the date and time combined a complex calculation would be required to derive the ChildLinkField in the subform. Moreover this calculation would be required on every record in the table before the subform could display.

In the separated fields the "day" field in the subform recordsouce can be indexed making the process literally hundreds of times faster.

Please forgive me but I am fairly new to this and what you just said I have no idea what it means.

I have a form right now that uses a querybuilder as the record source to the table I created. I have txt boxes for the date, hour, and other info. The problem I am having right now is that when the date is entered and the tab key is hit to go to the next box.... the date disappears. Now the hour boxes work as they should... I enter the info into the hour, tab to the next box and so forth. but the date does not.

 

[Prayder]

Now I am able to type in the date... tab down to the first row where I type in the hour and other info but when I tab to the second row the date disappears again... Isnt there a way I can type in the date once and then fill out all the corresponding hours and other info but have it all go to one table?

 

[RuralGuy]

Are you using a Form/SubForm arrangement?

 

[Prayder]

No... I am just using the form I created. What would I use a subform for and how would I create one?

 

[Prayder]

Here is a copy of the db. the tables and forms I am working with are called tblPastreadings and frmPastReadings. So you can see what I am referring to.

 

[RuralGuy]

I'll work on this and post back tomorrow. I've got to go to a meeting now.

 

[Cronk]

Galaxiom,

I'm at a loss. Why do you think it beneficial to save the hour of the day in a different field from the date.

Would you store the Kg and grams in separate fields, Pounds and Ounces in separate fields, kilometers and metres in separate fields.

It's not hard to drill down to all records on the one day by a query based on filtering all obs taken on the day regardless of the hour.

 

[RuralGuy]

I've created a frmNewPastReadings that uses a SubForm for you to preview.

 

[Galaxiom]

Galaxiom,

I'm at a loss. Why do you think it beneficial to save the hour of the day in a different field from the date.

Read the post again where I explained when the separation would be of benefit. If you understand what I am saying you will see it is not a simple as you imagine.

Would you store the Kg and grams in separate fields, Pounds and Ounces in separate fields, kilometers and metres in separate fields.

No but neither would I expect to require grouping by records that were between n.3 kilometers and (n+1).3 kilometres. Nor would I be likely to have a subform linked by the whole kilos and a bit and displaying the part kilos in the subform.

It's not hard to drill down to all records on the one day by a query based on filtering all obs taken on the day regardless of the hour.

No it isn't hard to get all the records for one day. However it is comparatively slow to return multiple "days" for use in linked subforms or a Group By in a query where multiple "days" are to be included.

Any query where the a function must be applied to each record will take far longer than using a indexed field that holds the exact data required. The extent of the slowdown depends on the number of records involved.

Where the date and time are combined, the problem can be reduced by first reducing the number of records to be returned by selecting the datetime range required (via the field's index) but still all records in that range would need to have a function applied before the required data could be returned.

This is an important principle in designing efficient data structures for querying and it would be well worth your time to understand my point.

 

[Cronk]

Galaxiom,

I understand your point. However, I disagree with your approach to split date/time into two fields. Incidentally, would you follow the same approach if minute readings were being recorded? ie a separate field for the minutes as well.

As you posted earlier
"BTW All days will go into the same table too. The date and time could be combined into one field or kept separate. The choice depends how you are working with the data."

Over and out on this one. We might have differing views on other matters in the future but hopefully we all benefit from any debate on different approaches.

 

[Galaxiom]

Galaxiom,

I understand your point. However, I disagree with your approach to split date/time into two fields. Incidentally, would you follow the same approach if minute readings were being recorded? ie a separate field for the minutes as well.

No I wouldn't. However your posing this question leaves me in no doubt that you do not understand the nature of the issues I have raised.