Union Query

[Pvarga]

I have a database that I have given to 5 different sites and now when I get a copy of each file back I want to combine all the totals and run 6 different queries on it. I do not want to append because I still need each separate also.

I started a UNION query and it seemed to be working until one of the tables has a different number of columns in it. I was using SELECT *. I tried to select one column at a time and it still said the cols were uneven. This one copy of the DB has 3 cols used for a different use the others do not need. without having to change allof them how can I combibe them?

And if I get the Union query to work how do I run my current queries on it?

Thansk much

Tricia

 

[RichMorrison]

Tricia,

It sounds like you are making this harder than it really is.

You can take your 5 "outside" tables and combine them into one with append queries. You can append into an empty table and keep the 5 originals.

Then you can run all the queries you want against the combined table.

RichM

 

[RV]

And you can it even more easier or yourself by translating your sites to your table structure.......

RV

 

[David R]

This follows from an earlier thread...

It can be found here: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30295

I'm not sure what RV means by translating to your table structure. However if not all your tables are the same structure then as you saw, a UNION query will not be so easy.

Two alternatives present themselves to me, depending on how often you'll be collecting your data from the remote sites and running reports on it.
1. Modify your UNION query so that it provides blank "dummy" fields for those tables that don't have the 3 extra columns. Null As [FieldName] should work. If your remote sites will be keeping their tables and just emailing you updated copies of them weekly or whatever, this should work. The drawback of UNION queries is they will have to calculate each time you run a report, and this can be slow. A new table that you Append to will be just as fast as a local table, once you've run it once.
2. If your remote sites are emptying out their tables every time they mail in, and starting fresh, then RichMorrison's Append Query idea will work. If the tables will retain their old data then you'll have to make sure you're not copying in duplicates of the old data when you Append. I dislike Maketable/Append queries for this reason, plus it can lead to database bloat.

 

[RV]

I was quite a bit vague..
Reading Tricia's post I get the idea that she's talking 'bout a (singele?) database which is spread amongst 5 different sites.
I guess the tables fysically are (in basic) the same.

So, why not translate the orrucence of sites to the tablestrucure (define an extra column Site) or create a separate table Site.

Then the data needed could be easily:

1) transfered to a main table
2) used for getting all data needed through ONE query without having to combine six queries with UNIONS (what if the were 100 sites?).

RV

 

[Pvarga]

Union Query prob cont.

I have had a little luck with the Union query. But for some reason if I try to use my field names in the statement "Race AS Race" it gives an error about circ. ref's. If I let it do it's own thing it uses Expr1, Expr2 etc. as my col headings and that will not work with my queries.

As far as an Append query it seems to leave a few records out each time. I have no idea why. If I look at the table it all looks the same but I guess when the users were entering they typed something different. One table is missing 3 and one 2. I have checked every col to see if there is a col with only that number of entries that may be excluded for some reason but see nothing.

I hate to think about totaling by hand. There are 5 different queries I need to run and each has many rows and cols of figures that are being calculated.

Guess I should have never said I could do this.

Thanks to all.

tricia

 

[David R]

You can still do this; it's just a matter of what method you choose.

I'd recommend picking one way or the other, not trying both simultaneous as you'll only make yourself crazy.

For the UNION query, you're right that it won't let you rename your field to itself (I think). So name it to TotalRace or something and change your query appropriately? You can copy an existing query that works on one table, and change the references in that to work on your UNION query, right?

As for Append Queries, I can only think there are some records that have simultaneous Primary Key entries. Don't import the primary key (if it's Autonumber) into your new table. Let it add a new separate PK unless this will thoroughly screw up your data. You can even include the old PKs as a field, just not as a PK, if that helps.

Keep plugging away at it...

 

[Pvarga]

Thank you.

I have my union query and it works fine with my renamed fields.
Now to run all my queries on this what do I do? Is there a way to save the result to a table?

I can copy the queries and make name changes if I have to. I have select queries and then crosstabs running off them because I had so many statistice to run. Ex. one i needed was program type listed in my first col and then accross the top a breakdown by race and sex. Then others might be broken down different ways using levels, age, race sex. It gets pretty complicated. I only have two tables though since it is just a lot of differnt things for each person. Prob. looks pretty poor to someone who know db's well but i am just learning.

I appreciate the help.

Tricia

 

[David R]

You don't usually save queries to tables..

If you need a record of the data right now, you can make a report based on your query or queries and print it off for a hard copy, or run the report and use the Snapshot option to save a copy of it. You might have to install the Snapshot Viewer, but I believe it comes with Access.
You can also export queries to Excel, if that helps. Also - if you can recreate this moment in time in your query by limiting the start and end dates, for example, then you don't need to save the query elsewhere at all. You'll be able to go back to the data anytime.

The idea is to not have to be going into your queries constantly in design view and change things. It may seem exciting and interesting now but when you're in production mode, you want results, not code and design grids. Try to design your queries accordingly. Make good use of parameters and forms with popup boxes that limit your reports/queries to the data you want to retrieve.

I don't think it sounds poor at all. If you're going to run a lot of small queries/crosstab data on one person and want to see it all at once, what I would do is this:
Have a small unbound form that lets you select the person and the data range (dates, accounts, services, etc) that you want to view. Have a button on this form that starts your report.
Your report can have many different subreports. If you design many subreports using all the queries you want to work on, and insert them in your report sequentially, then you can end up with a very professional looking analysis of this person's account with your agency. Then print or save the report as a Snapshot in their folder and you're good to go.

Post back if you have further questions.
David R

 

[Pvarga]

Thanks for the info. I did run one query to test and then ran it individually on each table too and the figures were fine.

First time around I was off a few but went back and added SS# which is my primary key and it seemed to fix it.

I had left it out on purpose incase 2 sites were working with the same person but I guess it is better to have it.

I will probably go ahead and change every query to work with my union query. It will take awhile but I will only run it once a year and the rest of the year let everyone enter in their own db's and just gahter then each June. Some of my queries have long IIf statements and about 20 separate cols but once I do this I can re-use it each year.

Now all I need is for my state (TN) to pass a budget so I can keep working. I am from NJ and am amazed at how TN cannot handle their finances.

Thanks much

Tricia