Simple Linear Regression on Multiple Data Fields

[waynemurry]

I have a Table that looks like the following:



A B C D E
10 12 8 4 14
9 10 7 5 12
12 13 10 3 17
10 11 6 6 15


I have figured out how to calculate the regression statistics for A vs B on a query. So it looks like this...

Predicted B Difference between
A B m b via regression Predicted and Actual
10 12 .9474 1.7895 11.263 -.737
9 10 .9474 1.7895 10.316 .316
12 13 .9474 1.7895 13.158 .158
10 11 .9474 1.7895 11.263 .263

Question: What I am looking to do is to duplicate this for A vs C, A vs D, A vs E, B vs C, B vs D, and so on until all of the pairs are calculated and put into one table/query. I know I can just create more fields to the right with the A vs C data and continue on that way, but this does not seem very efficient and I am limited in the number of fields that can be created. So it seems that "going down" rather than across makes more sense however I do not know how to move on to the next pair (A vs C and so on).

Another idea that I thought of was somehow running the regression query over and over again and "recording" the data generated into a table. Once again I have the same problem. How do I tell the regression query to run the next pair? If my data is setup incorrectly, I am willing to change that as well. Any help you may have is greatly appreciated.

Thanks,

Wayne

[June7]

I know next to nothing about statistics and have no idea what linear regression is, even after googling. Is this possibly a case where Excel is the better tool? Do you want to graph this data?

If data were structured like:

Grp	Cat	Qty
1	A	10
2	A	9
3	A	12
4	A	10
1	B	12
2	B	10
3	B	13
4	B	11
	etc

a cartesian query could possibly be constructed that uses the table twice (self-join) without a join clause. This will cause every record of one dataset to join with every record of the other dataset so you get all possible pairings (including AA, BB, CC, DD, EE but those could be filtered out and ignored).Then use the cartesian dataset as source for the regression calculations.

I did a quick test of this idea on the data I used in the normalized table. Those 8 records resulted in 64 cartesian records. With filtering I got it down to 8 records because the pairs duplicated (AB, BA). The query SQL:
SELECT Table1.Grp, Table1.Cat, Table1.Qty, Table1_1.Grp, Table1_1.Cat, Table1_1.Qty
FROM Table1, Table1 AS Table1_1
WHERE (((Table1.Grp)=[Table1_1].[Grp]) AND ((Table1.Cat)<>[Table1_1].[Cat]));

The query result:

Table1.Grp	Table1.Cat	Table1.Qty	Table1_1.Grp	Table1_1.Cat	Table1_1.Qty
1	B	12	1	A	10
2	B	10	2	A	9
3	B	13	3	A	12
4	B	11	4	A	10
1	A	10	1	B	12
2	A	9	2	B	10
3	A	12	3	B	13
4	A	10	4	B	11

Also, a query with join on the Grp fields achieves the same output.
SELECT Table1.Grp, Table1.Cat, Table1.Qty, Table1_1.Grp, Table1_1.Cat, Table1_1.Qty
FROM Table1 AS Table1_1 INNER JOIN Table1 ON Table1_1.Grp = Table1.Grp
WHERE (((Table1.Cat)<>[Table1_1].[Cat]));


Wait, scratch all that or just put it aside as interesting info that might become useful some day. Try manipulating your data with a UNION query to create the pairs. There is no wizard or designer for UNION, must type in SQL View of query designer. There is a limit of 50 lines. Like:

SELECT "AB" AS PairCode, [A] AS Qty1, [B] AS Qty2 FROM tablename
UNION SELECT ALL "AC", [A], [C] FROM tablename
UNION SELECT ALL "AD", [A], [D] FROM tablename
UNION SELECT ALL "AE", [A], [E] FROM tablename
UNION SELECT ALL "BC", [B], [C] FROM tablename
UNION SELECT ALL "BD", [B], [D] FROM tablename
UNION SELECT ALL "BE", [B], [E] FROM tablename
UNION SELECT ALL "CD", [C], [D] FROM tablename
UNION SELECT ALL "CE", [C], [E] FROM tablename
UNION SELECT ALL "DE", [D], [E] FROM tablename;

Use that UNION as source for another query to do the calcs or if you want might even be able to do the calcs within the UNION:

SELECT "AB" AS PairCode, [A] AS Qty1, [B] AS Qty2, calc1 AS Calc1, calc2 AS Calc2, calc3 AS Calc3, calc4 AS Calc4 FROM tablename
UNION SELECT ALL "AC", [A], [C], calc1, calc2, calc3, calc4 FROM tablename
etc.