Counting unique Session#

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I could use some help on how to count unique Session#. I've reviewed
several posts and tried to implement those suggestions but wasn't successful.

What I'm trying to do is come up with a way to count unique session#s from
the Sessions table. Here is my SQL:

SELECT SessionsRRCbyDate.Date, SessionsRRCbyDate.Name,
SessionsRRCbyDate.[Clinic Name], SessionsRRCbyDate.[Count of Visits],
SessionsRRCbyDate.PCT, Visits.[MR#], Visits.DOB, Visits.Abuse, Visits.ADHD,
Visits.Asthma, Visits.Obesity, Visits.Parent, Visits.DD, Visits.Dental,
Visits.Down, Visits.Trans, Visits.Vac, Visits.GER, Visits.Const,
Visits.Eczema, Visits.Premie, Visits.SzDO, Visits.Other, Sessions.[Session#]
FROM ((SessionsRRCbyDate INNER JOIN Residents ON
SessionsRRCbyDate.[Resident#] = Residents.[Resident#]) INNER JOIN Sessions ON
(SessionsRRCbyDate.[Session#] = Sessions.[Session#]) AND
(Residents.[Resident#] = Sessions.[Resident#])) INNER JOIN Visits ON
Sessions.[Session#] = Visits.[Session#]
WHERE (((SessionsRRCbyDate.Active)=Yes))
ORDER BY SessionsRRCbyDate.Name;

Thanks, Carol
 
Carol,

I would assume that the Sessions table is a list of all Sessions, and
that each Session is only recorded once in this table. Am I right? And
that the Session# is the Primary Key field in this table, right? So I
can't quite understand the question... Isn't the number of unique
Session# values the same as a simple count of the number of records in
the Sessions table? Maybe you could explain more what you want, or give
an example of what you are trying to achieve?

By the way, as an aside:
- 'name' and 'date' are both Reserved Words (i.e. have a special
meaning) in Access, and as such should not be used as the names of
fields or controls.
- It is not a good idea to use a # as part of the name of a field.
 
Sorry, I didn't explain very well. I'm trying to create a report that pulls
from a query that uses the SQL referenced below. I want to show all patient
sessions and diagnoses for each resident as well as a count of each session,
total number of patients seen, and average patients/session. All of the
information I need is in the query but the count function isn't counting
unique session# - it is counting all sessions.

Does this help explain better?

By the way, I didn't create this database, I'm being asked to help create a
report. I appreciate your explanation about Reserved Words and will pass
that on.

Thanks for helping, Carol

Steve Schapel said:
Carol,

I would assume that the Sessions table is a list of all Sessions, and
that each Session is only recorded once in this table. Am I right? And
that the Session# is the Primary Key field in this table, right? So I
can't quite understand the question... Isn't the number of unique
Session# values the same as a simple count of the number of records in
the Sessions table? Maybe you could explain more what you want, or give
an example of what you are trying to achieve?

By the way, as an aside:
- 'name' and 'date' are both Reserved Words (i.e. have a special
meaning) in Access, and as such should not be used as the names of
fields or controls.
- It is not a good idea to use a # as part of the name of a field.

--
Steve Schapel, Microsoft Access MVP

Hello, I could use some help on how to count unique Session#. I've reviewed
several posts and tried to implement those suggestions but wasn't successful.

What I'm trying to do is come up with a way to count unique session#s from
the Sessions table. Here is my SQL:

SELECT SessionsRRCbyDate.Date, SessionsRRCbyDate.Name,
SessionsRRCbyDate.[Clinic Name], SessionsRRCbyDate.[Count of Visits],
SessionsRRCbyDate.PCT, Visits.[MR#], Visits.DOB, Visits.Abuse, Visits.ADHD,
Visits.Asthma, Visits.Obesity, Visits.Parent, Visits.DD, Visits.Dental,
Visits.Down, Visits.Trans, Visits.Vac, Visits.GER, Visits.Const,
Visits.Eczema, Visits.Premie, Visits.SzDO, Visits.Other, Sessions.[Session#]
FROM ((SessionsRRCbyDate INNER JOIN Residents ON
SessionsRRCbyDate.[Resident#] = Residents.[Resident#]) INNER JOIN Sessions ON
(SessionsRRCbyDate.[Session#] = Sessions.[Session#]) AND
(Residents.[Resident#] = Sessions.[Resident#])) INNER JOIN Visits ON
Sessions.[Session#] = Visits.[Session#]
WHERE (((SessionsRRCbyDate.Active)=Yes))
ORDER BY SessionsRRCbyDate.Name;

Thanks, Carol
Click to expand...
Click to expand...
 
Sorry, I didn't explain very well. I'm trying to create a report that pulls
from a query that uses the SQL referenced below. I want to show all patient
sessions and diagnoses for each resident as well as a count of each session,
total number of patients seen, and average patients/session. All of the
information I need is in the query but the count function isn't counting
unique session# - it is counting all sessions.

Does this help explain better?

By the way, I didn't create this database, I'm being asked to help create a
report. I appreciate your explanation about Reserved Words and will pass
that on.

Thanks for helping, Carol

Steve Schapel said:
Carol,

I would assume that the Sessions table is a list of all Sessions, and
that each Session is only recorded once in this table. Am I right? And
that the Session# is the Primary Key field in this table, right? So I
can't quite understand the question... Isn't the number of unique
Session# values the same as a simple count of the number of records in
the Sessions table? Maybe you could explain more what you want, or give
an example of what you are trying to achieve?

By the way, as an aside:
- 'name' and 'date' are both Reserved Words (i.e. have a special
meaning) in Access, and as such should not be used as the names of
fields or controls.
- It is not a good idea to use a # as part of the name of a field.

--
Steve Schapel, Microsoft Access MVP

Hello, I could use some help on how to count unique Session#. I've reviewed
several posts and tried to implement those suggestions but wasn't successful.

What I'm trying to do is come up with a way to count unique session#s from
the Sessions table. Here is my SQL:

SELECT SessionsRRCbyDate.Date, SessionsRRCbyDate.Name,
SessionsRRCbyDate.[Clinic Name], SessionsRRCbyDate.[Count of Visits],
SessionsRRCbyDate.PCT, Visits.[MR#], Visits.DOB, Visits.Abuse, Visits.ADHD,
Visits.Asthma, Visits.Obesity, Visits.Parent, Visits.DD, Visits.Dental,
Visits.Down, Visits.Trans, Visits.Vac, Visits.GER, Visits.Const,
Visits.Eczema, Visits.Premie, Visits.SzDO, Visits.Other, Sessions.[Session#]
FROM ((SessionsRRCbyDate INNER JOIN Residents ON
SessionsRRCbyDate.[Resident#] = Residents.[Resident#]) INNER JOIN Sessions ON
(SessionsRRCbyDate.[Session#] = Sessions.[Session#]) AND
(Residents.[Resident#] = Sessions.[Resident#])) INNER JOIN Visits ON
Sessions.[Session#] = Visits.[Session#]
WHERE (((SessionsRRCbyDate.Active)=Yes))
ORDER BY SessionsRRCbyDate.Name;

Thanks, Carol
Click to expand...
Click to expand...
 
Thanks for the further explanation, Carol.

To be honest, I can't make too much sense of the query... but then, I'm
not familiar with your data and your database. The inclusion of the
Residents table in the query doesn't seem to serve any useful purpose.

But anyway, as I mentioned before, you will need to get the number of
sessions from the Sessions table. As you have quite correctly observed,
you won't get it from the query you have showed us so far. If you mean
the number of sessions for each Resident, well you have the Resident# in
the Sessions table, so this can be a Totals query. However, if this is
a report we are talking about, it will probably be easier to use Sorting
& Grouping in the report design, and do your statistics in the group
footer section of the report.
 
Back
Top