SQL noob needs help: Finding # of days with data on a date field that includes times

[Swoop]

I have an Access Db that lots of automated reports write an error report to with a date/time stamp in the format 'dd-mmm-yyyy hh:mm:ss'. I'm currently pulling stats from this table using the statement

"SELECT COUNT(ErrorCode) FROM ErrorsTbl WHERE ErrorReport='" & ReportName & "' AND ErrorDate>#" & Now - 28 & "#;"

(issued in VBA from Excel)

However, if a report tries to save outputs to 5 different locations while the drive is disconnected, for example, then it'll write 5 different errors to the Db. Therefore, I need to pull the number of days a report has logged an error in the last 28 rather than a count of errors logged.

If my date and time fields were split I think I could figure it out, but since they're merged into one.....and it's a major code rewrite of 200 ish reports to split them.....can someone please help me with the SQL to pull this?

 

[Minty]

You can use the Format(ErrorDate,"Short Date") to return just the date value. Then group on that.

 

[Swoop]

Thats a great suggestion, thanks. How do I do that?

(SQL noob - only familiar with really basic stuff)

 

[Minty]

The simple way is to create a query that brings in the data you need for your stats report, but instead of the ErrorDate put in a calculated field in. So in the query designer put the following;

ErrDateOnly: Format(ErrorDate,"Short Date")

This will give you a starting point, but still with repeating numbers of the same report. Now click on the totals symbol in the query designer ( Sigma symbol)
This will group your results.
Now run the query again - you will need to make sure there aren't any other fields that are unique per record.

If you get stuck post up a picture of the query in the designer.

 

[Swoop]

Ah. I see what you're saying. Minor issue though: No access to Access.


Seriously. The organisation I'm contracting for has an IT dept that has a policy that MS-Access is not included in any desktop rollout. I'm working for the finance dept who have a legacy Access Db that is accessed directly with SQL from Excel.

The short version of that story is: I can't use the query designer, I'm limited to writing SQL from scratch with VBA from Excel.

 

[Minty]

In that case you'll probably be better with a sub query(Depending on your data), but without knowing all your field names you need that will be a slight struggle.

See if you can get some where with this http://allenbrowne.com/subquery-01.html if not post up a spreadsheet of your table with some sanitised data in it.