Query for various servers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to have one query for tables from various servers?

For example, we have PO number in New York server and search for same PO
number in Paris server. All the tables in NY is same as in Paris.

How?
 
Hi Angus - sounds like a Union Query would do the trick. The help screen says
this -
A union query combines fields from two or more tables or queries into one
field in the query's results. You might use a union query to combine data
from two tables. For example, you might combine company name and city data
for all companies in Brazil that are listed in the Suppliers and Customers
tables. You could then create a make-table query based on the union query to
make a new table.
Hope this is what you want -- Yours Dika
 
Hi Angus - you should have no problem - I have one Union that has 3 tables
that all have the same fields with the same names (input files from different
servers) Part of my union query looks like this
SELECT [INPUT 1].[Appended?], [INPUT 1].[Which FE], [INPUT 1].[Date
Appended], [INPUT 1].[Entry Date], [INPUT 1].[Closed form], [INPUT
1].District, [INPUT 1].PCT, [INPUT 1].Team, [INPUT 1].[OT/PT], [INPUT
1].[First Name], [INPUT 1].Surname, [INPUT 1].DOB, [INPUT 1].[HOSP Number],
[INPUT 1].[ICS Number], [INPUT 1].[GP Surgery], [INPUT 1].[Referral Source],
[INPUT 1].[Referral Date], [INPUT 1].[Date Intervention Began], [INPUT
1].[Date Intervention Ceased]
FROM [INPUT 1]
UNION SELECT [INPUT 2].[Appended?], [INPUT 2].[Which FE], [INPUT 2].[Date
Appended], [INPUT 2].[Entry Date], [INPUT 2].[Closed form], [INPUT
2].District, [INPUT 2].PCT, [INPUT 2].Team, [INPUT 2].[OT/PT], [INPUT
2].[First Name], [INPUT 2].Surname, [INPUT 2].DOB, [INPUT 2].[HOSP Number],
[INPUT 2].[ICS Number], [INPUT 2].[GP Surgery], [INPUT 2].[Referral Source],
[INPUT 2].[Referral Date], [INPUT 2].[Date Intervention Began], [INPUT
2].[Date Intervention Ceased]
FROM [INPUT 2]

As you can see it works it out from the name of the DB. It really is the
best way to link tables like this.
Yours - Dika
 
Sorry for my late reply.

I guess this is a good way but I failed to test it. I tried to link the
servers by Access ADP but found my authorization is denied to create query in
servers (as usually i use MS query and create query in my machine).

Is is possible to make linked servers/ tables by MS Query? or

How to make it by ADO (i need two ADO objects? how to put them together with
SQL statesment?)

Thanks.


Kernow Girl said:
Hi Angus - you should have no problem - I have one Union that has 3 tables
that all have the same fields with the same names (input files from different
servers) Part of my union query looks like this
SELECT [INPUT 1].[Appended?], [INPUT 1].[Which FE], [INPUT 1].[Date
Appended], [INPUT 1].[Entry Date], [INPUT 1].[Closed form], [INPUT
1].District, [INPUT 1].PCT, [INPUT 1].Team, [INPUT 1].[OT/PT], [INPUT
1].[First Name], [INPUT 1].Surname, [INPUT 1].DOB, [INPUT 1].[HOSP Number],
[INPUT 1].[ICS Number], [INPUT 1].[GP Surgery], [INPUT 1].[Referral Source],
[INPUT 1].[Referral Date], [INPUT 1].[Date Intervention Began], [INPUT
1].[Date Intervention Ceased]
FROM [INPUT 1]
UNION SELECT [INPUT 2].[Appended?], [INPUT 2].[Which FE], [INPUT 2].[Date
Appended], [INPUT 2].[Entry Date], [INPUT 2].[Closed form], [INPUT
2].District, [INPUT 2].PCT, [INPUT 2].Team, [INPUT 2].[OT/PT], [INPUT
2].[First Name], [INPUT 2].Surname, [INPUT 2].DOB, [INPUT 2].[HOSP Number],
[INPUT 2].[ICS Number], [INPUT 2].[GP Surgery], [INPUT 2].[Referral Source],
[INPUT 2].[Referral Date], [INPUT 2].[Date Intervention Began], [INPUT
2].[Date Intervention Ceased]
FROM [INPUT 2]

As you can see it works it out from the name of the DB. It really is the
best way to link tables like this.
Yours - Dika

Angus said:
Thanks for your reply.

But i get two tables from two servers, does it work?
Click to expand...
Click to expand...
 
Hi Angus - no problem - I've not been in for the last couple of days either.
However, you've gone beyond anything I know to do - I think you ought to
re-post with the text below as your question as your first question was not
as specific. Good luck -- Dika

Angus said:
Sorry for my late reply.

I guess this is a good way but I failed to test it. I tried to link the
servers by Access ADP but found my authorization is denied to create query in
servers (as usually i use MS query and create query in my machine).

Is is possible to make linked servers/ tables by MS Query? or

How to make it by ADO (i need two ADO objects? how to put them together with
SQL statesment?)

Thanks.


Kernow Girl said:
Hi Angus - you should have no problem - I have one Union that has 3 tables
that all have the same fields with the same names (input files from different
servers) Part of my union query looks like this
SELECT [INPUT 1].[Appended?], [INPUT 1].[Which FE], [INPUT 1].[Date
Appended], [INPUT 1].[Entry Date], [INPUT 1].[Closed form], [INPUT
1].District, [INPUT 1].PCT, [INPUT 1].Team, [INPUT 1].[OT/PT], [INPUT
1].[First Name], [INPUT 1].Surname, [INPUT 1].DOB, [INPUT 1].[HOSP Number],
[INPUT 1].[ICS Number], [INPUT 1].[GP Surgery], [INPUT 1].[Referral Source],
[INPUT 1].[Referral Date], [INPUT 1].[Date Intervention Began], [INPUT
1].[Date Intervention Ceased]
FROM [INPUT 1]
UNION SELECT [INPUT 2].[Appended?], [INPUT 2].[Which FE], [INPUT 2].[Date
Appended], [INPUT 2].[Entry Date], [INPUT 2].[Closed form], [INPUT
2].District, [INPUT 2].PCT, [INPUT 2].Team, [INPUT 2].[OT/PT], [INPUT
2].[First Name], [INPUT 2].Surname, [INPUT 2].DOB, [INPUT 2].[HOSP Number],
[INPUT 2].[ICS Number], [INPUT 2].[GP Surgery], [INPUT 2].[Referral Source],
[INPUT 2].[Referral Date], [INPUT 2].[Date Intervention Began], [INPUT
2].[Date Intervention Ceased]
FROM [INPUT 2]

As you can see it works it out from the name of the DB. It really is the
best way to link tables like this.
Yours - Dika

Angus said:
Thanks for your reply.

But i get two tables from two servers, does it work?

:

Hi Angus - sounds like a Union Query would do the trick. The help screen says
this -
A union query combines fields from two or more tables or queries into one
field in the query's results. You might use a union query to combine data
from two tables. For example, you might combine company name and city data
for all companies in Brazil that are listed in the Suppliers and Customers
tables. You could then create a make-table query based on the union query to
make a new table.
Hope this is what you want -- Yours Dika

:

Is it possible to have one query for tables from various servers?

For example, we have PO number in New York server and search for same PO
number in Paris server. All the tables in NY is same as in Paris.

How?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top