Query, average of all found for each value

[ChrisCalvert]

This is a complicated (to me at least) query that I need to get done.

Here is what I have:
{Table1}
ID, Xavg, Yavg,Zavg

{Table2}
ID, A, B, C, Xn, Yn, Zn

What I need is a query that looks at record where:

Table1.ID = Table2.A OR Table2.B OR Table2.C

It then averages the Xn, Yn, and Zn of these records and updates the Xavg, Yavg, and Zavg , respectively to these values.

That is for every record in Table1, look in Table2 where either A, B or C equal the ID. Then average the Xn values of those records and store it in Xavg. (and the same for Yavg and Zavg)

ANy help would be much appreciated.
Thanks,
Chris

 

[gol4]

WOW! is this a relational database????
why would you need to store the average in table1?
should be able to get the averages something like this

SELECT Table2.id, Avg(Table2.xn) AS AvgOfx, Avg(Table2.yn) AS AvgOfy, Avg(Table2.zn) AS AvgOfz
FROM Table2
WHERE (((Table2.a) In (select id from table1))) OR (((Table2.b) In (select id from table1))) OR (((Table2.c) In (select id from table1)))
GROUP BY Table2.id;
)

or more accuratly (perhaps)create a union query then group by that or link that to table 1

select id, a as fldid, xn,yn,zn from table2
union select id, b as fldid, xn,yn,zn from table2
union select id, c as fldid, xn,yn,zn from table2

may need to use all predicate if values spread over fields

 

[ChrisCalvert]

gol4,

Thanks for your help. However, this is not quite what I am looking for. It seems to group these according to all the values in Table2, but I need the averages to be stored in Table1. Let me see if I can provide some better information.

Is it relational? ..not really. In fact, It's only these two tables, and I can rearrange the information. What it is is this:
Table1 lists 293 vertices in 3d space. It also has an x y and z value.
Table2 has a list of 500 triangles, the three vertices that define that triangle, and the x, y and z value of the normal (line perpendicular) of that triangle.

I need to average the normal values of every triangle that contains a particular vertex and store it as the x, y, and z value of that vertex.

So, basically, for each vertex (Table1.ID) I need to check all three vertexes (Tabel2.A, Table2.B, Table2.C) of each triangle, and if any of those are the vertex I am looking for, average the X,Y, and Z values (Table2.xn, Table2.yz, and Table2.zn) average them and store that average.
So, I should have 293 result rows, and although your solution looks very close, it gives me 500 rows, which seems to mean that it is grouping on the triangles (Table2) instead of the vertices (Table1).

Sorry to ramble on so, but thanks for your time and assistance.

 

[ChrisCalvert]

Actually, I just took the plunge and did it with code.
I know there is probably a better way, but I was able to understand this much easier, and it got me what I needed.

Thanks for your help though.

Just for anyone who might need it, here is my code:
Here, I have renamed the tables to Vertices and Triangles.
----------------------------------------------------------
Dim dbs As DAO.Database
Dim rstVert As DAO.Recordset
Dim rstTri As DAO.Recordset
Set dbs = CurrentDb
Set rstVert = dbs.OpenRecordset("Vertices&quot
Set rstTri = dbs.OpenRecordset("Triangles&quot


Dim XAV, YAV, ZAV, XTotal, YTotal, ZTotal As Double
Dim Instances As Integer

Do Until rstVert.EOF

Instances = 0
XTotal = 0
YTotal = 0
ZTotal = 0

Do Until rstTri.EOF
If (rstVert![ID] = rstTri![a]) Or (rstVert![ID] = rstTri!) Or (rstVert![ID] = rstTri![c]) Then
Instances = Instances + 1

XTotal = XTotal + rstTri![xn]
YTotal = YTotal + rstTri![yn]
ZTotal = ZTotal + rstTri![zn]

End If
rstTri.MoveNext
Loop


XAV = (XTotal / Instances)
YAV = (YTotal / Instances)
ZAV = (ZTotal / Instances)
rstVert.Edit
rstVert![Xavg] = XAV
rstVert![Yavg] = YAV
rstVert![Zavg] = ZAV
rstVert.Update
rstTri.MoveFirst
rstVert.MoveNext

Loop
---------------------------------------------------