design help - scheduling report

[Kelly]

I have my paper and pencil out, but nothing is coming to my mind, so I
thought I'd see if you guys could get me started. I am currently using
Outlook to schedule rooms at my school. I have a calendar for each room, and
I want to be able to print a weekly report for my maintenance staff to know
what rooms are being used that week. Well, Outlook doesn't do this, so I
thought I would export to Access and create a report that I can use each
week. So, since I'll be exporting from Outlook, I'm assuming I'll have a
table for each room.

What will my relationships look like?

Should the date be my primary id? I don't think that will work since I'll
have more than one entry per date? So, what should my primary id be?

I want the final outcome to be a report that has date/room number/start time
and end time/subject.

I have found some calendar programs that have been linked to this website,
but I don't need the whole calendar, just a weekly report. So, what do you
think?

 

[John W. Vinson]

I have my paper and pencil out, but nothing is coming to my mind, so I
thought I'd see if you guys could get me started. I am currently using
Outlook to schedule rooms at my school. I have a calendar for each room, and
I want to be able to print a weekly report for my maintenance staff to know
what rooms are being used that week. Well, Outlook doesn't do this, so I
thought I would export to Access and create a report that I can use each
week. So, since I'll be exporting from Outlook, I'm assuming I'll have a
table for each room.

Your assumption IS WRONG. Storing data - room numbers - in a tablename
is never a good idea.

Instead, you would have *one* schedule table with a field for the
room. If you argue that you need to have a separate table for each
export to Outlook, I'll argue that you can export from a Select Query
just as easily as from a Table.

What will my relationships look like?

Should the date be my primary id? I don't think that will work since I'll
have more than one entry per date? So, what should my primary id be?

I want the final outcome to be a report that has date/room number/start time
and end time/subject.

Schedule
ClassDate Date/Time <Primary Key> (don't use Date as a fieldname)
RoomNumber Text <Primary Key>
StartTime Date/Time <Primary Key>
EndTime Date/Time
SubjectID Long Integer

Subjects
SubjectID Long Integer or Autonumber <Primary Key>
Subject Text

Note that a Date/Time field actually is stored as a number - a count
of days (dates) and fractions of a day (times) since midnight,
December 30, 1899; as such you might just want one StartTime field
such as #2/12/2007 10:30am# rather than storing the date in one field
and the time in another. Note also that a Primary Key can consist of
up to TEN fields - in this case two or three (depending on how you
store the start time) will uniquely identify the record.

I have found some calendar programs that have been linked to this website,
but I don't need the whole calendar, just a weekly report. So, what do you
think?

No opinion, since I'm not using a website but a Newsgroup, and I have
no idea what calendar programs you're looking at.

John W. Vinson [MVP]

 

[Kelly]

Instead, you would have *one* schedule table with a field for the

room. If you argue that you need to have a separate table for each
export to Outlook, I'll argue that you can export from a Select Query
just as easily as from a Table.

John, this makes sense and I will try it if it's the only way, but what I'm
actually wanting to do is export from Outlook into Access. I currently have
a calendar in Outlook for each room, and I don't want to have to start from
scratch if I don't have to. I already played around with exporting from
Outlook and it makes a table for each room, so I was trying to figure out how
to link the tables and not make any more work than is necessary. Maybe it's
just not possible.

 

[John W. Vinson]

John, this makes sense and I will try it if it's the only way, but what I'm
actually wanting to do is export from Outlook into Access. I currently have
a calendar in Outlook for each room, and I don't want to have to start from
scratch if I don't have to. I already played around with exporting from
Outlook and it makes a table for each room, so I was trying to figure out how
to link the tables and not make any more work than is necessary. Maybe it's
just not possible.

I know it's possible from other discussions on this thread, but I've
never actually done it so I don't know how! Sorry.

Try searching http://groups.google.com; use the Advanced Search, limit
the groups to microsoft.public.access.*, and search for Outlook
Automation.

John W. Vinson [MVP]