Access Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following data:

year sales
2004 2500
2003 2200
2002 1900

How do I calculate the change in sale data from year to year in access query?

Thanks for your help.
 
In query design, type this expression into a fresh column in the Field Row:
(SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1)
That's one line.
Replace Table1 with the name of your table.
This assumes each year's sales figure occur once only.
Query results will be read-only.

Once you have verified it gives the right answer, you can extend it to show
the difference like this:
Delta: [Sales] - (SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1)

If you want it to show zero rather than blank for some year, and force the
result to display as currency:
Delta: CCur(Nz([Sales] - (SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1),0))

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
planningresult said:
I have the following data:

year sales
2004 2500
2003 2200
2002 1900

How do I calculate the change in sale data from year to year in access query?
Click to expand...


If your data is as nice and neat as that (consequtive
years), something like this will be adequate:

SELECT T.Year,
T.Sales - X.Sales
FROM table As T INNER JOIN table As X
ON T.Year = X.Year - 1
 
Allen,

I am unable to make the first two options work. Is it possible this is the
case because the "tables" providing the data in this new query are also
queries?

Dana

Allen Browne said:
In query design, type this expression into a fresh column in the Field Row:
(SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1)
That's one line.
Replace Table1 with the name of your table.
This assumes each year's sales figure occur once only.
Query results will be read-only.

Once you have verified it gives the right answer, you can extend it to show
the difference like this:
Delta: [Sales] - (SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1)

If you want it to show zero rather than blank for some year, and force the
result to display as currency:
Delta: CCur(Nz([Sales] - (SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1),0))

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

planningresult said:
I have the following data:

year sales
2004 2500
2003 2200
2002 1900

How do I calculate the change in sale data from year to year in access
query?

Thanks for your help.
Click to expand...
Click to expand...
 
It should be possible to create a subquery from a query source.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

planningresult said:
Allen,

I am unable to make the first two options work. Is it possible this is
the
case because the "tables" providing the data in this new query are also
queries?

Dana

Allen Browne said:
In query design, type this expression into a fresh column in the Field
Row:
(SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1)
That's one line.
Replace Table1 with the name of your table.
This assumes each year's sales figure occur once only.
Query results will be read-only.

Once you have verified it gives the right answer, you can extend it to
show
the difference like this:
Delta: [Sales] - (SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1)

If you want it to show zero rather than blank for some year, and force
the
result to display as currency:
Delta: CCur(Nz([Sales] - (SELECT [sales] from [Table1] AS Dupe
WHERE Dupe.[Year] = [Table1].[Year] - 1),0))

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

message
I have the following data:

year sales
2004 2500
2003 2200
2002 1900

How do I calculate the change in sale data from year to year in access
query?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top