Display data from month x-1 and x-2

[donodarazao]

Hello,

I ran into a little Access problem and I didn't find a solution yet.

We've got 500 trees in a long term experiment. On each tree, some variables are measured each month (now, we are at month 75).


The data used to be organized in Excel in a 'wide' format, with 'Tree1' - 'Tree500' in rows, 'Month1' to 'Month75' in columns.

	     month1	     ...	month75
tree1	    
...
tree500

I reorganized the data in the 'long' format and imported it into access, with three colums: 'treeid', 'month' and 'value'. The resulting table has (500*75)=37500 records.

The long format was necessary for further analysis of the data.

Now I need a field formulary for month x (now: Month 76) that shows the measured values for month x-1 (now: Month 75) and month x-2 (Month 74)… and I've got no idea how to do that in an easy way. I did a similar things a couple of years ago using quite some VBA, but this is no option this time because I don't have enough time and the database should be as transparent as possible for colleagues that aren't familiar with Access.

Any help would be very much appreciated!

[pbaldy]

If I understand correctly, your month field contains a value like 76? If no future data is entered (ie the greatest value in the table would be 76), you could have a query criteria of:

>= DMax("MonthFieldName", "TableName") - 2

[donodarazao]

Hello Paul,

Thanks for your quick answer.

I'll explain more in detail what I want:
I would like to have one column named 'Month 76' in the report. This column should be empty, because this data doesn't exist yet, but will be measured in the field. Next to that column, I want 'Month 75' and 'Month 74', with the respective measurements for those month.
This report (=> field formulary) is important, because the persons doing the field work need to know previous measurements, or else there would inevitably be much more measuring errors (kind of a quality control).

Future data will be entered every month, but the query would adapt to that, wouldn't it?

Your suggestion gives me a query with the values from the last two month. But I can't see yet how this helps me in achieving what I want.

[pbaldy]

Does a crosstab query get you the look you want?

[donodarazao]

Does a crosstab query get you the look you want?

I now tried making a crosstab query and it worked.
When I now prepare a report based on the crosstab query, I can chose the last two names as fields ('75' and '74'). However, this is not dynamic. When entering data for following month, the crosstab query adepts, but the report won't (error message when opening).
Do you maybe have any ideas how to solve this problem?

[pbaldy]

I would expect to be able to use a similar criteria and have it work for the crosstab query, thus making it dynamic. There are ways around the dynamic nature of the field names in the report.