dcount in query

G

Guest

hope someone can help. thought i knew what i was doing but....
here is a sql statment to show the appropriate records for contract. I want
the query to count the number of "rendered" services set to yes/no

I tried adding dcount("serviceid","services","rendered = -1") (for rendered)
but it counted all rendered services not just those specific to the contract
I entered.

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Services.Render, Services.ServiceID
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name],
Services.Render, Services.ServiceID
HAVING (((Contracts.ContractID)=[Enter Contract ID]));

Thanks
 
T

tina

well, do you want an actual field in the query output to show the total
number of rendered services for each contract? since the query is grouping
by the serviceID for each contract, that total value would be repeated in
each record - and the DCount() function would be run for each record.

is this query the basis for a form or report? if so, then suggest you
include a Sum() function in the form/report's header or footer section. add
an unbound textbox control to the section, and set the ControlSource
property to

=Abs(Sum([Render]))

hth
 
G

Guest

the 3 tables are each one-may Main>>Contracts>>Services.
I need to show just one specificied contract and count the services but show
the info from main. So i made the query with a parameter value of [enter
contract id]. Previously I had used an unbound text box with the contactid
in it (linked that to the query) then another unbound with the count. no
problem except that i had trouble with the name/contract name not requerying.
thought it would be easier to do in a query.....blah, blah.

What I need is to show Name, ContractID and a count of Rendered by YES/NO on
a form. Sorry -- long story to what I thought was an easy problem.



tina said:
well, do you want an actual field in the query output to show the total
number of rendered services for each contract? since the query is grouping
by the serviceID for each contract, that total value would be repeated in
each record - and the DCount() function would be run for each record.

is this query the basis for a form or report? if so, then suggest you
include a Sum() function in the form/report's header or footer section. add
an unbound textbox control to the section, and set the ControlSource
property to

=Abs(Sum([Render]))

hth


Ian said:
hope someone can help. thought i knew what i was doing but....
here is a sql statment to show the appropriate records for contract. I want
the query to count the number of "rendered" services set to yes/no

I tried adding dcount("serviceid","services","rendered = -1") (for rendered)
but it counted all rendered services not just those specific to the contract
I entered.

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Services.Render, Services.ServiceID
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name],
Services.Render, Services.ServiceID
HAVING (((Contracts.ContractID)=[Enter Contract ID]));

Thanks
Click to expand...
Click to expand...
 
T

tina

hmm, okay. i'm a bit confused as to why you'd have a service listed for a
contract, in the Services table, with a Render value of No (that service was
*not* rendered for this contract?). at any rate, if you have service records
listed for a contract, with Render values of Yes on some records, and No on
other records - and if you want to count only the services where Render =
Yes - then you don't need to *group on* the Rendered field and the ServiceID
field in the Totals query - that will return each record in the Services
table, for the specified ContractID. instead, try

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Count(Services.ServiceID) As TotalServices
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
WHERE (((Services.Render)=True) AND ((Contracts.ContractID)=[Enter Contract
ID]))
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name];

hth


Ian said:
the 3 tables are each one-may Main>>Contracts>>Services.
I need to show just one specificied contract and count the services but show
the info from main. So i made the query with a parameter value of [enter
contract id]. Previously I had used an unbound text box with the contactid
in it (linked that to the query) then another unbound with the count. no
problem except that i had trouble with the name/contract name not requerying.
thought it would be easier to do in a query.....blah, blah.

What I need is to show Name, ContractID and a count of Rendered by YES/NO on
a form. Sorry -- long story to what I thought was an easy problem.



tina said:
well, do you want an actual field in the query output to show the total
number of rendered services for each contract? since the query is grouping
by the serviceID for each contract, that total value would be repeated in
each record - and the DCount() function would be run for each record.

is this query the basis for a form or report? if so, then suggest you
include a Sum() function in the form/report's header or footer section. add
an unbound textbox control to the section, and set the ControlSource
property to

=Abs(Sum([Render]))

hth


Ian said:
hope someone can help. thought i knew what i was doing but....
here is a sql statment to show the appropriate records for contract.
Click to expand...
I
want
the query to count the number of "rendered" services set to yes/no

I tried adding dcount("serviceid","services","rendered = -1") (for rendered)
but it counted all rendered services not just those specific to the contract
I entered.

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Services.Render, Services.ServiceID
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name],
Services.Render, Services.ServiceID
HAVING (((Contracts.ContractID)=[Enter Contract ID]));

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

there are set services for each contract that have to be completed. the
contract is sold then thruout the year services are completed. as each is
completed it's rendered.

the query didn't really give me the info i need. I have to be able to see
the count of rendered and unrendered. in the query you sent i get the count
for rendered services but if a contract has no rendered services the is no
result. What i'd like to be able to put into a form in the end is:

Contract ID: xxxx
Name: xxxx
Count of Rendrered Services
Count of Unrendered Services

