timetable in a crosstab query

[Davide77]

Hallo,
I have a table with 4 fields: Organization, Start_Hour, End_Hour, Room. It is about conferences.

I made a crosstab query involving Organization, Start_Hour and Room. The organization as displayed data. Then I made the same with Organization, End_Hour and Room. Then I made a union query.
The result is a timetable where I see when a conference starts and when it ends, but I cannot see that between this two points the conference is still going on. Do you have a suggestion.

Here the code of the crosstab queries:

Query Start_Hour:

TRANSFORM First(tbl_Events.Org) AS FirstOfOrg
SELECT (Format([Start_Hour],"ddddd hh:nn&quot) AS Expr1
FROM tbl_Events
GROUP BY (Int([Start_Hour]*1440)), (Format([Start_Hour],"ddddd hh:nn&quot)
PIVOT tbl_Events.Room;

Query End Hour:

TRANSFORM First(tbl_Events.Org) AS FirstOfOrg
SELECT (Format([End_Hour],"ddddd hh:nn&quot) AS Expr1
FROM tbl_Events
GROUP BY (Int([End_Hour]*1440)), (Format([Start_Hour],"ddddd hh:nn&quot)
PIVOT tbl_Events.Room;

 

[hneal98]

What do your fields and data look like in your table?

 

[Davide77]

Organization is a text field
Start_Hour and End_hour are time fields
Room is Text field

They are placed in this order in the query resulting by the union of the two cross-tab query:

Time Room1 Room2 Room3 Room4 ....
08:00 Org1 Org2
08:30 Org3
09:00 Org1 Org3
10:00 Org2
10:30
....

In the first collumn there is the hour (union of start and end hour), the other columns are the rooms, and display the organization name (Org1, Org2,...). As you see in the table is shown when Org1 starts a conference and when it finisches it, but not during...

The original table is like this:

Room Start_Hour End_Hour Organization
Room1 8:00 9:00 Org1
Room2 8:00 10:00 Org2
Room3 8:30 9:00 Org3

 

[hneal98]

It sounds like you need a table that has ALL the times in it. Then do an update query with your union query that links by the time to update the rooms.