Query structure and distinct

P

Paul

Hi
I have a query where I am trying to return one "hearing" record (child
record) per "case" record (parent record) if the three WHERE qualifiers are
met. When use a DISTINCT statement however, the query omits hearing records
when there are multiple hearing records and only 1 record meets the qualifier
statements. When I don't use DISTINCT, the query obviously includes multiple
hearing records for a case record where more than 1 record meets the
qualifiers--which we don't want.

I am including the query below. If anyone has any thoughts I would
definitely appreciate it.

Paul



SELECT Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True));
 
V

vanderghast

You can use a GROUP BY query, list the fields for which you want only one on
their common value in the GROUP BY, and aggregate the other (with LAST, or
FIRST).


SELECT Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]


is the same as using DISTINCT.



SELECT Case.FinalDispositionDate, LAST(Case.DispositionCode),
LAST(Case.CaseNumber),
LAST(Hearing.[Confirmed?])
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate


return one record by FinalDispostionDate


SELECT LAST(Case.FinalDispositionDate), Case.DispositionCode,
LAST(Case.CaseNumber0,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.DispositionCode, Hearing.[Confirmed?]


return one record by ( DispositionCode, [Confirmed?] )

ie, you can have a row with ( 1010, true) and another row with (1010,
false), but NOT two rows (1010, true).


NOTE: each field in the SELECT not appearing in the GROUP BY list should be
AGGREGATED (SUM, MIN, MAX, AVG, FIRST, LAST, ...). In your case, it seems
that FIRST or LAST are the appropriate one: they pump the values from the
same record, one of them, among all records fitting in the defined GROUP BY
list. They are not the EARLIEST or LATEST record entered, but the first or
last record SEEN by the database engine in its strategy used to make the
result.



Vanderghast, Access MVP

Paul said:
Hi
I have a query where I am trying to return one "hearing" record (child
record) per "case" record (parent record) if the three WHERE qualifiers
are
met. When use a DISTINCT statement however, the query omits hearing
records
when there are multiple hearing records and only 1 record meets the
qualifier
statements. When I don't use DISTINCT, the query obviously includes
multiple
hearing records for a case record where more than 1 record meets the
qualifiers--which we don't want.

I am including the query below. If anyone has any thoughts I would
definitely appreciate it.

Paul



SELECT Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True));
Click to expand...
 
J

John Spencer

There is another problem. The left join is not effective since you have
applied criteria against the HEARING table.

You need a separate saved query to get the records in the Hearing table where
Confirmed? = True

This query will return all the hearing records where Confirmed? = True
SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True

Save that as qryConfirmedHearing

NOW you can use it in place of Hearing in your query.
SELECT Case.FinalDispositionDate
, Case.DispositionCode
, Case.CaseNumber
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode)="001"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
You can use a GROUP BY query, list the fields for which you want only
one on their common value in the GROUP BY, and aggregate the other (with
LAST, or FIRST).


SELECT Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]


is the same as using DISTINCT.



SELECT Case.FinalDispositionDate, LAST(Case.DispositionCode),
LAST(Case.CaseNumber),
LAST(Hearing.[Confirmed?])
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate


return one record by FinalDispostionDate


SELECT LAST(Case.FinalDispositionDate), Case.DispositionCode,
LAST(Case.CaseNumber0,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.DispositionCode, Hearing.[Confirmed?]


return one record by ( DispositionCode, [Confirmed?] )

ie, you can have a row with ( 1010, true) and another row with
(1010, false), but NOT two rows (1010, true).


NOTE: each field in the SELECT not appearing in the GROUP BY list
should be AGGREGATED (SUM, MIN, MAX, AVG, FIRST, LAST, ...). In your
case, it seems that FIRST or LAST are the appropriate one: they pump the
values from the same record, one of them, among all records fitting in
the defined GROUP BY list. They are not the EARLIEST or LATEST record
entered, but the first or last record SEEN by the database engine in its
strategy used to make the result.



Vanderghast, Access MVP

Paul said:
Hi
I have a query where I am trying to return one "hearing" record (child
record) per "case" record (parent record) if the three WHERE
qualifiers are
met. When use a DISTINCT statement however, the query omits hearing
records
when there are multiple hearing records and only 1 record meets the
qualifier
statements. When I don't use DISTINCT, the query obviously includes
multiple
hearing records for a case record where more than 1 record meets the
qualifiers--which we don't want.

I am including the query below. If anyone has any thoughts I would
definitely appreciate it.

Paul



SELECT Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True));
Click to expand...
Click to expand...
 
P

Paul

Hi
Based on both of the below comments, I edited the original query into 2
queries and added the Group By, however, when I run it, I get 0 records:


qryConfirmedHearing:

SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True;


