Total Numbers

[dah]

Hi,

I have developed a database that gives me information on the number of people attending college workshops and the number of workshops each person has attended.

I want a total of individual people who attended workshops and then the total of people who attended multiple workshops. I ran my queries and got the information.


Here are my queries:
1) Total Attendance 10/01 to 5/02
2) Total Attendance 8/02 to 3/03
3) Total Attendance 10/01 to 3/03

I need to know why the numbers for 1 and 2 do not add up to query 3? I feel that some people attending workshops for #1 did not attend workshops for #2 but my boss feels that the numbers should add up. Am I doing the queries wrong?

Thanks,

dah

 

[FoFa]

You have a gap between 5/02 and 8/02, that your third query would pick up not in your first two queries.

 

[dah]

Hi,

I thought of that too but there are no workshops between 5/02 and 8/02.

 

[FoFa]

Yes, but is there some data it is picking up (Maybe your criteria is not selective enough).

 

[neileg]

How are you specifying your date criteria? When you enter 5/2/2003, Access interprets this as 5/2/3003 00:00:00

So if you are testing for 10/01 to 5/02 as Between 10/1/2003 and 5/2/2003 you may be excluding courses on 5/02 if these values include an h:m:s element. If you test for >10/1 and <6/02 you should pick these all up.

Quite how the include exclude works will depend on a number of matters, but I hope you understand my point. This might have a bearing on your discrepancies

 

[dah]

I guess I am not understanding what a query will do for me.

Year 1 = 8/01 to 5/02
Year 2 = 8/02 to 5/03

Overall of year 1 and 2 = 8/01 to 5/03

There is no data for 5/02 to 8/02 (no workshops, dates or individuals).

So, if Year 1 saw 10 individual people attend workshops and Year 2 saw 7 individual people attend workshops. What is my total over both years? The over both years query gives me 14. Maybe I am not understanding the data. I thought I understood but my boss feels Year 1 and Year 2 should total 17. I don't see how this could be since some people in Year 1 could have attended workshops in Year 2.

 

[dah]

Maybe it makes a difference if I am looking of workshop numbers between those dates.

For example:

Workshops 1 to 18 are Year 1 = 8/1/2001 to 5/1/2002
Workshops 19 to 65 are Year 2 = 8/1/2002 to 5/1/2003

So I want total individuals who attended any workshops (1 to 65) from 10/1/2001 to 5/1/2003.

I can get the multiple workshops ok because I enter <=18 for Year 1 and >=19 for Year 2.