Date and Time tables

[Pilotwings_64]

I'm creating a database with many tables containing a date field and a time field. I'm playing around with the idea of giving the date field its own table and giving the Time field its own table. It seems like this would allow me to use a query-based form to view records from many tables simultaneously based on date and/or time. However, I would like to know if I could achieve this same result with the dates and times stored with the rest of the information in each individual table.

I guess what I'm thinking about doing is called normalization, and the opposite is denormalization. I would like to know which one is easiest to reverse. Would it be easier for me to transition from option A (leaving the Dates in their own table and the Times in their own table) and denormalizing to option B (storing the dates and times directly in the tables in the records to which they belong), or would it be easier to switch from option B and normalize to option A?

Thanks.

[jzwp11]

I generally do not see a table with just dates/times. Dates/times are usually attributes of something else say an event or something like that. You say that you have many tables with date/time fields, perhaps there may be a better way to manage the data in those other many tables? Could you provide some background as to what data those other tables hold? Also, can you provide some details as to the process you are trying to model? That should help us guide you to a solution.

[NTC]

"creating a database with many tables"

This is a phrase that flashes a caution sign to my eye. Yes, normalization is the term - but it fundamentally it is practicality too.

Date/time is just another field type value. A field type itself it has no bearing on whether or not a new or separate table should be used.

Whether via sub forms/reports or joins in a query it is common to bring together data for display based on a common cross referencing value (such as date/time.) So this issue also does not drive a need for a new or separate table.

Normalization is fundamentally the categorization of the information into separate groups and establishing the cross referencing fields between them so that data is always properly related together.

[Pilotwings_64]

Thank you jzwp11 and NTC.

I actually got a solution on another forum. A union query is what I needed. With that, no special date or time tables are necessary for the functionality I want.

Thanks again.

[jzwp11]

I'm glad you found a solution, but I am still curious as to your other tables. I rarely have a need to use a union query. I have run into others who need them because they are linking to tables from other databases and they bring the data together in Access using a union query. I might be all wet and your tables could be fine, but I just wanted to make sure you had a sound table design.

[Pilotwings_64]

@jzwp11

Well the short answer is that this data base is actually kind of a general life database. In it I will track several basic things in my life. Although most of the tables will be linked by other criteria, it might be useful sometimes to look at everything major that I entered into my database in a given period of time. Most of the time it would be for sentimental reasons, but it also might help me see patterns.

For instance, a long time ago I noticed (without a database) that after reading a Stephen Hawking book my improvisation on the piano was(in my opinion) much better for about a week.

I imagine there are lots of these things in my life that just aren't obvious enough to me to have been noticed yet, and I think this database can help me with that.