Multiple Rows Counted as 1 Record

[Guest]

I have a table with many prime numbers and each prime number may contain 1-5
rows (detail). I am trying to count each prime number as one but when I run
the report even the header still counts it anywhere from 1-5 insted of just 1
each. I tried to group by and then tally:1 but once I ungrouped everything
had a tally again so it didn't group them. I hope I am explaining this. Can
anyone help!!!

 

[Guest]

Post your query SQL.

 

[Guest]

SELECT Chatfield.[Rep Code], Chatfield.[Group Number], Chatfield.[Subgroup
Number], Chatfield.[Group Name], Chatfield.[County Code], Chatfield.[Next
Renewal Date], Chatfield.[High Level Product Code Description],
Chatfield.[Total Contract Count], Chatfield.[Street Address Line 1],
Chatfield.[Street Address Line 2], Chatfield.City, Chatfield.State,
Chatfield.[Zip Code], Chatfield.Broker
FROM Chatfield;


Basically I have 195 records that if I can count the actual Group Numbers
there are only 69. Is this posting in SQL?

 

[Guest]

Is this posting in SQL? Yes. But the query you posted is a
select query and can not count.

You said >>>> I am trying to count each prime number as one but when I
run
the report even the header still counts it anywhere from 1-5 insted of just
1 each.

What are you trying to count?

SELECT Chatfield.[Rep Code], Chatfield.[Group Number], Chatfield.[Subgroup
Number], Chatfield.[Group Name], Chatfield.[County Code], Chatfield.[Next
Renewal Date], Chatfield.[High Level Product Code Description],
Chatfield.[Total Contract Count], Chatfield.[Street Address Line 1],
Chatfield.[Street Address Line 2], Chatfield.City, Chatfield.State,
Chatfield.[Zip Code], Chatfield.Broker
FROM Chatfield;


Basically I have 195 records that if I can count the actual Group Numbers
there are only 69. Is this posting in SQL?

Post your query SQL.

 

[Guest]

I'm trying to count the "group numbers." When I made the report I put
everything except the products and contract counts into the header. Actually
I am trying to count the headers. At the top of the report I want only the
actual numbers of groups, not the actual numbers of product for each group.
I have 69 total group numbers but 195 actual rows becuase each group has more
than one product. So for group # ABC I only want it to count as 1 not as 3
since I have ABC with X product, ABC with Y product and ABC with Z product.

select query and can not count.

You said >>>> I am trying to count each prime number as one but when I
run
the report even the header still counts it anywhere from 1-5 insted of just
1 each.

What are you trying to count?

SELECT Chatfield.[Rep Code], Chatfield.[Group Number], Chatfield.[Subgroup
Number], Chatfield.[Group Name], Chatfield.[County Code], Chatfield.[Next
Renewal Date], Chatfield.[High Level Product Code Description],
Chatfield.[Total Contract Count], Chatfield.[Street Address Line 1],
Chatfield.[Street Address Line 2], Chatfield.City, Chatfield.State,
Chatfield.[Zip Code], Chatfield.Broker
FROM Chatfield;


Basically I have 195 records that if I can count the actual Group Numbers
there are only 69. Is this posting in SQL?

Post your query SQL.

:

I have a table with many prime numbers and each prime number may contain 1-5
rows (detail). I am trying to count each prime number as one but when I run
the report even the header still counts it anywhere from 1-5 insted of just 1
each. I tried to group by and then tally:1 but once I ungrouped everything
had a tally again so it didn't group them. I hope I am explaining this. Can
anyone help!!!

 

[Guest]

