Access Help!!

[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

 

[A Hashmani]

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

 

[Daniel]

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