show added records in a batabase

[meltingpot]

I have a small contacts data base system based on MS access. The interface is web based and written in ASP .

My boss needs a report showing how many new contacts have been added since the last report was run, expressed as a number. This will probably be a SQL query running a count(*).

Im stuck as to how to do this ... is it an ASP thing or SQL statement ..can anybody please help

 

[Tarwn]

This is a SQL thing. Hopefully you have some field that signifis when a contact was aded. If that is the case then all you will need to do is select the count from your table where the date is greater than whatever, where whatever is the date you last ran the report or beginning of the month or first day of the year or what have you.

Now you don't have a date field then you have a bigger problem. You have to determine how to tell whether a user has been added since the last time you queried. I can't think of any good examples of how to do that, probably because I always go with the date field method.

 

[meltingpot]

Thanks Tarwn - I did post in the access forum as well. Yep I do have a date entered field which I can use.... thanks for the tips, its eay when you know how...

many thanks

 

[Tarwn]

Since you do have a date field then basically all you should need to do is construct a SQL string something liek this:

Dim strSQL, dateVar
dateVar = "1/1/2006"
strSQL = "SELECT Count(someUniqueField) as CountAmount FROM YourTable WHERE yourDateField > '" & dateVar & "'"

I would suggest using your connection objects execute method to execute the query, rather than Recordset.Open, as Execute is generally more efficient:

'declare vars and setup connection
Dim conn, rsCount
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "your connection string"

'execute SQL string
Set rsCount = conn.Execute(strSQL)

'do some output
If rsCount.EOF Then 
   'you should never not get back something from a count
   Response.Write "Error<br/>"
Else
   Response.Write "number: " & rsCount("CountAmount") & "<br/>"
End If

Set rsCount = Nothing
conn.Close
Set conn = Nothing

Obviously the SQL statement is just an example of what yours would look like. The rest you probably already had, but on the off-chance that you weren't using Conn.Execute I wanted to show an example.

Hope this helps,
-T