Use fields from 4 databases using an UNION QERY to create new table

[Shusha52]

Hi there,

I wld appreciate any help in this. I have 4 areas that use an access database each one in a separate folder on our server. These Db need to be kept separate.

So far, every month I import the major tables to create a regional one and then use the Regional one for reporting to manaagement and the ministry.

Another reason the merges need to be done is that all the Regional clinicians need to use the latest version as all clients from the 4 areas are present in the merged version. So far manual merges have worked fine..

I am trying to centralize them now and i have created query that works fine for me but not for the clinicians as they don't have access to some of the folders and that may be one of the reasons I suspect. So i tried to create a Master Regional table with just 4 fields and insert it into a master field. I FAILED miserably.

So I need help.

I figured out how to create a union query to use in reports.. but for data entry purposes I created the UNION query like this and as mentioned in one of the postings tried to create a new table which the regional users can use.

I am unable to create a new table with these 4 databases using a union query. How wld i go about doing that.

Also, some of the regional staff do not have access to the other folders other than HW.. is this a reason why they can't see the data from the union query.

I can see it when i use the union query as a source for a drop down field for client name.

What am i doing wrong?

I wld like all regional staff to be able to use the union query as a soruce for that one field or alternatively is there a better thing i can do. Any help is approeciated

the Union Query i have is as follows. Let say i want to insert these fields from the 4 areas into one new table called PATNEW how ould I do that?


SELECT PAT.Masterno, PAT.ProgDesc,Pat.birthdate, PAT.RegDateTime
FROM PAT IN 'I:\RCMS - Hw\RCMS_Hw.mdb'
where PAT.aip = True

UNION SELECT PAT.Masterno, PAT.ProgDesc,Pat.birthdate, PAT.RegDateTime
FROM PAT IN 'I:\RCMS - NP\RCMS_Np.mdb'

UNION SELECT PAT.Masterno, PAT.ProgDesc,Pat.birthdate, PAT.RegDateTime
FROM PAT IN 'I:\RCMS - BR\Br.mdb'

UNION SELECT PAT.Masterno, PAT.ProgDesc,Pat.birthdate, PAT.RegDateTime
FROM PAT IN 'I:\RCMS - HN\HN.mdb';

can u tell what code is needed to either create this new table PATNEW or shd i get permission for all users to access the 4 areas folders ?

thanks
usha

 

[SkipVought]

Hi,

Link the 4 tables from each database into your database.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[lespaul]

Take your query and save it: qryAllFour

then you can use another query to get the data into another table:

INSERT INTO masterTable (SELECT * FROM qryAllFour)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com