countif?? paramenter

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

Guest

tx date ref
60 09/26/05 3123456789
65 09/26/05 3123456789
63 09/26/05 3123456789
66 09/26/05 3123456789
37 09/26/05 905
24 09/24/05 123456
37 09/26/05 905
60 09/25/05 3123456789
60 09/25/05 3123456789

I am trying to create a query/report that would perform a count on the above
- this is an extract of 3 columns from a table with 10 columns. I want to
select distinct records where the len([Ref)= 10 and the dates are the same
else select all where Len([Ref])<>10 but I am not sure how to write the
select statement. In the sample table above the count would be 5. Any
suggestions help would be greatly appreciated. The source data is from an
excel spreadsheet
 
I believe the CountIf() function is an Excel function. You could use a
query to find all records where Len([YourField]) = 10, or any other
selection criteria.

To find out how many (?count) rows have the same value for [YourDateField],
you could use the Totals query button and aggregate by (GroupBy) that date
field.

By the way, you DON'T want to use the field name "date" -- Access treats
this as a reserved word. You will only confuse Access and yourself if your
field has this name.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
SELECT Sum(IIF(Len(Ref)=10,1,RefCount)
FROM
(SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date) as Source

As two queries; Save this query as qCountSource
SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date

Then use that as the source of this query
SELECT Sum(IIF(Len(Ref)=10,1,RefCount)
FROM qCountSource
 
I should learn to check for matching parentheses sets.

SELECT Sum(IIF(Len(Ref)=10,1,RefCount))
FROM
(SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date) as Source

As two queries; Save this query as qCountSource
SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date

Then use that as the source of this query
SELECT Sum(IIF(Len(Ref)=10,1,RefCount))
FROM qCountSource

John Spencer said:
SELECT Sum(IIF(Len(Ref)=10,1,RefCount)
FROM
(SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date) as Source

As two queries; Save this query as qCountSource
SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date

Then use that as the source of this query
SELECT Sum(IIF(Len(Ref)=10,1,RefCount)
FROM qCountSource


jer said:
tx date ref
60 09/26/05 3123456789
65 09/26/05 3123456789
63 09/26/05 3123456789
66 09/26/05 3123456789
37 09/26/05 905
24 09/24/05 123456
37 09/26/05 905
60 09/25/05 3123456789
60 09/25/05 3123456789

I am trying to create a query/report that would perform a count on the
above
- this is an extract of 3 columns from a table with 10 columns. I want
to
select distinct records where the len([Ref)= 10 and the dates are the
same
else select all where Len([Ref])<>10 but I am not sure how to write the
select statement. In the sample table above the count would be 5. Any
suggestions help would be greatly appreciated. The source data is from
an
excel spreadsheet
Click to expand...
Click to expand...
 
Thanks Jeff, a note though what I looking for is how to get a count of the
records in table where some 'ref' numbers, if it is a duplicate to be
excluded, and some, if the 'ref' numbers are duplicate to include them
thanks again
--
thanks as always for the help


Jeff Boyce said:
I believe the CountIf() function is an Excel function. You could use a
query to find all records where Len([YourField]) = 10, or any other
selection criteria.

To find out how many (?count) rows have the same value for [YourDateField],
you could use the Totals query button and aggregate by (GroupBy) that date
field.

By the way, you DON'T want to use the field name "date" -- Access treats
this as a reserved word. You will only confuse Access and yourself if your
field has this name.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


jer said:
tx date ref
60 09/26/05 3123456789
65 09/26/05 3123456789
63 09/26/05 3123456789
66 09/26/05 3123456789
37 09/26/05 905
24 09/24/05 123456
37 09/26/05 905
60 09/25/05 3123456789
60 09/25/05 3123456789

I am trying to create a query/report that would perform a count on the above
- this is an extract of 3 columns from a table with 10 columns. I want to
select distinct records where the len([Ref)= 10 and the dates are the same
else select all where Len([Ref])<>10 but I am not sure how to write the
select statement. In the sample table above the count would be 5. Any
suggestions help would be greatly appreciated. The source data is from an
excel spreadsheet
Click to expand...
Click to expand...
 
thanks John
--
thanks as always for the help


John Spencer said:
I should learn to check for matching parentheses sets.

SELECT Sum(IIF(Len(Ref)=10,1,RefCount))
FROM
(SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date) as Source

As two queries; Save this query as qCountSource
SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date

Then use that as the source of this query
SELECT Sum(IIF(Len(Ref)=10,1,RefCount))
FROM qCountSource

John Spencer said:
SELECT Sum(IIF(Len(Ref)=10,1,RefCount)
FROM
(SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date) as Source

As two queries; Save this query as qCountSource
SELECT YOURTABLE.Ref, YOURTABLE.Date, Count(Ref) as RefCount
FROM YOURTABLE
GROUP BY YOURTABLE.Ref, YOURTABLE.Date

Then use that as the source of this query
SELECT Sum(IIF(Len(Ref)=10,1,RefCount)
FROM qCountSource


jer said:
tx date ref
60 09/26/05 3123456789
65 09/26/05 3123456789
63 09/26/05 3123456789
66 09/26/05 3123456789
37 09/26/05 905
24 09/24/05 123456
37 09/26/05 905
60 09/25/05 3123456789
60 09/25/05 3123456789

I am trying to create a query/report that would perform a count on the
above
- this is an extract of 3 columns from a table with 10 columns. I want
to
select distinct records where the len([Ref)= 10 and the dates are the
same
else select all where Len([Ref])<>10 but I am not sure how to write the
select statement. In the sample table above the count would be 5. Any
suggestions help would be greatly appreciated. The source data is from
an
excel spreadsheet
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fiscal year 2
Crosstab Query based on Ordinal Values 6
Advanced problem using auto-number and datediff to find readmissio 1
Query with hours 1
Counting within a count group by query? 4
Find then highlight in yellow 6
How to count the occurrence? 3
OT: Selective HP drivers (apologies for length) 7

Back
Top