Query based on result of another query

[BRZ-Ryan]

I have one table that has a list of people, each with their own 'Role'.



I need to cycle through this people list, selecting people with the role of 'Attendee' and count how many times they appear in a field of another table 'Events'

In the Events table, I have a field (Attendees) that contains a list of names (delimited by carriage return)

I want to end up with a list of names with the # of times they were found in the Attendee fields in Events.

PeopleTable [Name],[Role]
Names with Role= 'Attendee'
Name1
Name2
name3
Name4

EventsTable [Company],[Date],[Attendees]
Record 1
Name2
Name3

Record 2
Name1
Name2
Name4

Record 3
Name1
Name2
Name3

I want to end up with
Name1 2
Name2 3
Name3 2
Name4 1

SELECT Count(*)
FROM [Events],[People]
WHERE (([Events].[EventDate] >= Date()) AND (InStr([Events].[Attendees],[People].[Name])>0)) 
IN
(
SELECT [People.Name]
FROM [People]
WHERE ([People].[Role] = "Attendee") 
)
ORDER BY [People].[Name];

Or

SELECT [People.Name]
FROM [People]
WHERE ([People].[Role] = "Attendee")
IN
(
SELECT Count(*)
FROM [Events],[People]
WHERE (([Events].[EventDate] >= Date()) AND (InStr([Events].[Attendees],[People].[Name])>0))
)
ORDER BY [People].[Name];

[June7]

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.

[BRZ-Ryan]

Heres my two working queries that need to be tied together.

Retrieve list of names to get counts of:

SELECT People.Name
FROM People
WHERE ([People].[Access] = "Attendee");

Count the persons events (Need to replace THENAME with the resulting name from the first query, for each name)

SELECT Count(*)
FROM Events
WHERE (InStr([Events].[Attendees],THENAME)>0);

[June7]

I was editing my post as you were posting. Might read it again.

[BRZ-Ryan]

Is this type of query possible? Or should I do this manually in VBA. This report will be ran once a month, so efficiency will not impact the day-to-day of the db, but I'd prefer to do it in SQL if possible.

[BRZ-Ryan]

How would I normalize this? Create a separate table to store attendees for each event?

[June7]

Did you try the suggested query?

Yes, a related table would be normalization. They only other approach is a multi-value field, which would look like a comma separated string but is different.

[BRZ-Ryan]

Yes, it runs but I get no results, I'm assuming its because my people table had no id field (I added the field to get it to run).

[June7]

So it does work and now you must decide whether to keep it or normalize db.

[BRZ-Ryan]

What steps are involved in normalizing. I understand it'll involve moving my attendees to a separate table but based on the functionality of my current setup, how can I make it work?

Now, when attendees are selected from a listbox, the selected names populate in a textbox (via VB code) and this textbox is bound to Attendees field in Events. This is how I get my carriage-return delimited list of attendees.

[June7]

Normalizing would mean creating and populating table and redesigning queries and forms for data entry, probably with a form/subform arrangement, and reports for data output.

[BRZ-Ryan]

Do you think this is something I can do without redoing the entire project? I kind of explained the current arrangement as far as the users and attendees are concerned

[June7]

Certainly everything concerning attendees will have to be modified. I have no idea how much you've already developed.

[BRZ-Ryan]

Theres really only 2 other things involving attendees and this is for reports. The guys at utteraccess showed me a way using comboboxes in a sub form to add attendees which I kind of like.

[June7]

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