Difference formula from yr to yr Query

  • Thread starter Thread starter cedia
  • Start date Start date
C

cedia

i am referencing to one table in my query. i have year
field (date), site field (text)and about 8 (number) data
fields.
i would a query to give me the difference of any site1
for the each of the 8 number data fields from yr1 to yr2.

How can i do this? I don't know how to tell it to give me
difference for two different years of same field. Thanks.
 
Hey Cedia -

If I am understanding you correctly, you have something
like this:

DateField SiteName Data1
1/1/2004 Site1 5
1/1/2003 Site1 4

and you would like to see:

Site ThisYear LastYear Data1Diff
Site1 1/1/2004 1/1/2003 1 (that is: 5-4)

Assuming that's right here's what you need to do:

(1) Create a query that calcuates a "last year" column.
like this: (I named my test table Cedia.)
SELECT Cedia.*, DateAdd("yyyy",-1,[datefield]) AS LastYear
FROM Cedia;

(2) Create a new query and add both your table and the
query from (1) above. Join them on SiteName and then join
the "LastYear" column from the query to the "DateField" in
the table. Now you can just do the math by subtracting
[CediaQuery].Data1 - [Cedia].Data1. Here's the SQL for
the second query, assuming the first query is called
CediaQuery.

SELECT CediaQuery.SiteName, CediaQuery.LastYear,
Cedia.DateField, [CediaQuery].[Data1]-[cedia].[data1] AS
Data1Diff
FROM Cedia INNER JOIN CediaQuery ON (Cedia.DateField =
CediaQuery.LastYear) AND (Cedia.SiteName =
CediaQuery.SiteName);

Is that what you need?
 
Awesome! Thanks Anne! Thats EXACTLY what I needed!


-----Original Message-----
Hey Cedia -

If I am understanding you correctly, you have something
like this:

DateField SiteName Data1
1/1/2004 Site1 5
1/1/2003 Site1 4

and you would like to see:

Site ThisYear LastYear Data1Diff
Site1 1/1/2004 1/1/2003 1 (that is: 5-4)

Assuming that's right here's what you need to do:

(1) Create a query that calcuates a "last year" column.
like this: (I named my test table Cedia.)
SELECT Cedia.*, DateAdd("yyyy",-1,[datefield]) AS LastYear
FROM Cedia;

(2) Create a new query and add both your table and the
query from (1) above. Join them on SiteName and then join
the "LastYear" column from the query to the "DateField" in
the table. Now you can just do the math by subtracting
[CediaQuery].Data1 - [Cedia].Data1. Here's the SQL for
the second query, assuming the first query is called
CediaQuery.

SELECT CediaQuery.SiteName, CediaQuery.LastYear,
Cedia.DateField, [CediaQuery].[Data1]-[cedia].[data1] AS
Data1Diff
FROM Cedia INNER JOIN CediaQuery ON (Cedia.DateField =
CediaQuery.LastYear) AND (Cedia.SiteName =
CediaQuery.SiteName);

Is that what you need?



-----Original Message-----
i am referencing to one table in my query. i have year
field (date), site field (text)and about 8 (number) data
fields.
i would a query to give me the difference of any site1
for the each of the 8 number data fields from yr1 to yr2.

How can i do this? I don't know how to tell it to give me
difference for two different years of same field. Thanks.



.
Click to expand...
.
Click to expand...
 
-----Original Message-----
Awesome! Thanks Anne! Thats EXACTLY what I needed!


-----Original Message-----
Hey Cedia -

If I am understanding you correctly, you have something
like this:

DateField SiteName Data1
1/1/2004 Site1 5
1/1/2003 Site1 4

and you would like to see:

Site ThisYear LastYear Data1Diff
Site1 1/1/2004 1/1/2003 1 (that is: 5-4)

Assuming that's right here's what you need to do:

(1) Create a query that calcuates a "last year" column.
like this: (I named my test table Cedia.)
SELECT Cedia.*, DateAdd("yyyy",-1,[datefield]) AS LastYear
FROM Cedia;

(2) Create a new query and add both your table and the
query from (1) above. Join them on SiteName and then join
the "LastYear" column from the query to the "DateField" in
the table. Now you can just do the math by subtracting
[CediaQuery].Data1 - [Cedia].Data1. Here's the SQL for
the second query, assuming the first query is called
CediaQuery.

