Display Specific Grouped Records

[vmf]

I have a report grouped on 3 fields, Batch, Julian Date, & Work Type. In the
detail section I have the fields, Date, Start Time, Stop Time, Begin Sequence
#, End Sequence # and a few other fields. Example: In the group IB = 1, JD=
130, WT= ABC, you could have several records, some with the same date, some
with multiple dates. I want the report to display only those groups that
have records with dates that are not the same, ie. 5/15/2008, 5/16/2008.

Thank you,
vmf

 

[John Spencer]

I find your request unclear.

Do you mean you want to only show those records where there is only one record
for the combination of Batch (IB), Date (JD), and Worktype (WT)?

Or do you mean only those records where there is only one record of each
combination of Batch (IB), Date (JD), and Worktype (WT) plus Date?

Or do you not want to see the entire group based on Batch (IB), Date (JD), and
Worktype (WT) if there are any records within the group that have the same date?

For your given example of IB=1, JD=130, WT=ABC with Dates of
1/15
1/16
1/16
1/17
1/18
What records would you want returned, if any?

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[vmf]

I would want to see all detail records in the group because the dates are not
the same.

If the IB=1, JD=130, WT=ABC had 3 records with dates:
DATE BEG SEQ# END SEQ# START TIME END TIME
5/1 20 25 13:00 14:00
5/1 26 30 14:01 14:31
5/1 31 40 16:00 17:00
Then I do not want to see this group because all detail records have the
same date.

 

[John Spencer]

I think you need a query that looks something like the following.

SELECT Ta.*
FROM SOMETABLE as Ta
INNER JOIN
(SELECT IB, JD, WT
FROM
(SELECT Distinct S.IB, S.JD, S.WT, S.Date
FROM SomeTable as S)
Having Count(*) > 1) as TB
ON TA.IB = TB.IB
and TA.JD = TB.JD
and TA.WT =TB.WT

You can only do that all in one query if your table and field names consist of
only letters, numbers, and underscore characters. Otherwise, you will have to
use nested queries to accomplish the goal.

First query, return unique values for the four fields.
Second query uses the first query to get a count of the different dates by the
group
Third query, uses the second query and joins onthe three fields of the group
to return only records that match up.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[vmf]

Thank You for the 2 options! I'll see if I can make 1 of them work. This
site helped me so many times. Thanks again.

 

[vmf]

John, I tried the query option & it gave me exactly what I needed. Thank you
for your help.