Show only top record on many side of relationship if it meets criteria

[jwleonard]

Well I hope I didn't mess up with the design of my database but I can't seem to find a way to do what I want. For simplicity I have two tables; tblMain & tblStatus with one record in tblMain matching many in tblStatus. I can do what I need in my head but can't figure how to get Access to do it. I need a report that will show data from tblMain and tblStatus if the most recent status is for instance "In Work". If not I don't want the record from either table to show in the report. In my head I see first checking the top record in status and if it is "In Work" grab the matching record in tblMain based on the relationship and put the data in the report. Anyone have any idea how to make Access do this?

 

[gemma-the-husky]

prepare a query joining the two tables together, with your criteria to include/exclude records, and base your report on the query.

you stil lhave just the same formatting layout options.

 

[jwleonard]

Thanks for the reply. That is what I was trying but when the query runs it assumes that I want the top value where "In Work" is the status instead of the top value and associated record if the status is "In Work".
I can't think of anyway to make it work but I am certainly open to suggestions.
In the mean time I just created a current status in the tblMain that is updated after the status is changed and only shows the current status. This allows me to find the correct records for my report. I would perfer to do this the other way as with the current set up I would have an issue if the status gets out of sync.
Thanks for the help so far.

 

[jwleonard]

Does anyone else have any info on this? I would still like to do this based on the related table instead of just duplicating the last status in tblMain. It will allow me to do other things (like show the status of a record on any given date) if I can figure it out.