Comparison

[Joeclueless]

Hi,

I have one table that has records which can be grouped by like values in a field. I have another table that also has records that can be grouped by like values in a field. I am able to create separate reports with each of the tables that display these groups. What I am trying to do is to compare the grouped lists that have certain equal qualities in order to transfer the grouped by value from one of the lists to the other. How could this be done?

Anything helps!


Thanks again!

Joe

 

[lespaul]

Is this making sense now?

Still no....

Out of the sample records you show above...you want a query that returns which records from which tables...

 

[PHV]

What about this ?

SELECT K.CalKey, P.POLYID, P.SCHOOL_ID, P.LO_GRD, P.HI_GRD, P.LEVEL
FROM ((tblPolyKeyStack AS P
INNER JOIN (SELECT POLYID, Count(*) AS Num FROM tblPolyKeyStack GROUP BY POLYID
) AS C1 ON P.POLYID = C1.POLYID)
INNER JOIN tblCalKeyStack AS K ON P.SCHOOL_ID = K.SCHOOL_ID AND P.LO_GRD = K.LO_GRD
AND P.HI_GRD = K.HI_GRD AND P.LEVEL = K.LEVEL)
INNER JOIN (SELECT CalKey, Count(*) AS Num FROM tblCalKeyStack GROUP BY CalKey
) AS C2 ON K.CalKey = C2.CalKey
WHERE C1.Num = C2.Num

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Joeclueless]

Thanks for bearing with me Leslie!

Out of the sample records you show above...you want a query that returns which records from which tables...

tblPolyKeyStack
POLYID SCHOOL_ID LO_GRD HI_GRD LEVEL
1 1234 0 5 E
1 4582 6 8 M
1 8564 9 12 H

tblCalKeyStack
CalKey SCHOOL_ID LO_GRD HI_GRD LEVEL NEWFIELD
A 1234 0 5 E 1
A 4582 6 8 M 1
A 8564 9 12 H 1

NEWFIELD is to transfer the POLYID from tblPolyKeyStack to tblCalKeyStack.....




Thanks again!

Joe

 

[PHV]

Did you try my suggestion ?

 

[Joeclueless]

PHV,

That did not quite work... But look at the sample I posted for lespaul. if the return looked like this, it may work:

POLYID CalKeyStack SCHOOL_ID LO_GRD HI_GRD LEVEL
1 A 1234 0 5 E
1 A 4582 6 8 M
1 A 8564 9 12 H

On the query you posted, I see more records than expected (23,333). Many of the records having like POLYIDs have Different CalKeys, which the matched record sets should have all alike POLYIDs and all alike CalKeys....

Does that make sense?

Thanks again!

Joe

 

[lespaul]

ok to get this result:
[tt]
POLYID CalKeyStack SCHOOL_ID LO_GRD HI_GRD LEVEL
1 A 1234 0 5 E
1 A 4582 6 8 M
1 A 8564 9 12 H[/tt]

use this query:

SELECT PolyID, CalKeyStack, P.School_ID, P.Lo_Grd, P.Hi_grd, P.Level
FROM tblPolyKeyStack P
INNER JOIN tblCalKeyStack C INNER JOIN P.School_ID = C.School_ID AND P.Lo_Grd = C.Lo_grd AND P.HI_GRD = C.HI_GRD AND P.Level = C.Level

now you are also going to get:
[tt]
POLYID CalKeyStack SCHOOL_ID LO_GRD HI_GRD LEVEL
2 B 1234 0 5 E
3 C 4582 6 8 M
9 W 8564 9 12 H[/tt]
because they also match on those four fields...do you want to include those records or should they be excluded? if they should be excluded, why? what's the "formula" for determining the first set of records should be included and the last set of records should NOT? Is it because there is only one record in each table for the last set and there are multiple records in the first? what is the "rule" for determing inclusion?




Leslie

Have you met Hardy Heron?

 

[Joeclueless]

now you are also going to get:

POLYID CalKeyStack SCHOOL_ID LO_GRD HI_GRD LEVEL
2 B 1234 0 5 E
3 C 4582 6 8 M
9 W 8564 9 12 H
because they also match on those four fields...do you want to include those records or should they be excluded? if they should be excluded, why? what's the "formula" for determining the first set of records should be included and the last set of records should NOT? Is it because there is only one record in each table for the last set and there are multiple records in the first? what is the "rule" for determing inclusion?

I'm soooo sorry....

I forgot about those extra records in the sample..... Yes, they should be included....

I will try this right now...



Thanks again!

Joe

 

[Joeclueless]

SELECT PolyID, CalKeyStack, P.School_ID, P.Lo_Grd, P.Hi_grd, P.Level
FROM tblPolyKeyStack P
INNER JOIN tblCalKeyStack C INNER JOIN P.School_ID = C.School_ID AND P.Lo_Grd = C.Lo_grd AND P.HI_GRD = C.HI_GRD AND P.Level = C.Level

This returns a syntax error in FROM clause...

Thanks again!

Joe

 

[PHV]

SELECT K.CalKey, P.POLYID, P.SCHOOL_ID, P.LO_GRD, P.HI_GRD, P.LEVEL
FROM ((tblPolyKeyStack AS P
INNER JOIN (SELECT POLYID, Count(*) AS Num FROM tblPolyKeyStack GROUP BY POLYID
) AS C1 ON P.POLYID = C1.POLYID)
INNER JOIN tblCalKeyStack AS K ON P.SCHOOL_ID = K.SCHOOL_ID AND P.LO_GRD = K.LO_GRD
AND P.HI_GRD = K.HI_GRD AND P.LEVEL = K.LEVEL)
INNER JOIN (SELECT CalKey, Count(*) AS Num FROM tblCalKeyStack GROUP BY CalKey
) AS C2 ON K.CalKey = C2.CalKey
WHERE C1.Num = C2.Num[!] AND C1.Num > 1[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Joeclueless]

Need to collect myself... Just had experienced an Earthquake!

Thanks again!

Joe

 

[lespaul]

yep, heard about it on the news....hope you're all settled now!

 

[Joeclueless]

I'm going to need to get back to this tomorrow.

I'll create better examples or Attach sample tables...

Which do you think is better?

Thanks again!

Joe