(from this I need to create a set of rules about altering contracts). I
think the DCount is what I need in the query but I don't have a clue on how
to make it work for the contract.


tina said:
hmm, okay. i'm a bit confused as to why you'd have a service listed for a
contract, in the Services table, with a Render value of No (that service was
*not* rendered for this contract?). at any rate, if you have service records
listed for a contract, with Render values of Yes on some records, and No on
other records - and if you want to count only the services where Render =
Yes - then you don't need to *group on* the Rendered field and the ServiceID
field in the Totals query - that will return each record in the Services
table, for the specified ContractID. instead, try

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Count(Services.ServiceID) As TotalServices
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
WHERE (((Services.Render)=True) AND ((Contracts.ContractID)=[Enter Contract
ID]))
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name];

hth


Ian said:
the 3 tables are each one-may Main>>Contracts>>Services.
I need to show just one specificied contract and count the services but show
the info from main. So i made the query with a parameter value of [enter
contract id]. Previously I had used an unbound text box with the contactid
in it (linked that to the query) then another unbound with the count. no
problem except that i had trouble with the name/contract name not requerying.
thought it would be easier to do in a query.....blah, blah.

What I need is to show Name, ContractID and a count of Rendered by YES/NO on
a form. Sorry -- long story to what I thought was an easy problem.



tina said:
well, do you want an actual field in the query output to show the total
number of rendered services for each contract? since the query is grouping
by the serviceID for each contract, that total value would be repeated in
each record - and the DCount() function would be run for each record.

is this query the basis for a form or report? if so, then suggest you
include a Sum() function in the form/report's header or footer section. add
an unbound textbox control to the section, and set the ControlSource
property to

=Abs(Sum([Render]))

hth


hope someone can help. thought i knew what i was doing but....
here is a sql statment to show the appropriate records for contract. I
want
the query to count the number of "rendered" services set to yes/no

I tried adding dcount("serviceid","services","rendered = -1") (for
rendered)
but it counted all rendered services not just those specific to the
contract
I entered.

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Services.Render, Services.ServiceID
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name],
Services.Render, Services.ServiceID
HAVING (((Contracts.ContractID)=[Enter Contract ID]));

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

think I'm almost there -- this gives me everything i need but the criteria in
the IIf clause isn't recognizing the render checkbox (tried -1/0, yes/no and
true/false)

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Count(IIf([Services.Render]=-1,1,0)) AS TotalYes,
Count(IIf([Services.Render]=0,1,0)) AS TotalNo
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name]
HAVING (((Contracts.ContractID)=[Enter ContractID]));


any idea

tina said:
hmm, okay. i'm a bit confused as to why you'd have a service listed for a
contract, in the Services table, with a Render value of No (that service was
*not* rendered for this contract?). at any rate, if you have service records
listed for a contract, with Render values of Yes on some records, and No on
other records - and if you want to count only the services where Render =
Yes - then you don't need to *group on* the Rendered field and the ServiceID
field in the Totals query - that will return each record in the Services
table, for the specified ContractID. instead, try

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Count(Services.ServiceID) As TotalServices
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
WHERE (((Services.Render)=True) AND ((Contracts.ContractID)=[Enter Contract
ID]))
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name];

hth


Ian said:
the 3 tables are each one-may Main>>Contracts>>Services.
I need to show just one specificied contract and count the services but show
the info from main. So i made the query with a parameter value of [enter
contract id]. Previously I had used an unbound text box with the contactid
in it (linked that to the query) then another unbound with the count. no
problem except that i had trouble with the name/contract name not requerying.
thought it would be easier to do in a query.....blah, blah.

What I need is to show Name, ContractID and a count of Rendered by YES/NO on
a form. Sorry -- long story to what I thought was an easy problem.



tina said:
well, do you want an actual field in the query output to show the total
number of rendered services for each contract? since the query is grouping
by the serviceID for each contract, that total value would be repeated in
each record - and the DCount() function would be run for each record.

is this query the basis for a form or report? if so, then suggest you
include a Sum() function in the form/report's header or footer section. add
an unbound textbox control to the section, and set the ControlSource
property to

=Abs(Sum([Render]))

hth


hope someone can help. thought i knew what i was doing but....
here is a sql statment to show the appropriate records for contract. I
want
the query to count the number of "rendered" services set to yes/no

I tried adding dcount("serviceid","services","rendered = -1") (for
rendered)
but it counted all rendered services not just those specific to the
contract
I entered.

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Services.Render, Services.ServiceID
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name],
Services.Render, Services.ServiceID
HAVING (((Contracts.ContractID)=[Enter Contract ID]));

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

