Linking a master table to many external tables? 2

[DAmoss]

I have a 'Master' Access database that I wish to populate automatically with all the records from 5 other similar databases. The table structures are exactly the same in all cases. They are all kept in the same folder, but each database is named differently, they have all been created using Access 2003.

Ideally I would love to have a selectable database list on the screen and then be able to tick which ones to pull into the master table! but maybe I'm jumping the gun here.

Can someone give me some clues on how I can pull in these other external tables into the master table. I know I have to loop through them one by one and import the data accordingly, but any help or clues would be greatfully accepted.

Thanx.

 

[Remou]

Do you require this database to be updateable? If not, why not link the tables and dynamically create a union query to include selected 'sub-tables'.

 

[JerryKlmns]

DAmoss

Would this thread705-1444517 be of any help?

 

[DAmoss]

Hi Remou,

No I don't need the Master db to be updatable, the records are updated in the external db's, i just need to be able to pull them all in together and run off some reports when asked.

I could copy and paste (which is what I do at the moment)but there are thousands of records being updated on a quarterly basis, I'm just trying to make this task a bit easier if I can.

Hi Jerry,

I'll take a look at the link supplied and let you know later, cheers.

 

[lespaul]

then the only thing you need to do is link....

Leslie

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

http://www.ubuntu.com

 

[DAmoss]

Ok I've linked them in to my main database ... but how do I get them all to feed into my main table. I've tried creating a query to pull them together but it doesn't work.

