Counting Unique entries (1 Viewer)

[hazell]

Good Morning,
I have a database which records the number of people who attend a number of different sessions in a week, and I have a query which counts the number of people who attend the sessions in a quarter. This gives me the total number of attendees, but I also need to count the number of individual people - some people go to more than one event. I think I need some sort of DCount expression, but am not sure where to put it. I have two tables that I am using - one called People and one called Provision. the Provision table selects the attendees from the People table. Any ideas?
thanks

 

[Uncle Gizmo]

I think you need 3 tables, tblPeople, tblSessions and tblSessionsPeople.

With this arrangement your query would be simple.

I think I would put the session date in tblSessionsPeople, but would need more info to be sure.

 

[hazell]

I think I made an error when I set this db up, because I thought I was being clever in using multi-values to select the people who attended the provision. now it is making creating the queries really really difficult. I can get a list of people who attended each provision, but it is one long string seperated by commas. I don't seem to be able to get the query to treat these as seperate pieces of info, if you know what I mean. Any ideas?

 

[Uncle Gizmo]

Bearing in mind that I have never use Multi value fields because the general consensus when they came out was that they would be problematic and it was best to avoid them. However I do understand that some people have use them successfully I have this inkling there is some code on Allen Browne site for using them. In my opinion you would be better off going for a design without them.


Feature
Multi-valued fields http://allenbrowne.com/Access2007.html

Good
Easy for beginner to choose multiple values in field.

Bad
The relational structure behind multi-valued fields is not accessible.
Developers will find it harder to handle complex data types. Any generic procedure you write must be capable of handling fields that contain fields. You cannot use a table with a multi-valued field in an IN clause (i.e. in another database.)
Problems with filters - kb 926701

 

[arnelgp]

use the fieldName.Value in your query.

 

[Uncle Gizmo]

Here is a good stackoverflow article on it:-

Copy data from lookup column with multiple values to new record Access 2007

 

[Uncle Gizmo]

This YouTube Video on Multi Value fields is Exceptional!