Try putting a text box in the report footer with this as Control Source ---
=Count([[Group Number])

I'm trying to count the "group numbers." When I made the report I put
everything except the products and contract counts into the header. Actually
I am trying to count the headers. At the top of the report I want only the
actual numbers of groups, not the actual numbers of product for each group.
I have 69 total group numbers but 195 actual rows becuase each group has more
than one product. So for group # ABC I only want it to count as 1 not as 3
since I have ABC with X product, ABC with Y product and ABC with Z product.

Is this posting in SQL? Yes. But the query you posted is a

select query and can not count.

You said >>>> I am trying to count each prime number as one but when I
run
the report even the header still counts it anywhere from 1-5 insted of just
1 each.

What are you trying to count?

SELECT Chatfield.[Rep Code], Chatfield.[Group Number], Chatfield.[Subgroup
Number], Chatfield.[Group Name], Chatfield.[County Code], Chatfield.[Next
Renewal Date], Chatfield.[High Level Product Code Description],
Chatfield.[Total Contract Count], Chatfield.[Street Address Line 1],
Chatfield.[Street Address Line 2], Chatfield.City, Chatfield.State,
Chatfield.[Zip Code], Chatfield.Broker
FROM Chatfield;


Basically I have 195 records that if I can count the actual Group Numbers
there are only 69. Is this posting in SQL?

:

Post your query SQL.

:

I have a table with many prime numbers and each prime number may contain 1-5
rows (detail). I am trying to count each prime number as one but when I run
the report even the header still counts it anywhere from 1-5 insted of just 1
each. I tried to group by and then tally:1 but once I ungrouped everything
had a tally again so it didn't group them. I hope I am explaining this. Can
anyone help!!!

 

[Guest]

You can get a count of the distinct group values in the Chatfield table with
the following query:

SELECT COUNT(*) AS NumberOfGroups
FROM (SELECT DISTINCT [Group Number]
FROM Chatfield);

If you save this query, as qryChatfieldGroupNumber say, you can then look up
the value as the ControlSource of a text box in the report, e.g.

=DLook("NumberOfGroups", "qryChatfieldGroupNumber")

Strictly speaking you should have a separate Groups table with one row per
group. This should be related to the Chatfield table on the Group Number
columns and referential integrity enforced. This prevents an invalid group
number value being inserted in the Chatfield table. You could then count the
number of groups in the Chatfield table with:

SELECT COUNT(*) AS NumberOfGroups
FROM Groups
WHERE EXISTS
(SELECT *
FROM Chatfield
WHERE Chatfield.[Group Number] = Groups.[Group Number]);

Ken Sheridan
Stafford, England

I'm trying to count the "group numbers." When I made the report I put
everything except the products and contract counts into the header. Actually
I am trying to count the headers. At the top of the report I want only the
actual numbers of groups, not the actual numbers of product for each group.
I have 69 total group numbers but 195 actual rows becuase each group has more
than one product. So for group # ABC I only want it to count as 1 not as 3
since I have ABC with X product, ABC with Y product and ABC with Z product.

Is this posting in SQL? Yes. But the query you posted is a

select query and can not count.

You said >>>> I am trying to count each prime number as one but when I
run
the report even the header still counts it anywhere from 1-5 insted of just
1 each.

What are you trying to count?

SELECT Chatfield.[Rep Code], Chatfield.[Group Number], Chatfield.[Subgroup
Number], Chatfield.[Group Name], Chatfield.[County Code], Chatfield.[Next
Renewal Date], Chatfield.[High Level Product Code Description],
Chatfield.[Total Contract Count], Chatfield.[Street Address Line 1],
Chatfield.[Street Address Line 2], Chatfield.City, Chatfield.State,
Chatfield.[Zip Code], Chatfield.Broker
FROM Chatfield;


Basically I have 195 records that if I can count the actual Group Numbers
there are only 69. Is this posting in SQL?

:

Post your query SQL.

:

I have a table with many prime numbers and each prime number may contain 1-5
rows (detail). I am trying to count each prime number as one but when I run
the report even the header still counts it anywhere from 1-5 insted of just 1
each. I tried to group by and then tally:1 but once I ungrouped everything
had a tally again so it didn't group them. I hope I am explaining this. Can
anyone help!!!

 

[Guest]

That's my problem. It still does a count on the detail (meaning 3 vs 1). I
have a feeling it can't be done.

Try putting a text box in the report footer with this as Control Source ---
=Count([[Group Number])

I'm trying to count the "group numbers." When I made the report I put
everything except the products and contract counts into the header. Actually
I am trying to count the headers. At the top of the report I want only the
actual numbers of groups, not the actual numbers of product for each group.
I have 69 total group numbers but 195 actual rows becuase each group has more
than one product. So for group # ABC I only want it to count as 1 not as 3
since I have ABC with X product, ABC with Y product and ABC with Z product.

Is this posting in SQL? Yes. But the query you posted is a
select query and can not count.

You said >>>> I am trying to count each prime number as one but when I
run
the report even the header still counts it anywhere from 1-5 insted of just
1 each.

What are you trying to count?

:

SELECT Chatfield.[Rep Code], Chatfield.[Group Number], Chatfield.[Subgroup
Number], Chatfield.[Group Name], Chatfield.[County Code], Chatfield.[Next
Renewal Date], Chatfield.[High Level Product Code Description],
Chatfield.[Total Contract Count], Chatfield.[Street Address Line 1],
Chatfield.[Street Address Line 2], Chatfield.City, Chatfield.State,
Chatfield.[Zip Code], Chatfield.Broker
FROM Chatfield;


Basically I have 195 records that if I can count the actual Group Numbers
there are only 69. Is this posting in SQL?

:

Post your query SQL.

:

I have a table with many prime numbers and each prime number may contain 1-5
rows (detail). I am trying to count each prime number as one but when I run
the report even the header still counts it anywhere from 1-5 insted of just 1
each. I tried to group by and then tally:1 but once I ungrouped everything
had a tally again so it didn't group them. I hope I am explaining this. Can
anyone help!!!

 

[John Spencer]

Alternative solution is to do the count in the report

-- Select View: Sorting and Grouping from the menu
-- Group on Group Number with a Group footer
-- Close
-- Add a control to the Group footer (name it txtCountGroup)
-- Set its Control source to =1
-- Running Sum to Overall
-- Add another control to the report's Report Footer (name this
txtUniqueCount)
-- Set its control source to = [txtCountGroup]

If you need this in the value in the top of the report,
-- Add another control
-- Set its control source to = [txtUniqueCount]

You can set the visible property of the controls (and sections) to No if you
don't want to see them.

You can get a count of the distinct group values in the Chatfield table
with
the following query:

SELECT COUNT(*) AS NumberOfGroups
FROM (SELECT DISTINCT [Group Number]
FROM Chatfield);

If you save this query, as qryChatfieldGroupNumber say, you can then look
up
the value as the ControlSource of a text box in the report, e.g.

=DLook("NumberOfGroups", "qryChatfieldGroupNumber")

Strictly speaking you should have a separate Groups table with one row per
group. This should be related to the Chatfield table on the Group Number
columns and referential integrity enforced. This prevents an invalid
group
number value being inserted in the Chatfield table. You could then count
the
number of groups in the Chatfield table with:

SELECT COUNT(*) AS NumberOfGroups
FROM Groups
WHERE EXISTS
(SELECT *
FROM Chatfield
WHERE Chatfield.[Group Number] = Groups.[Group Number]);

Ken Sheridan
Stafford, England

I'm trying to count the "group numbers." When I made the report I put
everything except the products and contract counts into the header.
Actually
I am trying to count the headers. At the top of the report I want only
the
actual numbers of groups, not the actual numbers of product for each
group.
I have 69 total group numbers but 195 actual rows becuase each group has
more
than one product. So for group # ABC I only want it to count as 1 not as
3
since I have ABC with X product, ABC with Y product and ABC with Z
product.

Is this posting in SQL? Yes. But the query you posted is
a
select query and can not count.

You said >>>> I am trying to count each prime number as one but
when I
run
the report even the header still counts it anywhere from 1-5 insted of
just
1 each.

What are you trying to count?

:

SELECT Chatfield.[Rep Code], Chatfield.[Group Number],
Chatfield.[Subgroup
Number], Chatfield.[Group Name], Chatfield.[County Code],
Chatfield.[Next
Renewal Date], Chatfield.[High Level Product Code Description],
Chatfield.[Total Contract Count], Chatfield.[Street Address Line 1],
Chatfield.[Street Address Line 2], Chatfield.City, Chatfield.State,
Chatfield.[Zip Code], Chatfield.Broker
FROM Chatfield;


Basically I have 195 records that if I can count the actual Group
Numbers
there are only 69. Is this posting in SQL?

:

Post your query SQL.

:

I have a table with many prime numbers and each prime number may
contain 1-5
rows (detail). I am trying to count each prime number as one but
when I run
the report even the header still counts it anywhere from 1-5
insted of just 1
each. I tried to group by and then tally:1 but once I ungrouped
everything
had a tally again so it didn't group them. I hope I am
explaining this. Can
anyone help!!!

 

[Guest]

Ken your SQL suggestion worked great!
I need to sort my data by county. I tried to use the DLookUp you gave me in
the "County" header but it gave me the "total Group Numbers". How would I
get the county totals?
I hope this post doesn't get lost since it is initially from yesterday.
Thanks! Judy