ansaurus

Question

SQL for MS Access: Another question about COUNT, JOIN, 0s and Dates

Answer 1

A:

Try this:

SELECT grade.ID, Count(telephony.Grade) AS Total
FROM grade LEFT JOIN telephony ON grade.ID=telephony.Grade
GROUP BY grade.ID
HAVING COUNT(telephony.Grade) > 0
ORDER BY grade.ID;

eKek0 2010-08-05 15:58:42

Answer 2

A:

That's completely different. You want a range of individual dates joined with your first table, and the between clause isn't going to do that for you.

I think you'll need a table with all the dates you want, say from 1/1/2010 to 12/31/2010, or whatever range you need to support. One column, 365 or however many rows with one date value each.

then join that table with the ones with the dates and grades, and limit by your date range, then do the aggregation to count.

Take it one step at a time and it will be easier to figure out.

Beth 2010-08-05 16:04:21

Answer 3

+1 A:

Create a separate query on telephony which uses your BETWEEN #07/19/2010# AND #07/23/2010# constraint.

qryTelephonyDateRange:

SELECT *
FROM telephony
WHERE [Date] BETWEEN #07/19/2010# AND #07/23/2010#;

Then, in your original query, use:

LEFT JOIN qryTelephonyDateRange ON grade.ID=qryTelephonyDateRange.Grade

instead of

LEFT JOIN telephony ON grade.ID=telephony.Grade

You could use a subquery instead of a separate named query for qryTelephonyDateRange.

Note Date is a reserved word, so I bracketed the name to avoid ambiguity ... Access' database engine will understand it is supposed to be looking for a field named Date instead of the VBA Date() function. However, if it were my project, I would rename the field to avoid ambiguity ... name it something like tDate.

Update: You asked to see a subquery approach. Try this:

SELECT g.ID, t.[Date], Count(t.Grade) AS Total
FROM
    grade AS g
    LEFT JOIN (
        SELECT Grade, [Date]
        FROM telephony
        WHERE [Date] BETWEEN #07/19/2010# AND #07/23/2010#
        ) AS t
    ON g.ID=t.Grade
GROUP BY g.ID, t.[Date]
ORDER BY 1, 2;

HansUp 2010-08-05 17:02:03

Could you explain how I would use a subquery instead? Thanks

Richard L 2010-08-06 14:16:37

Tht works great, however I would like to group by t.Date as well as g.ID. When I add this in it still only returns dates with a grade value. Any ideas? Thanks for all your help.

Richard L 2010-08-10 12:26:03

I revised the example query. See if that's what you want.

HansUp 2010-08-10 17:28:52

Cheers, I really appreciate it and i'm almost there. When a grade isnt found it's reported as 0 but only once. It isnt reported as 0 for each date. Final question I promise!

Richard L 2010-08-11 08:39:50

Answer 4

A:

The way I got it to work was to:

Create a table named Dates with a single primary key date/time field named MyDate (I'm with HansUp on not using reserved words like "Date" for field names).
Fill the table with the date values I wanted (7/19/2010 to 7/23/2010, as in your example).
Write a query with the following SQL statement

SELECT x.MyDate AS [Date], x.ID, Count(t.ID) AS Total FROM (SELECT Dates.MyDate, Grade.ID FROM Dates, Grade) AS x LEFT JOIN Telephony AS t ON (x.MyDate = t.Date) AND (x.ID = t.Grade) GROUP BY x.MyDate, x.ID;

That should get the results you asked for.

The subquery statement in the SQL creates a cross-join to get you every combination of date in the Dates table and grade in the Grade table.

(SELECT Dates.MyDate, Grade.ID FROM Dates, Grade)  AS x

Once you have that, then it's just an outer join to the Telephony table to do the rest.

Forester93 2010-08-05 18:28:22

ansaurus

tags:

views:

answers:

SQL for MS Access: Another question about COUNT, JOIN, 0s and Dates

related questions