One query to multiple databases

[bvdahl]

Hello,

I have five different databasesd that I access through five different odbc
connections. They bases are identical, but the data in them are not. Several
times, every day, I run the same query on each of them, and then I combine
all the results I get from each base.

Is there anyway that I can do this automatically?

 

[KARL DEWEY]

Use a UNION query.

 

[bvdahl]

Thank you for your reply, but could you ba a bit more specific? I have no
idea how to connect to five different odbc's at the same time and then use a
Union query.

B

 

[KARL DEWEY]

Have them all linked in to the same Access database.
Create the union query in SQL (it can only be edited in SQL view).

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1
UNION ALL SELECT Table2.Field1, Table2.Field2, Table2.Field3
FROM Table2
UNION ALL SELECT Table3.Field1, Table3.Field2, Table3.Field3
FROM Table3;

 

[John W. Vinson]

Thank you for your reply, but could you ba a bit more specific? I have no
idea how to connect to five different odbc's at the same time and then use a
Union query.

It's a bit unusual but still perfectly legal to have a single frontend
database connected to multiple backends. Use File... Get External Data... Link
to link to each different ODBC connection (you will have to assign distinct
names to the linked table even if they all have the same name in their
respective databases).

 

[bvdahl]

Actually, I just did that.

I made the query:

SELECT plo_players.player_id, plo_players.screen_name,
plo_session.total_hands, plo_session.amount_won
FROM plo_session INNER JOIN plo_players ON plo_session.player_id =
plo_players.player_id
WHERE (((plo_session.session_start)>#2/1/2010 10:0:0#) AND
((plo_session.session_start)<#2/2/2010 18:30:0#))
UNION ALL SELECT nlhe_players.player_id, nlhe_players.screen_name,
nlhe_session.total_hands, nlhe_session.amount_won
FROM nlhe_session INNER JOIN nlhe_players ON nlhe_session.player_id =
nlhe_players.player_id
WHERE (((nlhe_session.session_start)>#2/1/2010 10:0:0#) AND
((nlhe_session.session_start)<#2/2/2010 18:30:0#))

and got a nice result.

I would, however, like to group by screen_name and make sums of total_hands
and amount_won. I tried running another simple query on top of this one:

SELECT player_winnings_sessions.screen_name,
Sum(player_winnings_sessions.total_hands) AS SumOftotal_hands,
Sum(player_winnings_sessions.amount_won) AS SumOfamount_won
FROM player_winnings_sessions
GROUP BY player_winnings_sessions.screen_name;

but just got the errormessage that the query was too complex.

What could be the problem here?

Baard

 

[KARL DEWEY]

Try this --
SELECT plo_players.screen_name, Sum(plo_session.total_hands) AS
SumOftotal_hands, Sum(plo_session.amount_won) AS SumAmtWon
FROM plo_session INNER JOIN plo_players ON plo_session.player_id =
plo_players.player_id
WHERE (((plo_session.session_start)>#2/1/2010 10:0:0#) AND
((plo_session.session_start)<#2/2/2010 18:30:0#))
UNION ALL SELECT nlhe_players.player_id, nlhe_players.screen_name,
nlhe_session.total_hands, nlhe_session.amount_won
FROM nlhe_session INNER JOIN nlhe_players ON nlhe_session.player_id =
nlhe_players.player_id
WHERE (((nlhe_session.session_start)>#2/1/2010 10:0:0#) AND
((nlhe_session.session_start)<#2/2/2010 18:30:0#));

 

[KARL DEWEY]

Read it wrong.
Try this (Tested) ---
Query6_A --
SELECT plo_players.screen_name, Nz([plo_session].[total_hands],0) AS
Sum_Total_Hands, Nz([plo_session].[amount_won],0) AS Sum_Total_Amount_Won
FROM plo_players INNER JOIN plo_session ON plo_players.player_id =
plo_session.player_id
WHERE (plo_session.session_start) Between #2/1/2010 10:0:0# And #2/2/2010
18:30:0#
UNION ALL SELECT plo_players.screen_name, Nz([nlhe_session].[total_hands],0)
AS Sum_Total_Hands, Nz([nlhe_session].[amount_won],0) AS Sum_Total_Amount_Won
FROM (plo_players INNER JOIN nlhe_session ON plo_players.player_id =
nlhe_session.player_id)
WHERE (nlhe_session.session_start) Between #2/1/2010 10:0:0# And #2/2/2010
18:30:0#;

SELECT Query6_A.screen_name, Sum(Query6_A.Sum_Total_Hands) AS
SumOfSum_Total_Hands, Sum(Query6_A.Sum_Total_Amount_Won) AS
SumOfSum_Total_Amount_Won
FROM Query6_A
GROUP BY Query6_A.screen_name;