SELECT Last(Case.FinalDispositionDate)
, Case.DispositionCode
, Last(Case.CaseNumber)
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode="001"
GROUP BY Case.DispositionCode, qryConfirmedHearing.[Confirmed?];




John Spencer said:
There is another problem. The left join is not effective since you have
applied criteria against the HEARING table.

You need a separate saved query to get the records in the Hearing table where
Confirmed? = True

This query will return all the hearing records where Confirmed? = True
SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True

Save that as qryConfirmedHearing

NOW you can use it in place of Hearing in your query.
SELECT Case.FinalDispositionDate
, Case.DispositionCode
, Case.CaseNumber
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode)="001"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
You can use a GROUP BY query, list the fields for which you want only
one on their common value in the GROUP BY, and aggregate the other (with
LAST, or FIRST).


SELECT Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]


is the same as using DISTINCT.



SELECT Case.FinalDispositionDate, LAST(Case.DispositionCode),
LAST(Case.CaseNumber),
LAST(Hearing.[Confirmed?])
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate


return one record by FinalDispostionDate


SELECT LAST(Case.FinalDispositionDate), Case.DispositionCode,
LAST(Case.CaseNumber0,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.DispositionCode, Hearing.[Confirmed?]


return one record by ( DispositionCode, [Confirmed?] )

ie, you can have a row with ( 1010, true) and another row with
(1010, false), but NOT two rows (1010, true).


NOTE: each field in the SELECT not appearing in the GROUP BY list
should be AGGREGATED (SUM, MIN, MAX, AVG, FIRST, LAST, ...). In your
case, it seems that FIRST or LAST are the appropriate one: they pump the
values from the same record, one of them, among all records fitting in
the defined GROUP BY list. They are not the EARLIEST or LATEST record
entered, but the first or last record SEEN by the database engine in its
strategy used to make the result.



Vanderghast, Access MVP

Paul said:
Hi
I have a query where I am trying to return one "hearing" record (child
record) per "case" record (parent record) if the three WHERE
qualifiers are
met. When use a DISTINCT statement however, the query omits hearing
records
when there are multiple hearing records and only 1 record meets the
qualifier
statements. When I don't use DISTINCT, the query obviously includes
multiple
hearing records for a case record where more than 1 record meets the
qualifiers--which we don't want.

I am including the query below. If anyone has any thoughts I would
definitely appreciate it.

Paul



SELECT Case.FinalDispositionDate, Case.DispositionCode, Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True));
Click to expand...
Click to expand...
.
Click to expand...
 
V

vanderghast

If you remove the WHERE clause in the last query, you get something? if you
do, then the problem may be a date problem ( 1/10/2010 is January 10th or
in October First?), or dt_begin is after dt_end, or DispositionCode is never
STRICTLY equals to "001", or similar (if you want 'starting with "001", and
followed by something, rather use: DispositionCode LIKE "001*" ).


Vanderghast, Access MVP


Paul said:
Hi
Based on both of the below comments, I edited the original query into 2
queries and added the Group By, however, when I run it, I get 0 records:


qryConfirmedHearing:

SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True;


SELECT Last(Case.FinalDispositionDate)
, Case.DispositionCode
, Last(Case.CaseNumber)
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode="001"
GROUP BY Case.DispositionCode, qryConfirmedHearing.[Confirmed?];




John Spencer said:
There is another problem. The left join is not effective since you have
applied criteria against the HEARING table.

You need a separate saved query to get the records in the Hearing table
where
Confirmed? = True

This query will return all the hearing records where Confirmed? = True
SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True

Save that as qryConfirmedHearing

NOW you can use it in place of Hearing in your query.
SELECT Case.FinalDispositionDate
, Case.DispositionCode
, Case.CaseNumber
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode)="001"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
You can use a GROUP BY query, list the fields for which you want only
one on their common value in the GROUP BY, and aggregate the other
(with
LAST, or FIRST).


SELECT Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]


is the same as using DISTINCT.



SELECT Case.FinalDispositionDate, LAST(Case.DispositionCode),
LAST(Case.CaseNumber),
LAST(Hearing.[Confirmed?])
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate


return one record by FinalDispostionDate


SELECT LAST(Case.FinalDispositionDate), Case.DispositionCode,
LAST(Case.CaseNumber0,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.DispositionCode, Hearing.[Confirmed?]


return one record by ( DispositionCode, [Confirmed?] )

ie, you can have a row with ( 1010, true) and another row with
(1010, false), but NOT two rows (1010, true).


NOTE: each field in the SELECT not appearing in the GROUP BY list
should be AGGREGATED (SUM, MIN, MAX, AVG, FIRST, LAST, ...). In your
case, it seems that FIRST or LAST are the appropriate one: they pump
the
values from the same record, one of them, among all records fitting in
the defined GROUP BY list. They are not the EARLIEST or LATEST record
entered, but the first or last record SEEN by the database engine in
its
strategy used to make the result.



Vanderghast, Access MVP

Hi
I have a query where I am trying to return one "hearing" record (child
record) per "case" record (parent record) if the three WHERE
qualifiers are
met. When use a DISTINCT statement however, the query omits hearing
records
when there are multiple hearing records and only 1 record meets the
qualifier
statements. When I don't use DISTINCT, the query obviously includes
multiple
hearing records for a case record where more than 1 record meets the
qualifiers--which we don't want.

I am including the query below. If anyone has any thoughts I would
definitely appreciate it.

Paul



SELECT Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True));
Click to expand...
.
Click to expand...
Click to expand...
 
