query average of multiple tables, output to existing table

[dumbledown]

Hi,



I have n tables (1, 2, 3,.......n) called table_1, table_2, table_3,.......table_n, I have the number n defined.
Each table is formatted identically and has the same columns.
Each table has links (link_xxx) and values, like:

link_1 - 10
link_24 - 11
link_16 - 27
link_72 - 17

The links are present in all tables in different orders.

Does anyone know how I can write some query that will average for all links over all tables n? And ideally output into an existing, but blank, table.

Many thanks
Hugh

[jzwp11]

First, having a number of tables all with the same structure violates normalization rules. Like data should be in 1 table.

In answer to your question, you will have to create a UNION query to get all of the data together


SELECT field1, field2 etc.
FROM table1

UNION

SELECT field1, field2 etc.
FROM table2

etc.

You can only do this in the SQL view window of a query.

Once you have that query created and saved, create a second query that does the averaging.

Outputting the data to yet another table also is not good database practice. Just use the results of the query.

[dumbledown]

Thanks jzwp11,
using the union I've created a function for an n number of tables, it doesn't recognise the SQL though. I can't work out why?

Public Function averagelinks()
Dim strSQL As String

strSQL = "SELECT * " & _
"INTO newlinksjoined " & _
"FROM [outputlinks1]"

For i = 2 To n

strSQL = strSQL + " UNION SELECT * FROM [outputlinks" & i & "]"
Next i

strSQL = strSQL + ";"

DoCmd.RunSQL strSQL
End Function

[jzwp11]

I typically use a debug.print statement (see placement in red below) and push the SQL text of the query to the VBA immediate window to check for any syntax issues (you can also copy and paste the text to a new query to test it from there as well). I assume you have "n" defined somewhere else in your code.


Public Function averagelinks()
Dim strSQL As String

strSQL = "SELECT * " & _
"INTO newlinksjoined " & _
"FROM [outputlinks1]"

For i = 2 To n

strSQL = strSQL + " UNION SELECT * FROM [outputlinks" & i & "]"
Next i

strSQL = strSQL + ";"

debug.print strSQL
DoCmd.RunSQL strSQL
End Function

[dumbledown]

Thanks, that debug thing is great.
However, when I run it, it says it needs an argument consisting of an SQL statement, BUT the output in the debug immediate pane works perfectly as a separate query.
I'm very confused

[jzwp11]

If it is asking for a parameter, it typically means something is spelled wrong, but since the query runs fine when pulled from the immediate window something else might be going on. I've also noticed that VBA sometimes has issues with the *. Can you substitute the actual field names? You might also try replacing the * with the corresponding ASCII character code CHR(42)

strSQL = "SELECT " & chr(42) & " & _
"INTO newlinksjoined " & _
"FROM [outputlinks1]"

Can you post the output you got from the debug.print statement?

[dumbledown]

Thanks,

If I try without the into statement
strSQL = "SELECT " & Chr(42) & " " & _
"FROM outputlinks1 " & _
"UNION ALL " & _
"SELECT " & Chr(42) & " " & _
"FROM outputlinks2;"

It outputs SELECT * FROM outputlinks1 UNION ALL SELECT * FROM outputlinks2;
which then works as a separate query but fails to work within the VBA.

If I Try it with the INTO statement, it still doesn't work in VBA,
and in the debugger it looks like:

SELECT * INTO newlinksjoined FROM outputlinks1 UNION ALL SELECT * FROM outputlinks2;

And then to compound things it says an action query can not be used as a row source

Thanks for persevering!

[jzwp11]

You cannot "run" a SELECT query via VBA you can only "run" action queries. So not getting anything for a SELECT query is expected. You would typically use a SELECT query when you want to open & work with a recordset in VBA which is totally different than what you are trying to do.

The typical syntax of the make table query is as follows:

SELECT * INTO destinationtable
FROM (SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3)


The only way it will work is if the UNION query (which is actually a subquery) is enclosed in (). I do not see the () in your code.

Perhaps something like this (not tested, air code):


Public Function averagelinks()

Dim strSQL As String
Dim qryFinal as string

qryFinal = "SELECT * " & _
"INTO newlinksjoined " & _
"FROM ("

For i = 1 To n
IF i=1 THEN
strSQL="SELECT * FROM [outputlinks" & i & "]"
ELSE
strSQL = strSQL + " UNION SELECT * FROM [outputlinks" & i & "]"
END IF
Next i

qryFinal = qryFinal & strSQL & ")"

debug.print strSQL
DoCmd.RunSQL qryFinal

End Function


BTW, where do you calculate the average?

[dumbledown]

That's great - thanks.
That works well.
Regarding the average - I'll do that in another function accessing newlinksjoined. Haven't worked that out yet, but I think we've broken the back of it.
Cheers!

[jzwp11]

You're welcome. Sometimes it is just those little subtle things that can cause big headaches!