check for previous results in a table at the same time 1

[L3igh]

If I have a table like:

EventStart EventEnd FunctionName LocCode EventDate StatusID
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Chemistry Year 12 - Master Class CONFERENCE ROOM 03 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Maths Methods Year 12 - Master Class CONFERENCE ROOM 05 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Physics Year 12 - Master Class CONFERENCE ROOM 06 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Chemistry Year 11 - Master Class CONFERENCE ROOM 08 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Maths Methods Year 11 - Master Class CONFERENCE ROOM 09 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Specialist Maths Year 12- Master Class CONFERENCE ROOM 10 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM English Year 12 - Text First MEETING ROOM 04 30-Mar-12 {CF56AC74}
30-Mar-12 5:00:00 PM 30-Mar-12 7:00:00 PM English Year 12 - Language First - Master Class CONFERENCE ROOM 02 30-Mar-12 {CF56AC74}
30-Mar-12 5:00:00 PM 30-Mar-12 7:00:00 PM Biology Year 12 - Master Class CONFERENCE ROOM 04 30-Mar-12 {CF56AC74}
30-Mar-12 5:30:00 PM 30-Mar-12 7:30:00 PM Chemistry Year 12 - Master Class CONFERENCE ROOM 01 30-Mar-12 {CF56AC74}
30-Mar-12 5:30:00 PM 30-Mar-12 7:30:00 PM Psychology Year 12 - Master Class CONFERENCE ROOM 07 30-Mar-12 {CF56AC74}
30-Mar-12 7:00:00 PM 30-Mar-12 9:00:00 PM Chemistry Year 12 - Master Class CONFERENCE ROOM 03 30-Mar-12 {CF56AC74}
30-Mar-12 7:00:00 PM 30-Mar-12 9:00:00 PM English Year 11 - Master Class CONFERENCE ROOM 09 30-Mar-12 {CF56AC74}
30-Mar-12 7:00:00 PM 30-Mar-12 9:00:00 PM Maths Methods Year 12 - Master Class CONFERENCE ROOM 10 30-Mar-12 {CF56AC74}

Basically I have to set up a timetable display with 10 entries per page for 3 different pages. We have 10 conference rooms, 4 meeting rooms. due to the fact I can only display 10 entries per page due to screen resolution I need a method of displaying all the information.

the first query (page) would sort to display conference room 1 - 10 at the current time OR conference rooms and meeting rooms
the second query would display nothing OR meeting room bookings only when conference rooms 1 - 10 were being used.
and the third query would display any upcoming functions that haven't started yet.

I already have the third query easily enough, but the trick for me is sorting query 1 so that if all conference rooms were being used meeting room's would be displayed at the end of the query or not at all.
and setting up the second query to display meeting rooms only if all conference rooms were being used.

For the first query so far I have:

SELECT EventDetail.EventStart, EventDetail.EventEnd, EventDetail.FunctionName, EventDetail.LocCode, EventDetail.EventDate, EventDetail.StatusID
FROM (EventDetail INNER JOIN Event ON EventDetail.BookNo = Event.BookNo) INNER JOIN Company ON Event.CompanyID = Company.UniqueID
WHERE
(((EventDetail.EventStart)<Now()) AND ((EventDetail.EventEnd)>Now()) AND ((EventDetail.FunctionName)>'') AND ((EventDetail.EventDate)=Date()) AND ((EventDetail.StatusID)="{CF56AC74}"))
OR
(((EventDetail.EventStart)<Now()) AND ((EventDetail.EventEnd)>Now()) AND ((EventDetail.FunctionName)>'') AND ((EventDetail.EventDate)=Date()) AND ((EventDetail.StatusID)="{BE807F3E}"))
ORDER BY EventDetail.EventStart, EventDetail.EventEnd, EventDetail.LocCode, EventDetail.EventDate;


Thanks in advance for any help you can provide.

 

[L3igh]

Apologies for the table not quite being displayed correctly.
This is my first post by the way.

 

[dhookom]

