Show only most recent record

jackiew · April 22nd, 2009, 04:26 PM

I want to build a *very* simple form, one field. I want a person to be able to go in and update a single number. (example: '10 people are signed up out of 100 entries' - with 10 being the form number) I only want the most recent number to show up, not the previous numbers. (example: I don't want yesterday's 9 to show up once I put in today's 10)

I could set it up in a typical Do, While, Wend loop, but of course that isn't going to work. Is there a simple, quick way to pull only the most recent number and display only that number from a database?

I plan on using an 'ID' unique field. Also, I plan on using 'SubmitDate' to show the date of each entry. The only other field I plan on is 'DayCount'.

Old Pedant · April 22nd, 2009, 05:45 PM

Scratching my head...

Why should you NEED to "update a single number"? Why can't/shouldn't you just get that number from data that's already *IN* the database??

And if there is only a "single number", then why is there such a thing as "most recent number"??

In any case, you should never update something by hand that can, instead, be derived from data in the DB.

Can you show us your DB schema?? All the relevant fields of all the relevant tables??

Conceptually, this is trivial, but I am really afraid there is something badly messed up with the DB design.

You could use SQL to get the most recent record:

Code:

SELECT ID, SubmitDate, DayCount
FROM table
WHERE SubmitDate = ( SELECT MAX(SubmitDate) FROM table )

Or, if the ID field is an autonumber field,

Code:

SELECT ID, SubmitDate, DayCount
FROM table
WHERE ID = ( SELECT MAX(ID) FROM table )

Or, depending on what DB your are using (yes, it matters)

Code:

SELECT TOP 1 ID, SubmitDate, DayCount
FROM table
ORDER BY ID DESC

or

Code:

SELECT TOP 1 ID, SubmitDate, DayCount
FROM table
ORDER BY SubmitDate DESC

or or or ...

But it's not clear to me why you should be updating DayCount manually, at all.

jackiew · April 22nd, 2009, 06:07 PM

Well, I haven't started the programming as of yet, cause I knew there was a way to do what I wanted. But, here's my thoughts.

Table columns: ID(unique) - DayCount - SubmitDate

The page itself would be text:

"We have 100 spots open - Right now ## have signed up!"

I want someone, other than me, to be able to go in each day (maybe several times a day??) and update the ##. Didn't want them to have to 'Delete' yesterday, or do any other finagling, just type in the ## and hit submit. The page then would automatically update with the new ##.

So, the database would actually have a lot of numbers in it by the time it's run it's life. That is where the 'most recent' comes into play. Just wanted to update the page with the number they just typed in. I know how to display all the records, but this just picking one has me stumped.

Thanks!

Old Pedant · April 22nd, 2009, 06:20 PM

Nope. Do NOT do it that way!

Let's say you have a table like this:

Code:

Table: Signups
   ID int autonumber primary key 
   name text
   email text
   whenSignedUp datetime (and records *both* date and time!)
   ... other fields ...

So now all you do is:

Code:

<%
CONST SLOTS = 100
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "...your connection string..."

SQL = "SELECT COUNT(*) FROM Signups"
Set RS = conn.Execute( SQL )
count = 0
If Not RS.EOF Then count = RS(0)
RS.Close
%>
We have <%=SLOTS%> slots available.<br/>
<%=count%> have already signed up.<br/>
<% If count >= SLOTS Then %>
    Sorry, there are no more slots available.
<% Else %>
    There are still <%=(SLOTS-count)%> slots available.
<% End If %>
...

Now you have the number of signups CORRECT TO THE MILLISECOND!

Old Pedant · April 22nd, 2009, 06:25 PM

And by the way, you could *ALSO* get the count at any time in the past, if you had reason to do so.

For example, you could find out how many had signed up a week ago via

Code:

SELECT COUNT(*) FROM Signups WHERE whenSignedUp < DATE()-6

(That's for Access DB; slight differences for other DBs.)

Or you could find out how many signed up yesterday, say:

Code:

SELECT COUNT(*) FROM Signups WHERE DATEVALUE(whenSignedUp) = DATE()-1

(Again, that's for Access. Ask if you want it for a different DB.)

Many many variations on that theme possible.