Counting unduplicate data

[Guest]

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!

 

[Marshall Barton]

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!

The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City

 

[Guest]

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!

The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City

 

[Guest]

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!

The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City

 

[Marshall Barton]

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!

The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City

Thanks! That worked perfectly! You really know your stuff! Unfortunately, I now have another problem. Say, instead of having one date, I have twenty (one for each class of a twenty-session class). I need to count all the clients that have any one of these dates within a specific time period. I know I can do this by simply writing out twenty WHERE clauses separated by or’s, but is there an easier way to do this. Some function that checks a group of fields at once so I don’t have to individually check each one.

If your services table has 20 date fields, your troubles are
just beginning. Repeating fields such as that are a major
violation of the rules of Relational Database Normalization.
Instead, you should have an attendance table with fields for
the person id, the class id and the date. This way you
would have 20 records (instead of 20 fields) and your Where
clause would only need to check a single field.

 

[Guest]

eileenjess wrote:

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!


The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City

Thanks! That worked perfectly! You really know your stuff! Unfortunately, I now have another problem. Say, instead of having one date, I have twenty (one for each class of a twenty-session class). I need to count all the clients that have any one of these dates within a specific time period. I know I can do this by simply writing out twenty WHERE clauses separated by or’s, but is there an easier way to do this. Some function that checks a group of fields at once so I don’t have to individually check each one.

If your services table has 20 date fields, your troubles are
just beginning. Repeating fields such as that are a major
violation of the rules of Relational Database Normalization.
Instead, you should have an attendance table with fields for
the person id, the class id and the date. This way you
would have 20 records (instead of 20 fields) and your Where
clause would only need to check a single field.

 

[Marshall Barton]

:

eileenjess wrote:

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!


The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City


Thanks! That worked perfectly! You really know your stuff! Unfortunately, I now have another problem. Say, instead of having one date, I have twenty (one for each class of a twenty-session class). I need to count all the clients that have any one of these dates within a specific time period. I know I can do this by simply writing out twenty WHERE clauses separated by or’s, but is there an easier way to do this. Some function that checks a group of fields at once so I don’t have to individually check each one.

If your services table has 20 date fields, your troubles are
just beginning. Repeating fields such as that are a major
violation of the rules of Relational Database Normalization.
Instead, you should have an attendance table with fields for
the person id, the class id and the date. This way you
would have 20 records (instead of 20 fields) and your Where
clause would only need to check a single field.

That sounds like a good idea, but this would create a subform within the subform I already have. Would this be okay? For each class session a client takes (subform) we need to know the dates for which they attended (another subform). Will this work? And, if so, how do I modify the code to work for nestled subforms?

Not that its a good idea, bu you can nest subforms to 7
levels. The restriction is that all except the lowest level
must be in Single view. If you don't like that, you can
have two continuous subforms next to each other on the main
form. Then, with the help of a hidden text box and a simple
line of code, you can arrange it so the attendance subform
stays in sync with the current person and class.

The only change to the query would be to use the attendance
table instead of the services table.

 

[Guest]

eileenjess wrote:

:

eileenjess wrote:

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!


The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City


Thanks! That worked perfectly! You really know your stuff! Unfortunately, I now have another problem. Say, instead of having one date, I have twenty (one for each class of a twenty-session class). I need to count all the clients that have any one of these dates within a specific time period. I know I can do this by simply writing out twenty WHERE clauses separated by or’s, but is there an easier way to do this. Some function that checks a group of fields at once so I don’t have to individually check each one.


If your services table has 20 date fields, your troubles are
just beginning. Repeating fields such as that are a major
violation of the rules of Relational Database Normalization.
Instead, you should have an attendance table with fields for
the person id, the class id and the date. This way you
would have 20 records (instead of 20 fields) and your Where
clause would only need to check a single field.

That sounds like a good idea, but this would create a subform within the subform I already have. Would this be okay? For each class session a client takes (subform) we need to know the dates for which they attended (another subform). Will this work? And, if so, how do I modify the code to work for nestled subforms?

Not that its a good idea, bu you can nest subforms to 7
levels. The restriction is that all except the lowest level
must be in Single view. If you don't like that, you can
have two continuous subforms next to each other on the main
form. Then, with the help of a hidden text box and a simple
line of code, you can arrange it so the attendance subform
stays in sync with the current person and class.

The only change to the query would be to use the attendance
table instead of the services table.

 

[Marshall Barton]

