Query based on result of another query

[BRZ-Ryan]

Is that a multi-value field? I don't use multi-value fields.

I'm not sure what the wording is for this, but its a subform with a single combobox. When you select a name, another combobox appears below it allowing you to make another selection. I'd prefer to use a multi-select combobox if possible.

[June7]

Sounds like you are simply seeing multiple rows of a form set for datasheet view. Each selection creates a new record.

Unless any of these combobox dropdowns shows a checkbox next to each item in the list, you are seeing normal data entry control.

Should get a thorough understanding of multi-value field before deciding to use. I refuse to use.

[BRZ-Ryan]

Sounds like you are simply seeing multiple rows of a form set for datasheet view. Each selection creates a new record.

Unless any of these combobox dropdowns shows a checkbox next to each item in the list, you are seeing normal data entry control.

Should get a thorough understanding of multi-value field before deciding to use. I refuse to use.

I have the query from above working now so I am happy. This report is ran once a month, and the most attendees we will ever possibly have is less than 50. I am not experienced enough to properly 'normalize' the tables.

Just a question for you..

(People table is actually named Security, and Role is Access)

In your query, you had Security.ID in Count(), does it matter what is in here? I works when I replace it with another field from the table (Since ID does not exist). Do you see anything not proper with this? It works perfectly.

SELECT Name, Count(Security.Name) AS Events
FROM (SELECT Events.ID, Name, Attendees, Access
FROM Events, Security
WHERE (InStr([Attendees],[Name])>0) AND (Access="Attendee") AND (EventDate >= Date())) AS Q1
GROUP BY Name;

This also works, but it shows all people (even with 0 events)

SELECT Security.Name, Security.Access, DCount("[CompanyName]","[Events]","Instr([Attendees],'" & [Name] & "')>0") AS AttendanceCount
FROM Security
WHERE (((Security.Access)="Attendee"));

[June7]

Count(*) should work just as well.

[BRZ-Ryan]

Here's the final one I think I'll go with, I added order by. Is it OK to ORDER BY Count(*)? Also, what does the Q1 represent? I see the first "As Events" results in the Column heading being labeled 'Events'

SELECT Name, Count(*) AS Events
FROM (SELECT Attendees, Name, Access
FROM Events, Security
WHERE (InStr([Attendees],[Name])>0) AND (Access="Attendee") AND (EventDate >= Date())) AS Q1
GROUP BY Name
ORDER BY Count(*) Desc, Name;

[June7]

So it worked?

Q1 is an alias name for the inner nested query. It can be whatever you want.

[BRZ-Ryan]

So it worked?

Q1 is an alias name for the inner nested query. It can be whatever you want.

Yes, and thank you for all of your help. Is there any way to list people with 0's if they have no events with the query from my previous post?

[June7]

That requires a query that joins the Count query with a master dataset of all people, join type "Include all records from People ..."

[BRZ-Ryan]

What is a master dataset of people? Would this be my 'people' table? Your query runs a lot faster than the one that uses DCount so I prefer to use yours but I need it to show 0's. Also, I need to add a column for how many 'Monday' events each person has in the report, DatePart('weekday',[EventDate])=2?

[June7]

Yes, probably your 'people' table.

For the Monday count, try:

SELECT Name, Count(*) AS Events, Sum(IIf(Weekday([EventDate])=2,1,0)) AS CountMon
FROM (SELECT Attendees, Name, Access
FROM Events, Security
WHERE (InStr([Attendees],[Name])>0) AND (Access="Attendee") AND (EventDate >= Date())) AS Q1
GROUP BY Name
ORDER BY Count(*) Desc, Name;

[BRZ-Ryan]

It's prompting me for EventDate (this field is in Events)

[June7]

SELECT Name, Count(*) AS Events, Sum(Mon) AS CountMon
FROM (SELECT Attendees, Name, Access, IIf(Weekday([EventDate])=2,1,0)) AS Mon
FROM Events, Security
WHERE (InStr([Attendees],[Name])>0) AND (Access="Attendee") AND (EventDate >= Date())) AS Q1
GROUP BY Name
ORDER BY Count(*) Desc, Name;

[BRZ-Ryan]

Works great! thanks, is it possible to show 0's for the people with no events?

[June7]

Now join that query to the 'all people' table, join type 'Include all records from 'all people' ...

Really should use unique ID instead of Name in the grouping query then join to people table by joining on ID.

Also, Name is a reserved word and should not use reserved words as names.

[BRZ-Ryan]

What do you mean by join the query?

I have added an ID field to people table, I also want to make an attendance table to record who goes to what event, instead of storing people as a string in the events table. What do you think is a good layout for this table that will be easy for reporting?
[ID][Event#][AttendeeID#]?