find last value in the last field (column) of a record

[skoolz]

Hi All,

Is there a way to write a code or query to find the last value in the last Field (column) of a record in a table?

Or find the last field name that contains data, in a record?

Thanks in advance.

 

[jdraw]

Last doesn't really apply to a table.

Tables don't have any order. To get some order meaningful to you, use a query and an Order By keyword.

What exactly do you mean by last? Most recent date? Largest number in a series? Field has greatest value in an alpha sort (A-Z)?

 

[skoolz]

Last doesn't really apply to a table.

Tables don't have any order. To get some order meaningful to you, use a query and an Order By keyword.

What exactly do you mean by last? Most recent date? Largest number in a series? Field has greatest value in an alpha sort (A-Z)?

Hi jdraw, thanks for the response...

I know the Last() function or expression and that does not the context I mean. By last I mean the End (Latest) eg.

           Yr1   Yr2   Yr3   Yr4  Yr5
Rec1       Y      Y      Y
Rec2       Y  
Rec3       Y      Y      Y
Rec4       Y      Y     
Rec5       Y      Y      Y    Y     Y

The above I want to know the Year (field) Re3 last had a Y in (was active). which in the example is Yr3. and for Rec2 it would be Yr1 and they have not been active since.

Thanks

 

[MarkK]

That table is a spreadsheet, not a relational database table. It is handy to display data like that, but it is extremely hazardous to store it like that, as evidenced by how hard it is now to retrieve that data. Your database table should be . . .

tYourTable
RecID
Rec
Yr
Value

. . . with data like . . .

RecID Rec Yr Value
    1   1  1     Y
    2   1  2     Y
    3   1  3     Y
    4   2  1     Y
    5   3  1     Y       
[COLOR="Green"]... and so on[/COLOR]

. . . so that each discrete data point has its own row in a table. This set of principles is called database normalization.

That said, if you have a fields collection, in a recordset or table, you can refer to the last field using . . .

object.Fields(object.Fields.Count - 1)

Finding the last field that contains a particular value: you'll need to use a recordset, and you'll need to enumerate the fields collection somehow. One possibility is . . .

dim fld as dao.field
with currentdb.openrecordset("yourtable")
   do while not .eof
      for each fld in .fields
[COLOR="Green"]        'your code to keep track of field values--and when they change--here[/COLOR]
      next
      .movenext
   loop
   .close
end with

. . . but that's just the structure of a loop that will work for you. You still need to write code and build structures to re-store that data somewhere once you've extracted it from your spreadsheet.

So you can start to see how hard it is to use data in the spreadsheet format. If I was in your shoes I would "normalize" the data first. Then everything will be easier.

hth

 

[skoolz]

That table is a spreadsheet, not a relational database table. It is handy to display data like that, but it is extremely hazardous to store it like that, as evidenced by how hard it is now to retrieve that data. Your database table should be . . .

. . . with data like . . .

RecID Rec Yr Value
    1   1  1     Y
    2   1  2     Y
    3   1  3     Y
    4   2  1     Y
    5   3  1     Y       
[COLOR=Green]... and so on[/COLOR]

. . . so that each discrete data point has its own row in a table. This set of principles is called database normalization.

That said, if you have a fields collection, in a recordset or table, you can refer to the last field using . . .

object.Fields(object.Fields.Count - 1)

Finding the last field that contains a particular value: you'll need to use a recordset, and you'll need to enumerate the fields collection somehow. One possibility is . . .

dim fld as dao.field
with currentdb.openrecordset("yourtable")
   do while not .eof
      for each fld in .fields
[COLOR=Green]        'your code to keep track of field values--and when they change--here[/COLOR]
      next
      .movenext
   loop
   .close
end with

. . . but that's just the structure of a loop that will work for you. You still need to write code and build structures to re-store that data somewhere once you've extracted it from your spreadsheet.

So you can start to see how hard it is to use data in the spreadsheet format. If I was in your shoes I would "normalize" the data first. Then everything will be easier.

hth

I hear you loud and clear MarkK, luckily I was still at the brainstorming and creation stage of the tables hence why I thought I would test my approach thinking first and I got a informative response.

I will set my tables according to your advice in which case I will be able to retrieve the data using functions like DMax...

Thanks for the advice!

 

[vbaInet]

I will be able to retrieve the data using functions like DMax...

Or Max in a Totals Query grouped by the relevant fields.