:

eileenjess wrote:

:

eileenjess wrote:

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!


The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City


Thanks! That worked perfectly! You really know your stuff! Unfortunately, I now have another problem. Say, instead of having one date, I have twenty (one for each class of a twenty-session class). I need to count all the clients that have any one of these dates within a specific time period. I know I can do this by simply writing out twenty WHERE clauses separated by or’s, but is there an easier way to do this. Some function that checks a group of fields at once so I don’t have to individually check each one.


If your services table has 20 date fields, your troubles are
just beginning. Repeating fields such as that are a major
violation of the rules of Relational Database Normalization.
Instead, you should have an attendance table with fields for
the person id, the class id and the date. This way you
would have 20 records (instead of 20 fields) and your Where
clause would only need to check a single field.


That sounds like a good idea, but this would create a subform within the subform I already have. Would this be okay? For each class session a client takes (subform) we need to know the dates for which they attended (another subform). Will this work? And, if so, how do I modify the code to work for nestled subforms?

Not that its a good idea, bu you can nest subforms to 7
levels. The restriction is that all except the lowest level
must be in Single view. If you don't like that, you can
have two continuous subforms next to each other on the main
form. Then, with the help of a hidden text box and a simple
line of code, you can arrange it so the attendance subform
stays in sync with the current person and class.

The only change to the query would be to use the attendance
table instead of the services table.


That sounds perfect! I haven't tried it yet, but it sounds straightforward enougn. I'm assuming if there are two subforms they can simply be linked by the client ID, correct? Thanks for all your help!

If you want one subform to be "linked" to another subform at
the same level (instead of nested), then add a text box
named txtLink to their parent form (class form). The
attendance subform's Link Master Fields property would be:
ClassID;txtLink
and the Link Child Fields property would be:
ClassID;PersonID

The line of code in the class subform's Current event would
be:
Me.Parent.txtLink = Me.PersonID

The person subform would be linked to the class form using
the ClassID field for both the link master an child
properties.

 

[Guest]

eileenjess wrote:

:

eileenjess wrote:

:

eileenjess wrote:

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!


The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City


Thanks! That worked perfectly! You really know your stuff! Unfortunately, I now have another problem. Say, instead of having one date, I have twenty (one for each class of a twenty-session class). I need to count all the clients that have any one of these dates within a specific time period. I know I can do this by simply writing out twenty WHERE clauses separated by or’s, but is there an easier way to do this. Some function that checks a group of fields at once so I don’t have to individually check each one.


If your services table has 20 date fields, your troubles are
just beginning. Repeating fields such as that are a major
violation of the rules of Relational Database Normalization.
Instead, you should have an attendance table with fields for
the person id, the class id and the date. This way you
would have 20 records (instead of 20 fields) and your Where
clause would only need to check a single field.


That sounds like a good idea, but this would create a subform within the subform I already have. Would this be okay? For each class session a client takes (subform) we need to know the dates for which they attended (another subform). Will this work? And, if so, how do I modify the code to work for nestled subforms?


Not that its a good idea, bu you can nest subforms to 7
levels. The restriction is that all except the lowest level
must be in Single view. If you don't like that, you can
have two continuous subforms next to each other on the main
form. Then, with the help of a hidden text box and a simple
line of code, you can arrange it so the attendance subform
stays in sync with the current person and class.

The only change to the query would be to use the attendance
table instead of the services table.


That sounds perfect! I haven't tried it yet, but it sounds straightforward enougn. I'm assuming if there are two subforms they can simply be linked by the client ID, correct? Thanks for all your help!

If you want one subform to be "linked" to another subform at
the same level (instead of nested), then add a text box
named txtLink to their parent form (class form). The
attendance subform's Link Master Fields property would be:
ClassID;txtLink
and the Link Child Fields property would be:
ClassID;PersonID

The line of code in the class subform's Current event would
be:
Me.Parent.txtLink = Me.PersonID

The person subform would be linked to the class form using
the ClassID field for both the link master an child
properties.

 

[Marshall Barton]

:

eileenjess wrote:

:

eileenjess wrote:

:

eileenjess wrote:

I have a form containing general client information (ex: name, address, etc)
and a subform within that form which is filled out each time the client
returns for a service. I am trying to create a query/report which gives the
total number of clients that have come from each city in out county.
However, if a client has come more than once (i.e. more than one subform is
filled out for them) the information on the parent form (ex: city) is counted
more than once. How sum the total number of people from a city w/o
duplicates? Can I perhaps base it on a the unique client ID? Any help would
be greatly appreciated. Thanks!


