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.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.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.
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.
This also works, but it shows all people (even with 0 events)Code: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;
Code:SELECT Security.Name, Security.Access, DCount("[CompanyName]","[Events]","Instr([Attendees],'" & [Name] & "')>0") AS AttendanceCount FROM Security WHERE (((Security.Access)="Attendee"));
Count(*) should work just as well.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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'
Code: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;
So it worked?
Q1 is an alias name for the inner nested query. It can be whatever you want.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
That requires a query that joins the Count query with a master dataset of all people, join type "Include all records from People ..."
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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?
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;
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
It's prompting me for EventDate (this field is in Events)
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;
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Works great! thanks, is it possible to show 0's for the people with no events?
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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#]?