Help with a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 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...
 
Michael D. said:
I have 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);
Click to expand...
Hi Michael,

Simple example:
("Num" is type Number in below table)

tblMicheal:

Num Grp
7404 D
7405 D
7406 D
8683 C
8684 C
8764 D


qryMichael:

SELECT
t.Num, t.Grp,
[Num]+1 AS NextNum,
Exists (SELECT A.Num FROM tblMichael As A
WHERE A.Num=t.Num +1 AND A.Grp=t.Grp) AS NextNumExists
FROM tblMichael AS t;

gives:

Num Grp NextNum NextNumExists
7404 D 7405 -1
7405 D 7406 -1
7406 D 7407 0
8683 C 8684 -1
8684 C 8685 0
8764 D 8765 0

query to get what I think you want:

SELECT
t1.Num,
t1.Grp,
(SELECT Min(Q.Num) FROM qryMichael As Q
WHERE Q.Num>=t1.Num
AND Q.Grp=t1.Grp
AND Q.NextNumExists=0) AS EndRange
FROM tblMichael AS t1;


Num Grp EndRange
7404 D 7406
7405 D 7406
7406 D 7406
8683 C 8684
8684 C 8684
8764 D 8764

All that's left would be to convert to UPDATE qry.

Good Luck,

gary
 
Just one more thought...

I am not sure I would store
this "EndOfRange" in a table.
It is something that can always
be "calculated" for current data.

gary
 
Back
Top