Select help please

M

MikeR

My table looks like this. All text fields, except 'RESOLVED' which is Y/N.
NAME ZONE REGION REP RESOLVED QSLR
Jones 21 SE 41 Y abc
Smith 21 SE 41 axy
Ivy 21 MW 41 z
Martin 24 SE 40 o
Barnes 24 SE 40 cc
Doe 21 SE 43 np
Bass 21 SE 44

The query needs to return rows where:
1. 'Resolved' is N (or not Y)
2. QSLR is not empty (NULL or space)
3. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = Y , exclude that row.
4. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = N , return only 1 row.

Should return rows for Ivy, Martin and Doe

The first 2 I can do. The last 2 have me buffaloed.
TIA, Mike
 
J

John W. Vinson

My table looks like this. All text fields, except 'RESOLVED' which is Y/N.
NAME ZONE REGION REP RESOLVED QSLR
Jones 21 SE 41 Y abc
Smith 21 SE 41 axy
Ivy 21 MW 41 z
Martin 24 SE 40 o
Barnes 24 SE 40 cc
Doe 21 SE 43 np
Bass 21 SE 44

The query needs to return rows where:
1. 'Resolved' is N (or not Y)
2. QSLR is not empty (NULL or space)
3. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = Y , exclude that row.
4. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = N , return only 1 row.

Should return rows for Ivy, Martin and Doe
Click to expand...

Ok... a SelfJoin query will be needed here.

SELECT A.[Name], A.Zone, A.Region, A.Rep, First(A.QSLR) AS FirstOfQSLR
FROM table AS A INNER JOIN table AS B
ON A.Zone = B.Zone AND A.Region = B.Region AND A.Rep = B.Rep
WHERE A.Resolved = "N" Or A.Resolved IS NULL
AND (A.Resolved = "N" AND B.Resolved <> "Y")
GROUP BY A.[Name], A.Zone, A.Region, A.Rep;
 
M

MikeR

John said:
My table looks like this. All text fields, except 'RESOLVED' which is Y/N.
NAME ZONE REGION REP RESOLVED QSLR
Jones 21 SE 41 Y abc
Smith 21 SE 41 axy
Ivy 21 MW 41 z
Martin 24 SE 40 o
Barnes 24 SE 40 cc
Doe 21 SE 43 np
Bass 21 SE 44

The query needs to return rows where:
1. 'Resolved' is N (or not Y)
2. QSLR is not empty (NULL or space)
3. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = Y , exclude that row.
4. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = N , return only 1 row.

Should return rows for Ivy, Martin and Doe
Click to expand...

Ok... a SelfJoin query will be needed here.

SELECT A.[Name], A.Zone, A.Region, A.Rep, First(A.QSLR) AS FirstOfQSLR
FROM table AS A INNER JOIN table AS B
ON A.Zone = B.Zone AND A.Region = B.Region AND A.Rep = B.Rep
WHERE A.Resolved = "N" Or A.Resolved IS NULL
AND (A.Resolved = "N" AND B.Resolved <> "Y")
GROUP BY A.[Name], A.Zone, A.Region, A.Rep;
Click to expand...
John -
Thanks, it's almost perfect. The miss is entirely my fault, and I apologize for
misleading you. I left out a field, and gave you a wrong data type for another. Zone
is a number, and all zones with the same 1, 2, or 3 digits before the decimal should
be considered equal (21, 21.3, 21.777 are equal) or (1.8, 1.8105, 1.8022 are equal).

NAME ZONE REGION REP RESOLVED QSLR CID
Jones 21 SE 41 Y abc K
Smith 21.3 SE 41 axy YU
Ivy 1.8 MW 41 z J6
Martin 24 SE 40 o XE
Barnes 24 SE 40 cc K
Doe 21 SE 43 np LU
Bass 21.6 SE 44 LU
Haire 1.8025 MW 41 xx J6

Exclude from result set
Jones - Resolved is true
Bass - QSLR is blank
Haire - Matches Ivy

Include
Smith
Ivy
Martin
Barnes
Doe
 
J

John W. Vinson

Thanks, it's almost perfect. The miss is entirely my fault, and I apologize for
misleading you. I left out a field, and gave you a wrong data type for another. Zone
is a number, and all zones with the same 1, 2, or 3 digits before the decimal should
be considered equal (21, 21.3, 21.777 are equal) or (1.8, 1.8105, 1.8022 are equal).
Click to expand...

Then it should be two fields. If it's not a number to be used in calculations,
I'd really recommend two Text fields; at the very least, two Long Integer
fields!

As it is, you'll need to join on a calculated expression (which will certainly
slow things down and make the recordset non-updateable):

SELECT A.[Name], A.Zone, A.Region, A.Rep, First(A.QSLR) AS FirstOfQSLR
FROM table AS A INNER JOIN table AS B
ON CLng(A.Zone) = CLng(B.Zone)
AND A.Region = B.Region
AND A.Rep = B.Rep
WHERE A.Resolved = "N" Or A.Resolved IS NULL
AND (A.Resolved = "N" AND B.Resolved <> "Y")
GROUP BY A.[Name], A.Zone, A.Region, A.Rep;

This will just ignore anything after the decimal.

I take it that the value after the decimal and the value of QSLR in the second
instance of the table are irrelevant?
 
M

MikeR

John said:
My table looks like this. All text fields, except 'RESOLVED' which is Y/N.
NAME ZONE REGION REP RESOLVED QSLR
Jones 21 SE 41 Y abc
Smith 21 SE 41 axy
Ivy 21 MW 41 z
Martin 24 SE 40 o
Barnes 24 SE 40 cc
Doe 21 SE 43 np
Bass 21 SE 44

