count by date 1

[OCM]

Greetings,

Given the following fields;

DOS (date of service)
CODE eg. 747.12, 767.24, 777.56 etc

I am trying to find out (count) those CODES that start with 767 per DOS.
I only want one line per DOS.

TIA

Regards,


OCM

 

[Andrzejek]

Could you show the sample of the data you are working with?

Something like (probably wrong)
[pre]
DOS CODE
1/1/2010 747.12
1/1/2010 767.24
1/1/2010 777.56
2/2/2018 54.67
2/2/2018 767.00
2/2/2018 25.55
[/pre]
Outcome for 767[pre]
DOS COUNT
1/1/2010 1
2/2/2018 1
[/pre]
Am I close?


---- Andy

There is a great need for a sarcasm font.

 

[OCM]

Andy, that is correct.

Regards,

OCM

 

[Andrzejek]

Since you want to treat 767.24 as just 767, the values have to be converted to Integers (?) first, or rounded down to the whole number, so I would try something like:
[tt]
Select DOS, COUNT(CINT(CODE)) AS MYCOUNT
FROM MyTable
WHERE CODE BETWEEN 767 AND 767.99999
GROUP BY DOS[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

OCM,
You have been a member here for 16 years and started 65 threads. I'm not sure why your first post couldn't have been similar to Andy's first reply with some sample records and desired output?

Andy took the time to enter and format some sample records as well as suggest the expected output . This is awesome support from Andy (many other Tek-Tippers do the same).

A little extra effort on the part of Original Posters (0Ps) who ask questions would go a long way to improving understanding, shortening the resolution, and showing effort on the OP's part.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Andy,
Thank you for your input. That is exactly what I was trying to accomplish.
Sorry for not providing samples with my original post.

Regards,


OCM