need help with queryies 1

[vz]

I am trying to create a database that is used to schedule 2 different conference rooms and I want to print a report that shows all the time blocks but only fills in the peoples names where they scheduled. I have 2 tables the 1st table has the fields:ReservedBy, StartTime, EndTime, Date, Room, and Confirmed the 2nd table I have has Id, and Time. Time holds all of the available time blocks which I want printed, and Starttime and endtime is the scheduled time block that I want inserted into the report where they are equal does anyone know how to do this????

 

[Kincaco]

It sounds like to you want to base your report on a query that has both tables and uses the time field from the second table.

Hope this helps.

 

[Ma3x323]

Hey vz,
It seems like you want time from the 2nd table to relate to start time on the 1st table. When you do this, it should have a "+" symbol on the side (subdatasheet) which would show you who is on there and info about that time block. You have to put more detail on :"Starttime and endtime is the scheduled time block that I want inserted into the report where they are equal ".
I don't quite seem to get what you are doing.

Hope this helps.
-Feryl -Feryl
--> Just a silly software engineer wannabee.

 

[vz]

I'm not sure what you mean about the + sign, I tried to create a query but I'm not getting what I need and i am not sure what to do with a plus sign. Start time is like if a meeting is supposed to satrt and 7:30 am and endtime is if the meeting ends at 8:00am. Can you walk me through this a little more?

 

[Ma3x323]

Hope you know about relationships. Well, anyways, on the third to the last icon on Access should have a relationship icon. Go into it. Show both tables by right clicking. Oh, make sure Time is a primary key. When you look at the relationships diagram, Time should be bold if it is primary. Click on Time and drag it to Start Time and press OK. Now you have a relationship. When this happens, there should be a plus sign as the first field (even though it is not a field). When you click on it, it should show the contents of the Time.

Hope this didnt confuse you.
Post more if you need more help. I'll try to do my best to help. -Feryl
--> Just a silly software engineer wannabee.

 

[vz]

I already had that relationship set. Here is what my tables look like:
ReservedBy Start Time End Time Date Room Confirmed
Violet 6:00 AM 6:30 AM 5/1/2001 Red Conference Room Yes
Scott 6:00 AM 6:30 AM 5/4/2001 Green Conference Room Yes
rick 6:30 AM 7:00 AM 5/1/2001 Red Conference Room Yes
violet 6:30 AM 7:00 AM 6/3/2001 Green Conference Room Yes
Bill 7:00 AM 8:00 AM 6/3/2001 Green Conference Room Yes
Violet 7:30 AM 8:00 AM 5/2/2001 Green Conference Room Yes
scott 8:30 AM 9:00 AM 6/1/2001 Red Conference Room Yes


ID Time TimeBlocks
1 6:00 AM 6:00 to 6:30 AM
2 6:30 AM 6:30 to 7:00 AM
3 7:00 AM 7:00 to 7:30 AM
4 7:30 AM 7:30 to 8:00 AM
5 8:00 AM 8:00 to 8:30 AM
6 8:30 AM 8:30 to 9:00 AM
7 9:00 AM 9:00 to 9:30 AM
8 9:30 AM 9:30 to 10:00 AM
9 10:00 AM10:00 to 10:30 AM
10 10:30 AM10:30 to 11:00 AM
11 11:00 AM11:00 to 11:30 AM
12 11:30 AM11:30 AM to 12:00 PM
13 12:00 PM12:00 to 12:30 PM
14 12:30 PM12:30 to 1:00 PM
15 1:00 PM 1:00 to 1:30 PM
16 1:30 PM 1:30 to 2:00 PM
17 2:00 PM 2:00 to 2:30 PM
18 2:30 PM 2:30 to 3:00 PM
19 3:00 PM 3:00 to 3:30 PM
20 3:30 PM 3:30 to 4:00 PM
21 4:00 PM 4:00 to 4:30 PM
22 4:30 PM 4:30 to 5:00 PM
23 5:00 PM 5:00 to 5:30 PM
24 5:30 PM 5:30 to 6:00 PM
25 6:00 PM 6:00 to 6:30 PM

What I want to do is display all of the time field in the 2nd table and place the reserved by names in the time slots that they match. Do u know how I might do that?

 

[JaneC]

In QBE grid add both tables. Link Time to start_time, click on link and change to include all records from timesavailable and matching timesbooked.
select id and time from second table and whatever you want from the first (reserved by etc).

Add any criteria, ie data or room.

This should work !

HTH

Jane

 

[vz]

That worked however if someone is scheduled over a half an hour it only places their name next to the start time I would like their names to be placed in every slot this is what its doing:
Time Reserved By StartTime EndTime
6:00 Bill 6:00 7:00
6:30
7:00 jane 7:00 7:30
7:30 joe 7:30 8:00
8:00 jack 8:00 9:00
8:30
etc...

I would like to have Bills name also placed at 6:30 and Jack at 8:30 is there any criteria I can use for this?

 

[melginger]

Maybe it'll sound stupid but where do you find QBE grid ?

Thank you

 

[vz]

It is the bottom screen where u drag all of the fields into when u create a query in design form

 

[melginger]

Thanks I'm a frenchie so, sometime I get lost with your English technical terms...