SQL Statement Question

[news.microsoft.com]

Hi everyone,

I have the following SQL Statement for a mdb query:

SELECT t1.Month, t1.[Unique Users], ((t1.[Unique Users]-t2.[Unique
Users])/t2.[Unique Users]) AS UUChg, t1.[Unique Sessions], ((t1.[Unique
Sessions]-t2.[Unique Sessions])/t2.[Unique Sessions]) AS USChg, t1.[Content
Views], ((t1.[Content Views]-t2.[Content Views])/t2.[Content Views]) AS
CVChg
FROM [CV Unique Usage Data] AS t1 INNER JOIN [CV Unique Usage Data] AS t2 ON
DateSerial(Year(t1.Month),Month(t1.Month-1),Day(t1.Month))=t2.Month
WHERE (((t1.ProivderID)=29) AND ((t2.ProivderID)=29))
ORDER BY t1.Month;

The query worked great in 2006... but I just realized that it won't compare
results from Jan-07 to Dec-06 data. Does anyone know how I could modify
this query so that it will compare each month's data in the mdb table to the
prior month's data? Does Access have a similar command as Excel's EOMONTH
function?

Thanks,

Rob

 

[Bill Mosca, MS Access MVP]

Rob

Why for gosh sakes are you storing the month and not a date? And if you are
storing a date why store a month as well?

 

[news.microsoft.com]

I am storing the month because the table contains trending data for several
providers. I decided to use a decided to use a date because I needed a way
to write the subquery to pull the prior month's data to see the percent
change for various fields month over month. I would have used the ID
field... (i.e., for record 2... get the prior month's data stored in the
previous record #1) but, because there are several providers, I couldn't
figure out how to find the previous record ID number for a given provider
w/o writing several queries.

The Month field is a date/time field.... The monthy data is stored as if it
was data for the first day of the month... i.e., Dec-06 data is 12/1/06,
Jan-07 data is 1/1/07, etc., etc.. So, the query below knows to compare
12/1/06 data with 11/1/06 data. The problem with the query is that I didn't
consider what would happen when the year changed. If you look at the t1 ON
t2 clause, t2 is the same as t1's data except it is for the prior month.
When Jan-07 data is in t1, t2 has no data because it is looking for data
from 12/1/2007 (which doesn't exist).

That is what I need fixed... just, I'm not sure how to go about it. :-(

Any suggestions?

Rob

Rob

Why for gosh sakes are you storing the month and not a date? And if you
are storing a date why store a month as well?

--
Bill Mosca, MS Access MVP

Hi everyone,

I have the following SQL Statement for a mdb query:

SELECT t1.Month, t1.[Unique Users], ((t1.[Unique Users]-t2.[Unique
Users])/t2.[Unique Users]) AS UUChg, t1.[Unique Sessions], ((t1.[Unique
Sessions]-t2.[Unique Sessions])/t2.[Unique Sessions]) AS USChg,
t1.[Content Views], ((t1.[Content Views]-t2.[Content Views])/t2.[Content
Views]) AS CVChg
FROM [CV Unique Usage Data] AS t1 INNER JOIN [CV Unique Usage Data] AS t2
ON DateSerial(Year(t1.Month),Month(t1.Month-1),Day(t1.Month))=t2.Month
WHERE (((t1.ProivderID)=29) AND ((t2.ProivderID)=29))
ORDER BY t1.Month;

The query worked great in 2006... but I just realized that it won't
compare results from Jan-07 to Dec-06 data. Does anyone know how I could
modify this query so that it will compare each month's data in the mdb
table to the prior month's data? Does Access have a similar command as
Excel's EOMONTH function?

Thanks,

Rob