SELECT CediaQuery.SiteName, CediaQuery.LastYear,
Cedia.DateField, [CediaQuery].[Data1]-[cedia].[data1] AS
Data1Diff
FROM Cedia INNER JOIN CediaQuery ON (Cedia.DateField =
CediaQuery.LastYear) AND (Cedia.SiteName =
CediaQuery.SiteName);

Is that what you need?



-----Original Message-----
i am referencing to one table in my query. i have year
field (date), site field (text)and about 8 (number) data
fields.
i would a query to give me the difference of any site1
for the each of the 8 number data fields from yr1 to yr2.

How can i do this? I don't know how to tell it to give me
difference for two different years of same field. Thanks.



.
Click to expand...
.
Click to expand...
.
Click to expand...
 
-----Original Message-----
Awesome! Thanks Anne! Thats EXACTLY what I needed!


-----Original Message-----
Hey Cedia -

If I am understanding you correctly, you have something
like this:

DateField SiteName Data1
1/1/2004 Site1 5
1/1/2003 Site1 4

and you would like to see:

Site ThisYear LastYear Data1Diff
Site1 1/1/2004 1/1/2003 1 (that is: 5-4)

Assuming that's right here's what you need to do:

(1) Create a query that calcuates a "last year" column.
like this: (I named my test table Cedia.)
SELECT Cedia.*, DateAdd("yyyy",-1,[datefield]) AS LastYear
FROM Cedia;

(2) Create a new query and add both your table and the
query from (1) above. Join them on SiteName and then join
the "LastYear" column from the query to the "DateField" in
the table. Now you can just do the math by subtracting
[CediaQuery].Data1 - [Cedia].Data1. Here's the SQL for
the second query, assuming the first query is called
CediaQuery.

SELECT CediaQuery.SiteName, CediaQuery.LastYear,
Cedia.DateField, [CediaQuery].[Data1]-[cedia].[data1] AS
Data1Diff
FROM Cedia INNER JOIN CediaQuery ON (Cedia.DateField =
CediaQuery.LastYear) AND (Cedia.SiteName =
CediaQuery.SiteName);

Is that what you need?



-----Original Message-----
i am referencing to one table in my query. i have year
field (date), site field (text)and about 8 (number) data
fields.
i would a query to give me the difference of any site1
for the each of the 8 number data fields from yr1 to yr2.

How can i do this? I don't know how to tell it to give me
difference for two different years of same field. Thanks.



.
Click to expand...
.
Click to expand...
.
Click to expand...
 
:) Glad to help.
-----Original Message-----
Awesome! Thanks Anne! Thats EXACTLY what I needed!


-----Original Message-----
Hey Cedia -

If I am understanding you correctly, you have something
like this:

DateField SiteName Data1
1/1/2004 Site1 5
1/1/2003 Site1 4

and you would like to see:

Site ThisYear LastYear Data1Diff
Site1 1/1/2004 1/1/2003 1 (that is: 5-4)

Assuming that's right here's what you need to do:

(1) Create a query that calcuates a "last year" column.
like this: (I named my test table Cedia.)
SELECT Cedia.*, DateAdd("yyyy",-1,[datefield]) AS LastYear
FROM Cedia;

(2) Create a new query and add both your table and the
query from (1) above. Join them on SiteName and then join
the "LastYear" column from the query to the "DateField" in
the table. Now you can just do the math by subtracting
[CediaQuery].Data1 - [Cedia].Data1. Here's the SQL for
the second query, assuming the first query is called
CediaQuery.

SELECT CediaQuery.SiteName, CediaQuery.LastYear,
Cedia.DateField, [CediaQuery].[Data1]-[cedia].[data1] AS
Data1Diff
FROM Cedia INNER JOIN CediaQuery ON (Cedia.DateField =
CediaQuery.LastYear) AND (Cedia.SiteName =
CediaQuery.SiteName);

Is that what you need?



-----Original Message-----
i am referencing to one table in my query. i have year
field (date), site field (text)and about 8 (number) data
fields.
i would a query to give me the difference of any site1
for the each of the 8 number data fields from yr1 to yr2.

How can i do this? I don't know how to tell it to give me
difference for two different years of same field. Thanks.



.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top