Day and Time Format

[AccessHawaii]

I need help with this one guys. The user wants to track operating hours of different business and be able to locate business by hours of operation.

The catch is that it won't always be a straight shot like M-F 08:00 am to 05:00 pm, some days the operating hours may be different.

Does anyone know the best way of setting this up? Right now as I'm viewing it, it seems like I'll need seven different fields and also what would be the best way to format the fields, I was thinking day and time but I can't figure out how to format it that way. Any help anyone could provide, would be appreciated.

 

[JeremyNYC]

Preface: I started in to writing this and realized that this is, in fact, fairly complex. I'll continue with my thoughts, just because I'm already a decent way into it, but my real suggestion, if you don't get any responses from people with some serious experience setting up a database very similar to this (and not making stuff up, like I'm doing), would be to head to groups.google.com and search the archives of comp.databases.ms-access. I'm sure this has been answered there by some very experienced folk.

Now, on to the blather...

Well you don't want to use date fields, because a date field records a specific moment in time--there is always both a date and a time associated with it, so that part of the question is simple. I would say use a text field, and do some serious data validation to make sure all data are formatted -0:00. Use a 24 hour clock here, to capture am and pm.

I would suggest one of two ways:
1) Use 14 fields, an opening time and a closing time for each day. If the place doesn't open on particular days, leave the fields blank. If it stays open past midnight, that will just be shown by the closing time being less than 12:00.
2) Use a lookup table that has one record per shop per day of the week it's open. This would mean that some shops would have seven records, but not all of them. Each record would have two of the text fields I described, and an ID field keyed to the PK of the main table that holds the business info.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[cheerio]

I think Jeremy is on the right lines - I would favour the second of his options but with some changes.

First, text times limit your queries. Suppose you want a list of locations that are open at 8pm on a Thursday. If the times are stored as text it is very difficult to check whether 8pm falls within an open period.

Secondly, there is a possibility that a unit could close in the middle of the day, say for lunch. So the design may need to cope with more than one session in a day. Jeremy's second option could easily adapt to this by having as session column in the table.

Finally, if the locations are in more than one time zone you need to decide whether to record data in local time or in a standard time.

 

[AccessHawaii]

Thanks for the help. I got it working.