Connecting Tables

[James]

I am making a scheduling system. I have a table that will have many records
(One record is one date) and it has 20 fields (one field is one posistion/job
title). In 7 other tables I have the start times for each posistion (one
table is one day tblSundayPosition, tblMondayPosition, etc) (I cannot use one
table because there are more than 255 fields) and the start times. On a
report I need it to have the dates as the columns and the names of the
employees as the rows. (I know how to do that.) Instead of showing the
posistion the employee is in I need it to display the time in for that
position from the position tables (tblSundayPosition). If the date is on a
Sunday it will pull from tblSundayPosition, etc.

How do I accomplish the above connecting of tables? How do I display the
position in time on the report?

 

[Duane Hookom]

Stop... Read through some of the links regarding table design at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

Then read the reply to your previous posting.

 

[James]

Thanks for your help. I have read some of those white papers, but nothing is
new to me. I understand table design and structure. I don't see anything
wrong with the way I have the tables setup. Can you be more specific?

PS I am not woried about the date thing. I have that figured out!

 

[Duane Hookom]

You don't have a good handle on normalization if you have tables for Sunday,
Monday,...

 

[Amy Blankenship]

I think her point is that you should be thinking of multiple records in
fewer tables rather than having a ton of columns to handle what should be
records.

Perhaps you should reread the articles with the thought in your head that
you might not necessarily be applying the concepts you've read in the past
as thoroughly as you've thought?

-Amy

 

[Duane Hookom]

"her point"... ;-)
https://mvp.support.microsoft.com/profile=f0750c82-5540-4eac-a041-05c95e457287

I'd make a fairly large, homely "her".

 

[Amy Blankenship]

Aw, just shave the moustache and put on a bustier and away you go...

-Amy

 

[Tim Ferguson]

"her point"... ;-)
https://mvp.support.microsoft.com/profile=f0750c82-5540-4eac-a041-05c95
e457287

I'd make a fairly large, homely "her".

I guess she meant it as a compliment..!

Tim F

 

[Amy Blankenship]

Absolutely. My husband and I met one of our dearest friends by sitting in
the front row of Rocky. After Frankenfurter had Steve on stage and sat in
his lap, he introduced himself after the play. And the rest is history...

-Amy

 

[Duane Hookom]

Hmmm, just in time for halloween
http://www.christopher-robin.co.uk/sexy.htm.

Note to self: set Outlook reminder to shave legs and mustache.

 

[James]

I was thinking the "her" thing was wrong, but I wasn't sure! I thought mabey
the name wasn't pronounced "Dwayne"!

Anyways, I see what you are talking about know. I don't know what I was
thinking making all of those fields. I should have known better. Here is
what I have now:
----------------------------------------------
tblSchedule
scheduleId(pk), positionId(fk), employeeId(fk), date (relationship to
tblDate that has the dates for one year. This will allow scrolling through
the dates with out having to create a new record)

tblPosition
posistionID(pk), shiftID(fk), PositionName, DefaultTimeIn, etc.

tblEmployee
employeeID(pk), FirstName, etc.

tblShift
shiftID(pk), ShiftName

 

[Lynn Trapp]

Note to self: set Outlook reminder to shave legs and mustache.
Ah, you'll be the darling of Seattle this week.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Fred Boer]

Dear Lynn, et. al. :

Oh yes...I see the MVP summit is set for this week! I expect you'll all have
a blast gallivanting about! Don't worry, I'll do my best to help hold down
the fort while you are gone. Of course, I can only deal with the simplest of
questions; however, with many of you away, I *might* actually be able to
respond to the "simple" questions before you all jump on them first!

BTW, I hope there are hazing rituals for the new MVPs!! Being shaved bald,
perhaps? With pictures distributed? I've always been jealous of Jeff's thick
head of hair! <g>

Hoping you all have a great time!

Fred

 

[Amy Blankenship]

It's better, but hard to say if it's optimal without knowing more about how
you're using the data.

HTH;

Amy

 

[Duane Hookom]

Returned from a great Summit at Redmond... In honor of this thread (thanks
Amy) a fellow Access MVP created this shot.
http://www.hookom.net/images/IMG_1426.jpg

I'm glad they didn't gang up on me to hold me down, shave me, and dress me
up!

 

[Amy Blankenship]

Is there a way I can go to this Summit thing next year? If so, I volunteer
to organize ;-)

-Amy

 

[Duane Hookom]

Sorry, this was an MVPs only event. I can share with you that Office 12 will
support PDF creation in all applications. You will be able to easily print a
report to a PDF file.

 

[Fred Boer]

Dear Duane and Amy:

Nice to hear that the Summit was "great"! Thanks for the picture, although
it *is* a little tame...

Hey Amy! Next year, let's you and I crash the party! I'll bring some
shears - you bring the fishnet stockings!

Cheers!
Fred

 

[Amy Blankenship]

Dear Duane and Amy:

Nice to hear that the Summit was "great"! Thanks for the picture, although
it *is* a little tame...

Hey Amy! Next year, let's you and I crash the party! I'll bring some
shears - you bring the fishnet stockings!

hmmmm

 

[Amy Blankenship]

How hard can it be to become an MVP? ;-)

-Amy

Sorry, this was an MVPs only event. I can share with you that Office 12
will support PDF creation in all applications. You will be able to easily
print a report to a PDF file.