UNION OF SELECTED TABLES ONLY.

[Angela]

Hi,


I have a access file in which I have linked some text files.
The file names are SR01.txt, SR02.txt, SR03.txt .... and so on.

There is a union query which returns union of all these text files.

Now I want to be able to return the union of selected files only, like
if I want union of file SR01 & SR03, the union should return result of
these two files only. Is that possible?

Would appreciate if anyone can help with this.

Thanks.

 

[Arvin Meyer [MVP]]

Sure, but you'll need to do it in code:

Function fUnion(T1 As String, T2 As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM " & T1 & " Union All Select * FROM " & T2
Set qdf = db.CreateQueryDef("qryUnionTest", strSQL)
End Function

Test in the immediate window:
? fUnion("SR01", "SR02")

Now add a button to a form which can use form parameters, so that you can
pick the table names from text boxes.

Sub cmdUnion_Click()
Call fUnion(Me.txtTable1, Me.txtTable2)
End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Disclaimer: Any code or opinions are offered here as is. Some of that
code has been well tested for number of years. Some of it is untested
"aircode" typed directly into the post. Some may be code from other
authors. Some of the products recommended have been purchased and
used by the author. Others have been furnished by their manufacturers.
Still others have not been personally tested, but have been
recommended by others whom this author respects.

You can thank the FTC of the USA for making this disclaimer necessary.

 

[Klatuu]

First question is how would you select the file names to include? You could
use a multi select List box that would list the possibilities and allow the
user to select from the list.

Then you would have to create the SQL statment using VBA to include
references to the select files.

 

[Angela]

Hey guys,

Well I can make a table with two fields.

Name of this table can be FILEs.

1st field
Table Name (This will have SR01, SR02, SR03.... the names of the the
text files.)
2nd field
Select (A check box, Check=Yes & Uncheck=No)

One more thing, I would like to return a column in the union query in
which I want to show the selected table name as reference so that I
can further differentiate that from which file the line is actually
shown/picked.

Thanks.

 

[TedMi]

DON'T DO THAT!
Your method requires selecting your choices by directly entering data into a
table. That's a no-no. Tables are for storing data, forms are for
interacting with users. If you store the selections in a table, you have to
undo them before selecting a new set of tables for the Union. And this is a
disaster in a multi-user environment, if two or more users try to update the
table with their own selections.
You should create a form on which a user can select the tables to include in
the query.
Arvin Meyer's method works for two tables, but gets cumbersome for any more.
Use Klatuu's suggestion of presenting the list of tables in a multi-select
list box - that will require creating a table of tablenames, but you won't
be writing to this table to make your selections. That table will be the
rowsource for the listbox from which you select.

Then in the Click event of a button that runs the Query, you will loop
through the rows of the listbox and build a SQL query in a string variable,
adding a Union Select clause for each row that is selected. Finally, execute
the query so constructed. Let us know if you need details of that code.

One other issue: Do you need to specify any criteria for the Select, or do
you want all records from each table? If the former, are the criteria the
same or different for each table?

-TedMi

 

[John W. Vinson]

One more thing, I would like to return a column in the union query in
which I want to show the selected table name as reference so that I
can further differentiate that from which file the line is actually
shown/picked.

That's easily done: the UNION query should contain a calculated text field
identifying the table:

SELECT "SR01" AS Tablename, [SR01].thisfield, [SR01].thatfield
FROM [SRO1]
WHERE <criteria>
UNION ALL
SELECT "SR02", [SR02].thisfield, [SR02].thatfield
FROM [SR02]
WHERE...

 

[Angela]

Thanks John W. Vinson [MVP] for the input. Will try that.

TedMi,

I have a folder in which I have 13 files around 80MB each. Files are
pipe "|" delimited with header line starting at line 9.
I have to open the file, wait, then remove first 8 lines so that the
header line comes to 1st row which I can easily link/import in access
error free.

In this case, I have linked them in access instead of import keeping
in mind the size.

All I want is a choice as to which files I want to select for use. I
have linked all files, just need to have choice.
It could be one file or union of multiple files.

The select option will work in the query.
I will check the checkbox against the file that I want to use in the
FILEs table.

The union query will return the union of selected files as mentioned
in the FILEs table.
The file name could be just file name since all text files are linked
and the link already contains the location part of the file, like you
can have "SR01" or "c:\db\file\SR01.txt".

Have said that, need to remember that files are pipe "|" delimited.

 

[Angela]

No luck with this post : (

 

[Maximiliano Gabriel Avila]

HOLA HOLA

 

[John W. Vinson]

No luck with this post : (

I have no idea what you mean, Angela. What did you try? What went wrong? What
was the outcome?