Sequential Count of a value based on date of entry (Need Help)

[AccessHope]

Cust #	Call Date	Count
123	1/1/2020	3
123	12/20/2019	2
123	11/14/2019	1
123	1/24/2020	4
567	12/21/2019	1
567	1/7/2020	2
987	11/8/2019	1
987	1/29/2020	3
987	12/23/2019	2

This above is my goal. I have customer numbers that called on a certain date. My goal is to have a count of the customers but 1 being the earliest call date. SQL or VBA Is not my specialty but I can comprehend and if shown an example relating to something I have done. How do I go about this?

[pbaldy]

See if this helps:

http://allenbrowne.com/ranking.html

[CJ_London]

this is another method

SELECT myTable.Cust, myTable.CallDate, Count(myTable_1.Cust) AS CountOfCust
FROM myTable INNER JOIN myTable AS myTable_1 ON myTable.Cust = myTable_1.Cust
WHERE (((myTable_1.CallDate)<=[myTable].[CallDate]))
GROUP BY myTable.Cust, myTable.CallDate

[AccessHope]

I just tried this and getting a syntax error. Is mytable_1 a new table/query being created?

this is another method

SELECT myTable.Cust, myTable.CallDate, Count(myTable_1.Cust) AS CountOfCust
FROM myTable INNER JOIN myTable AS myTable_1 ON myTable.Cust = myTable_1.Cust
WHERE (((myTable_1.CallDate)<=[myTable].[CallDate]))
GROUP BY myTable.Cust, myTable.CallDate

[pbaldy]

Looks like Ajax is offline. mytable_1 is an alias for your table, necessary because the same table is being used twice. You realize you have to replace the table name with your table name, and adjust the field names if different.

[AccessHope]

Looks like Ajax is offline. mytable_1 is an alias for your table, necessary because the same table is being used twice. You realize you have to replace the table name with your table name, and adjust the field names if different.

I figured it out but not getting the result I want. It may be me not knowing the correct terminology. This should sum it up better by explaining what I am trying to do. If I could get the minimum value for the date grouped by the customer number would be great. I just need one date row of information with the date to show up and it needs to be the earliest. The date has to be the earliest date for the customer #. Unfortunately, I have multiples of the same date from combining other information and it doubling up to that date. So basically, it would be the same as the original I stated but there would be multiples of the same call day plus other call days in reference to that customer #. I just need to have one record of just the earliest call day for each.

I did take the time to look over the information you provided and I think its helpful but not bridging my mind from the knowledge I have with this stuff. I ended up working out Ajax's method but am getting multiple counts of the same date. What I was planning on doing: take the value 1 for each of the customer numbers and using it as a criteria to filter all of this. The problem is the earliest date now might have a value higher than one, because there are duplicates of the same day.

I plan on taking your information and sitting down this weekend. I love to take knowledge and have it for future uses and to be creative! Thank you!

[CJ_London]

thanks Paul

this is where the table is aliased in my example

...myTable AS myTable_1...

it produces the result you want in in post #1.

I ended up working out Ajax's method but am getting multiple counts of the same date.

If you need precise help, provide the detail - actual table and field names and data types and realistic data. Your data example does not include duplicate dates. If it had I would have provided something else. I won't do so now because I don't know what else may affect the answer - for example, does the table contain additional fields? what are typical values for those fields? etc

If I could get the minimum value for the date grouped by the customer number would be great.

that is a simple aggregate query

SELECT myTable.Cust, min(myTable.CallDate) AS minDate
FROM myTable 
GROUP BY myTable.Cust

[AccessHope]

You helped wonderfully. I added a field and added another min value to it. Everything works wonderful! I learned some stuff along the way as well. This is so great!

thanks Paul

this is where the table is aliased in my example

...myTable AS myTable_1...

it produces the result you want in in post #1.



If you need precise help, provide the detail - actual table and field names and data types and realistic data. Your data example does not include duplicate dates. If it had I would have provided something else. I won't do so now because I don't know what else may affect the answer - for example, does the table contain additional fields? what are typical values for those fields? etc

that is a simple aggregate query

SELECT myTable.Cust, min(myTable.CallDate) AS minDate
FROM myTable 
GROUP BY myTable.Cust