SQL SUM TOTAL Date Range Issue

[dids72]

I am having an issue with a SQL query I put together using information
from this site. Unfortunately I do not have any knowledge of SQL or
writing query’s so I am looking for a little help to resolve the
issue. The issue is when I put in my date range it doesn’t appear to
actually be using the specified range correctly.

For example. If I put in the date 07/01/08 in the QUEUE Start box
and then put the date 02/01/09 in the QUEUE END box. It is including
data from 03/17/09 in the SUM total for each report which it shouldn’t
do since it is outside the date range specified.

I have listed below a small cross section of my table data for example
and the SQL I am using. Any help would be greatly appreciated.


RAW DATA TABLE:

REPORT PAGES QUEUE
AI001841 3 07/17/08
AI001841 6 09/17/08
AI001841 3 01/17/09
AI001841 6 03/17/09



SQL STATEMENT:

SELECT [Nightly Purge Table].REPORT, Sum([Nightly Purge Table].PAGES)
AS PAGESQTY, [QUEUE Start] AS QUEUESTART, [QUEUE END] AS QUEUEEND
FROM [Nightly Purge Table]
WHERE ((([Nightly Purge Table].QUEUE) Between [QUEUE Start] And [QUEUE
END]))
GROUP BY [Nightly Purge Table].REPORT;




INCORRECT RESULTS:

REPORT PAGESQTY QUEUESTART QUEUEEND
AI001841 18 07/01/08 02/01/09




SHOULD BE :

REPORT PAGESQTY QUEUESTART QUEUEEND
AI001841 12 07/01/08 02/01/09

 

[KARL DEWEY]

What is the datatype of QUEUE? Is it Text?
Post your SQL so it can be viewed.

 

[dids72]

Hi Karl,

Thank you for your response. I checked the datatype and it was Text.
I changed it to Date/Time and the SQL query appears to be working
correctly now. In regards to your question of posting the SQL isn't
that the statement I listed originally and below?

SELECT [Nightly Purge Table].REPORT, Sum([Nightly Purge Table].PAGES)
AS PAGESQTY, [QUEUE Start] AS QUEUESTART, [QUEUE END] AS QUEUEEND
FROM [Nightly Purge Table]
WHERE ((([Nightly Purge Table].QUEUE) Between [QUEUE Start] And
[QUEUE
END]))
GROUP BY [Nightly Purge Table].REPORT;


Thank you for your assistance.

 

[KARL DEWEY]

I did not scroll far enough in your post.

Hi Karl,

Thank you for your response. I checked the datatype and it was Text.
I changed it to Date/Time and the SQL query appears to be working
correctly now. In regards to your question of posting the SQL isn't
that the statement I listed originally and below?

SELECT [Nightly Purge Table].REPORT, Sum([Nightly Purge Table].PAGES)
AS PAGESQTY, [QUEUE Start] AS QUEUESTART, [QUEUE END] AS QUEUEEND
FROM [Nightly Purge Table]
WHERE ((([Nightly Purge Table].QUEUE) Between [QUEUE Start] And
[QUEUE
END]))
GROUP BY [Nightly Purge Table].REPORT;


Thank you for your assistance.

What is the datatype of QUEUE? Is it Text?
Post your SQL so it can be viewed.