Finding Overlapping Data from a Table

I have an Access table that contains just over 200 groups of data
identified by a unique # for each. Each group has monthly dates, and
twenty-five columns of data. Often the data in these columns on each
date matches the data for another group. I need to know the number of
overlaps between each group for each date as a total sum in order to
see the degree of correlation between each group. The result I'm
thinking of is a matrix that shows all groups along the x and y axis
and then the # of overlapping data points in each cell. Is there a
way in Access to accomplish this goal?

Any pointers or suggestions at all would be greatly appreciated.

"FG" wrote:
I have an Access table that contains just over 200 groups of data
identified by a unique # for each. Each group has monthly dates, and
twenty-five columns of data. Often the data in these columns on each
date matches the data for another group. I need to know the number of
overlaps between each group for each date as a total sum in order to
see the degree of correlation between each group. The result I'm
thinking of is a matrix that shows all groups along the x and y axis
and then the # of overlapping data points in each cell. Is there a
way in Access to accomplish this goal?

Any pointers or suggestions at all would be greatly appreciated.

I don't know if this will help or not...

I have a db of books used for classes within specific terms.
My goal was an Excel spreadsheet that showed the terms
down the side ("r" = "rows") and those same terms across the
top ("c" = "columns"), and in the grid, the number of book titles
shared between the 2 terms of the intersection.

A crosstab did the trick....

TRANSFORM Count(c.ISBN) AS CountOfISBN
SELECT
r.Term,
r.fTermDate
FROM
qryDistinctTermISBN AS r
INNER JOIN
qryDistinctTermISBN AS c
ON
r.ISBN = c.ISBN
GROUP BY
r.Term,
r.fTermDate
ORDER BY
r.fTermDate DESC
PIVOT c.Term;

I actually built an "IN (...)" expression for the
PIVOT clause so that the column terms "sorted"
in the same order as the row terms, but hopefully
you can see how this might apply to your situation.

I don't fully understand your data (plus I need to
get back to work), but something like:

TRANSFORM Abs(Sum(c.f1=r.f1)) As f1SameCnt
SELECT
r.GroupNum
FROM
yurtable AS r
INNER JOIN
yurtable AS c
ON
r.GroupNum = c.GroupNum
GROUP BY
r.GroupNum
PIVOT c.GroupNum

would count number of records within a group
where field "f1" from row group are the same
as "f1" from col group....

I know that's not exactly what you want, but I
gotta get to work, so maybe it will it least get
you started...

On further thought....

why not let the join do the work for you...

TRANSFORM Count(*) As SameFldValsCnt
SELECT
r.GroupNum
FROM
yurtable AS r
INNER JOIN
yurtable AS c
ON
r.GroupDate = c.GroupDate
AND
(r.f1=c.f1
OR
r.f2=c.f2
OR
r.f3=c.f3)
GROUP BY
r.GroupNum
PIVOT c.GroupNum;

the "field expressions" in the join
might better suit you if used "AND"
instead of "OR" -- you know best.

the query above is saying to me...

the join:

for a specific group r.GroupNum
return a record when the dates match
and *at least one of the fields match.*

the pivot:

Then sort those records for specific column groups
and show count of the records for each c.GroupNum.

whereas, the following join

ON

r.GroupDate = c.GroupDate
AND
(r.f1=c.f1
AND
r.f2=c.f2
AND
r.f3=c.f3)

return a record in a group when the dates match
and *all 3 fields are the same* (which might be more
like what you wanted).

the join "field expression" could be expanded out
to include all the fields of course...

And, of course, I may have completely misunderstood...

"Gary Walter" wrote:

"FG" wrote:
I have an Access table that contains just over 200 groups of data
identified by a unique # for each. Each group has monthly dates, and
twenty-five columns of data. Often the data in these columns on each
date matches the data for another group. I need to know the number of
overlaps between each group for each date as a total sum in order to
see the degree of correlation between each group. The result I'm
thinking of is a matrix that shows all groups along the x and y axis
and then the # of overlapping data points in each cell. Is there a
way in Access to accomplish this goal?

Any pointers or suggestions at all would be greatly appreciated.

I don't know if this will help or not...

I have a db of books used for classes within specific terms.
My goal was an Excel spreadsheet that showed the terms
down the side ("r" = "rows") and those same terms across the
top ("c" = "columns"), and in the grid, the number of book titles
shared between the 2 terms of the intersection.

A crosstab did the trick....

TRANSFORM Count(c.ISBN) AS CountOfISBN
SELECT
r.Term,
r.fTermDate
FROM
qryDistinctTermISBN AS r
INNER JOIN
qryDistinctTermISBN AS c
ON
r.ISBN = c.ISBN
GROUP BY
r.Term,
r.fTermDate
ORDER BY
r.fTermDate DESC
PIVOT c.Term;

I actually built an "IN (...)" expression for the
PIVOT clause so that the column terms "sorted"
in the same order as the row terms, but hopefully
you can see how this might apply to your situation.

I don't fully understand your data (plus I need to
get back to work), but something like:

TRANSFORM Abs(Sum(c.f1=r.f1)) As f1SameCnt
SELECT
r.GroupNum
FROM
yurtable AS r
INNER JOIN
yurtable AS c
ON
r.GroupNum = c.GroupNum
GROUP BY
r.GroupNum
PIVOT c.GroupNum

would count number of records within a group
where field "f1" from row group are the same
as "f1" from col group....

I know that's not exactly what you want, but I
gotta get to work, so maybe it will it least get
you started...

Gary: Thanks for the assistance. I ended up having to move to VB code
to pull this off after putting the data into SQL Server db.