The query will need to join the client table to the a
nonduplicated services list. Then you can make it a Totals
type query to count the client with one or more service
entries. Here's the general idea:

SELECT C.City, Count(C.ClientID) As ClentCount
FROM Clients As C
INNER JOIN
(SELECT DISTINCT X.ClientID
FROM Services As X
WHERE X.ServDate Between [Start Date] And [End Date]
) AS S
ON C.ClientID = S.ClientID
GROUP BY C.City


Thanks! That worked perfectly! You really know your stuff! Unfortunately, I now have another problem. Say, instead of having one date, I have twenty (one for each class of a twenty-session class). I need to count all the clients that have any one of these dates within a specific time period. I know I can do this by simply writing out twenty WHERE clauses separated by or’s, but is there an easier way to do this. Some function that checks a group of fields at once so I don’t have to individually check each one.


If your services table has 20 date fields, your troubles are
just beginning. Repeating fields such as that are a major
violation of the rules of Relational Database Normalization.
Instead, you should have an attendance table with fields for
the person id, the class id and the date. This way you
would have 20 records (instead of 20 fields) and your Where
clause would only need to check a single field.


That sounds like a good idea, but this would create a subform within the subform I already have. Would this be okay? For each class session a client takes (subform) we need to know the dates for which they attended (another subform). Will this work? And, if so, how do I modify the code to work for nestled subforms?


Not that its a good idea, bu you can nest subforms to 7
levels. The restriction is that all except the lowest level
must be in Single view. If you don't like that, you can
have two continuous subforms next to each other on the main
form. Then, with the help of a hidden text box and a simple
line of code, you can arrange it so the attendance subform
stays in sync with the current person and class.

The only change to the query would be to use the attendance
table instead of the services table.


That sounds perfect! I haven't tried it yet, but it sounds straightforward enougn. I'm assuming if there are two subforms they can simply be linked by the client ID, correct? Thanks for all your help!

If you want one subform to be "linked" to another subform at
the same level (instead of nested), then add a text box
named txtLink to their parent form (class form). The
attendance subform's Link Master Fields property would be:
ClassID;txtLink
and the Link Child Fields property would be:
ClassID;PersonID

The line of code in the class subform's Current event would
be:
Me.Parent.txtLink = Me.PersonID

The person subform would be linked to the class form using
the ClassID field for both the link master an child
properties.


I'm confused about what you mean by PersonID and ClassID. Currently, I have a Client ID which links tha parent (Client) form and the (class) subform. Also, what is txtlink suppose to be? Will require some sort of data? Thanks for your help.

I think I'm confused about about which form is the main
form. Maybe I should have said that the Link Master property
for the attendance subform would be
ClientID;txtLink
and the LinkChild property:
ClientID;ClassID

The txtLink text box is only used for the LinkMaster
property and you set its value in the class subform's
Current event:
Me.Parent.txtLink = Me.ClassID
There is no other interaction with this text box.

 

[Guest]

I'm not sure I understand what you mean. I don't unserstand how this links
the wo subforms. Let's say the parent subform is called frmClient and the
two subforms are frmClass and frmAttendance. frmClient has a Client ID which
links it to both subforms. Similarly, frmClass and frmAttendance have a
classID with which I was hoping to link the two. I am on the right track or
am I just confused. Thank you so much for all your help.

 

[Marshall Barton]

I'm not sure I understand what you mean. I don't unserstand how this links
the wo subforms. Let's say the parent subform is called frmClient and the
two subforms are frmClass and frmAttendance. frmClient has a Client ID which
links it to both subforms. Similarly, frmClass and frmAttendance have a
classID with which I was hoping to link the two. I am on the right track or
am I just confused. Thank you so much for all your help.

We are finally on the same page. That's exactly what I was
trying to explain.

The way what I suggested works is that frmClass Links to the
mainform by using the link master/child properties set to
ClientID.

frmAttendance would link to the ClientID on the main form
too, but we also need to link frmAttendance to frmClass.
This is done by using the text box on the main form to
contain the ClassID of the current record in frmClass and
the line of code in frmClass's Current event sets the text
box's value to the current record's ClassID.

Therefore, frmAttendance's Link Master property is the two
values ClientID and the text box txtLink. The Link Child
property is of course the two fields ClientID and ClassID.