Challenging analysis inquiry...

B

Bonnie A

Hi everyone. Using A02 on XP. While I am stumped, I know this is easy for you
guys.

I have been given a spreadsheet containing SSNPolNum and AssetCode. This is
the contract number and a sum of fund numbers. My project is to identify all
SSNPolNum records where there is more than one record. Then I need to compare
the AssetCode to see if they are all equal. If they are Result=Yes, if not
Result=No.

For example:

SSNPolNum AssetCode Result
123456789 150 No 150 does not equal 147
123456789 147 No

456789123 253 Yes 253 equals 253 equals 253
456789123 253 Yes
456789123 253 Yes

789123456 1054 No 1054 equals 1054 does not
equal 553
789123456 1054 No
789123456 553 No

If there is only 1 record, I need to ignore it.

I know how to create a report to group by SSNPolNum but how do I get each
piece of grouped data to compare to each other?

ANY advice or assistance would be greatly appreciated.

Thank you in advance for your time.

Sincerely,

Bonnie
 
J

Jerry Whittle

Not quite right as it doesn't show a record for each SSNPolNum but it does
get the Yes and No right.

Replace Bonnie with the correct table name.

SELECT Bonnie.SSNPolNum,
Max(Bonnie.AssetCode) AS TheMaxAssetCode,
Min(Bonnie.AssetCode) AS TheMinAssetCode,
"Yes" AS Result
FROM Bonnie
GROUP BY Bonnie.SSNPolNum
HAVING (((Count(Bonnie.AssetCode))>1)
AND ((Sum(Bonnie.AssetCode))=Count([AssetCode])*Max([AssetCode])))
UNION ALL
SELECT Bonnie.SSNPolNum,
Max(Bonnie.AssetCode) AS TheAssetCode,
Min(Bonnie.AssetCode) AS TheMinAssetCode,
"No" AS Result
FROM Bonnie
GROUP BY Bonnie.SSNPolNum
HAVING (((Count(Bonnie.AssetCode))>1)
AND ((Sum(Bonnie.AssetCode))<>Count([AssetCode])*Max([AssetCode])));
 
K

KARL DEWEY

Try these three queries --
BonnieA_X_1 --
SELECT BonnieA_X.SSNPolNum, BonnieA_X.AssetCode
FROM BonnieA_X
GROUP BY BonnieA_X.SSNPolNum, BonnieA_X.AssetCode;

BonnieA_X_2 --
SELECT BonnieA_X_1.SSNPolNum
FROM BonnieA_X_1
GROUP BY BonnieA_X_1.SSNPolNum
HAVING (((Count(BonnieA_X_1.AssetCode))=1));

SELECT BonnieA_X.SSNPolNum, BonnieA_X.AssetCode,
IIf([BonnieA_X_2].[SSNPolNum] Is Not Null,"Yes","No") AS AssetCode_Match
FROM BonnieA_X LEFT JOIN BonnieA_X_2 ON BonnieA_X.SSNPolNum =
BonnieA_X_2.SSNPolNum;
 
M

Michel Walsh

SELECT ssnPolNum,
assetCode,
assetCode=ALL(SELECT b.assetCode
FROM tableName AS b
WHERE a.ssnPolNum=b.ssnPolNum) AS Result
FROM tableNAme AS a
GROUP BY ssnPolNum, AssetCode
HAVING COUNT(*) >= 2



which assumes no assetCode IS NULL



Vanderghast, Access MVP
 
M

Michel Walsh

After some thoughts, the HAVING COUNT(*) >=2 is probably not what you want,
as I suggested, since, as I did, it only keep those having at least two
records for a given pair (ssnPolNum, AssetCode), and you want seems to be
those having at least two records, given ONLY the ssnPolNum. Probably what
you want should be more like:




SELECT ssnPolNum,
assetCode,
assetCode=ALL(SELECT b.assetCode
FROM tableName AS b
WHERE a.ssnPolNum=b.ssnPolNum) AS Result
FROM tableName AS a
WHERE ssnPolNum IN( SELECT ssnPolNum
FROM tableName
GROUP BY ssnPolNum
HAVING COUNT(*) >= 2)
GROUP BY ssnPolNum, AssetCode





Vanderghast, Access MVP
 
B

Bonnie A

Hi Jerry,

Tried your code and I get the error "Data Type Mismatch in criteria
expression".

Both my fields SSNPolNum and AssetCode are text fields. SSNPolNum must be a
text field to retain leading zeros. I tried to change it to a number field
and still got the same error.

Thanks for trying though!
--
Bonnie W. Anderson
Cincinnati, OH


Jerry Whittle said:
Not quite right as it doesn't show a record for each SSNPolNum but it does
get the Yes and No right.

Replace Bonnie with the correct table name.

SELECT Bonnie.SSNPolNum,
Max(Bonnie.AssetCode) AS TheMaxAssetCode,
Min(Bonnie.AssetCode) AS TheMinAssetCode,
"Yes" AS Result
FROM Bonnie
GROUP BY Bonnie.SSNPolNum
HAVING (((Count(Bonnie.AssetCode))>1)
AND ((Sum(Bonnie.AssetCode))=Count([AssetCode])*Max([AssetCode])))
UNION ALL
SELECT Bonnie.SSNPolNum,
Max(Bonnie.AssetCode) AS TheAssetCode,
Min(Bonnie.AssetCode) AS TheMinAssetCode,
"No" AS Result
FROM Bonnie
GROUP BY Bonnie.SSNPolNum
HAVING (((Count(Bonnie.AssetCode))>1)
AND ((Sum(Bonnie.AssetCode))<>Count([AssetCode])*Max([AssetCode])));
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Bonnie A said:
Hi everyone. Using A02 on XP. While I am stumped, I know this is easy for you
guys.