think I'm almost there -- this gives me everything i need but the criteria in
the IIf clause isn't recognizing the render checkbox (tried -1/0, yes/no and
true/false)

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Count(IIf([Services.Render]=-1,1,0)) AS TotalYes,
Count(IIf([Services.Render]=0,1,0)) AS TotalNo
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name]
HAVING (((Contracts.ContractID)=[Enter ContractID]));
Click to expand...

Problem with the bracketing I'd say - try [Services].[Render]


John W. Vinson [MVP]
 
T

tina

the query didn't really give me the info i need. I have to be able to see
the count of rendered and unrendered. in the query you sent i get the count
for rendered services but if a contract has no rendered services the is no
result.
Click to expand...

then just group by the Render field as well, that should give you a count of
both, as

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Services.Render, Count(Services.ServiceID) As TotalServices
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
WHERE ((Contracts.ContractID)=[Enter Contract
ID]))
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name],
Services.Render;

hth


Ian said:
there are set services for each contract that have to be completed. the
contract is sold then thruout the year services are completed. as each is
completed it's rendered.

the query didn't really give me the info i need. I have to be able to see
the count of rendered and unrendered. in the query you sent i get the count
for rendered services but if a contract has no rendered services the is no
result. What i'd like to be able to put into a form in the end is:

Contract ID: xxxx
Name: xxxx
Count of Rendrered Services
Count of Unrendered Services

(from this I need to create a set of rules about altering contracts). I
think the DCount is what I need in the query but I don't have a clue on how
to make it work for the contract.


tina said:
hmm, okay. i'm a bit confused as to why you'd have a service listed for a
contract, in the Services table, with a Render value of No (that service was
*not* rendered for this contract?). at any rate, if you have service records
listed for a contract, with Render values of Yes on some records, and No on
other records - and if you want to count only the services where Render =
Yes - then you don't need to *group on* the Rendered field and the ServiceID
field in the Totals query - that will return each record in the Services
table, for the specified ContractID. instead, try

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Count(Services.ServiceID) As TotalServices
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
WHERE (((Services.Render)=True) AND ((Contracts.ContractID)=[Enter Contract
ID]))
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name];

hth


Ian said:
the 3 tables are each one-may Main>>Contracts>>Services.
I need to show just one specificied contract and count the services
Click to expand...
but
show
the info from main. So i made the query with a parameter value of [enter
contract id]. Previously I had used an unbound text box with the contactid
in it (linked that to the query) then another unbound with the count. no
problem except that i had trouble with the name/contract name not requerying.
thought it would be easier to do in a query.....blah, blah.

What I need is to show Name, ContractID and a count of Rendered by
Click to expand...
YES/NO
on
a form. Sorry -- long story to what I thought was an easy problem.



:

well, do you want an actual field in the query output to show the total
number of rendered services for each contract? since the query is grouping
by the serviceID for each contract, that total value would be
Click to expand...
repeated
in
each record - and the DCount() function would be run for each record.

is this query the basis for a form or report? if so, then suggest you
include a Sum() function in the form/report's header or footer
Click to expand...
section.
add
an unbound textbox control to the section, and set the ControlSource
property to

=Abs(Sum([Render]))

hth


hope someone can help. thought i knew what i was doing but....
here is a sql statment to show the appropriate records for
Click to expand...
contract.
I
want
the query to count the number of "rendered" services set to yes/no

I tried adding dcount("serviceid","services","rendered = -1") (for
rendered)
but it counted all rendered services not just those specific to the
contract
I entered.

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Services.Render, Services.ServiceID
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name],
Services.Render, Services.ServiceID
HAVING (((Contracts.ContractID)=[Enter Contract ID]));

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Got it. The problem was with the count. It was counting the 1's and 0's so
each result for yes/no was giving the same result (count of the number of
boxes) Changed it to Sum(IIf.... and that did the trick. thanks for all the
help.



John W. Vinson said:
think I'm almost there -- this gives me everything i need but the criteria in
the IIf clause isn't recognizing the render checkbox (tried -1/0, yes/no and
true/false)

SELECT Contracts.ContractID, [Last Name] & ", " & [First Name] AS Name,
Count(IIf([Services.Render]=-1,1,0)) AS TotalYes,
Count(IIf([Services.Render]=0,1,0)) AS TotalNo
FROM (Contracts INNER JOIN Services ON Contracts.ContractID =
Services.ContractID) INNER JOIN Main ON Contracts.ClientID = Main.ClientID
GROUP BY Contracts.ContractID, [Last Name] & ", " & [First Name]
HAVING (((Contracts.ContractID)=[Enter ContractID]));
Click to expand...

Problem with the bracketing I'd say - try [Services].[Render]


John W. Vinson [MVP]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Am I using too many filters? 5
Crosstab AND not OR 2
Access2000: complex query problem 2
HELP WITH Access QUERY PLEASE :) 8
Dcount returning no results!!! 0
Verrrrrrry slow Query due to dcount 2
Summary query sum from different tables 15
dcount in a crosstab query 3

Top