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

 

[SoCalAccessPro]

Create 2 sub queries.

Query one is Groupby ID field from table 1.
Query two is Groupby ID field from table 2.

Create a 3rd query and include both queries you just created, join on the ID field.

The join Q1 to Q2 with all records from Q1 and only records from Q2 that match. In the ID field for Q2, type is null. The resulting list is everything in Q1 that doesn't have a matching record in Q2.

Do the same thing again, only in reverse. This will tell you what is in Q2 but not in Q1.



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Joeclueless]

Thanks Tyrone,

What if the IDs in the two tables are not the same?

Thanks again!

Joe

 

[SoCalAccessPro]

When I say ID, I mean whatever fields that are in both tables that should match.

I'm assuming you have two tables that you suspect have dulicate values, abd your trying to fisgure out what they are, correct ?

Another way of doing this would to be to use the Access find duplicates query wizard.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Joeclueless]

Tyrone,

Here is the thing, there is no common field. The groupings should have equal and similar records, but I am wanting to pass the ID from one group to the other...

The first table has:
Group_ID
Item_ID
LowGrade
HiGrade
Type
Name
Range

The second table has:
Group_ID (different from above)
Code (as reference)
Item_ID (same format as Item_ID in first table)
LowGrade (same format as LowGrade in first table)
HiGrade (same format as HiGrade in first table)
Type (as reference)
Name (Same format as Name in first table)
Range (Same format as Name in first table)

I want to compare and identify matching groups to pass the first tables ID to the matching group in the other table.

Any help helps,



Thanks again!

Joe

 

[SoCalAccessPro]

Well, you do have a common field, albeit a concatenated one.

Use the same process I outlined, but use:

Item_ID
LowGrade
HiGrade
Type
Name
Range

In your queries.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Joeclueless]

Hey,

Thanks Tyrone, but I suppose I need to get into deeper details. There is a spatial element to this data, that exists in only one of the tables. The group ID in that first table is based on those regions.

The second table, that I want to transfer the region IDs to, has similar info within it as compared to the first table.

The issue here is that many of the groupings in both tables will have individual records that can be found in other groupings (barring the region ID field that grouping in table 1 is based upon). The actual unique combination of records grouped in table 2 is what essentially replicates the regions from table 1.

The first table has:
Group_ID (region based)
Item_ID *
LowGrade *
HiGrade *
Type *
Name *
Range *

*duplicate combinations of these fields may exist among multiple groupings.

The second table has:
Group_ID (Combination Based)
Code *
Item_ID *
LowGrade *
HiGrade *
Type *
Name *
Range *

*duplicate combinations of these fields may exist among multiple groupings. It is the unique combination of records in the whole group that essentially replicates the region based groupings in table 1.

Is this making sense? Anyhow, thanks for reading. Any help helps!



Thanks again!

Joe

 

[SoCalAccessPro]

Yeah, that's pretty much what I said

I'm heading out for a couple of hours. I'll post a SQL statement for you when I get back if someone doesn't beat me to it.




Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[SoCalAccessPro]

OK, this will get you every unique combination of records that is the same on both tables, and return the GroupID and the Code from the 2nd table.

Note: I renamed some of your fields as you're using reserved words. Never name a field Type, Name, Range, Etc.

SELECT tblGroupCode.GroupID, tblGroupCode.Code
FROM tblGroupCode INNER JOIN tblGroup ON (tblGroupCode.Rangez = tblGroup.Rangez) AND (tblGroupCode.Namez = tblGroup.Namez) AND (tblGroupCode.Typez = tblGroup.Typez) AND (tblGroupCode.HiGrade = tblGroup.HiGrade) AND (tblGroupCode.LowGrade = tblGroup.LowGrade) AND (tblGroupCode.Item_ID = tblGroup.Item_ID);

From here, you can just change to an update query and update the 1st tables groupID to the 2nd tables groupID.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[lespaul]

and if you are going to use reserve words you need to surround them with [brackets] when you use them in queries and such.

Leslie

Have you met Hardy Heron?

 

[Joeclueless]

Well,

I fixed up my tables to eliminate reserved words....

so I now have the code snippet modified to match. I am getting different results that I expected.

Here is the query code:

SELECT tblCalKeyStack.*, tblPolyKeyStack.*
FROM tblCalKeyStack INNER JOIN tblPolyKeyStack ON (tblCalKeyStack.LEVEL = tblPolyKeyStack.LEVEL) AND (tblCalKeyStack.HI_GRD = tblPolyKeyStack.HI_GRD) AND (tblCalKeyStack.LO_GRD = tblPolyKeyStack.LO_GRD) AND (tblCalKeyStack.SCHOOL_ID = tblPolyKeyStack.SCHOOL_ID);

I still don't quite think this is doing what I need it to. I have omitted the join on Range and Name since these can be found from SCHOOL_ID. I also do not need Code in the select statement.

In the tblCalKeyStack, there are 4,172 records and 1,006 unique groups if I group on CalKey.

In the tblPolyKeyStack, there are 3,790 records and 974 unique groups if I group on POLYID.