I have been given a spreadsheet containing SSNPolNum and AssetCode. This is
the contract number and a sum of fund numbers. My project is to identify all
SSNPolNum records where there is more than one record. Then I need to compare
the AssetCode to see if they are all equal. If they are Result=Yes, if not
Result=No.

For example:

SSNPolNum AssetCode Result
123456789 150 No 150 does not equal 147
123456789 147 No

456789123 253 Yes 253 equals 253 equals 253
456789123 253 Yes
456789123 253 Yes

789123456 1054 No 1054 equals 1054 does not
equal 553
789123456 1054 No
789123456 553 No

If there is only 1 record, I need to ignore it.

I know how to create a report to group by SSNPolNum but how do I get each
piece of grouped data to compare to each other?

ANY advice or assistance would be greatly appreciated.

Thank you in advance for your time.

Sincerely,

Bonnie
Click to expand...
Click to expand...
 
B

Bonnie A

Hi Michel,

It works great on just a few hundred records but bogs down for the 51000 I
have. I've tried to break it in two but 25000 seems to bog down. Must I let
it run for an hour or so?

Thanks for the info!
 
B

Bonnie A

Hi again,

Sorry, I am braindead today. SSN field is text for leading zeros but the
AssetCode is a number field.

--
Bonnie W. Anderson
Cincinnati, OH


Bonnie A said:
Hi Jerry,

Tried your code and I get the error "Data Type Mismatch in criteria
expression".

Both my fields SSNPolNum and AssetCode are text fields. SSNPolNum must be a
text field to retain leading zeros. I tried to change it to a number field
and still got the same error.

Thanks for trying though!
--
Bonnie W. Anderson
Cincinnati, OH


Jerry Whittle said:
Not quite right as it doesn't show a record for each SSNPolNum but it does
get the Yes and No right.

Replace Bonnie with the correct table name.

SELECT Bonnie.SSNPolNum,
Max(Bonnie.AssetCode) AS TheMaxAssetCode,
Min(Bonnie.AssetCode) AS TheMinAssetCode,
"Yes" AS Result
FROM Bonnie
GROUP BY Bonnie.SSNPolNum
HAVING (((Count(Bonnie.AssetCode))>1)
AND ((Sum(Bonnie.AssetCode))=Count([AssetCode])*Max([AssetCode])))
UNION ALL
SELECT Bonnie.SSNPolNum,
Max(Bonnie.AssetCode) AS TheAssetCode,
Min(Bonnie.AssetCode) AS TheMinAssetCode,
"No" AS Result
FROM Bonnie
GROUP BY Bonnie.SSNPolNum
HAVING (((Count(Bonnie.AssetCode))>1)
AND ((Sum(Bonnie.AssetCode))<>Count([AssetCode])*Max([AssetCode])));
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Bonnie A said:
Hi everyone. Using A02 on XP. While I am stumped, I know this is easy for you
guys.

I have been given a spreadsheet containing SSNPolNum and AssetCode. This is
the contract number and a sum of fund numbers. My project is to identify all
SSNPolNum records where there is more than one record. Then I need to compare
the AssetCode to see if they are all equal. If they are Result=Yes, if not
Result=No.

For example:

SSNPolNum AssetCode Result
123456789 150 No 150 does not equal 147
123456789 147 No

456789123 253 Yes 253 equals 253 equals 253
456789123 253 Yes
456789123 253 Yes

789123456 1054 No 1054 equals 1054 does not
equal 553
789123456 1054 No
789123456 553 No

If there is only 1 record, I need to ignore it.

I know how to create a report to group by SSNPolNum but how do I get each
piece of grouped data to compare to each other?

ANY advice or assistance would be greatly appreciated.

Thank you in advance for your time.

Sincerely,

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

John Spencer

This query would identify all SSNPolNum with Asset codes that don't match

SSheet is the name of the table (spreadsheet). Create the following
query and save it as qMultipleAssetCodes. This will identify the
records with multiple asset codes and the same SSNPolNum.

SELECT DISTINCT A.SSNPolNum
FROM SSheet as A INNER JOIN SSheet as B
ON A.SSNPolNum = B.SSNPolNum
WHERE A.AssetCode <> B.AssetCode

So using that query and your table the final query would be something like:

SELECT C.SSNPolNum
, C.AssetCode
, (Q.SSNPolNum is null) as Result
FROM SSheet as C LEFT JOIN qMultipleAssetCodes as Q
ON C.SSNPolNum = Q.SSNPolNum

That will return the single records also. You can fix that by adding a
where clause to the above

SELECT C.SSNPolNum
, C.AssetCode
, (Q.SSNPolNum is null) as Result
FROM SSheet as C LEFT JOIN qMultipleAssetCodes as Q
ON C.SSNPolNum = Q.SSNPolNum
WHERE C.SSNPolNum in
(SELECT SSNPolNum
FROM SSheet
GROUP BY SSNPolNum
HAVING Count(*) > 1)

If the performance is slow, I would suggest importing the data into a
table and applying indexes to the SSNPolNum and AssetCode fields.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
M

Michel Walsh

Have you give a try to John's solution, using a kind of double negation,
instead of the =ALL( ...) can be faster, indeed.


Vanderghast, Access MVP
 

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

Ranking multiple Columns 2
Cross Tab Queries 4
Parameter queries with multiple parameters and null values 5
Countif, IF condition is met 2
IIF Function Within A Query 8
Help With Expression Query 3
Select Portion of a Text String 4
DataSet Question-Please Help 1

Top