Exclude grouped Schools if one of their bookings is unconfirmed

[Ruegen]

I'll start over



I have a form with date range, value list combo box (2 options) and a listbox

the listbox currently has row source = "qry_whatever".

When you change the combo box, it changes the row source value and you pull in a different query.

One of the queries has a sub query - I can't just tell the row source to be strSQL because the query has a sub within it.



However if you are right and I can make two queries (still yet to learn how) that are using group fields that allow me to have one list with all confirmed and one list with confirmed/unconfirmed

no school can be in both list.

Later when you hit the button, I make a dao.recordset from the chosen query and run through it.

however at the moment that doesn't work and I just load in the listbox and grab the ID of each school instead with a for loop. Works the same but I have to have functions like getSchoolName() that I make which gathers info with a dlookup inside of it using the ID of the school (passed in)

I'd rather have it where I can go rs!SchoolName etc

[Ruegen]

The query with only confirmed bookings (using the other query). I would prefer this as an sql string rather than having to pull in a saved query like the one in the image. (all three queries I would like to convert to a string in vba - or at least have two)
Attachment 18731

[June7]

"I can't just tell the row source to be strSQL because the query has a sub within it."

I really don't know why not. But without being able to analyze the SQL and VBA, can't resolve.

[Ruegen]

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.

I don't even know how to get 'distinct' in a query - truth

[June7]

Can type it in the SQL View window or click into the query builder table section and choose UniqueValues Yes from the query Property Sheet.

[Ruegen]

Can type it in the SQL View window or click into the query builder table section and choose UniqueValues Yes from the query Property Sheet.

Thanks

I get the same number of records, does not work

My query below

I have schools with bookings that have 4 show, however if those same schools have bookings that are not confirmed then they should not show.

[Ruegen]

Here is the other query that shows either - schools with and without bookings that are confrimed.

[Ruegen]

Any of the schools in post #22 should not show in the query of post #21

[June7]

Not seeing the IN or NOT IN suggested criteria. Post SQL statement.

[Ruegen]

First query

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),"Fix Email","") AS Email, IsValidEmail([SchoolEmail]) AS Expr1
FROM (tblSchools INNER JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) INNER JOIN (tblShows INNER JOIN (tblBookings INNER JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID
WHERE (((tblBookings.StatusID)=4) AND ((tblBookings.BookingDate) Between [forms]![frmSchoolEndOfYearEmail]![DateFrom_Box] And [forms]![frmSchoolEndOfYearEmail]![DateUntil_Box]) AND ((tblShows.TheatreShow)=False))
GROUP BY tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),"Fix Email",""), IsValidEmail([SchoolEmail])
HAVING (((tblSchools.NewSchoolsID)<>9389))
ORDER BY tblSchools.SchoolName;

Second

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),"Fix Email","") AS Email
FROM (tblSchools INNER JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) INNER JOIN (tblShows INNER JOIN (tblBookings INNER JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID
WHERE (((IIf([statusid]=2 Or [statusid]=3 Or [statusid]=6 Or [statusid]=7 Or [statusid]=11 Or [statusid]=12,1,0))=1) AND ((tblBookings.BookingDate) Between [forms]![frmSchoolEndOfYearEmail]![DateFrom_Box] And [forms]![frmSchoolEndOfYearEmail]![DateUntil_Box]) AND ((tblShows.TheatreShow)=False))
GROUP BY tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),"Fix Email","")
HAVING (((tblSchools.NewSchoolsID)<>9389))
ORDER BY tblSchools.SchoolName;

Third (and the one that excludes schools by using the other)

SELECT qry_EOY_ConfirmedInWriting.NewSchoolsID, qry_EOY_ConfirmedInWriting.SchoolName, qry_EOY_NotConfirmed.Email
FROM qry_EOY_ConfirmedInWriting LEFT JOIN qry_EOY_NotConfirmed ON qry_EOY_ConfirmedInWriting.NewSchoolsID = qry_EOY_NotConfirmed.NewSchoolsID
WHERE (((qry_EOY_ConfirmedInWriting.NewSchoolsID)<>9389) AND ((qry_EOY_NotConfirmed.NewSchoolsID) Is Null))
ORDER BY qry_EOY_ConfirmedInWriting.SchoolName;

That's 3 queries that I'd rather have as a string for making a dao.recordset for code (and not having to keep 3 saved queries)

[Ruegen]

There is no IN statement.

I'd have to write one.

[June7]

See posts 6,8,11,13. That's all I can offer.

[Ruegen]

See posts 6,8,11,13. That's all I can offer.

I actually resolved the issue previously in this

https://www.accessforums.net/access/...set-46677.html

however I don't think it was the best method for it.

I wouldn't mind an object that acts a temp query, and the another temp object query uses that to query to query... if at all possible.

[Ruegen]

See posts 6,8,11,13. That's all I can offer.

would it be possible to make three strings and then refer to the string within the other string? Perhaps using QueryDef ?

[June7]

You mean 3 variables? Concatenate strings or variables, basically same (6 of 1, 1/2 dozen of another).

Could modify query with QueryDefs, no idea how that pertains to your situation.

Good Luck on this, I am way lost Ruegen.