Anyone really good with queries?? :-)

[MikeD.]

Given this database structure:

GroupNumber GN End Range GroupName CustServInd
7404 Don and Deb Lawyer A
7405 Don and Deb Lawyer A
7406 Don and Deb Lawyer A
8683 Central Hardware Service A
8684 Central Hardware Service A
7616 Chemical Company HQ A
9528 Wild Workout Center A
0407 Wild Workout Center A
8219 Wood Mill A
8764 Don and Deb Lawyer A


TheGN End Range field is to be populated with the maximum GroupNumber for a
group, where a group is a unique GroupNumber and GroupName combo, AND THE
GroupNumber's are in SEQUENCE. The Group Number End Range is only to be
filled when there is more than 1 GroupNumber for a group. Thus, when the
module is complete, the above record set should look like this:

GroupNumber GN End Range GroupName CustServInd
7404 7406 Don and Deb Lawyer A
8683 8684 Central Hardware Service A
7616 Chemical Company HQ A
9528 Wild Workout Center A
0407 Wild Workout Center A
8219 Wood Mill A
8764 Don and Deb Lawyer A

The query I have below fills in Group Number End Range for Don and Deb
Lawyer as 8764, but this is incorrect, as it is not in sequence. Another
example would be Wild Workout Center. My query groups them as one, with an
end range of 0407. but they should be treated as separate groups, since the
numbers are not in sequence.

SELECT Min([GroupNumber]) AS MinGroupNumber,
IIf(Max([GroupNumber])>Min([GroupNumber]),Max([GroupNumber]),Null) AS
MaxGroupNumber, GroupName, CustSvcInd
FROM tblGroups
GROUP BY CustSvcInd, GroupName
ORDER BY CustSvcInd, Min(Groups.GroupNumber);

Hopefully this all makes sense! I really need to take an sql class, I know
bits and pieces but that's it...

[paross1]

Having only seen what you posted, and not knowing what the relationship is
between GroupNumber and GroupName, this hints of a hosed up data model. What
are you trying to do, exactly? Why would you have multiple groun numbers for
the same group name? It looks like GrouNumber may be a primary key, but is
GroupName a foreign key value of some kind, or is it unrelated to any other
entity?

Phil

[MikeD.]

I think it IS a hosed up data model, but I don't have any say over it... It is
data that is stored in some old mainframe program, I really don't even
understand the data. They just want to know when a Group Name appears multiple
times, and what the lowest and highest number is.

It comes to me as an excel spreadsheet, it's not a database with any
relationships or anything like that.

Thanks!

[The ScareCrow]

Select Max(GroupNumber) as maxnumber, Min(GroupNumber) As minnumber, GroupName
From myTable
Where 0=0
Having Count(GroupName) > 1
Group By GroupName

Not sure if you need the where clause of zero equals zero as I'm unable to
test now, but it will not hurt anyway.

Ken