DCount... I think?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry for what is probably a simple question but I am having problems.

I have a query which is just returning records that have a 'Location Code'
of STHT. In each record is an ID number of a person. Each person can have
multiple records in the query. I want to add a new column to the query that
counts how many times the ID number (within that record) appears in the
entire query.

I have used the expression builder to do the following but it just counts
the total number of ID numbers in the entire query.

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]")

I think I somehow need to specify that the criteria only uses the particular
ID number from the record being calculated.

I am very limited with Access so please go easy on me. This is my last
attempt at trying to make friends with Access after which I will ignore its
existance and just use Excel!!!

Any assistance would be greatly appreciated.

Cheers

Paul
 
pablo bellissimo said:
Sorry for what is probably a simple question but I am having problems.

I have a query which is just returning records that have a 'Location Code'
of STHT. In each record is an ID number of a person. Each person can
have
multiple records in the query. I want to add a new column to the query
that
counts how many times the ID number (within that record) appears in the
entire query.

I have used the expression builder to do the following but it just counts
the total number of ID numbers in the entire query.

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]")

I think I somehow need to specify that the criteria only uses the
particular
ID number from the record being calculated.

I am very limited with Access so please go easy on me. This is my last
attempt at trying to make friends with Access after which I will ignore
its
existance and just use Excel!!!
Click to expand...

If ID_NO is a numeric field use this:

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]=" & [ID_NO])

otherwise use this:

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]='" & [ID_NO] & "'")

Don't forget the apostrophes in the second example!

Tom Lake
 
Tom - thanks for your help. It does seem to work but is taking forever.
There are 23k records and 40+ fields. i will definately use this in the
future though.

Paul

Tom Lake said:
pablo bellissimo said:
Sorry for what is probably a simple question but I am having problems.

I have a query which is just returning records that have a 'Location Code'
of STHT. In each record is an ID number of a person. Each person can
have
multiple records in the query. I want to add a new column to the query
that
counts how many times the ID number (within that record) appears in the
entire query.

I have used the expression builder to do the following but it just counts
the total number of ID numbers in the entire query.

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]")

I think I somehow need to specify that the criteria only uses the
particular
ID number from the record being calculated.

I am very limited with Access so please go easy on me. This is my last
attempt at trying to make friends with Access after which I will ignore
its
existance and just use Excel!!!
Click to expand...

If ID_NO is a numeric field use this:

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]=" & [ID_NO])

otherwise use this:

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]='" & [ID_NO] & "'")

Don't forget the apostrophes in the second example!

Tom Lake
Click to expand...
 
pablo bellissimo said:
Tom - thanks for your help. It does seem to work but is taking forever.
There are 23k records and 40+ fields. i will definately use this in the
future though.

Paul

Tom Lake said:
pablo bellissimo said:
Sorry for what is probably a simple question but I am having problems.

I have a query which is just returning records that have a 'Location
Code'
of STHT. In each record is an ID number of a person. Each person can
have
multiple records in the query. I want to add a new column to the query
that
counts how many times the ID number (within that record) appears in the
entire query.

I have used the expression builder to do the following but it just
counts
the total number of ID numbers in the entire query.

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]")

I think I somehow need to specify that the criteria only uses the
particular
ID number from the record being calculated.

I am very limited with Access so please go easy on me. This is my last
attempt at trying to make friends with Access after which I will ignore
its
existance and just use Excel!!!
Click to expand...

If ID_NO is a numeric field use this:

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]=" & [ID_NO])

otherwise use this:

IDCount: DCount("[ID_NO]","[STHT]","[ID_NO]='" & [ID_NO] & "'")

Don't forget the apostrophes in the second example!

Tom Lake
Click to expand...
Click to expand...



Although you can use DCOUNT in queries it is, as you have found out, very
slow. It is OK to show a single count on a form, but for use in queries you
need to use a single SQL statement - which will be much faster. If you need
help with this, just let us know the table structure you have.
 
Back
Top