SQL Count Date()

Question

INSERT INTO Table1 ( cnt )

SELECT Count(Table1.dt) AS CountOfdt

FROM Table1

GROUP BY DateValue([dt])

HAVING (((DateValue([dt]))=Date()))

ORDER BY DateValue([dt]);

SQL is a little over my head. I built this in the design view and it works... as long as there is a date in the table to be counted. I erased the table to see if it would count and insert the count number. Failed! 0 rows inserted.

dt's default value is now()

cnt recieves the counter value

Any thoughts or suggestions?

Answer 1

This is called an "Intelligent Key" - and unfortunately that's NOT a compliment. All fields - especially unique identifiers - should be "Atomic", having only one piece of information. Storing a date concatenated with a count does not make the field any more unique (than an Autonumber, say); it just makes it harder to understand and to maintain.

If you're concerned about someone hacking your backend, remember they can hack your [cnt] field just as easily as they can delete a record. You're better off securing your back end (in SQL/Server or another true Client/Server database).

That said... if your intent is to stamp each record with the date it was generated and a sequential count of which record it was on that day, I'd just use two fields, DateGenerated (default value =Date()), and Seq, assigned sequentially by either code on your data entry form or in a Table On Insert trigger.

Answer 2

I'm sorry. I actually didn't finish explaining in the last message like I intended to. The count number represents the call or reports unique number. It will be part of a string (ex. YY-MMDD000# and YY-MM00000#) and used to validate that no records are being deleted by someone who managed to hack the back end (if [cnt] > count statement then last [cnt]+1 and send alert to admin]. How many records isn't the key to the counting, the value is.

If I am still missing what you are trying to say then I'm not sure how to achieve my goal the way you're describing.

Answer 3

You missed John's main point here. 

"Later this will be used to count multiple calls for service during a day and reports during a month."

If you want to return a recordset that counts the number of records for a specified period of time then you use a query to do so. No need to store that information. For single days, you run a Group By query, grouping by Date and counting the calls. For a month, you can group by Month. 

So don't bother trying to append a count to a table. It is not the correct approach. Use queries.

Answer 4

Time does play a roll in this.

Table1 is just my text table to get my formulas right. Table1 has ID = AutoNumber (replication id), dt = Date/Time(general date), txt = short text (just for testing), and cnt = number (where the count goes into).

For today's date only I need to count how many records were created. Later this will be used to count multiple calls for service during a day and reports during a month. I already have the counting park figured out and that is what is in the code above. Update might be the better route when the code is complete but for now I was just adding the count number by running the append.

The problem I am having though is when the clock rolls past midnight and starts a new day. My query shows that the new day does not exist therefore does not append a value for count and shows that 0 records are created.

I would like it to append 1 for the first record of the day.

Answer 5

As a rule, there is no good reason to create a new Table (or even a new record in Table1) just to have a count of records; a simple Totals query will do the same job, with less overhead.

What is the context? What are you trying to accomplish? What will you do with Table1 and this field cnt? 

If you just want a count of records for today's date assuming there is a field named Dt in Table1, you could simply use

SELECT Count(*) AS CountOfDt

FROM table1

WHERE Table1.dt >= Date() AND Table1.dt < DateAdd("d", 1, Date());

A Date() is stored as a count of days and fractions of a day (times), with a fractional part of 0 meaning midnight at the start of the day; so my suggested criterion will count all records where the field dt is between midnight last night up to but not including this coming midnight.