Comparing Combined Data Over 2 Dates - Help!

  • Thread starter Thread starter Cjle10
  • Start date Start date
C

Cjle10

Hello chaps,

I am really hoping somebody can help me out with this one as I just cant get
it solved. Here is what I am trying to do.

I have a table of items, each item has an age and a date it was uploaded into
the database.

From this table I have a query that counts items for each date and breaks
them down into aged groups.

eg.
Date 0-7 days count
03/11/06 100
04/11/06 105

What i am loking to do is calculate the change in the items in each group,
each day vs the previous day. Any suggestions on how I can get this achieved
would be appreciated.
 
Probably need to use a subquery. Here's a generic approach:

SELECT T.TheDateField, Count(T.FieldBeingCounted) AS TheDateCount,
(SELECT Count(Q.FieldBeingCounted) FROM TableName AS Q
WHERE Q.TheDateField= DateDiff("d", -1, T.TheDateField)) AS YesterdayCount
FROM TableName AS T
GROUP BY T.TheDateField;
 
Ken many thanks for that. Once I get it up and running it will be a major
problem solved.

Just to avoid any syntax issues and to assist my learning could you clarify
for me what the terms T and Q refer to.

Many thanks for your help.




Probably need to use a subquery. Here's a generic approach:

SELECT T.TheDateField, Count(T.FieldBeingCounted) AS TheDateCount,
(SELECT Count(Q.FieldBeingCounted) FROM TableName AS Q
WHERE Q.TheDateField= DateDiff("d", -1, T.TheDateField)) AS YesterdayCount
FROM TableName AS T
GROUP BY T.TheDateField;
Hello chaps,
Click to expand...
[quoted text clipped - 18 lines]
achieved
would be appreciated.
Click to expand...
Click to expand...
 
T and Q are aliases for the TableName. That is done so that the query does
not get confused over which instance of TableName the query is to use and
which instance the subquery is to use.

--

Ken Snell
<MS ACCESS MVP>

Cjle10 said:
Ken many thanks for that. Once I get it up and running it will be a major
problem solved.

Just to avoid any syntax issues and to assist my learning could you
clarify
for me what the terms T and Q refer to.

Many thanks for your help.




Probably need to use a subquery. Here's a generic approach:

SELECT T.TheDateField, Count(T.FieldBeingCounted) AS TheDateCount,
(SELECT Count(Q.FieldBeingCounted) FROM TableName AS Q
WHERE Q.TheDateField= DateDiff("d", -1, T.TheDateField)) AS YesterdayCount
FROM TableName AS T
GROUP BY T.TheDateField;
Hello chaps,
Click to expand...
[quoted text clipped - 18 lines]
achieved
would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top