Count without duplicates

[Guest]

I have a report in Access 2000 called repBusRoster. It is grouped by bus stop
(showing all of the stops on the route). Under each stop it shows the people
who are using the stop and if they are using it inbound, outbound, or both.
Inbound and Outbound designations are stored in a text box and notated as
“Inâ€, “Outâ€, or “In/Outâ€. I need to count how many people are on the bus
inbound and how many are on the bus outbound. In the report footer I have
put =Abs(Sum([InOut]="In" Or [InOut]="In/Out")) to count inbound and would
use a similar thing to then count outbound. The problem is a person can be
assigned to two different stops on the same route and are therefore being
counted twice. I’m thinking I need to work in the persons unique ID and hide
duplicates but I have had no success with this. Any help with this would
greatly appreciated.

 

[Steve Schapel]

KC,

It appears to me that if your report's detail records allow for a person
to be listed more than once, then any aggregating within the report
itself will necessarily include them more than once. I think you will
need to use a different approach to obtaining your totals. One such
approach would be to use a DCount() function instead, based on a query
that only includes each person once. The specific details of this will
depend on your table stucture. In constructing this, you will also have
to decide how you want to handle a situation where the same person is
assigned to two stops, but their In/Out designation differs from one
stop to another.

 

[Guest]

Hi Steve,
Sorry for taking so long to get back to this. I ended up restructuring my
tables a bit because it was decided that we wanted to actually maintain
inbound and outbound stop assignments seperatly rather than just having an
in/out designation on the stop assignment. I still have duplicates because a
person can have more than one inbound stop and more than one outbound stop on
the same or different busses. However, I no longer have to contend with the
in/out aspect since they are seperated now. I've taken your advise to do the
count in a query. I am just not sure how to get the query not to show (and
count) a person twice if they are assigned to two stop on the same bus. My
tables look like this:

tblStudents
StudentID
Name
Address

tblRoute
RouteID
Distance
Vehicle
ArrivalTime

tblBusStops
Route ID
StopID
StopSequence
StopDescription
PickUpTime

tblInboundAssignments
StudentID
StopID
Mon
Tues
Wed
Thurs
Fri

tblOutboundAssignements
StudentID
StopID
Mon
Tues
Wed
Thurs
Fri

I have a query I made in design view that counts students on each bus.
SELECT tblRoutes.[Route ID], Count(tblInStopAssignments.StudentID) AS
CountOfStudentID
FROM (tblRoutes INNER JOIN Stops ON tblRoutes.[Route ID] = Stops.Route_ID)
INNER JOIN tblInStopAssignments ON Stops.ID = tblInStopAssignments.StopID
GROUP BY tblRoutes.[Route ID];
I just need to get rid of duplicates if a student appears on the same bus
twice. Apologies for the long post and time delay. Thanks for your help
KC
Access 2003

KC,

It appears to me that if your report's detail records allow for a person
to be listed more than once, then any aggregating within the report
itself will necessarily include them more than once. I think you will
need to use a different approach to obtaining your totals. One such
approach would be to use a DCount() function instead, based on a query
that only includes each person once. The specific details of this will
depend on your table stucture. In constructing this, you will also have
to decide how you want to handle a situation where the same person is
assigned to two stops, but their In/Out designation differs from one
stop to another.

--
Steve Schapel, Microsoft Access MVP

I have a report in Access 2000 called repBusRoster. It is grouped by bus stop
(showing all of the stops on the route). Under each stop it shows the people
who are using the stop and if they are using it inbound, outbound, or both.
Inbound and Outbound designations are stored in a text box and notated as
“Inâ€, “Outâ€, or “In/Outâ€. I need to count how many people are on the bus
inbound and how many are on the bus outbound. In the report footer I have
put =Abs(Sum([InOut]="In" Or [InOut]="In/Out")) to count inbound and would
use a similar thing to then count outbound. The problem is a person can be
assigned to two different stops on the same route and are therefore being
counted twice. I’m thinking I need to work in the persons unique ID and hide
duplicates but I have had no success with this. Any help with this would
greatly appreciated.

 

[Steve Schapel]

KC,

Top of my head here, but should be right I think...
SELECT tblBusStops.[Route ID], tblInboundAssignments.StudentID,
Count(tblInboundAssignments.StopID) AS Students
FROM tblBusStops INNER JOIN tblInboundAssignments ON tblBusStops.StopID
= tblInboundAssignments.StopID
GROUP BY tblBusStops.[Route ID],tblInboundAssignments.StudentID;

 

[Steve Schapel]

KC,

Oh dear, ignore the last post, not thinking straight! Sorry.

 

[Steve Schapel]

KC,

The easiest way to do this is probabley a two-step process. First make
a query to get the unique Students per Route...
SELECT DISTINCT tblBusStops.[Route ID], tblInboundAssignments.StudentID
FROM tblBusStops INNER JOIN tblInboundAssignments ON
tblBusStops.StopID = tblInboundAssignments.StopID

Then, do your tally in another query based on the first query...
SELECT YourFirstQuery.[Route ID], Count(YourFirstQuery.StudentID AS
Students
FROM YourFirstQuery
GROUP BY [Route ID]

 

[Guest]

This is Brilliant! It worked perfectly. I did the same thing for the
outbound. Thanks so much for your help and taking the time.
KC

KC,

The easiest way to do this is probabley a two-step process. First make
a query to get the unique Students per Route...
SELECT DISTINCT tblBusStops.[Route ID], tblInboundAssignments.StudentID
FROM tblBusStops INNER JOIN tblInboundAssignments ON
tblBusStops.StopID = tblInboundAssignments.StopID

Then, do your tally in another query based on the first query...
SELECT YourFirstQuery.[Route ID], Count(YourFirstQuery.StudentID AS
Students
FROM YourFirstQuery
GROUP BY [Route ID]

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
Sorry for taking so long to get back to this. I ended up restructuring my
tables a bit because it was decided that we wanted to actually maintain
inbound and outbound stop assignments seperatly rather than just having an
in/out designation on the stop assignment. I still have duplicates because a
person can have more than one inbound stop and more than one outbound stop on
the same or different busses. However, I no longer have to contend with the
in/out aspect since they are seperated now. I've taken your advise to do the
count in a query. I am just not sure how to get the query not to show (and
count) a person twice if they are assigned to two stop on the same bus. My
tables look like this:

tblStudents
StudentID
Name
Address

tblRoute
RouteID
Distance
Vehicle
ArrivalTime

tblBusStops
Route ID
StopID
StopSequence
StopDescription
PickUpTime

tblInboundAssignments
StudentID
StopID
Mon
Tues
Wed
Thurs
Fri

tblOutboundAssignements
StudentID
StopID
Mon
Tues
Wed
Thurs
Fri

I have a query I made in design view that counts students on each bus.
SELECT tblRoutes.[Route ID], Count(tblInStopAssignments.StudentID) AS
CountOfStudentID
FROM (tblRoutes INNER JOIN Stops ON tblRoutes.[Route ID] = Stops.Route_ID)
INNER JOIN tblInStopAssignments ON Stops.ID = tblInStopAssignments.StopID
GROUP BY tblRoutes.[Route ID];
I just need to get rid of duplicates if a student appears on the same bus
twice. Apologies for the long post and time delay. Thanks for your help
KC
Access 2003