Access Help!!

  • Thread starter Thread starter asifhashmani
  • Start date Start date
A

asifhashmani

Hi I have a small query with regards to my database, basically what
I've got is a form which shows item number and cost. The cost changes
on a yearly basis e.g. for year 2006 Item 1 was priced at £2.00
however for 2007 Item 1 is now priced at £3.00. What I am after is
when the user clicks on the following year i.e. from 2006 to 2007 I
want the cost column for 2006 to be shown on the form as well as the
cost for the current year (2007) and then when the users clicks on the
following year the column for 2007 cost is shown and column for 2008
etc so only two columns are shown one for previous year and one for
current year. Is there a way of achieving this in access?

If I've confused anyone please let me know and I'll try explaining it
again

Regards

A Hashmani
 
If I understand correctly, you want to display this year and last
year's prices beside each other on the same form.

How are you storing the price information? Each year, do you add
another field to your table? Or do you have a price table, maybe like
this:
item price year

If it's the first case, then your query will probably end up with an
IIF statement to choose the year based on the year on your form.
Maybe something like this in your query (air code):

IIF(forms![your form].[year textbox].value = 2006, [2006 price field],
IIF(forms![your form].[year textbox].value = 2005, [2005 price
field], ...) AS price1

Construct a similar statement for price2

and set the control's source to price1

In the second case, which is a properly normalized form, it is not
nearly so messy, but the way I did it was a bit complicated. Post
back if that's your case. Someone else might also have better
suggestions.

HTH,
Daniel
 
Thanks for your reply Daniel, my table is as you've mentioned

ITEM 2006 PRICE 2007 PRICE
1 £2 £3
2 £2 £1

In my table I've created a field for each year ranging from 2006 to
2020 but displaying all those fields in the form will not be very
efficient hence only display current year and previous year

Regards

A Hashmani
 
Hi A.,

It might be a good idea to change your table structure to something
like this, but it would likely be a lot of effort (for a good pay-
off):
Item Price Year
1 2 2006
1 3 2006
2 2 2007
2 1 2007

If you don't do that, then the way to proceed is probably to change
the recordsource whenever the year is changed.

In the change event for the year control, you can change the
recordsource for the form. For a textbox, txtYear, with the year
construct it something like this:

me.recordsource = "SELECT t1.field1, t2.field2, ..., t1.[" &
txtYear.value & " PRICE], t2.[" & txtYear.value + 1 & "] FROM [your
table] AS t1"

Remember to add error-checking to the textbox if you do this.

HTH,
Daniel
 
Back
Top