Access 2003 Union queryor any other wayCombining records from 4 tables 1

[Anushkas]

Hi there,

Can one of you experts pls help me. I have 4 Access 2003 databases used by 4 different area offices. They are identical databases except that each area office has to be kept separate for confidentiality reasons.

The databases have several tables but one main TABLE named PAT is the one that contains Client records.

for ex:
area office Ottawa has 200 records in PAT Table
area office Toronto has 400 records in PAT table
area office Ottawa has 200 records in PAT table
area office calgary has 100 records in PAT table

Each of these tables have a Masterno as the key but linking them will not do anything as each of them have an identified set. No common links. But all fields from the tables have same name etc.. except the db has different name.

All Along at the end of every mth i have merged them manually to get a Regional table called PAT and do the stats. I am planning on moving the db to a more secure SQL platform but prior that I want to make sure i can access the data.

The help i am looking for is that I want to do away with the manual appends i have to do every mth.

Is there a simple way of seeing all 900 records using a union query and use that query to do my monthly stats?

If it is possible how shd i do this. I am lost here. Any help or pointers to help is appreciated.

Thanks a lot

Usha

 

[MakeItSo]

You can do this with the help of a UNION ALL query combined with the "IN" factor...
Example:

SELECT Customer_ID, Sales_Rep_ID
FROM m_customer_orders IN 'p:\database\shoe_orders_data.mdb';

allows you to query a table that is located in another access db.

Presumed the table structure of all PAT talbes is exactly the same:

SELECT "Ottawa" AS [Location],  PAT.* FROM PAT IN '[full path to your ottawa.mdb]'
UNION ALL
SELECT "Toronto" AS [Location],  PAT.* FROM PAT IN '[full path to your toronto.mdb]'
UNION ALL
...

should do the trick.

;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Anushkas]

Hi there,

Thks for ur prompt response. I reposted it because for some sick reason I thought I had posted it in the crystal reports forum, hence the dbl post.

I tried the union code with both tables in the same datbase and it did not work yesterday. I will try it from the different locations as all of our area offices access our server through citrix. with that said, your location confuses me.
Let me give u an example:

Our ottawa db is in the share folder F:/nas1/ottawa and the db name is ottawa.mdb
our Calgary db is also in the same share folder F:/nas1/Calgary and the db name is Calgary.mdb

with that said can u tell what is the code that wld go into building this query. And yes all regions have the same fld structure, names etc.

Thks for ur help
Usha

 

[MakeItSo]

Hi Usha,

it should work just as I posted:

SELECT "Ottawa" AS [Location],  PAT.* FROM PAT IN 'F:\nas1\ottawa\ottawa.mdb'
UNION ALL
SELECT "Calgary" AS [Location],  PAT.* FROM PAT IN 'F:\nas1\Calgary\Calgary.mdb'
UNION ALL
...

It might not have worked with the tables in the SAME DB, as they have the same name, hence Access could't tell which you refered to.
Should be fine when querying different DBs though, as their path clearly identifies them.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Addendum if it doesn't - then you can try this:

SELECT "Ottawa" AS [Location],  [b]P1.* FROM PAT AS P1[/b] IN 'F:\nas1\ottawa\ottawa.mdb'
UNION ALL
SELECT "Calgary" AS [Location],  [b]P2.* FROM PAT AS P2[/b] IN 'F:\nas1\Calgary\Calgary.mdb'
UNION ALL
...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Anushkas]

Hi there,
Sorry for bothering u. I appreciate the time you are spending in helping me and am grateful for that.
When u say
SELECT "Ottawa" AS [Location],

what exactly do u mean. Do i have to create my select statement

with
SELECT "Ottawa" AS [Location], or is it just plain


SELECT P1.* FROM PAT AS P1 IN 'F:\nas1\ottawa\ottawa.mdb'
UNION ALL
SELECT P2.* FROM PAT AS P2 IN 'F:\nas1\calgary\calgary.mdb'
UNION ALL
SELECT P3.* FROM PAT AS P3 IN 'F:\nas1\toronto\toronto.mdb'
UNION ALL
SELECT P4.* FROM PAT AS P4 IN 'F:\nas1\guelph\guelph.mdb'


