easy relationships question

[databaser]

I'm working on a database of votes in the california legislature.

In one of my tables, every record is a vote. (i.e. if there was a senate floor vote on SB 1 on 1/1/2008, that floor vote would be it's own record, storing, among other things, fields that list everyone who voted yes, no, and abstained.)

What I'm sure is easy, that i'm not sure how to do now, is get each record to know if it is most recent of it's kind.

Specifically:

If i have a record of a senate floor vote on SB 1 on 1/1/2008, i want that record to "know" (like have a new field with a value of 1) whether it is the most recent Senate floor vote on SB 1.

for such a record, there would be three relevant fields:

date (a date field) would equal "1/1/2008"

House = "Senate"

location = "floor"

now it might be nice, if i could create a field that would equal 1 when a vote is the newest of its kind for its bill (so that if it's in the senate health committee and it's the most recent vote on that bill in the senate health committee, it would equal 1 --- all the data is uniform - the committee name would be identified exactly the same in every case),

but i'm also looking to be able to identify records specifically, where newfield would equal 1, where it is the newest senate floor vote on that bill

i'd also prefer that none of this depend on found sets (if that's possible, which i think it is)

[mr_vodka]

Wouldnt your structure be something like this:

Tables:

Bill Info ( General info about the bill)

Session ( Date, which bill, etc)

Voters

Vote ( Who voted for what and how during what session )

[databaser]

not really.

This database is mainly concerned with how legislators voted on bills. (so i mainly have one table, where each vote is a record)

of course sometimes bills will go to the assembly and/or senate floor over and over again (the assembly might pass it; the senate might amend and pass; then it goes back to the assembly, who may amend it further, so it would have to go back to the senate...

so if there are 5 records of the same bill being voted on on the senate floor (because it was voted on 5 times), i want the (record for the) most recent senate floor vote on that bill to 'know' that it is most recent.

also, there are tens of thousands of such votes each year, and all my data comes from importing text files and then pulling things out of them with calculations, and i'm trying to keep this as purely automated as possible (and probably not complicate things with a table indexing the bills, or whatever).

also, i'm assuming a self join is probably involved in solving this current issue.

[comment]

there would be three relevant fields:

date (a date field) would equal "1/1/2008"

House = "Senate"

location = "floor"

I think you forgot a field to identify the bill being voted on. Properly, you should have a table of Bills (or rather Drafts), and identify the latest votes from there - since being the last in a group is not really an attribute of a single record, but of the group.

If keeping such table is not practical, you must indeed use a self-join relationship of the Votes table. This can be done in a number of ways, for example:

Votes::BillID = Votes 2::BillID

AND

Votes::House = Votes 2::House

AND

Votes::Location = Votes 2::Location

Here, the most recent votes will be those for which the calculation:

Date = Max ( Votes 2::Date )

returns true.

[databaser]

Thanks.

I think self joins are great.