dcount in a query?

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

Guest

Hi,

I am trying to count the number of related records each member of an
organisation has.

eg.

main table with member specific info

MemberNumber
member name
address, phone etc


Extras table with info on "extra claims" made

member_number
claim type
service number
provider number etc

What I want is a count of claims each member has made.

I am trying to use a dcount in a query column but am having a problem with
the criteria portion of the dcount syntax.

I get a message "microsoft Access can't find the field MemberNumber entered
in the expression".

Here is the SQL for the query:

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]=MemberNumber") AS extra
FROM customer;

Any suggestions or areas to look into would be appreciated.

Access 2002 SP3

Terry
 
Try this

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]= " & customer.[MemberNumber]) AS extra
FROM customer
 
Ofer,

Thanks for the quick response. Tried your suggestion and recieve the same
message (with a customer.) in the can't find part of the message.

Note: I am not using a totals query because I wish to use this for an update
when I get the results I want.

Thanks again Ofer for your response.

Terry

Ofer said:
Try this

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]= " & customer.[MemberNumber]) AS extra
FROM customer


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


TerryC said:
Hi,

I am trying to count the number of related records each member of an
organisation has.

eg.

main table with member specific info

MemberNumber
member name
address, phone etc


Extras table with info on "extra claims" made

member_number
claim type
service number
provider number etc

What I want is a count of claims each member has made.

I am trying to use a dcount in a query column but am having a problem with
the criteria portion of the dcount syntax.

I get a message "microsoft Access can't find the field MemberNumber entered
in the expression".

Here is the SQL for the query:

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]=MemberNumber") AS extra
FROM customer;

Any suggestions or areas to look into would be appreciated.

Access 2002 SP3

Terry
Click to expand...
Click to expand...
 
Is MemberNumber a text field? If yes, you need to delimit the value with '
characters.

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]= '" & customer.[MemberNumber] & "'") AS
extra
FROM customer

Note that using a subquery in this situation is usually faster than using
DCount:

SELECT customer.MemberNumber,
(SELECT Count(*) FROM extras
WHERE extras.[member_number]= '" &
customer.[MemberNumber] & "'") AS extra
FROM customer;
--

Ken Snell
<MS ACCESS MVP>


TerryC said:
Ofer,

Thanks for the quick response. Tried your suggestion and recieve the same
message (with a customer.) in the can't find part of the message.

Note: I am not using a totals query because I wish to use this for an
update
when I get the results I want.

Thanks again Ofer for your response.

Terry

Ofer said:
Try this

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]= " & customer.[MemberNumber]) AS
extra
FROM customer


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


TerryC said:
Hi,

I am trying to count the number of related records each member of an
organisation has.

eg.

main table with member specific info

MemberNumber
member name
address, phone etc


Extras table with info on "extra claims" made

member_number
claim type
service number
provider number etc

What I want is a count of claims each member has made.

I am trying to use a dcount in a query column but am having a problem
with
the criteria portion of the dcount syntax.

I get a message "microsoft Access can't find the field MemberNumber
entered
in the expression".

Here is the SQL for the query:

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]=MemberNumber") AS extra
FROM customer;

Any suggestions or areas to look into would be appreciated.

Access 2002 SP3

Terry
Click to expand...
Click to expand...
Click to expand...
 
Ken,

Thanks. The sub-query did the trick. And no the memberneumber and
member_number fields are both long integers.

It still puzzles me why the dcount wouldn't do what I expected .... but I'm
not losing any more sleep over that.

Thanks again

Terry

Ken Snell said:
Is MemberNumber a text field? If yes, you need to delimit the value with '
characters.

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]= '" & customer.[MemberNumber] & "'") AS
extra
FROM customer

Note that using a subquery in this situation is usually faster than using
DCount:

SELECT customer.MemberNumber,
(SELECT Count(*) FROM extras
WHERE extras.[member_number]= '" &
customer.[MemberNumber] & "'") AS extra
FROM customer;
--

Ken Snell
<MS ACCESS MVP>


TerryC said:
Ofer,

Thanks for the quick response. Tried your suggestion and recieve the same
message (with a customer.) in the can't find part of the message.

Note: I am not using a totals query because I wish to use this for an
update
when I get the results I want.

Thanks again Ofer for your response.

Terry

Ofer said:
Try this

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]= " & customer.[MemberNumber]) AS
extra
FROM customer


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


:

Hi,

I am trying to count the number of related records each member of an
organisation has.

eg.

main table with member specific info

MemberNumber
member name
address, phone etc


Extras table with info on "extra claims" made

member_number
claim type
service number
provider number etc

What I want is a count of claims each member has made.

I am trying to use a dcount in a query column but am having a problem
with
the criteria portion of the dcount syntax.

I get a message "microsoft Access can't find the field MemberNumber
entered
in the expression".

Here is the SQL for the query:

SELECT customer.MemberNumber,
DCount("*","extras","[member_number]=MemberNumber") AS extra
FROM customer;

Any suggestions or areas to look into would be appreciated.

Access 2002 SP3

Terry
Click to expand...
Click to expand...
Click to expand...
 
Back
Top