Subquery/Union? help (1 Viewer)

[lution]

Think what I need is a subquery but not sure how to format it correctly. I have a table: tblCitationTransmit that I'm trying to use to create a report.

I'm pulling all the fields in the table based on the dtmTransmitTime (when the record was sent) so select * from tblCitationTransmit where dtmTransmitTime = [parameter]. What I need to do on the report is include details if the citation was previously transmitted.

So if Citation 1 was transmitted on 5/24/14, 4/23/14, and 4/2/14 and I set the dtmTransmitTime parameter to 5/24/15 I want to include details for 5/24 & 4/23. If I use 4/23 for the date, then I'd use 4.23 & 4/2. If I picked 4/2, then there wouldn't be any previous data for it.

Note, that there will be more than one citation transmitted on each date.
Thanks, Lution

 

[Cronk]

Why not just base your query on
dtmTransmitTime <= [parameter]
to get all citations sent prior to the date sought?

 

[lution]

Thanks Cronk. That got me going down the right path. I had to setup a query to get the max transaction id < [StartDate], grouped on citation number. This gets me the last time the citation was sent outside the user's date range. Had a couple other steps afterwards but this was the main thing that was confusing me.