The query needs to return rows where:
1. 'Resolved' is N (or not Y)
2. QSLR is not empty (NULL or space)
3. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = Y , exclude that row.
4. If a row with 'Resolved' = N matches (on 'Zone', 'Region', and 'REP') with one
where 'Resolved' = N , return only 1 row.

Should return rows for Ivy, Martin and Doe
Click to expand...

Ok... a SelfJoin query will be needed here.

SELECT A.[Name], A.Zone, A.Region, A.Rep, First(A.QSLR) AS FirstOfQSLR
FROM table AS A INNER JOIN table AS B
ON A.Zone = B.Zone AND A.Region = B.Region AND A.Rep = B.Rep
WHERE A.Resolved = "N" Or A.Resolved IS NULL
AND (A.Resolved = "N" AND B.Resolved <> "Y")
GROUP BY A.[Name], A.Zone, A.Region, A.Rep;
Click to expand...
John -
Thanks, it's almost perfect. The miss is entirely my fault, and I apologize for
misleading you. I left out a field, and gave you a wrong data type for another, and
missed an equality. Zone is a number, and all zones with the same 1, 2, or 3 digits
before the decimal should be considered equal (21, 21.3, 21.777 are equal) or (1.8,
1.8105, 1.8022 are equal).
Additionally, Rep containing SSB, USL, LSB, AM, PHO are equal.
Is this possible? Do I detect a table design flaw?

LNAME ZONE REGION REP RESOLVED QSLR CID
Jones 21 SE 41SSB Y abc K
Smith 21.3 SE 41USB axy K
Ivy 1.8 MW 41LSB z J6
Martin 24 SE 40CW o XE
Barnes 24 SE 40AM cc K
Doe 21 SE 43TTY np LU
Bass 21.6 SE 44USB LU
Haire 1.8025 MW 41PHO xx J6

Exclude from result set
Jones - Resolved is true
Smith - Matches Jones
Bass - QSLR is blank
Haire - Matches Ivy

Include
Ivy - Resolved is false and QSLR is not blank
Martin - 1st occurrence of Zone, Region, Rep and CID, Resolved is F, QSLR not blank
Barnes - 1st occurrence of Zone, Region, Rep and CID, Resolved is F, QSLR not blank
Doe - 1st occurrence of Zone, Region, Rep and CID, Resolved is F, QSLR not blank
 
J

John W. Vinson

Additionally, Rep containing SSB, USL, LSB, AM, PHO are equal.
Is this possible? Do I detect a table design flaw?
Click to expand...

Yep. Your Rep field is not atomic - if a value of XYZ is treated differently
than a value USL you really need another field! You'll need to use a (MUCH
less efficient) WHERE clause join:

SELECT A.[Name], A.Zone, A.Region, A.Rep, First(A.QSLR) AS FirstOfQSLR
FROM table AS A INNER JOIN table AS B
A.Region = B.Region
WHERE CLng(A.Zone) = CLng(B.Zone)
AND (A.Rep = B.Rep
OR (A.Rep IN ("SSB", "USL", "LSB", "AM", "PHO")
AND B.Rep IN ("SSB", "USL", "LSB", "AM", "PHO")))
AND A.Resolved = "N" Or A.Resolved IS NULL
AND (A.Resolved = "N" AND B.Resolved <> "Y")
GROUP BY A.[Name], A.Zone, A.Region, A.Rep;
 
M

MikeR

John W. Vinson wrote:
I see you got to this before I cancelled it, and replaced it with the one below.
Then it should be two fields. If it's not a number to be used in calculations,
I'd really recommend two Text fields; at the very least, two Long Integer
fields!

As it is, you'll need to join on a calculated expression (which will certainly
slow things down and make the recordset non-updateable):
Click to expand...
Non updateable is OK. It may be a small enough set where the speed isn't too badly
degraded.
SELECT A.[Name], A.Zone, A.Region, A.Rep, First(A.QSLR) AS FirstOfQSLR
FROM table AS A INNER JOIN table AS B
ON CLng(A.Zone) = CLng(B.Zone)
AND A.Region = B.Region
AND A.Rep = B.Rep
WHERE A.Resolved = "N" Or A.Resolved IS NULL
AND (A.Resolved = "N" AND B.Resolved <> "Y")
GROUP BY A.[Name], A.Zone, A.Region, A.Rep;

This will just ignore anything after the decimal.

I take it that the value after the decimal and the value of QSLR in the second
instance of the table are irrelevant?
Click to expand...
Yes and yes - QSLR just has to be non blank
 
M

MikeR

John said:
Yep. Your Rep field is not atomic - if a value of XYZ is treated differently
than a value USL you really need another field! You'll need to use a (MUCH
less efficient) WHERE clause join:

SELECT A.[Name], A.Zone, A.Region, A.Rep, First(A.QSLR) AS FirstOfQSLR
FROM table AS A INNER JOIN table AS B
A.Region = B.Region
WHERE CLng(A.Zone) = CLng(B.Zone)
AND (A.Rep = B.Rep
OR (A.Rep IN ("SSB", "USL", "LSB", "AM", "PHO")
AND B.Rep IN ("SSB", "USL", "LSB", "AM", "PHO")))
AND A.Resolved = "N" Or A.Resolved IS NULL
AND (A.Resolved = "N" AND B.Resolved <> "Y")
GROUP BY A.[Name], A.Zone, A.Region, A.Rep;
Click to expand...
John -
Thank you. It's MAGIC! Works like a charm. I'm trying to understand how <G>.
Mike
 

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

The Mt. Everest of requests 4
Export CSV dash is \226 (unicode) 3
Numbering rows when printing 7
Double Match 6
Pivot Table Summarizing Quirk - Please Help 1
Windows crash - Help with finding source please! 1
Please Help, Comp Freezes! 3
Ready for this? It's a doozy! 4

Top