When I use the above code in a query, I get 46,964 records returned.

What I do need to compare are the groupings of records from the two tables. Not match up individual records from the two tables.

I'm pretty rusty at all this. Thanks for your patience.

Thanks for posting! Any other ideas are more than welcome!


Thanks again!

Joe

 

[SoCalAccessPro]

When I use the above code in a query, I get 46,964 records returned.

That's because you deleted the join on Range and Code.

Unless I'm totally mis-understanding you, all of the feilds combined should be a unique ID, correct ? Why would you drop 2 fields ?

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Joeclueless]

Hey,

The fields combined don't really make it unique. Many records in many groups have the same combination in both tables. The unique thing is the combination of records that are grouped by [CalKey] or [POLYID].

As for the question of dropped fields, the Code field [tblCalKey.ZeroCode] is not relevant to these groups and many records do not have a ZeroCode value. As for Range, it is derived from SCHOOL_ID and does not add to the uniqueness of the records.

Does this make sense?

I want to match up the groupings of records based on the unique combination of groups in tblCalKeyStack and tblPolyKeyStack.

Example:

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

This is a simple example of a tblPolyKeyStack grouping...
I want to find the same grouping in tblCalKeyStack and transfer the POLYID to a new field in the table.


Does this make it any clearer? Sorry if it is not.....

I also need to compare similar groupings that do not match exactly. But matching up exact matches would be a GREAT start.


Thanks for all the patience...

Thanks again!

Joe

 

[lespaul]

matching up exact matches would be a GREAT start.

then for each of those fields you need to include it in the JOIN section of the query:

SELECT POLYID, SCHOOL_ID, LO_GRD, HI_GRD, LEVEL
FROM tblPolyKeyStack As P
INNER JOIN tblCalKeyStack As C ON P.POLYID = C.POLYID AND 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 if the field names in tblCalKeyStack don't match, then you'll need to change the above C.FieldName to the correct name that matches in tblPolyKeyStack. This query will give you all the records where all that information is the SAME in BOTH tables.

HTH




Leslie

Have you met Hardy Heron?

 

[Joeclueless]

matching up exact matches would be a GREAT start.

I should have said matching up exact groupings would be a GREAT start.

Does this rewording shed light on the difference I am trying to emphasize?

Thanks for all posts!

Again, tblPolyKeyStack is grouped on POLYID and tblCalKeyStack is grouped on CalKey. These two fields have nothing in common.... The record groupings are The commonality....



Thanks again!

Joe

 

[Joeclueless]

If possible, could someone let me know if they understand what I mean by matched record groupings and how they differ from matched records?

Thanks again!

Joe

 

[lespaul]

no I don't understand....you've given this example data:
[tt]
POLYID SCHOOL_ID LO_GRD HI_GRD LEVEL
1 1234 0 5 E
1 4582 6 8 M
1 8564 9 12 H[/tt]

and that's from one table. What's the data in the other table look like? How do you determine the "matches" based on the sample data that you provide from both tables?

that will make it much easier for someone to understand what you are trying to explain. A picture is worth a thousand words and we'd like to see the picture of the data...thanks.
Leslie

 

[Joeclueless]

Leslie, thanks for letting me know that I am not communicating my dilemma effectively....
Sorry for any vague descriptions....

Here are the samples:

tblPolyKeyStack Sample

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


tblCalKeyStack Sample

CalKey SCHOOL_ID LO_GRD HI_GRD LEVEL]
A 1234 0 5 E
A 4582 6 8 M
A 8564 9 12 H
B 1234 0 5 E
C 4582 6 8 M
W 8564 9 12 H

So, here in the samples I have one matched grouping where the records in tblPolyKeyStack have POLYID=1 and tblCalKeyStack have CalKey=A. This "match" is based on the combination of criteria (SCHOOL_ID, LO_GRD, HI_GRD, and LEVEL) being the same for the three matching records that have a "common" ID within their respective, separate tables.

The other records illustrate that the combination of SCHOOL_ID, LO_GRD, HI_GRD and LEVEL can be identical for many records in both tables. It is the "grouped on" set of records that I want to match up.

Once I have matched up the groupings I will transfer the POLYID value from the matched records in tblPolyKeyStack to a new field in the tblCalKeyStack.

Does this clear up any of this mud?

Thanks sooo much for the patience!




Thanks again!

Joe

 

[lespaul]

So out of those two tables what information do you want your query to return?

Leslie

Have you met Hardy Heron?

 

[Joeclueless]

Leslie,

Right now what I have are two reports that are grouped on the ID fields. What I want to do is compare the groupings and transfer the POLYID value from tblPolyKeyStack to the matched set of records in CalKeyStack.

So, Maybe what I need to do is have a report with a subreport. The main report listing the tblPolyKeyStack groups and the subreport listing the matched tblCalKeyStack groups. I suppose I could manually enter the POLYID into the matched tblCalKeyStack records....

Is this making sense now?

I have no idea how to match up these groupings...

The only other way I can think of is to create a make table query for each of the two tables and re-structure the groupings into single records that have many fields...





Thanks again!

Joe