P

Paul

No, the same result: "no current record" message.

Is the "group by" information correct? I tried it a few ways, but nothing
worked.

Paul




vanderghast said:
If you remove the WHERE clause in the last query, you get something? if you
do, then the problem may be a date problem ( 1/10/2010 is January 10th or
in October First?), or dt_begin is after dt_end, or DispositionCode is never
STRICTLY equals to "001", or similar (if you want 'starting with "001", and
followed by something, rather use: DispositionCode LIKE "001*" ).


Vanderghast, Access MVP


Paul said:
Hi
Based on both of the below comments, I edited the original query into 2
queries and added the Group By, however, when I run it, I get 0 records:


qryConfirmedHearing:

SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True;


SELECT Last(Case.FinalDispositionDate)
, Case.DispositionCode
, Last(Case.CaseNumber)
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode="001"
GROUP BY Case.DispositionCode, qryConfirmedHearing.[Confirmed?];




John Spencer said:
There is another problem. The left join is not effective since you have
applied criteria against the HEARING table.

You need a separate saved query to get the records in the Hearing table
where
Confirmed? = True

This query will return all the hearing records where Confirmed? = True
SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True

Save that as qryConfirmedHearing

NOW you can use it in place of Hearing in your query.
SELECT Case.FinalDispositionDate
, Case.DispositionCode
, Case.CaseNumber
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode)="001"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

vanderghast wrote:
You can use a GROUP BY query, list the fields for which you want only
one on their common value in the GROUP BY, and aggregate the other
(with
LAST, or FIRST).


SELECT Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]


is the same as using DISTINCT.



SELECT Case.FinalDispositionDate, LAST(Case.DispositionCode),
LAST(Case.CaseNumber),
LAST(Hearing.[Confirmed?])
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate


return one record by FinalDispostionDate


SELECT LAST(Case.FinalDispositionDate), Case.DispositionCode,
LAST(Case.CaseNumber0,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.DispositionCode, Hearing.[Confirmed?]


return one record by ( DispositionCode, [Confirmed?] )

ie, you can have a row with ( 1010, true) and another row with
(1010, false), but NOT two rows (1010, true).


NOTE: each field in the SELECT not appearing in the GROUP BY list
should be AGGREGATED (SUM, MIN, MAX, AVG, FIRST, LAST, ...). In your
case, it seems that FIRST or LAST are the appropriate one: they pump
the
values from the same record, one of them, among all records fitting in
the defined GROUP BY list. They are not the EARLIEST or LATEST record
entered, but the first or last record SEEN by the database engine in
its
strategy used to make the result.



Vanderghast, Access MVP

Hi
I have a query where I am trying to return one "hearing" record (child
record) per "case" record (parent record) if the three WHERE
qualifiers are
met. When use a DISTINCT statement however, the query omits hearing
records
when there are multiple hearing records and only 1 record meets the
qualifier
statements. When I don't use DISTINCT, the query obviously includes
multiple
hearing records for a case record where more than 1 record meets the
qualifiers--which we don't want.

I am including the query below. If anyone has any thoughts I would
definitely appreciate it.

Paul



SELECT Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber = Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin] And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True));



.
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

The GROUP BY won't reduce the number of records to zero. The 'worse' that
may happen is to reduce all records to ONE, unless there is no record to
start with. The only way to have no record with a GROUP BY (with no HAVING
clause) is to have no record AFTER the WHERE clause is executed. Since you
have removed the where clause... is


SELECT * FROM [Case]


has any record? If it has none, the result would also have none.



Vanderghast, Access MVP.



Paul said:
No, the same result: "no current record" message.

Is the "group by" information correct? I tried it a few ways, but nothing
worked.

Paul




vanderghast said:
If you remove the WHERE clause in the last query, you get something? if
you
do, then the problem may be a date problem ( 1/10/2010 is January 10th
or
in October First?), or dt_begin is after dt_end, or DispositionCode is
never
STRICTLY equals to "001", or similar (if you want 'starting with "001",
and
followed by something, rather use: DispositionCode LIKE "001*" ).


Vanderghast, Access MVP


