Return Max Date before a certain date

[Fiasco]

I am hoping somebody can help me out as this is the first time I have had to ask a question, normally I can search and find an answer.

My problem is I need to find the maximum date before another date.

I have 2 tables 1 with a bunch of records with a create date and another with a start date of an Owner. I need to know the owner whos start date is the last one before the create date.

I wnat something like Max(startdate<=CreateDate). The trouble I'm having is if there are 2 or more Start dates before the create date the query is returning both.

I record could have more than 1 owner but the start dates dont overlap.

 

[jzwp22]

Welcome to the forum!

Not knowing how the two tables are related, I am guessing that you need a nested query

query name: qryCreateAndStartDates
SELECT createdate, (SELECT Top 1 startdate FROM ownertable where startdate<=createdate AND **possibly other criteria here *** order by startdate Desc ) as OwnerStartDate
FROM table1

Then you will probably have to create a second query that joins the qryCreateAndStartDates back to the owner's table. This assumes that the start date is unique to the owner


SELECT createdate, ownerstartdate, owner
FROM qryCreateAndStartDates INNER JOIN ownertable ON qryCreateAndStartDates.ownerstartdate=ownertable.startdate

 

[Fiasco]

Thanks JZWp22 for the reply.
I keep getting the lowest date. Here is how the 2 tables basically are and what I'm trying to get. This has to do with sales and the BDM who has a relationship with a client. As BDM's come and go they want to keep the original BDM the sold the deal.

Tbl1Tbl2Record#Owner IDCreateDteOwnerIDStartDteOwner1111112/02/201111/01/2011A2222113/03/201111/03/2011B3333213/02/201111/04/2011C4444312/02/201121/01/2011D5555114/04/201131/01/2011EResultsRecord#OwnerIDCreateDteOwner1111112/02/2011A2222113/03/2011B3333213/02/2011D4444312/02/2011E5555114/04/2011C

This is a cut down but I hope it gives you an idea of what I'm after.

 

[Fiasco]

Sorry I Screwed that up. Try to copy it from a spreadsheet and hit submit instead of Preview

Thanks JZWp22 for the reply.
I keep getting the lowest date. Here is how the 2 tables basically are and what I'm trying to get. This has to do with sales and the BDM who has a relationship with a client. As BDM's come and go they want to keep the original BDM the sold the deal.

This is a cut down but I hope it gives you an idea of what I'm after.

 

[jzwp22]

I had to make a few modifications now that I see your data. An example database is attached. Also, remember SQL always interprets dates as if they are in month/day/year format no matter how you have them formatted in Access. (see this site)

 

[Fiasco]

Thanks jzwp22 for your help with this I got your suggestion to work. Appreciate your swift response soryy I didnt get back with thanks quicker I have been in and out of the office.

 

[jzwp22]

Glad to hear that you got it resolved. Good luck with the database.