get record based on date time

[cybertek23]

HI
Im creating a site that generates leads. When a lead is generated it will
select a broker in the list based on certain criteria. The thing i need to do
is to select that broker which was used the last.

There is a datetime field in my database which is used with the
createodbcdatetime function

How can i do this

Thanx

Carly

[paross1]

There isn't enough information in your request for anyone to give you a very
specific answer. You mentioned that <i>"There is a datetime field in my
database"</i> which doesn't tell us much. What is the structure of the data
model, such as is there something like a broker table, and/or a lead table, and
what is the relationship between the tables, and how do you know which broker
was used last, and what table contains the datetime field that you mentioned,
etc.?

Phil

[cybertek23]

I have two tables broker and lead. They are joined by the field broker ID.

The date field is in the broker table. Each time a lead is recieved by a
broker it will insert the date and time which is created by createODBCdatetime
function. The next time a lead is recieved it will go to the broker who was
last to recieved a lead

[paross1]

Something like this will give you the broker ID and name with the most recent
date.

SELECT b.id, b.name
FROM broker b
WHERE b.datefield = (SELECT MAX(b1.datefield)
FROM broker b1
WHERE b1.id = b.id)

Phil

[Dan Bracuk]

It would make more sense if the date field was in the lead table.

[cybertek23]

Thanks
The date field is in both. i need it in the broker to know which broker recieved the last lead

[cybertek23]

Thanks

I dont need the most recent i need the date which is last in the list

[paross1]

When someone normally says "last" date, most people would assume it to mean the
most recent, as opposed to "first" which would be the oldest date, etc. So, you
want the earliest date, so use MIN instead of MAX in the query.

Phil

[cybertek23]

I am getting the following error

ODBC Error Code = 07001 (Wrong number of parameters)


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

Hint: The cause of this error is usually that your query contains a reference
to a field which does not exist. You should verify that the fields included in
your query exist and that you have specified their names correctly.

My code is

SELECT b.brokerid, b.contactname
FROM broker b
WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
FROM broker b
WHERE b1.brokerid = b.brokerid)

I have checked all the names and they are all correct

[paross1]

Look at your subquery and the table alias. You have it b where it should be b1.

SELECT b.brokerid, b.contactname
FROM broker b
WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
FROM broker <b>b1</b>
WHERE b1.brokerid = b.brokerid)

Phil

[cybertek23]

I am now not getting any errors but it is displaying all of my records

[Dan Bracuk]

If you are getting all the records, it suggests that they all have the same value in the dateandtimelastvisited field.

[paross1]

Do they all have the same dateandtimelastvisited? Does this work any better?

SELECT TOP 1 b.brokerid, b.contactname
FROM broker b
WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
FROM broker b1
WHERE b1.brokerid = b.brokerid)
AND b.dateandtimelastvisited IS NOT NULL

Phil

[cybertek23]

Hi

I have added that code but it is only selecting the broker on the top of the
list Which is the date 21/05/2006 14:14:34

I have the date fields as broker1 = 21/05/2006 14:14:34 broker 2= 20/04/2006
08:14:34,

Thanks

[paross1]

Sorry, my mistake... you don't need to correlate the two tables on brokerid.

SELECT TOP 1 b.brokerid, b.contactname
FROM broker b
WHERE b.dateandtimelastvisited = (SELECT MIN(b1.dateandtimelastvisited)
FROM broker b1)
AND b.dateandtimelastvisited IS NOT NULL

Phil

[cybertek23]

Thank you so much