Paul said:
Hi
Based on both of the below comments, I edited the original query into 2
queries and added the Group By, however, when I run it, I get 0
records:


qryConfirmedHearing:

SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True;


SELECT Last(Case.FinalDispositionDate)
, Case.DispositionCode
, Last(Case.CaseNumber)
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode="001"
GROUP BY Case.DispositionCode, qryConfirmedHearing.[Confirmed?];




:

There is another problem. The left join is not effective since you
have
applied criteria against the HEARING table.

You need a separate saved query to get the records in the Hearing
table
where
Confirmed? = True

This query will return all the hearing records where Confirmed? = True
SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True

Save that as qryConfirmedHearing

NOW you can use it in place of Hearing in your query.
SELECT Case.FinalDispositionDate
, Case.DispositionCode
, Case.CaseNumber
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode)="001"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

vanderghast wrote:
You can use a GROUP BY query, list the fields for which you want
only
one on their common value in the GROUP BY, and aggregate the other
(with
LAST, or FIRST).


SELECT Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber =
Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin]
And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]


is the same as using DISTINCT.



SELECT Case.FinalDispositionDate, LAST(Case.DispositionCode),
LAST(Case.CaseNumber),
LAST(Hearing.[Confirmed?])
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber =
Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin]
And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.FinalDispositionDate


return one record by FinalDispostionDate


SELECT LAST(Case.FinalDispositionDate), Case.DispositionCode,
LAST(Case.CaseNumber0,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber =
Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin]
And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True))

GROUP BY Case.DispositionCode, Hearing.[Confirmed?]


return one record by ( DispositionCode, [Confirmed?] )

ie, you can have a row with ( 1010, true) and another row with
(1010, false), but NOT two rows (1010, true).


NOTE: each field in the SELECT not appearing in the GROUP BY list
should be AGGREGATED (SUM, MIN, MAX, AVG, FIRST, LAST, ...). In your
case, it seems that FIRST or LAST are the appropriate one: they pump
the
values from the same record, one of them, among all records fitting
in
the defined GROUP BY list. They are not the EARLIEST or LATEST
record
entered, but the first or last record SEEN by the database engine in
its
strategy used to make the result.



Vanderghast, Access MVP

Hi
I have a query where I am trying to return one "hearing" record
(child
record) per "case" record (parent record) if the three WHERE
qualifiers are
met. When use a DISTINCT statement however, the query omits hearing
records
when there are multiple hearing records and only 1 record meets the
qualifier
statements. When I don't use DISTINCT, the query obviously includes
multiple
hearing records for a case record where more than 1 record meets
the
qualifiers--which we don't want.

I am including the query below. If anyone has any thoughts I would
definitely appreciate it.

Paul



SELECT Case.FinalDispositionDate, Case.DispositionCode,
Case.CaseNumber,
Hearing.[Confirmed?]
FROM [Case] LEFT JOIN Hearing ON Case.CaseNumber =
Hearing.CaseNumber
WHERE (((Case.FinalDispositionDate)>=[Forms]![f_Dates]![dt_begin]
And
(Case.FinalDispositionDate)<[Forms]![f_Dates]![dt_end]) AND
((Case.DispositionCode)="001") AND ((Hearing.[Confirmed?])=True));



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

John Spencer

Does this query return records?

SELECT CaseNumber, [Confirmed?]
FROM Hearing
WHERE [Confirmed?] = True;

Does this query return records?
SELECT DISTINCT Case.FinalDispositionDate
, Case.DispositionCode
, Case.CaseNumber
FROM [Case]
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode="001"

If not try declaring the parameters so the query knows the parameter types.
It is possible that the query is interpreting the parameters as an arithmetic
expression instead of as dates. 12/1/2009 becomes a number that represents a
date & time on Dec 30, 1899 and 12-1-2009 becomes a number that represents a
date in 1894.

Parameters [Forms]![f_Dates]![dt_begin] DateTime,
[Forms]![f_Dates]![dt_end];
SELECT Case.FinalDispositionDate
, Case.DispositionCode
, Case.CaseNumber
, qryConfirmedHearing.[Confirmed?]
FROM [Case] LEFT JOIN qryConfirmedHearing
ON Case.CaseNumber = qryConfirmedHearing.CaseNumber
WHERE Case.FinalDispositionDate>=[Forms]![f_Dates]![dt_begin] And
Case.FinalDispositionDate<[Forms]![f_Dates]![dt_end] AND
Case.DispositionCode)="001"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 

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

Query loses records? 5
How do I modify a query and consolidate data? 1
Remove Duplicates in a Union Query 15
Union Query - Select Distinct 2 fields 7
Update query - sequential numbering 3
1) Run query through a table and 2) keep (or throw away) records 3
Append Query Locks my Database! 1
counting distinct from a query 2

Top