group by

[Guest]

Hi,
I have a Table of Cases that are worked on by different people in the
office. Most of the cases have just one person assigned to them, but some
have multiple people. I want to be able to run a query to sum up data for
each individuals cases. When I run a query and Group By (Assigned Name) along
with a parameter entry of the persons name (so that I only see that persons
entries) I am getting different rows for each time that the person has worked
with another. For instance: I will see "John Smith", the next column has a
case count of 53 and the other columns give sums of other data. Then there is
a row for "John Smith/Jane Doe" with a case count of 1, a row for "John Smith
and Bob Johnson" with a case count of 1. Is there a way to consolidate all of
these rows so that I can just see one row with all cases that John Smith's
name appears in and get the summary data for all of them combined?
Note: my parameter looks like this:

Like "*" & [Enter Last Name] & "*"


Thank you

 

[Lynn Trapp]

Are you entering "John Smith/Jane Doe" in the same field for one record? If
so, then you will not be able to do what you want without extreme
difficulty. If you can have multiple people assigned to a given case, then
you need to have a table that can store the multiple instances of the
assignees and then you will be able to easily do what you want.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Yes I the multiple name entries are in the same field, I do not want to
change that, I just want to group all entries that contain a common name. I
will try making a separate table, but am not exactly clear on what you mean.
Thanks for the help, though

Are you entering "John Smith/Jane Doe" in the same field for one record? If
so, then you will not be able to do what you want without extreme
difficulty. If you can have multiple people assigned to a given case, then
you need to have a table that can store the multiple instances of the
assignees and then you will be able to easily do what you want.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Hi,
I have a Table of Cases that are worked on by different people in the
office. Most of the cases have just one person assigned to them, but some
have multiple people. I want to be able to run a query to sum up data for
each individuals cases. When I run a query and Group By (Assigned Name)
along
with a parameter entry of the persons name (so that I only see that
persons
entries) I am getting different rows for each time that the person has
worked
with another. For instance: I will see "John Smith", the next column has a
case count of 53 and the other columns give sums of other data. Then there
is
a row for "John Smith/Jane Doe" with a case count of 1, a row for "John
Smith
and Bob Johnson" with a case count of 1. Is there a way to consolidate all
of
these rows so that I can just see one row with all cases that John Smith's
name appears in and get the summary data for all of them combined?
Note: my parameter looks like this:

Like "*" & [Enter Last Name] & "*"


Thank you

 

[Guest]

What you have is a many to many relation ship. You will need 3 tables:

Employees
EmployeeID-PK
FirstName
LastName
Any other details

Cases
CaseID-PK
Case details

CaseAssignments
CaseID
EmployeeID
The primary key should be the combination of both, that way you will not
have the same person on the same case more than once.

Your current system is not normailized and will only give you trouble. Your
solution will fail the first time you hire Bob Smith. You can easily design
a subform for the data entry. With the above soution it becomes very easy to
get John Smith with 54 case and not mulitple line with John Smith in them. I
suspect with you current system you are typing in the names, so then you will
have a problem when somebody enter John Smtih, I would suggest looking into
combo box for the data entry to help eliminate typos.

Yes I the multiple name entries are in the same field, I do not want to
change that, I just want to group all entries that contain a common name. I
will try making a separate table, but am not exactly clear on what you mean.
Thanks for the help, though

Are you entering "John Smith/Jane Doe" in the same field for one record? If
so, then you will not be able to do what you want without extreme
difficulty. If you can have multiple people assigned to a given case, then
you need to have a table that can store the multiple instances of the
assignees and then you will be able to easily do what you want.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Hi,
I have a Table of Cases that are worked on by different people in the
office. Most of the cases have just one person assigned to them, but some
have multiple people. I want to be able to run a query to sum up data for
each individuals cases. When I run a query and Group By (Assigned Name)
along
with a parameter entry of the persons name (so that I only see that
persons
entries) I am getting different rows for each time that the person has
worked
with another. For instance: I will see "John Smith", the next column has a
case count of 53 and the other columns give sums of other data. Then there
is
a row for "John Smith/Jane Doe" with a case count of 1, a row for "John
Smith
and Bob Johnson" with a case count of 1. Is there a way to consolidate all
of
these rows so that I can just see one row with all cases that John Smith's
name appears in and get the summary data for all of them combined?
Note: my parameter looks like this:

Like "*" & [Enter Last Name] & "*"


Thank you