how to do this

[icemonster]

i dont really know how to do this or even how to ask this question but here's what i intend to do, since my last record has a major flaw in it, i figured, there must be another way, i have tried grouping, dlast, dmax almost every other function but cant seem to get it right.

basically i have a 2 tables, tblA holds all of the client info, while tblB, holds all of their resource info, now every now and then, they would put in a new resource info hence the 2nd table.

but the thing is, i want to be able to only retrieve the last record inputted for tblB that is related to tblA.

so how do i do this?

i mean is there a way to query this and then just show the last record related to tblA?

tblA:
PK
name
etc.

tblB:
PK
FK
record
etc.

how will i query this so that it will only show the last record related to tblA? is it even possible with access? thanks

 

[John Big Booty]

You could add a Date/Time stamp to indicate when each record was added to your TblB, then it would be a simple matter of finding the Max value for that field.

 

[icemonster]

yeah, but it only would show the max value for that entire tbl, how would i set it that it would still show all of the clients but only show the max record on the tblrecords?

how is max used?

 

[John Big Booty]

You would select only those records related to the client you are interested in and then in a group query you would select the max of your time stamp field. and then use that in a second query to select the record you are interested in.

Now having said that if you used a customs auto incrementing number function (so you can be assured of having a sequential set of number) as your PK for TblB you could simply apply that same principal to the PK of TblB.

 

[icemonster]

well yeah, if i do that, it does work but what am trying to do is this:

all records from tblA are related to tblB, what i want to do is query them together and only show the last record from tblB that is related to tblA, so if tblA has 5 records, let say 5 clients, SAM, MARK, JOHN, PETER, CRIS and on tblB, is there record info, if i had 3 records for JOHN in that table and 2 records for CRIS and 1 record for SAM, all the records from tblA would still show up but on the tblB records, the field with the datestamp would only show the last record for each and every one of them.

 

[John Big Booty]

You can still do that, first you need to create a group by query. You would group by your clients in TblA, and get the Max of the PK (now this assumes that you have set up this PK to be a sequential number (the autonumber function can not always be relied upon to do this, see discussion on this subject elsewhere in these forums) from TblB. Now this should return the last PK of each record per client. Now use this query in a second query, you can link the PK (from TblB) returned in your first query to the PK in TblB and thus return all the info in the record.

 

[John Big Booty]

Check the attached for a very basic example see Query2 in particular.

See also the AfterUpdate event of the Combo on Form1 for an example of how to get a sequentially numbered PK.

 

[icemonster]

wow cool. but is this usable with date/time stamp? because eventually, when the data becomes bigger we would have to move it to an sql server and those kinds have the zulu time which is pretty nifty being that date/time stamps can have a disastrous effect when the internal clocks are messed up and not synced.

 

[John Big Booty]

wow cool. but is this usable with date/time stamp? because eventually, when the data becomes bigger we would have to move it to an sql server and those kinds have the zulu time which is pretty nifty being that date/time stamps can have a disastrous effect when the internal clocks are messed up and not synced.

I imagine so but the only way for you to find out will be to have a play with it yourself and report back