Union Query by choice..

[Angela]

Hi,

I have a table in access. Field names are

File Name: (Containing table names) Table1, Table2, Table3........
Select: (Check box)Yes/No

I would like to return a query based on union of selected tables of my
choice.
It could be all, it could be 1 etc.

Any help is appreciated as way forward.


Thx.

 

[Steve]

Hello Angela,

This is a highly unusual question! Please tell us what the fields are in
Table1, Table2, Table3........ and what exactly you want the query for. Most
likely there is an underlying problem in your database that is causing you
to want to create this query. If so, we can advise you on how to better
handle the problem.

Steve
(e-mail address removed)

 

[Angela]

Well, I have around 16 text files placed in a folder each having
around 250K rows. One file for each month.
For my ease, I have renamed files to make them easy to remember.

I have linked them in access as text files.
I have a union query of all tables which is huge.

I want to return union based on my choice of tables that I select.

I have placed table names in a table..

...and u know wht I have just clicked the solution! Bingo!
I will post it after I get a reply from you.

Thx.

 

[Steve]

You could simplify retrieving the data you want by adding a Year and a Month
field to each set of data, updating the Year and Month in each data set then
appending all data into one table. Then to retrieve the data you want you
would only need to set an appropriate criteria for Year and Month.

Steve

 

[KARL DEWEY]

One file for each month.
That is the wrong way to structure your database. Use one table with added
field to define your month in a DateTime datatype.

 

[Tom Wickerath]

Hi Angela,

To make your life easier--a lot easier--append (add) all of the data to one
table. You can add a new field that indicates the source of the data, such as
MonthVal. I recommend against using "Month" or "MonthName" as fieldnames, as
these are considered a reserved words in Access:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Once you have all of the data in one table, you can easily set the
appropriate criteria in a query to return data for the selected months.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Angela]

Thank you all 3 of you Steve, Karl & Tom.

Yes I have added that and instead of using file name, I have tagged
each file with additional column with simplified month name.

I have got a table with month names instead of file or table name.
From there I have set a criteria pointing at particular month that I
want to select.

Here I would like to point that the UNION of all tables is the key to
all this.
A second query from that union will be performing what I require i.e.
(selection)

Sometimes we seek very simple things which are right around the
corner..

 

[Tom Wickerath]

Hi Angela,

Here I would like to point that the UNION of all tables is the key to
all this.
A second query from that union will be performing what I require i.e.
(selection)

So, basically, what you are doing is creating a virtual table, using the
UNION query, and then querying this virtual table with your second query. Why
not just start with a properly normalized database in the first place, to
eliminate the need for the UNION query (which, by the way, renders the
results read-only).

I would use the UNION query *only* as a one-time source of data for a new
Append query. Append the records into a single table, adding a field if
desired to designate the source of the data. After getting the data into one
local table, delete the UNION query, and delete all the various links to the
text files. Then do a compact and repair.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Angela]

Yes Tom..

I'm still working on it to speed up things.

The files that I'm using are like 250K rows each.. like 250K X 14
months.
So append for a new selected(choice) 4 months would create a huge
table right.
So instead of creating a table, I'm just quering linked text files
from a folder.

The file that I test with this huge DB is like few records.. not more
than 45000 & comfortably fit in an xl sheet.
I place that sheet(usually import) as a table in the db to get
matching results from selected(choice takes) query.

This is a back & forth activity and limited to the sheet that I work
on.
I check the sheet for the required months and fit the db query size
accordingly like for instance if I don't have some months in the xl
sheet, I exclude them.

I would like to get this with some VB automation & forms but at the
moment going step by step : )

 

[John W. Vinson]

Yes Tom..

I'm still working on it to speed up things.

The files that I'm using are like 250K rows each.. like 250K X 14
months.
So append for a new selected(choice) 4 months would create a huge
table right.
So instead of creating a table, I'm just quering linked text files
from a folder.

The file that I test with this huge DB is like few records.. not more
than 45000 & comfortably fit in an xl sheet.
I place that sheet(usually import) as a table in the db to get
matching results from selected(choice takes) query.

This is a back & forth activity and limited to the sheet that I work
on.
I check the sheet for the required months and fit the db query size
accordingly like for instance if I don't have some months in the xl
sheet, I exclude them.

I would like to get this with some VB automation & forms but at the
moment going step by step : )

Try it, in a test database. I think you may be pleasantly surprised.

Text files store trailing blanks, Access tables don't - so your table may be
considerably smaller than the text file. A million rows (four months) is a
respectable table but WELL within Access' capabilities; I'd be very willing to
try all 14 months. And when you put indexes on the fields that you use for
selecting and sorting, your queries will be *vastly* faster than a <shudder>
UNION query of unindexed external text files!

 

[Angela]

Hi John W. Vinson,

I would love to do that.. where do I begin with this below indexes
that you mentioned.
I have heard about this indexes but never used em.. A little help will
do right? : )


John wrote:
And when you put indexes on the fields that you use for
selecting and sorting, your queries will be *vastly* faster than a
<shudder>
UNION query of unindexed external text files!

 

[John W. Vinson]

Hi John W. Vinson,

I would love to do that.. where do I begin with this below indexes
that you mentioned.

Sure. Once you have a Table (an Access/JET table in the database, not linked),
open it in design view. Select a field that you will be using for searching or
sorting (the date field would be a likely choice). In the field's properties
box on the lower left change the Indexed property from blank to "Indexed
(duplicates allowed)" if there may be multiple records with the same value, or
"Indexed (no duplicates)" if you want the field to be unique. The Primary Key
of a table is automatically indexed uniquely for you, just by making it the
Primary Key, so don't create an extra index.

Don't go overboard on indexing though: you're limited to 32; each index takes
up space, and in a 4 million row table this could be a LOT of space, counting
toward the 2 GByte limit for the entire database; and though indexes speed
searching, they can markedly slow updating. It's somewhat of a Black Art to
determine the indexing scheme for a large database; it's why SQL DBAs often
seem to have missing souls, they've sold them for the knowledge. <g>