Ps, all of these external data tables do not have any primary keys (don't ask!) so i can't link them that way.

Any suggestions?

 

[Remou]

Have you looked at a Union query?

SELECT a, b, c FROM t1
UNION ALL
SELECT a, b, c FROM t2
UNION ALL
SELECT a, b, c FROM t3

 

[DAmoss]

Thanks Remou, easy when you know how, here's my code that now works:

SELECT [INDIVIDUALS Starter Info].* FROM [INDIVIDUALS Starter Info]
UNION ALL
SELECT [Linked Starters Proj04].* FROM [Linked Starters Proj04]
UNION ALL
SELECT [Linked Starters Proj07].* FROM [Linked Starters Proj07]
UNION ALL
SELECT [Linked Starters Proj20].* FROM [Linked Starters Proj20];

But, I still doesn't add all the linked tables into the first listed table, do I need to append all these records as well?

 

[DAmoss]

Answered my own question ... I've now got rid of the first SELECT statement and created a seperate APPEND query instead. My only problem now is that I only wnt to append any new records, not the whole lot again and again every time it's run, am I missing something?

Here's my 'Append' code below:

INSERT INTO [INDIVIDUALS Starter Info]
SELECT [All External Data].*
FROM [All External Data];

 

[Remou]

I am not quite sure why you need to append data when a union query will include everything from the linked tables. Appending only new records when you do not have a key field can be tedious. You could try a Left Join on a sufficient number of important fields and import only those records that have no match.

 

[lespaul]

why do you need to insert them into your own table, you stated above that you don't need to update any information. Get rid of your "master" table and just use a query to get the most recent information from the linked tables....


Leslie

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

http://www.ubuntu.com

 

[DAmoss]

Remou / lespaul

Thanks for your comments, I need to insert them into my own table because I already have a lot of other seperate queries / reports and links to external Crystal reports that use that particular table already, don't want to rock the boat as they say. All the updating is done in the linked tables, but I need an overall global view of the data.

I've now created a macro to empty my main table, put all the data returns into a 'merge all' table and then append them all to my main table for statistical reporting. Might not be the most efficient but it now works a treat.

 

[JerryKlmns]

DAmoss

Don't forget then to compact & repair after each time you do that.

 

[Shusha52]

Hi there,

I seem to have a similar problem where i have 4 sets of data that come to me on a monthly basis. I do a merge on it every month for example:

I have 4 databases DB1; db2; db3; db4 which have many tables. I have to merge three of the tables - namely family, client and regst tables from each one of them and make a master Family table, Client table and Restration table.
The regst table is used as a master table for all corporate reports and a lot of the reports use this master regst table.
I created a Union query, pointing them to the tables in the various folders and managed to get all the records. these records do not need to updated at all. ALL i need is a master table called PAT with all of these regst tables information only.

Pls see code attached. How do i create a table called PAT from these 4 tables. I tried to insert it into a table with a similar structure called Pat_U but nothing seems to work. Pls help.

Code below

SELECT PAT.Masterno, PAT.ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer, PAT.Fullname, PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\Admin\RCMS_HW_2008.MDB' WHERE PAT.PROGDESC = "HW"

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer, PAT.Fullname, PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'Y:\rcms_np_2008.mdb'

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer, PAT.Fullname, PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\RCMS_BR.MDB'

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer, PAT.Fullname, PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\RCMS_hn.MDB'

ORDER BY FULLNAME DESC;

The above code does give me all the data. I am stuck on how to create a brand new table called PAT.

Should I have an empty table with identical structure of the above tables and then insert or ???


Any help pointing me in the right direction is appreciated
thks
Usha

 

[lespaul]

Should I have an empty table with identical structure of the above tables and then insert

Yes, you need an EMPTY table in YOUR database with the exact structure and then you will run the query:

INSERT INTO EmptyPAT
(SELECT PAT.Masterno, PAT.ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\Admin\RCMS_HW_2008.MDB' WHERE PAT.PROGDESC = "HW"

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'Y:\rcms_np_2008.mdb'

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\RCMS_BR.MDB'

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\RCMS_hn.MDB')

in the future, please start a new post instead...HTH

now, are the fields listed above ALL the fields in the PAT table in every database? If so, you don't need to explicitly list each field, you can use the SELECT *

INSERT INTO EmptyPAT
(SELECT * FROM PAT IN 'F:\Admin\RCMS_HW_2008.MDB' WHERE PAT.PROGDESC = "HW"
UNION 
SELECT *
FROM PAT IN 'Y:\rcms_np_2008.mdb'
UNION 
SELECT * FROM PAT IN 'F:\RCMS_BR.MDB'
UNION 
SELECT * FROM PAT IN 'F:\RCMS_hn.MDB')

Leslie

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

http://www.ubuntu.com

 

[Shusha52]

Hi,

thks for ur input. I tried it exactly like u said.. Some of the tables may or may not have all the flds. I am going to update all of the f4 sites with similar structure which makes coding so much easier.
But for now, i am just trying to see if it will work.. Without the Insert it shows me the 1000 over records and each area office numbers corresxpond to the clients there.
But the moment I introduce the Insert into EMPTYPAT ( I created a table called EMPTYPAT with the same structure as detailed and it gives me syntax error.
thks a million Leslie

 

[lespaul]

ok you may need to explicitly declare your insert into fields

INSERT INTO EmptyPAT [b](MasterNo, Progdesc, RegDateTime, ...TCDReason) [/b]
(SELECT PAT.Masterno, PAT.ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\Admin\RCMS_HW_2008.MDB' WHERE PAT.PROGDESC = "HW"

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'Y:\rcms_np_2008.mdb'

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\RCMS_BR.MDB'

UNION SELECT PAT.MASTERNO, ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral], PAT.Transfer,  PAT.Fullname,  PAT.DFMCYS, PAT.DirectFundedYN, PAT.TRIntfg, PAT.TRadddatetime, PAT.TRIBIndate, PAT.IBIEndDate, PAT.TCYN, PAT.TCSDate, PAT.TCEDate, PAT.StsDate, PAT.pstsdate, PAT.AddDateTime, PAT.RegStsDesc, PAT.ClientId, PAT.Famid, PAT.EdateYn, PAT.IBIENDED, PAT.IBIEndReason, PAT.DischargeReason, PAT.ReRegst, PAT.EligibleYN, PAT.Ptransfer, PAT.PRegdatetime, PAT.PEDate, PAT.PWTDATE, PAT.ProjStdate, PAT.PAddDateTime, PAT.ProJenddate, PAT.ACTFlag, PAT.EDate, PAT.FDate, PAT.WTDate, PAT.IBIDischDate, PAT.NonMatdate, PAT.NonMatDiDate, PAT.LastName, PAT.FirstName, PAT.Birthdate, PAT.IBIInitAllochrs, PAT.ActAloc, PAT.WASDate, PAT.LastADate, PAT.CFConDate, PAT.CFStDt, PAT.CFEndDt, PAT.CfssDecDT, PAT.InEligDisDate, PAT.NonMatReas, PAT.InEligibleReason, PAT.IbiEndReas, PAT.CFSS, PAT.CFSSDReas, PAT.CfssDec, PAT.StaticDate, PAT.TCRef, PAT.TCName, PAT.TCPre, PAT.TCPost, PAT.TCDReason
FROM PAT IN 'F:\RCMS_hn.MDB')

Leslie

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

http://www.ubuntu.com

 

[Shusha52]

Hi,
Wow u are super fast.. I am still having probs.. it says there is problem with the Insert Into .. Syntax errorin Insert into statement
thx
Usha

 

[lespaul]

maybe:

INSERT INTO EmptyPAT (MasterNo, Progdesc, RegDateTime, ...TCDReason) [b]VALUES[/b]
(SELECT PAT.Masterno, PAT.ProgDesc, PAT.RegDateTime, PAT.ProjNotes, PAT.[Re-Referral],....

sorry I don't have a version of Access at this location to be able to test!
Leslie

 

[Shusha52]

Hi Leslie,

Thks for ur message. I had a doc's appt and had to rush. I will try this first thing in the morning and let u know how it went.
Thx again
Usha