Help with count function

  • Thread starter Thread starter Ben M Rowe
  • Start date Start date
B

Ben M Rowe

Hi there, thanks in advance for any help, and apologies if this is a question
from the dumb kid at the back of the class.

I'm trying to get to grips with Access and so am designing a DVD/Book/CD
database. At the moment it's very simple. There are a number of linked
tables, but this query is only really interested in two of them.

The first table is a list of of what I own - a list of all my books, CDs and
DVDs (although not populated yet). One of the columns in the table is 'Type'.
This column is linked to another table (tblMedia) which has just three
records in it - 'Book', 'CD' and 'DVD'.

So then, what I want to do is add another column into the tblMedia which
counts how many I have of its respected media type.

What do I do?
 
what I want to do is add another column into the tblMedia which counts how
many I have of its respected media type.
Tables can not count. Queries can but you would need to keep updating the
table all the time so that is not good.
Just run a totals query whenever you need the information.
Or you can use DCount for an unbound text box on your forms.
 
Could you elaborate on the DCount option for me a bit? Thanks in advance.
Click to expand...

The datatype of [Type] is Text?
Using 3 unbound control on a form (or Report):
=DCount("*","TableName","[Type] = 'Book'")
=DCount("*","TableName","[Type] = 'CD'")
=DCount("*","TableName","[Type] = 'DVD'")

Change TableName to the name of the main table (not tblMedia).
These values are NOT saved in any table.

Look up DCount as well as
Restrict Data to a Subset of Records
in VBA help files.
 
The first table is a list of of what I own - a list of all my books, CDs and
DVDs (although not populated yet). One of the columns in the table is 'Type'.
This column is linked to another table (tblMedia) which has just three
records in it - 'Book', 'CD' and 'DVD'.

So then, what I want to do is add another column into the tblMedia which
counts how many I have of its respected media type.
Click to expand...

You should NOT store this count anywhere in any table - any value that you
store will be wrong the next time you add a record.

Instead, you can calculate the count, on demand, any time by using a Totals
query.

Create a new Query based on your table.
Select the Primary Key of your list of books, CDs and DVDs, and the Type field
(which you should change to MediaType since Type is a reserved word).

Change the query to a Totals query by clicking the Greek Sigma icon. Leave the
default Group By on MediaType (since you want to count how many items are in
each group of types); and change it to Count on the primary key field. Open
the query.

You'll see three rows in the resulting query - a count of books, a count of
CDs, and a count of DVDs.

Add new items to the table and rerun the query - you'll see the new counts.
 
Try these --

DCount("TYPE","tblMedia","TYPE = 'CD'")
DCount("TYPE","tblMedia","TYPE = 'DVD'")
DCount("TYPE","tblMedia","TYPE = 'Book'")

Use double quotes around your field name and table and single quotes around a
string criteria the 'where' part of the DCount statement
 
John W. Vinson said:
You should NOT store this count anywhere in any table - any value that you
store will be wrong the next time you add a record.

Instead, you can calculate the count, on demand, any time by using a Totals
query.

Create a new Query based on your table.
Select the Primary Key of your list of books, CDs and DVDs, and the Type field
(which you should change to MediaType since Type is a reserved word).

Change the query to a Totals query by clicking the Greek Sigma icon. Leave the
default Group By on MediaType (since you want to count how many items are in
each group of types); and change it to Count on the primary key field. Open
the query.

You'll see three rows in the resulting query - a count of books, a count of
CDs, and a count of DVDs.

Add new items to the table and rerun the query - you'll see the new counts.
Click to expand...

Mr Vinson that was extraordinarily helpful, thank you and I have now
achieved the first part of exactly what I wanted.

As an aside, I also have a 'genres' table, is it possible to count objects
from the main table that are, for instance, both DVDs and 'action'?
 
As an aside, I also have a 'genres' table, is it possible to count objects
from the main table that are, for instance, both DVDs and 'action'?
Click to expand...

Absolutely. Include the Genres field in the totals query; change its "totals"
row entry from Group By to Where; and put a criterion of "Action" on the
criteria line.

You might want to see some of the "how to use Access" tutorials on these
sites; Crystal's tutorial and/or video would be good places to start:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Back
Top