Using a table for times and have multiple instances of the same table

[sprtrmp]

this is going to seem a lot like an earlier post, but is somewhat different:



I have a form in my DB that asks an employee four questions related to time:

What time did you leave your house?
what time did you arrive at work?
what time did you leave work?
what time did you arrive back home?

(because we actually care about that type of stuff...) I have one table that lists times throughout the day in 15 minute increments (0730, 0745, 0800... etc.) and this table is used on a form as the rowsource for four combo boxes that ask each of the above questions. The table has 2 fields (one containing a unique ID number, and one for the 15 minute time increments)
for normalization and resource conservation reasons, I decided to just store the ID number in the DB (up to 80+ employees filling out the same 4 questions 9 times out of each 2 week period, seems better to store a one-or-two digit number than the same 4 digit time over and over can build up data pretty quickly).

All of the above seems to work OK, but I am storing the ID numbers in fields (time1, time2, time3, time4, for example) that are set up in "realtionships" and in the query each with a separate iteration of the "Time" table. (Time, Time_1, Time_2, Time_3) with the ID primary key referenced to the foreign keys ("Time" thru "Time_3"). Does this make sense to anyone?
Is it "OK" to use this type of setup, with the same table related to 4 different fields on another table?
Is there a better way of accomplishing this?

The ultimate desire is to have the employee select the time they left home/arrived at work/left work/arrived at home from a list (we don't want them entering times on their own), and storing that data as efficiently as possible.

[Micron]

No, not really. Nothing wrong with storing the list id in the time fields though. You can do it this way, but it's the result of designing a db with a spreadsheet mindset. Data should be kept in rows, but you cannot with your design. To do so, you'd need one or more fields in the time table (unless there are linking tables we don't know about). That would be, for example, EmplID and WorkDate (NOT Date). There would be 4 rows per empl per date, one for each time. Your current method will require you to redesign this table if you add a new time stamp (e.g.BedTime) - not good. Your form design would require that as well, but that's a different issue.

[sprtrmp]

Right, it SEEMS to me like this is the "wrong" way to do it. it seems rather "cumbersome". but, do to a lack of experience, I am stuck for a better way to do it. Any suggestions that would still allow me to accomplish the requirement of selecting a time from a list?

The data relating to the times (and other info) WILL be STORED in rows. the TIMES on the "Time" table is more columnar though. there is a separate table that stores a date ID, employeeID, (and some other info) and then 4 fields for times. each of those fields uses the same table (tblTime) as the source of the data.
unfortunately, I am not able t post the DB at this point, but if I could it would be clearer. But, the "time" table is not where I am looking to stored the data for each person's 4 daily times. (sorry, I didn't make that clearer).

[Micron]

Well, your original question was around whether or not we could see a better way. Sounds like no. If what you create works, then it's not for me to criticize, but if you're looking for further input, maybe you can post a picture of the relationships window. It's difficult, if not impossible, to grasp the structure by your descriptions and I don't get the need for the table with the 4 time fields if you have something else as well:

there is a separate table that stores a date ID, employeeID, (and some other info) and then 4 fields for times

[sprtrmp]

Yes, great idea, why didn't I just do THAT to begin with?? This is what I am working with:

First, I have a Form that allows the user to input their 4 different times via Combo boxes ("DepartHome", "AFTPStart", "AFTPEnd", "ArriveHome"):


Each of the 4 Combo boxes uses the same table ("tblTime") as it's rowsource. That table is set up with an autoNumber ("TimeID_PK") and a list of times in 15 minute increments ("Time"):


Once the user is done filling out the above form, the data is saved to a table ("tblAFTPDetails") like so:

The table stores the "CrewID" number, which is just an "employee ID", the "DateID" and the "TimeID_PK" for each of the 4 times selected in the 4 combo boxes.

There is a second form, that administrative personnel use to list all of the employee's times by date, that lists these times:


Since I am storing the "TimeID_PK" that represents the 15 minute increment. for the purpose of THIS form, I had to work out a method of relating the stored "Time_ID" back to the actual time.
The way I figured that out was to set up a relationship between each of the 4 time fields ("DepartHome", "AFTPStart", "AFTPEnd", "ArriveHome") as foreign keys, and the "TimeID_PK" field on tblTime, thusly:


This works pretty well so far, as much as I can tell, but I have never run into this type of situation before, and it seems like there SHOULD be a better, more efficient way of doing this (but I am not really experienced enough to say whether there is or not).

Thanks for taking the time with this, let me know if anything is less-than-clear, and I apologize for being a little thick

Mike

[Micron]

Well, I said you were storing your data in columns, and you are in spite of what you believe. If in the future you had to add a time type (I gave BedTime as a perhaps silly example) and this required you to add a column to a table, the data is being stored in columns, not rows. If all you had to do was add a row to a table, then the design is 'more' normalized.
Here's how you avoid having to use a copy of a table 4x, or Heaven forbid, have to add another table for another type. You'd use a type table for the time types

tblTimeType
TypeID	autonum	1,2,3, etc.
TimeType	text	values AFTPStart, AFTPEnd, etc.

Data storage would look like this (I don't get DATEID_FK rather than an actual date, so I didn't put data in)

AFTP_PK	CREWID_FK	DATEID_FK??	TYPEID
74	75		1
74	75		2
74	75		3
74	75		4
75	63		1
75	63		2
75	63		3
75	63		4

Only the basic principle is being stressed here. If you were to do this, the target table would probably have the actual date and time rather than ID's for date and time. This would likely require some redesign, which I venture you're not willing to do. If that's your thinking, I'd sleep on that for one or two days to see if you feel different about it, because I can pretty much guarantee you that the deeper you go, the worse it will get when you start off with a problem design. In the meantime, here's some reading that I strongly suggest you check out:
http://allenbrowne.com/tips.html
http://allenbrowne.com/AppIssueBadWord.html
http://access.mvps.org/access/general/gen0012.htm
http://www.access-programmers.co.uk/...d.php?t=225837
http://www.utteraccess.com/wiki/index.php/Autonumbers
and last, but really FIRST read this page and explore the table design topics in the left nav pane
http://www.fmsinc.com/free/newtips/primarykey.asp

[sprtrmp]

AHHH, OK. I think I see what you are saying: the tblTimeType would identify what each time stands for... and in the actual table, instead of having 4 different columns for the different time categories, there is ONE for "Time" and a semi-related field that describes what that time applies to. I think I see the light coming on.

BUT, this DOES open the door to other questions (and I will admit, I am continuing to struggle with some of these ideas). 1) while i see the benefit of doing it this way, doesn't this require having to store more data? (4 lines for each crewmember per day, instead of just one). 2) is it better, resource-wise, to store an actual date/time (over and over again) or to have a separate table with a list of dates/times with a numeric key and to store the numeric key that represents that date/time? aren't you storing smaller chunks of data if you just store the key? ("DateID_FK" is, in fact, a foreign key related to a PK on another table that stores a list of "workdates" that tells which date these times were performed on. Again, I thought it was better to just store the ID and not the date)

And I didn't quite understand your earlier comment about storing data in rows, although I get it now... and I see my earlier error in thinking that I was storing things that way.

Although it will require me to go back and re-do a few things, I am perfectly willing to redesign as necessary: I KNOW I haven't quite gotten all of this figured out yet. I am a noob. that's why I am coming to this forum looking for advice on how to do it better.

[Micron]

1) yes, some of it repeated. That's better than having to redesign forms/queries/tables to suit a column item which should be in rows. In fairness to myself, it's a proposal I arrived at without benefit of seeing the whole picture.
2) Size wise, the db would be smaller IF the data types are not equal in terms of the number of bytes of space they require and your choice is to use the smaller. An Access db can be as large as 2Gb. I seriously doubt you will even come close to 1/4 of that. The size difference between the ID number and a date is a mere 4 bytes. I'd say it's better to store ID's as much as possible if you're selling the db and want to make it difficult to follow the data structure, otherwise, you'll make it tougher for yourself.
Gotta run. Make sure you research the links.