Access 2000 - Counting records with specific criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chester1 - 30 Oct 2007 15:23 GMT

Please can anyone help?

I have a table that hold Course Attendees the ACode filed in the table can
be either
A - Attended
B - Booked on Course
C - Cancelled
D - Did Not Attend

I want to create something that will just count the records that are = to B
- not sure where to start as I am not sure about VBA or whether I can just
do it in a query. If you can help be please can you explain in a very basic
language for me to follow.

Signature

Regards Clair

Reply to this Message

Klatuu - 30 Oct 2007 16:34 GMT

=DCount("*","Table Name", "[ACode] = 'B'")

Signature

Dave Hargis, Microsoft Access MVP

> Please can anyone help?
>
[quoted text clipped - 9 lines]
> do it in a query. If you can help be please can you explain in a very basic
> language for me to follow.

Reply to this Message

Chester1 - 31 Oct 2007 12:33 GMT

Sorry this may seem a bit thick but where do I type this code

Signature

Regards Clair

> =DCount("*","Table Name", "[ACode] = 'B'")
>
[quoted text clipped - 11 lines]
> > do it in a query. If you can help be please can you explain in a very basic
> > language for me to follow.

Reply to this Message

Chester1 - 31 Oct 2007 13:06 GMT

The instructions you gave me worked fine, but I think I have asked the wrong
question. I need to know how many people a re booked on each course eg Word
Beginners, Word Intermediate etc. The Instructions give me a total count of
the table which is very good but I now need to break the number down to give
me the count for each course. Sorry for any inconvenience. Thanks in
anticipation!

Signature

Regards Clair

> Sorry this may seem a bit thick but where do I type this code
>
[quoted text clipped - 13 lines]
> > > do it in a query. If you can help be please can you explain in a very basic
> > > language for me to follow.

Reply to this Message

Klatuu - 31 Oct 2007 15:25 GMT

The last argument of the DCount function
v---------------v
=DCount("*","Table Name", "[ACode] = 'B'")

is how you do your filtering. It is just like writing a WHERE clause for a
query without the word WHERE. I was using your comments to build this. In
this example, it should count all records where the field Acode is equal to B.

Signature

Dave Hargis, Microsoft Access MVP

> The instructions you gave me worked fine, but I think I have asked the wrong
> question. I need to know how many people a re booked on each course eg Word
[quoted text clipped - 20 lines]
> > > > do it in a query. If you can help be please can you explain in a very basic
> > > > language for me to follow.

Reply to this Message

Chester1 - 31 Oct 2007 15:38 GMT

Klatuu

Please can you tell me how I would write a code now that will count the B's
on all the different courses scheduled so that I can then do a calculation
that will show how many places are left on the individual courses.

Regards Clair

> The last argument of the DCount function
> v---------------v
[quoted text clipped - 28 lines]
> > > > > do it in a query. If you can help be please can you explain in a very basic
> > > > > language for me to follow.

Reply to this Message

Klatuu - 31 Oct 2007 16:01 GMT

I can provide some ideas, but I don't know your data well enough to write it
for you.

First, you need to filter by course as well as by the B.

=DCount("*","Table Name", "[ACode] = 'B' AND CourseCode = '" & Me.Course &
"'")

To do the math you have to know how many total seats are available for the
course.

Signature

Dave Hargis, Microsoft Access MVP

> Klatuu
>
[quoted text clipped - 36 lines]
> > > > > > do it in a query. If you can help be please can you explain in a very basic
> > > > > > language for me to follow.

Reply to this Message

Chester1 - 31 Oct 2007 16:50 GMT

With all your help I have now managed to work out a way to get what is
required.

Many Thanks

Signature

Regards Clair

> I can provide some ideas, but I don't know your data well enough to write it
> for you.
[quoted text clipped - 47 lines]
> > > > > > > do it in a query. If you can help be please can you explain in a very basic
> > > > > > > language for me to follow.

Reply to this Message