L3igh,
Welcome to Tek-Tips.
Apparently you allow zero-length strings in you fields.

I don't have time to look at your complete question but I think you could make your query less complex using IN () like:

SELECT EventDetail.EventStart, EventDetail.EventEnd, EventDetail.FunctionName, 
 EventDetail.LocCode, EventDetail.EventDate, EventDetail.StatusID
FROM (EventDetail INNER JOIN Event ON EventDetail.BookNo = Event.BookNo)
 INNER JOIN Company ON Event.CompanyID = Company.UniqueID
WHERE EventDetail.EventStart <Now() AND EventDetail.EventEnd>Now() AND
 EventDetail.FunctionName>'' AND EventDetail.EventDate=Date() AND
 EventDetail.StatusID IN ("{CF56AC74}","{BE807F3E}") 
ORDER BY EventDetail.EventStart, EventDetail.EventEnd,
 EventDetail.LocCode, EventDetail.EventDate;

You might want to figure out how to use TGML. There is some information in the preview page.

Duane
Hook'D on Access
MS Access MVP

 

[L3igh]

Thanks very much for the head up with the IN() function, much cleaner.

And yes I realized about TGML shortly after I posted the question. I didn't notice the table was out of format until after I submitted the question:

[tt]EventStart [tab] EventEnd [tab][tab] FunctionName [tab][tab][tab][tab][tab] LocCode [tab] EventDate StatusID
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Chemistry Year 12 - Master Class[tab][tab][tab] CONFERENCE ROOM 03 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Maths Methods Year 12 - Master Class[tab][tab] CONFERENCE ROOM 05 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Physics Year 12 - Master Class[tab][tab][tab] CONFERENCE ROOM 06 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Chemistry Year 11 - Master Class[tab][tab][tab][tab] CONFERENCE ROOM 08 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Maths Methods Year 11 - Master Class[tab][tab] CONFERENCE ROOM 09 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM Specialist Maths Year 12- Master Class[tab][tab] CONFERENCE ROOM 10 30-Mar-12 {CF56AC74}
30-Mar-12 4:30:00 PM 30-Mar-12 6:30:00 PM English Year 12 - Text First MEETING ROOM 04 30-Mar-12 {CF56AC74}
30-Mar-12 5:00:00 PM 30-Mar-12 7:00:00 PM English Year 12 - Language First - Master Class CONFERENCE ROOM 02 30-Mar-12 {CF56AC74}
30-Mar-12 5:00:00 PM 30-Mar-12 7:00:00 PM Biology Year 12 - Master Class[tab][tab] CONFERENCE ROOM 04 30-Mar-12 {CF56AC74}
30-Mar-12 5:30:00 PM 30-Mar-12 7:30:00 PM Chemistry Year 12 - Master Class[tab][tab] CONFERENCE ROOM 01 30-Mar-12 {CF56AC74}
30-Mar-12 5:30:00 PM 30-Mar-12 7:30:00 PM Psychology Year 12 - Master Class[tab][tab] CONFERENCE ROOM 07 30-Mar-12 {CF56AC74}
30-Mar-12 7:00:00 PM 30-Mar-12 9:00:00 PM Chemistry Year 12 - Master Class[tab][tab] CONFERENCE ROOM 03 30-Mar-12 {CF56AC74}
30-Mar-12 7:00:00 PM 30-Mar-12 9:00:00 PM English Year 11 - Master Class[tab][tab] CONFERENCE ROOM 09 30-Mar-12 {CF56AC74}
30-Mar-12 7:00:00 PM 30-Mar-12 9:00:00 PM Maths Methods Year 12 - Master Class [tab] CONFERENCE ROOM 10 30-Mar-12 {CF56AC74}[/tt]

 

[dhookom]

I'm not sure why you don't just use a continuous form that allows users to scroll up and down through the records. You can add filters for date/time ranges, conference rooms, etc.

Duane
Hook'D on Access
MS Access MVP

 

[L3igh]

The timetable will be displayed on a TV screen in our foyer, it is supposed to be entirely automated.
The software we are using allows SQL when querying access databases but not much else like stored procedures and such.
It can take xml input but that would require constant updates in case of room changes, cancellations, etc...
Ill have a look into that I guess.
Thanks again for your time.
If you have any other suggestions feel free to chuck them my way.

 

[dhookom]

Queries can select the TOP 10 records or the TOP 10 that are not in the TOP 10. Are you having trouble sorting the records? Do you have a primary key field like an autonumber in the EventDetail table?

Duane
Hook'D on Access
MS Access MVP

 

[L3igh]

Yes There is a Primary Key field in the EventDetail table. How is that relevant sorry?
so to select the top 10 (I think i was just wording the question badly when searching for a method):
SELECT TOP 10 EventDetail.EventStart, EventDetail.EventEnd, EventDetail.FunctionName, EventDetail.LocCode, EventDetail.EventDate, EventDetail.StatusID
FROM EventDetail
GROUP BY EventDetail.EventStart, EventDetail.EventEnd, EventDetail.FunctionName, EventDetail.LocCode, EventDetail.EventDate, EventDetail.StatusID
HAVING (((EventDetail.FunctionName)>'') AND ((EventDetail.EventDate)=#4/1/2012#) AND ((EventDetail.StatusID) In ("{CF56AC74}","{BE807F3E}")))
ORDER BY EventDetail.EventStart , EventDetail.EventEnd, EventDetail.LocCode, EventDetail.EventDate DESC;

And how would you select the TOP 10 that are not in the TOP 10?

 

[dhookom]

Is there a reason for having the "GROUP BY" in the query? There are no aggregates in the query.

If you want the top 10 then use TOP 10. If you want the next 10 you select the TOP 10 where the primary key field is not in the primary key field of the TOP 10.

SELECT TOP 10 EmployeeID, LastName, FirstName
FROM tblEmployees
WHERE EmployeeID NOT IN 
(SELECT TOP 10 EmployeeID FROM tblEmployees ORDER BY LastName, FirstName)
ORDER BY LastName, FirstName;

Duane
Hook'D on Access
MS Access MVP

 

[L3igh]

The GROUP BY was requested by access after I used the IN()function.
Although your code seems a lot neater I can't seem to implement it correctly.

I figured out this way and it seems to work well with a lot of tests:

SELECT TOP 10 * FROM (
SELECT TOP 10 * FROM (
SELECT TOP 20 * FROM (
SELECT TOP 20 EventDetail.EventStart, EventDetail.EventEnd, EventDetail.FunctionName, EventDetail.LocCode, EventDetail.EventDate, EventDetail.StatusID FROM EventDetail GROUP BY EventDetail.EventStart, EventDetail.EventEnd, EventDetail.FunctionName, EventDetail.LocCode, EventDetail.EventDate, EventDetail.StatusID HAVING (((EventDetail.FunctionName)>'') AND ((EventDetail.EventDate)=#4/1/2012#) AND ((EventDetail.StatusID) In ("{CF56AC74-21A5-4C96-87FE-029B0D85C552}","{BE807F3E-0F97-4610-BF5C-FB3BB9BD260A}"))) 
ORDER BY EventDetail.EventStart, EventDetail.EventEnd, EventDetail.LocCode)  AS [%$##@_Alias] 
ORDER BY EventDetail.EventStart DESC , EventDetail.EventEnd)  AS [%$##@_Alias])  AS [%$##@_Alias]
ORDER BY EventDetail.EventStart, EventDetail.EventEnd;

If I find more time later I will definitely look into the last method you suggested as it seems far less complicated.

Thank you very much for your help

 

[dhookom]

The IN () clause would not cause the group by. Someone could probably help if you provided the primary key field from EventDetail.

Duane
Hook'D on Access
MS Access MVP

 

[L3igh]

Its called EventDetail.UniqueID
and I'm not too sure, the query without in and group by worked fine, and then when I implemented the IN() clause it complained until I added group by