Exclude grouped Schools if one of their bookings is unconfirmed

[Ruegen]

I have a list of schools with many bookings



However if one of their bookings are unconfirmed I don't want them to show

So I want them to show if they have a booking confirmed however I don't want them to show if one of those bookings (say 1 out of 5) is unconfirmed.

I have an iif field that if status was 2 or 3 (meaning unconfirmed) then show the school and because I am using grouped I don't get double records of the same school. I don't need to see the bookings, just the school name.

This works well to show schools that have confirmed and unconfirmed however I want a second query list that shows only schools with all bookings confirmed and excludes any schools that happen to have one booking that is unconfirmed.

[thebigthing313]

What is your structure like? Assuming you have schools in one table, and their bookings in a separate one, you can use a SELECT QUERY with a Count aggregation.

Something similar to:

SELECT SchoolID, Count(BookingID) AS CountOfUnconfirmed
FROM tblSchools LEFT JOIN tblBookings ON tblSchools.SchoolID = tblBookings.SchoolID
WHERE BookingStatus IN (2,3)
GROUP BY SchoolID
HAVING Count(BookingID)=0

[NTC]

you must exclude all records of the school where there is one unconfirm - - before grouping. so you need a 'pre-query' (or queries) that accomplishes establishing the correct baseline record set before you do the aggregate query of grouping.... that pre-query would be along the lines of first finding all with an unconfirm, and then a no-match type query against those....I could imagine at least 2 queries to set up the baseline record set before doing the aggregate grouping query.....

[Ruegen]

you must exclude all records of the school where there is one unconfirm - - before grouping. so you need a 'pre-query' (or queries) that accomplishes establishing the correct baseline record set before you do the aggregate query of grouping.... that pre-query would be along the lines of first finding all with an unconfirm, and then a no-match type query against those....I could imagine at least 2 queries to set up the baseline record set before doing the aggregate grouping query.....

Yeah two queries, then one with both and where one matches the other it is excluded. I end up with two queries. The problem I have is I can't import the query with the prequery inside into a recordset because I get a parameter error because I'm not running the first query in VBA first.

Not sure how to get around that.

[Ruegen]

This would be the string of that Query with the prequery

However this won't work and if I want it to stay strictly a string I have no idea how to write it because I'd rather work without saved queries

strSQLConfirmed = "SELECT qry_EOY_ConfirmedInWriting.NewSchoolsID, qry_EOY_ConfirmedInWriting.SchoolName, qry_EOY_NotConfirmed.Email " & vbCrLf & _
"FROM qry_EOY_ConfirmedInWriting LEFT JOIN qry_EOY_NotConfirmed ON qry_EOY_ConfirmedInWriting.NewSchoolsID = qry_EOY_NotConfirmed.NewSchoolsID " & vbCrLf & _
"WHERE (((qry_EOY_NotConfirmed.NewSchoolsID) Is Null)) " & vbCrLf & _
"ORDER BY qry_EOY_ConfirmedInWriting.SchoolName;"

[June7]

Maybe like:

SELECT DISTINCT SchoolID FROM bookings WHERE NOT SchoolID IN (SELECT DISTINCT SchoolID FROM bookings WHERE confirmed = False);

[Ruegen]

Maybe like:

SELECT DISTINCT SchoolID FROM bookings WHERE NOT SchoolID IN (SELECT DISTINCT SchoolID FROM bookings WHERE confirmed = False);

I don't quite understand....

The schools that have both confirmed and unconfirmed need to be emailed, however those who have all confirmed need to be separated. Two lists basically.

[June7]

That gives you the list of schools that have only confirmations, no unconfirmed.

A query for the opposite would remove the NOT qualifier.

Two lists.

Open recordsets and loop through records to send individual emails or build one long addressees string.

[Ruegen]

That gives you the list of schools that have only confirmations, no unconfirmed.

A query for the opposite would remove the NOT qualifier.

Two lists.

Open recordsets and loop through records to send individual emails or build one long addressees string.

Yes, separate emails. The ones with unconfirmed get a second PDF for them to sign.

I will give distinct a try - I am pulling bookings for each school, some with status confirmed and some with status unconfirmed and some with both confirmed & unconfirmed.

[Ruegen]

That gives you the list of schools that have only confirmations, no unconfirmed.

A query for the opposite would remove the NOT qualifier.

Two lists.

Open recordsets and loop through records to send individual emails or build one long addressees string.

I thought about doing that, if I loop through each school though just to check if they have an unconfirmed that's a lot of work, I wanted two quick recordsets and go with either.

[June7]

I am confused. Why would you have to check if they have an unconfirmed? That's already been done in the two queries. The process would be:

1. open all confirmed query recordset, loop through records, send email(s)

2. open non-confirmed query recordset, loop through records, send email(s)

[Ruegen]

I am confused. Why would you have to check if they have an unconfirmed? That's already been done in the two queries. The process would be:

2. open non-confirmed query recordset, loop through records, send email(s)

This recordset may contain schools from 1. unless they are excluded. That is why I used a prequery - then any matching ID is excluded.

This works fine when you open a query and pull in two other queries, join the IDs and you get a query that lists only what you want.

However I try to pull this in as a string for dao.recordset vba stuff and I get parameter errors because of the prequery.

[June7]

No school should be in both recordsets.

1. query has only schools where ALL bookings are confirmed

2. query has only schools where ANY booking is unconfirmed

Your original query had all schools with an identifier that indicated if there was an unconfirmed booking. My two queries separate them and allow you to send different emails. Do you still want to send email 1 to schools that have confirmed bookings even though they also have unconfirmed?

I don't really understand issue with 'prequery'. Should be able to open a recordset based on a query with nested subquery.

[Ruegen]

No school should be in both recordsets.

1. query has only schools where ALL bookings are confirmed

2. query has only schools where ANY booking is unconfirmed

Your original query had all schools with an identifier that indicated if there was an unconfirmed booking. My two queries separate them and allow you to send different emails. Do you still want to send email 1 to schools that have confirmed bookings even though they also have unconfirmed?

I don't really understand issue with 'prequery'. Should be able to open a recordset based on a query with nested subquery.

The dao.recordset in vba would rely on either a string currentdb.openrecordset(string)

however if I build a string like in post 5 I am using queries. I want to remove saved queries, have a string but at the same time have prequeries in that string

[June7]

Sorry, I am lost.

If 'prequery' means a nested subquery, I don't understand why that is an issue. The queries I suggested work for me as source for recordset.