find first record in a set of fields

[geordiekev]

I have 12 fields, each containing a number. I want to display the last non zero value in a query. I've looked at "last" but this returns the items in chronological order, which is not neccessarily the order my data is entered in.

eg.
fields: 1 2 3 4 5 6 7 8 9 10
data: 0 7 3 0 0 5 4 0 0 0

I would want the query to return 4, even if the last value entered was the 7.

To make it more complicated, I have many rows of data for each set I am using, and have to aggregate these based on a key column

eg.

Fields: key 1 2 3 4 5 6
Data: 1 0 0 0 2 0 1
1 0 0 0 3 0 3
1 6 5 4 3 2 1
2 1 2 3 4 5 6
2 0 0 0 0 0 0

Any tips?

Cheers
Kev

 

[Sammy B]

I know this may not help you but this is how I choose a 'last' number that was not '0' in one of my queries.

SELECT Last([Table1].[Number]) AS My_Last
FROM Table1
WHERE (([Table1].[number]>0));

I had a table (Table1) with single number entries and this query pulled that last number that was not a 0.

Sam

 

[geordiekev]

Thanks, but I am looking to bring back a different field depending on which other ones are empty, rather than the last data in a particular field.

Kev

 

[Sammy B]

Does it have to be a query or could it be done in code?

Sam

 

[geordiekev]

The results are to populate a report. I guess code would be ok, as long as it can do that.

update: I've got it by running a query to group by year, and a second query to select the maximum year off that query. not pretty, but it works.

Cheers
Kev