I am not sure what the p1 p2 p3 and p4 are.. I am new to sql statements hence the confusion.

Thks
Usha

 

[Anushkas]

Addendum...
I tried the code and it says too many fields defined. All i want is to have a dataset which has all regions information, without having to merge it every single time..
Area office records keep changing , meaning status , so i wld like to merge/append the files..

Pat from Region1 containing 100 records
pat from region2 containing 400 records
pat from region3 containing 100 records
pat from region4 containing 200 records

so that way when i am looking at the table once the union query or any other method i wld aplly will give me 800 records in all.

I am hope i am clear in my desc. ifu need further information please let me know
thks
Usha

 

[MakeItSo]

SELECT "Ottawa" AS [Location]" will do this:

Lets say your PAT tables all contain the fields
ID[tab]Customer

Then you couldn't tell which entries came from which DB if you just merged all in one output.
The above SQL will create one additional column titled "Location", and this column will hold the entry you have put in quotes before [Location]. Hence the output of the query will be:

Location[tab]ID[tab]Customer
Ottawa[tab]1[tab]Mikey
Ottawa[tab]2[tab]Johnny
Ottawa[tab]3[tab]Jimmy
...
Calgary[tab]1[tab]Joe
...

It is sort of a "fake" column that will help you to identify which table the respective record is from.

P1, P2 etc. are aliases for your tables, just to discern them within the code. I don't think they are actually necessary though.

"Too many fields" sounds like your tables are not normalized , i.e. have bad structural setup. How many columns do they have? More than 255?
The number of records is irrelevant.

I think you will have to specify the columns then:

SELECT "Ottawa" AS [Location],  [column1],[column2],[column3] FROM PAT IN 'F:\nas1\ottawa\ottawa.mdb'
UNION ALL
SELECT "Calgary" AS [Location],  [column1],[column2],[column3] FROM PAT IN 'F:\nas1\Calgary\Calgary.mdb'
UNION ALL
...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Anushkas]

Hey,

You certainly deserve a star my friend for the time you have spent in responding and helping me understand the code. You must have teaching backgd for sure

While i was awaiting your response I tried the [] thingie. I created a simple query, took all relevant fields and took the sql from there copied it over to my Union query to save time and to eliminate errors and bingo it worked.
Your explanation of the location is much appreciated.

It empowers me with a lot more information and i thank u from the bottom of my heart( I do have one ) for the time you guys take in responding so accurately and with such speed. What will i do without tek-tips and its members I always wonder as it is a great meeting point for us.

When i ran the query i created with the [] I found that for one region it is creating dups though the original table has none. Actually the calgary table has 322 records, but when i run this query it creates 334. I am not sure why.

I am goign to enclose the code i have created for you to have a look at where i went wrong. thks in advance
Usha

Here is my code:

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:\Autism Admin\RCMS_HW_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 'Y:\autism\rcms_np_2008.mdb'
ORDER BY PROGDESC DESC

 

[MakeItSo]

Hey, thanks a lot!
(I actually DO have a teaching background, hehehe - and have been taught a zillions myself since joining tek-tips in '03)

Your code looks good to me and it does not contain (many) superfluous/non-normalized fields.

Your tables do contain name/address information which would be better placed in a seperate "persons" or "customers" table - but that is only No. 97 in the agenda. No. 1 is getting your data...


So, in fact all looks good so far, except for the dupes.
You say they come from one DB only?
That kind of eliminates the query as source. There may not be physical dupes in your DB, but perhaps some rubbish remaining from update/deletes or whatever in that Calgary table. A simple "Compact and Repair" of the database might rid you of these dupes already.

If that is not the source then I am admittedly stumped - for now, that is...


P.S: You wouldn't want to know how little I knew before I frequented this place!


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell