Skip to main content
R
rockhiker
Inspiring
August 5, 2009
Question

count records and update total in table

  • August 5, 2009
  • 2 replies
  • 3169 views

I have a class schedule that people use to register for a class. The schedule displays how many people are registered but that is a manual entery be me in the database. Is there a way to set this up to where all of the registrations for a class are counted and the total displayed in the schedule?

Using Access 2007.

registratron form

action page

schedule page

thnks Jim

This topic has been closed for replies.

2 replies

A
Anonymous
August 5, 2009

You can run a query using your database 's native functions: in mysql it would look like:

<cfquery name="myquery" datasource="mydsn">

     select count(id) as total from mytable

</cfquery>

#myquery.total

Not sure what the syntax for access would be, but it should be similar.

-sean

EDIT: actually, if you are selecting the entire recorset anyway you don't need to run another query, coldfusion automatically makes the recordcount for the query available...  #myquery.RecordCount# returns the number of records returned from your original query.

R
rockhikerAuthor
Inspiring
August 5, 2009

I think I under stand the query you provided but I do not get how to popluate the number registered field in the db when a users registers.

A
Anonymous
August 5, 2009

Ok, you are storing the number of people that registered in your database? and each registered person gets one record in the database?

probably you don't need to store the number of records ie. the number of people since your database already knows how many records are there.

just display the count on the page. if you try to store the count you run the risk of introducing errors

but if you must store the count, you could use a subselect, run 2 queries, one to count and the other to insert, probably lots more options and probably lots more elegant options in fact I'd bet you could write an internal function or proceedure that updates your count table when your registration tables gets a new record automatically [though don't quote me on that, I don't use access much]

subselect:

<cfquery name="keepcount" datasource="mydsn">

     insert into mycounttable(select count(id) as total from mytable)

</cfquery>

2 queries

<cfquery name="myquery" datasource="mydsn">

     select count(id) as total from mytable

</cfquery>

<cfquery name="myquery" datasource="mydsn">

      insert into mycounttable ('countcol') values ('#myquery.RecordCount#')

</cfquery>

I tested neither of those and don't vouch for the syntax, but the logic should be good.

ilssac
ilssac
Inspiring
August 5, 2009

Yes, there is a way, probably thousands of ways.

You would use your registration form to get data that is processed on your action page into your Access 2007 database and subsequently displayed on the schedule page.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices