Brining table data in single row

[Ramu]

Brining table data in single row

I have a table with the week number and the price, I want to bring the data if the user selects the week number – I have to get the following data in single row

1. selected week data
2. one week before the selected week
3. two week before the selected week
4. one week after the selected week
5. two week after the selected week

Something likes the following

Week Number Price
42 1000
43 1100
44 990
45 1150
46 1125



result
44 43 42 45 46
990 1100 1000 1150 1125

I am very raw in access or sql or vb - can anybody help me to solve the above, please

Thanks
Ramus

 

[DCrake]

What you are asking is very unconventional. Unless someone has actually had this issue in the past it may be unlikely that anyone will spend time on resolving your issue. However this is not to say that no one will. So please be patient.

 

[Big Pat]

Hi,

I think you probably mean you want to DISPLAY the surrounding weeks together in one part of a form or a report.

Have a look at my attachment. I've entered sample prices for 52 weeks of next year in tbl_Prices. Each week has a Week No which is the primary key.

Next I placed a list box on a form called Form1. You could just as easily use a combo box. You use this to select a date but Access actually stores the week number.

Then a used a DLOOKUP formula to find the week number stored above, find the matching week number in the table and display the price from that record. That's the central "selected week" formula. Have a look at the formula in design view.

When I was sure the previous text box was working properly, I made four copies. I only needed to change which week I was looking up, so to the end of each formula I added -2, -1, +1, +2 as appropriate.

Finally, I added some code to the AfterUpdate event of the List box. This ensures that when the user selects a new date, the five text boxes are "requeried".

Now some folks on here will tell you that DLookUps are basically the spawn of Satan and are to be avoided at all costs. I've never understood why myself. I'm pretty much a novice at VBA and most of the code I see posted on here is WAY over my head, but I use DLookUps a fair bit and they work OK for me.

Let me know if this was helpful.

Pat.