Different Results with Detail and Summary

[sxschech]

I'm trying to figure out why this query yields 157 records (the correct number), but when I change the query to a summary query, I get 166. I must be overlooking something, but don't see what it is. I am joining three tables because each of the two main data tables are using a different ID system. The two queries are actually the same query, only difference between the queries below is that I went into design view and clicked on the Summary Button.

Detail Query=157 records (Correct amount)
[tt]
SELECT DISTINCT 3 AS SortOrder, [APP-2004-LST].Fall, "Enrolled" AS Status, [BILL-DEPOSIT-06FY].eg_H, [APP-2004-LST].ETHNIC, [APP-2004-LST].CWID
FROM ([APP-2004-LST] INNER JOIN SSNCWID ON [APP-2004-LST].CWID = SSNCWID.CWID) INNER JOIN [BILL-DEPOSIT-06FY] ON SSNCWID.SSN = [BILL-DEPOSIT-06FY].eg_5854139771
WHERE ((([BILL-DEPOSIT-06FY].eg_H)="LA") AND (([BILL-DEPOSIT-06FY].eg_CC)="CC") AND (([BILL-DEPOSIT-06FY].[eg_*]) Like "*") AND (([BILL-DEPOSIT-06FY].A) In ("D","G")));
[/tt]

Summary Query = 166 (Extra 9)
[tt]
SELECT DISTINCT 3 AS SortOrder, [APP-2004-LST].Fall, "Enrolled" AS Status, [BILL-DEPOSIT-06FY].eg_H, [APP-2004-LST].ETHNIC, Count([APP-2004-LST].CWID) AS CountOfCWID
FROM ([APP-2004-LST] INNER JOIN SSNCWID ON [APP-2004-LST].CWID = SSNCWID.CWID) INNER JOIN [BILL-DEPOSIT-06FY] ON SSNCWID.SSN = [BILL-DEPOSIT-06FY].eg_5854139771
GROUP BY [APP-2004-LST].Fall, [BILL-DEPOSIT-06FY].eg_H, [APP-2004-LST].ETHNIC, [BILL-DEPOSIT-06FY].eg_CC, [BILL-DEPOSIT-06FY].[eg_*], [BILL-DEPOSIT-06FY].A
HAVING ((([BILL-DEPOSIT-06FY].eg_H)="LA") AND (([BILL-DEPOSIT-06FY].eg_CC)="CC") AND (([BILL-DEPOSIT-06FY].[eg_*]) Like "*") AND (([BILL-DEPOSIT-06FY].A) In ("D","G")));
[/tt]

 

[dhookom]

Your group by should use WHERE rather than HAVING. Try:

SELECT DISTINCT 3 AS SortOrder, [APP-2004-LST].Fall, "Enrolled" AS Status, [BILL-DEPOSIT-06FY].eg_H, [APP-2004-LST].ETHNIC, Count([APP-2004-LST].CWID) AS CountOfCWID
FROM ([APP-2004-LST] INNER JOIN SSNCWID ON [APP-2004-LST].CWID = SSNCWID.CWID) INNER JOIN [BILL-DEPOSIT-06FY] ON SSNCWID.SSN = [BILL-DEPOSIT-06FY].eg_5854139771
WHERE ((([BILL-DEPOSIT-06FY].eg_H)="LA") AND (([BILL-DEPOSIT-06FY].eg_CC)="CC") AND (([BILL-DEPOSIT-06FY].[eg_*]) Like "*") AND (([BILL-DEPOSIT-06FY].A) In ("D","G")))
GROUP BY [APP-2004-LST].Fall, [BILL-DEPOSIT-06FY].eg_H, [APP-2004-LST].ETHNIC, [BILL-DEPOSIT-06FY].eg_CC, [BILL-DEPOSIT-06FY].[eg_*], [BILL-DEPOSIT-06FY].A;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sxschech]

Duane,

I pasted the revised sql and it still results in 166. In the meantime, what I have done is left the original query as a standard select query and than created a second query to do the summary and that one results in the correct answer. Still seems odd that haven't been able to do this in one query.

 

[Golom]

Its probably because of this field

[red][BILL-DEPOSIT-06FY].[eg_*][/red]

First, you are filtering on it with

(([BILL-DEPOSIT-06FY].[eg_*]) Like "*")

which is guaranteed to return all values of the field (i.e. no filtering). Then you are Grouping By that field so that different values of that field will create separate records.

In the detail version of the query that field doesn't appear in the SELECT clause so different values for it do not result in DISTINCT records.

 

[sxschech]

So I'll need to substitute a different character for the data field, or would is not null do the trick as the data value in the actual field is literally an asterisk (*).

 

[PHV]

Perhaps this ?
SELECT 3 AS SortOrder, [APP-2004-LST].Fall, "Enrolled" AS Status, [BILL-DEPOSIT-06FY].eg_H, [APP-2004-LST].ETHNIC, Count([APP-2004-LST].CWID) AS CountOfCWID
FROM ([APP-2004-LST]
INNER JOIN SSNCWID ON [APP-2004-LST].CWID = SSNCWID.CWID) INNER JOIN [BILL-DEPOSIT-06FY] ON SSNCWID.SSN = [BILL-DEPOSIT-06FY].eg_5854139771
WHERE [BILL-DEPOSIT-06FY].eg_H='LA' AND [BILL-DEPOSIT-06FY].eg_CC='CC' AND [BILL-DEPOSIT-06FY].[eg_*]='*' AND [BILL-DEPOSIT-06FY].A In ('D','G')
GROUP BY [APP-2004-LST].Fall, [BILL-DEPOSIT-06FY].eg_H, [APP-2004-LST].ETHNIC;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Depends on what you are trying to do.

If you want those records where the field contains an asterisk then

[BILL-DEPOSIT-06FY].[eg_*] = "*"

If it's the records where the field doesn't contain NULL then

[BILL-DEPOSIT-06FY].[eg_*] IS NOT NULL

The first of these will guarantee that every record has "*" for the value of that field and will therefore not create multiple Group By records.

The second (i.e. IS NOT NULL) has no such guarantee and you might get multiple records being created for different values in the field.

 

[sxschech]

PHV, Golom,

I tried your suggestions and still get 166. Also tried is not null and even len([eg_*])=1

 

[PHV]

I tried your suggestions
Really ? even the GROUP BY clause with only 3 fields ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sxschech]

I copied and pasted the SQL statement, was that not the way to go?

Results:
CountOfCWID
7
27
12
16
3
83
18
--------
166

Result using two queries
CountOfCWID
7
24
12
16
3
80
15
-------
157

 

[PHV]

Ah, you want a Count(DISTINCT [APP-2004-LST].CWID) ?
Unfortunately, JetSQL lacks this aggregate function, so you're stuck with an aggregate query counting a select distinct subquery.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886