dcount in a query?

[TerryC]

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

 

[Ofer]

Try this

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

 

[TerryC]

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

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

 

[Ken Snell [MVP]]

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>

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

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

 

[TerryC]

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

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>

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

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