Access Database design w/ Dates and Times

[dmuroff]

Hi all,
I'd just like some suggestions on how to go about designing the tables for this database.

It is somewhat of a schedule system.

I have multiple users.

A user will enter a date and a form will display that date and the next 6 days (ex: if user enters 1/1/2004..it will show that and 1/2/2004, 1/3/2004 etc.

Now on every date they will select a time and then enter info such as: location, city,company, etc.

This info is unique to the time and date.

Here's what I have been thinking:

1) Userinfo table: (UserID, UserName)
2) Schedule table: ScheduleID, Time, Location, city, company
3) User Schedule table: ScheduleID, UserID, Date.

I've done a test run on this but hasn't worked out so great.

Is simply using 2 tables enough here? (UserInfo and Schedule table including date)

Any suggestions would be greatly appreciated. Thanks!

 

[SQLSister]

This isn;t an Access forum, but what I would do is store the data data as a datetime field and use a concatentation of the two form fields as the input. Then you only need two tables as the data and time are stored iteh schedule table.

Questions about posting. See faq183-874

 

[dmuroff]

Hi SQLsister,

This is a general discussion forum for DBMS packages and last time I checked Access was a DBMS.

Your advice here is greatly appreciated. 2 tables is definately the way to go with this one.

Thanks!

 

[SQLSister]

Sorry i thought I waw still in the SQL Server forum. My mistake.

Questions about posting. See faq183-874

 

[ceh4702]

You may at some time want to sort by the company or location so you need some way to make sure you arent mispelling company names. You may want a company or location table to keep from getting duplicates due to mis-spellings or slight variation in company names.

I dont know what you charge for your services or if you write contracts for your clients but sooner or later you need a billing system. You might need to know what their business arrangement is.

It just seems each schedule could have a schedule number and the schedule number and the date would combine to make up individual schedule dates. You might also need to know who in your organization the meeting is with. This might create a many to many relationship.

If you do not like my post feel free to point out your opinion or my errors.