simple query count

[Guest]

i have a query that will return a large number of results (0000's) - but all
i really need the total of entries under different criteria.

Is there a way i can get the query to product just one result (the count )
rather than every record? the catergory i wish to count a result of a
calculated field.

ie - to count all "cpty"entries I have tried typing
sum(iif[field]="cpty",1,0) into the query field, but still no result?

any ideas what i;m doing wrong?
thx

 

[Duane Hookom]

SUYS (show us your sql)

 

[Guest]

ok....

SELECT DISTINCTROW IIf(Left([dbo_client]![customerAccount],3)="071" Or
Left([dbo_client]![customerAccount],3)="074","swng","cpty") AS trdtype,
Sum(IIf([trdtype]="cpty",1,0)) AS Total
FROM ((dbo_deal INNER JOIN dbo_security ON dbo_deal.cusip =
dbo_security.cusip) INNER JOIN dbo_equity_deal ON dbo_deal.dealReference =
dbo_equity_deal.dealReference) INNER JOIN dbo_client ON
dbo_deal.customerAccount = dbo_client.customerAccount
WHERE (((IIf(Left([dbo_client]![customerAccount],3)="071" Or
Left([dbo_client]![customerAccount],3)="074","swng","cpty"))="cpty") AND
((dbo_deal.valueDate) Between [Type a Start Date in 'dd/mm/yy' format:] And
[Type an End Date in 'dd/mm/yy' format:]) AND ((dbo_deal.customerAccount) Not
Like "045A00021" And (dbo_deal.customerAccount) Not Like "045A01771") AND
((IIf([structuredProductType]="DA","DT",IIf([structuredProductType]="FS","SP",[structuredProductType])))="DT") AND ((dbo_deal.tradedProductType)="EQ"))
ORDER BY dbo_deal.valueDate;

SUYS (show us your sql)

--
Duane Hookom
MS Access MVP
--

i have a query that will return a large number of results (0000's) - but
all
i really need the total of entries under different criteria.

Is there a way i can get the query to product just one result (the count )
rather than every record? the catergory i wish to count a result of a
calculated field.

ie - to count all "cpty"entries I have tried typing
sum(iif[field]="cpty",1,0) into the query field, but still no result?

any ideas what i;m doing wrong?
thx

 

[Duane Hookom]

Does that SQL even work? There is a Sum() without a GROUP BY section.
I can't figure out the purpose of:
((IIf([structuredProductType]="DA","DT",IIf([structuredProductType]="FS","SP",[structuredProductType])))="DT")

If you want only one value returned, try remove the first IIf(....)
expression from the SELECT...

--
Duane Hookom
MS Access MVP

ok....

SELECT DISTINCTROW IIf(Left([dbo_client]![customerAccount],3)="071" Or
Left([dbo_client]![customerAccount],3)="074","swng","cpty") AS trdtype,
Sum(IIf([trdtype]="cpty",1,0)) AS Total
FROM ((dbo_deal INNER JOIN dbo_security ON dbo_deal.cusip =
dbo_security.cusip) INNER JOIN dbo_equity_deal ON dbo_deal.dealReference =
dbo_equity_deal.dealReference) INNER JOIN dbo_client ON
dbo_deal.customerAccount = dbo_client.customerAccount
WHERE (((IIf(Left([dbo_client]![customerAccount],3)="071" Or
Left([dbo_client]![customerAccount],3)="074","swng","cpty"))="cpty") AND
((dbo_deal.valueDate) Between [Type a Start Date in 'dd/mm/yy' format:]
And
[Type an End Date in 'dd/mm/yy' format:]) AND ((dbo_deal.customerAccount)
Not
Like "045A00021" And (dbo_deal.customerAccount) Not Like "045A01771") AND
((IIf([structuredProductType]="DA","DT",IIf([structuredProductType]="FS","SP",[structuredProductType])))="DT")
AND ((dbo_deal.tradedProductType)="EQ"))
ORDER BY dbo_deal.valueDate;

SUYS (show us your sql)

--
Duane Hookom
MS Access MVP
--

i have a query that will return a large number of results (0000's) - but
all
i really need the total of entries under different criteria.

Is there a way i can get the query to product just one result (the
count )
rather than every record? the catergory i wish to count a result of a
calculated field.

ie - to count all "cpty"entries I have tried typing
sum(iif[field]="cpty",1,0) into the query field, but still no result?

any ideas what i;m doing wrong?
thx