Schedule form...What type of query or queries?

[jdbit2byte]

I am trying to make a schedule form. The customer wants the top of the form
to have monday-friday (as columns). These columns will display the work
scheduled for each technician and these columns need to be matched with the
current week.
The technician's names will list down the left side of the form (as rows).
I am planning on having a seperate edit and add form.
I have an existing Tech table with all the employees and their info:
tbl_Tech
TechID
TechFName
TechLName
TechType
etc....

So far I think it would be best to make a schedule table. Along the lines of:
tbl_Schedule
TechID
ScheduledDate
JobNum 'Number used by company to reference jobs
JobName 'Customer Name
JobLocation 'Site address
StartTime 'Time techs will meet on given day to go to work

Then (and I'm still trying to plan it out) the form will detect all the
dates in the current week and populate the form based on what entries are in
the table for week. This is where I get lost because I don't know how to
make a whole week match up with a table of dates. Also the technicians need
to be grouped on the schedule depending on their technician type. So the
left side of the form needs to display the techs in a predetermined order and
place.
I am familiar with Select Queries mainly. Is there another type of query
that would be better? I am also up to any suggestions on the whole setup of
this form.
Thanks,
Justin

 

[Peter Hibbs]

A Flex Grid control would do what you want (if you are prepared to
write a fair bit of VBA code). Have a look at my Flex Grid demo
program at -
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hibbs,Peter S
for some examples.

Peter Hibbs.

 

[jdbit2byte]

Hey thx for the info I was not aware of Flex Grid controls but I have been
looking into them since you informed me of them. I can see a lot of uses for
the flex grid but I think this form would work with a cross-tab query and
would cut back on a lot of code writing that I don't understand yet (flex
grid).
I have a cross tab query that fills in my schedule form properly. The only
problem is the columns reference a week that is set in my query not the
current week. I can't figure how to make the query look up and populate the
current week from tbl_schedule.
thx again for the info I will keep researching flex grid controls because
they look really helpful and versatile.

Here is a quick visual of what I want:

(Dates = Current Week) Mon ///// Tues ///// Wed ///// Thurs /////
Fri

John Doe Baker Elem. ///// 7-11 ///// Safeco ///// AmPm /////
Baker Elem.
Jane Doe AmPm ///// 7-11 ///// AmPm ///// Gates /////
Google
Bob Seiger
Ziggy
Don Smith

I have an entry form to input which technicians need to go to which job on a
given day. The table then stores this scheduled day and I want to recall all
entries into my cross-tab form for the current or selected week.

 

[Peter Hibbs]

Justin

I don't know much about Cross Tab queries as I never use them. I still
think a Flex Grid control would be better for what you want.

Is the reason that you don't want to use a Flex Grid purely because
you are not comfortable with writing the necessary VBA code. If so I
might be prepared to write the code for you, looking at the table and
field set up in your original post it looks a relatively simple task.
If you are using A2000 or A2003 and you are interested send me an
e-mail with your zipped database file/s attached and I will have a
look, no guarantees mind. My e-mail address is (convert to a valid
e-mail address first) :-

peter dot hibbs at btinternet dot com

Peter Hibbs.

 

[jdbit2byte]

I think I'm going to check out your flex grid examples more because I would
like to learn how to use them. I agree, cross-tab queries don't really seem
to accomplish what I want and flex grid will. I will keep the offer in mind
though and I appreciate your help.

 

[Peter Hibbs]

Justin

As a matter of interest I was intrigued by your problem so I made up a
DB with a form using your table schema to do what you want. It is
quite small with only a few dozen lines of code. If you would like a
copy to get you started just drop me your e-mail address and I will
send it on.

Peter Hibbs.

 

[jdbit2byte]

lol I'm scratching my head right now.
Email: jdbit2byte at hotmail dot com
thx again for all your help