Query based on result of another query

[June7]

Did you try:

SELECT AttendeeReportQueryALL.*, People.Name


FROM AttendeeReportQueryAll RIGHT JOIN People ON AttendeeReportQueryAll.Name = People.Name


BTW, Name is a reserved word. Really should avoid reserved words in naming.

[BRZ-Ryan]

It is doing the same thing, except instead of all of the People table data, it only shows People.Name (to the right of my Name Count CountMon). I try to use People.Name as the Source for the name textbox but it gives me that same error as above.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved in issue.

[ItsMe]

I didn't read the whole thread but when June mentioned reserved name it made me think that something like this would be more appropriate.

SELECT AttendeeReportQueryALL.*, People.[Name]
 FROM AttendeeReportQueryAll RIGHT JOIN People ON AttendeeReportQueryAll.Name = People.[Name]

[BRZ-Ryan]

Thx all for your timeTestRpt.zip

[June7]

I can't figure out why that error but I expect it has something to do with nested query in AttendeeReportQueryAll.

Why show both name fields on the report? Only pull one of the fields into query. If you want all the related info from People table then pull those fields into the query design.

SELECT People.ID, People.Name, AttendeeReportQueryAll.Events, AttendeeReportQueryAll.CountMon, People.Email, People.Access
FROM AttendeeReportQueryAll RIGHT JOIN People ON AttendeeReportQueryAll.Name = People.Name
ORDER BY AttendeeReportQueryAll.Events DESC , AttendeeReportQueryAll.Name;

Also, don't see need for the sorting in AttendeeReportQueryAll.


Conventional approach with names is to store name parts in separate fields. As you have it now, can't sort by last name.

[ItsMe]

I was able to get rid of the error by creating alias' for the Name fields. Seems the AttendeeReportQueryAll query is creating expressions that are somehow conflicting with the report's ability to bind its controls. Creating an alias for the expression field alone was not enough. In the end, I felt as though there must be a better approach than placing alias' everywhere (Like not including so many fields like June is suggesting?). Don't know if this info helps. FWIW anyway.....

[BRZ-Ryan]

Why show both name fields on the report? Only pull one of the fields into query. If you want all the related info from People table then pull those fields into the query design.

Conventional approach with names is to store name parts in separate fields. As you have it now, can't sort by last name.

I didn't want\need both names, that is how the query designer made the report when I selected the query. But it looks/works great now, thanks much for your time and patients.

Yes, right now I have 2 versions of the db front-end, one experimental copy using comboboxes for name selection (individual) and one with a listbox\textbox (multi in one field). If I can get all of my reporting to work properly with the combo method I will go with that. However, the end-users are ok with first-name sorting and there should not be a need for sorting beyond what you provided here. I'm not comfortable with the combo method because it is more confusing to me right now. This was my first project/experience with Access. I have years of programming eduction\experience in VB\Pawn\C but I've never touched Access. I am interested in learning more though. What do most users use to write SQL? Manually in notepad or w\e or with the Design view? I feel like design view could help me out but I'm still not sure how to properly use it.

Also, I am working on another project where I have a similar objective and I did use an independent table for attendance. Learning more every day.

[June7]

The query builder Design View will help you build most of the queries you will need. Even if nested queries are desired the builder can help. Build the inner query first. Build outer query that uses the first query. Copy/paste the SQL of first query into the second query. I use copy/paste a lot in the SQL View window. Some queries must be constructed in SQL View and won't even display in Design View - UNION comes to mind.

[BRZ-Ryan]

What's the principle behind nested queries? Is that querying from another query? And whats the basic layout?

SELECT A FROM (SELECT B FROM C WHERE A=1)? In experimenting with these, I get confused on where to reference the table in the FROM..ie, which FROM do I put the tables.

[June7]

Nested queries can involve several tables or one table.

A nested query is often utilized when selection of records depends on values in other records of same table. Another use is to calculate with values of sequential records. Review http://allenbrowne.com/subquery-01.html

[BRZ-Ryan]

This would be so easy with an aggregate query if the data were normalized. Even a multi-value field would be manageable and I don't like multi-values. A delimited string is the worst arrangement for this sort of data analysis.

I could get this to work only with a unique ID field in each table. I used an autonumber type.

SELECT Name, Count(People.ID) AS CountAttendance
FROM (SELECT Events.ID, People.ID, Name, Attendees, Role
FROM Events, People
WHERE InStr([Attendees],[Name])>0 AND Role="Attendees") AS Q1
GROUP BY Name;


BTW, Name is a reserved word and should not use reserved word as fieldname.

I've since went with the normalized method and am working on the counts (total and mondays), can someone please assist? My numbers are coming out too high, and I can't figure out why. Can someone please review and explain what was wrong and how to fix?

SELECT LastName, Count(People.ID) AS CountAttendance, Sum(Q1.Mon) AS CountMon
FROM (
SELECT People.ID, People.LastName, People.Role, IIf(Weekday([Events.EventDate])=2,1,0) As Mon
FROM Events, People, Attendance
WHERE (((Attendance.EmployeeID=People.ID) AND (People.Role="Attendee")) AND ([EventDate] BETWEEN #1/1/2013# AND #1/1/2014#))
) AS Q1
GROUP BY LastName;

[June7]

Do you want to provide latest version of the db?

[BRZ-Ryan]

Thanks..and can you explain the routine in words on how you would do each count?

dbTest.zip

[BRZ-Ryan]

Ok, I have the counts working. Now I just need to get it to show people with 0 counts. I also noticed I have Attendee mis-spelled for one of the people in tblPeople, so if your test counts are off that's why.

SELECT Q1.LastName, Count(tblPeople.ID) AS CountAttendance, Sum(Q1.Mon) AS CountMon

FROM (SELECT tblPeople.ID, tblPeople.LastName, tblPeople.Role, IIf(Weekday([tblEvents.EventDate])=2,1,0) AS Mon 

FROM tblEvents, tblPeople, tblAttendance 

WHERE ((tblAttendance.EmployeeID=tblPeople.ID) AND (tblEvents.ID = tblAttendance.EventID) AND (tblPeople.Role="Attendee") AND ([tblEvents].[EventDate] BETWEEN #01/01/2013# AND #01/01/2015#))) AS Q1

GROUP BY Q1.LastName;