retreive info of 2 subsequent years

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

Guest

hi!

i have a table with such a fields:
year ........... (possible values: 1, 2, 3, 4, 5)
product
amount_sould

i want to make a parameter query, so that i see the amount_sold for the asked year and a year before.
for example, if i give a parameter 5 for the year, i can see the amount_sold for the year 4 and year 5.
(i get only information of the year "1" if i give parameter 1.)
 
Under the year you would put criteria like:

[EnterYear] or [EnterYear]-1



hi!

i have a table with such a fields:
year ........... (possible values: 1, 2, 3, 4, 5)
product
amount_sould

i want to make a parameter query, so that i see the amount_sold for the
asked year and a year before.
for example, if i give a parameter 5 for the year, i can see the amount_sold
for the year 4 and year 5.
(i get only information of the year "1" if i give parameter 1.)
 
YEs, but I am not sure how.

Hopefully someone will respond.

Rick B


Thx! It works.

Can I make a qeury with such fields:
year
product
amount_sold_this_year
amount_sold_last_year
 
Rick,

Not sure why you would want the year, product, this year, last year format,
when you input the year value.

How about something like:

SELECT Product,
SUM(IIF([Year] = [Enter Year], [Amount_Sold], 0)) as
ThisYear,
SUM(IIF([Year] = [Enter Year] - 1, [Amount_Sold], 0)) as
PrevYear
FROM yourTable
GROUP BY Product

If you really want the you entered in the query, it would be something like
(untested)

SELECT [Enter Year] as YearEntered,
Product,
SUM(IIF([Year] = [Enter Year], [Amount_Sold], 0)) as
ThisYear,
SUM(IIF([Year] = [Enter Year] - 1, [Amount_Sold], 0)) as
PrevYear
FROM yourTable
GROUP BY Product
WHERE [Year] = [Enter Year] or [Year] = [Enter Year] - 1

BTW, Year is a reserved work (function) in access, and is therefore a really
bad choice for a field name.

HTH
Dale

Rick B said:
YEs, but I am not sure how.

Hopefully someone will respond.

Rick B


Thx! It works.

Can I make a qeury with such fields:
year
product
amount_sold_this_year
amount_sold_last_year

Rick B said:
Under the year you would put criteria like:

[EnterYear] or [EnterYear]-1



hi!

i have a table with such a fields:
year ........... (possible values: 1, 2, 3, 4, 5)
product
amount_sould

i want to make a parameter query, so that i see the amount_sold for the
asked year and a year before.
for example, if i give a parameter 5 for the year, i can see the amount_sold
for the year 4 and year 5.
(i get only information of the year "1" if i give parameter 1.)
Click to expand...
Click to expand...
 
Back
Top