union by joining ?

  • Thread starter Thread starter pamelafluente
  • Start date Start date
P

pamelafluente

I have T1, T2, T3, tabels tithe the same structure.

Assume I want to make a UNION of the so that
all records are in on table.

Is it formally possible to do the union using, instead,
a JOIN statement or it does not make any sense ?

-P
 
Pamela:

It seems you are trying to do something like this:
1. Append all the records in Table 2 to Table 1.
2. Append all the records in Table 3 to Table 1.
Then Table 1 will contain all the records.

If you are trying to do this, then the easiest way is probably to create two
Append queries.

Geoff
 
Geoff ha scritto:
Pamela:

It seems you are trying to do something like this:
1. Append all the records in Table 2 to Table 1.
2. Append all the records in Table 3 to Table 1.
Then Table 1 will contain all the records.

If you are trying to do this, then the easiest way is probably to create two
Append queries.
Click to expand...

What I was wondering is that if one has, for instance, something like:

SELECT
A.Field1 AS [Field1],
A.[Field 2] AS [Field 2],
FROM
[T1] A;

UNION

SELECT
A.Field1 AS [Field1],
A.[Field 2] AS [Field 2],
FROM
[T1] A;


would it be possible to write a JOIN command (perhaps using
some dummy field) which gives the same result ? Or is it impossible ?

-P
 
Geoff ha scritto:
Pamela:

It seems you are trying to do something like this:
1. Append all the records in Table 2 to Table 1.
2. Append all the records in Table 3 to Table 1.
Then Table 1 will contain all the records.

If you are trying to do this, then the easiest way is probably to create
two
Append queries.
Click to expand...

What I was wondering is that if one has, for instance, something like:

SELECT
A.Field1 AS [Field1],
A.[Field 2] AS [Field 2],
FROM
[T1] A;

UNION

SELECT
A.Field1 AS [Field1],
A.[Field 2] AS [Field 2],
FROM
[T1] A;


would it be possible to write a JOIN command (perhaps using
some dummy field) which gives the same result ? Or is it impossible ?
Click to expand...


No, you cannot use a Join to retrieve the equivalent of a Union (and vice
versa)
 
Pamela,

You might get a better answer if you post to the newsgroup:
microsoft.public.access.queries

However:
would it be possible to write a JOIN command (perhaps using
some dummy field) which gives the same result ? Or is it impossible ?
Click to expand...

I think the answer to your question is you cannot use the JOIN command to
achieve the same result as the UNION command. You'd probably end up with
the query returning no records; or, the query might match every record in
Table1 with every record in Table2, resulting in the query returning a huge
number of records (there is a name for this I can't remember).

If you want to experiment, you can design a query and switch to SQL view,
where JOIN statements would be on display. However, in one book I have on
Access 2000 is says:

"You must use SQL to create a union query in Access; there is no equivalent
QBE method for creating a union query. UNION is not a SQL statement or
event a clause. Instead, it is an operator you use to vertically splice
together two or more compatile queries." (Quoted from the "Access 2000
Developers Handbook" by Getz, Litwin and Gilbert.)

I'm not an expert on SQL. As I say, the "queries" newsgroup might be the
better place for your question.

Geoff



Geoff ha scritto:
Pamela:

It seems you are trying to do something like this:
1. Append all the records in Table 2 to Table 1.
2. Append all the records in Table 3 to Table 1.
Then Table 1 will contain all the records.

If you are trying to do this, then the easiest way is probably to create
two
Append queries.
Click to expand...

What I was wondering is that if one has, for instance, something like:

SELECT
A.Field1 AS [Field1],
A.[Field 2] AS [Field 2],
FROM
[T1] A;

UNION

SELECT
A.Field1 AS [Field1],
A.[Field 2] AS [Field 2],
FROM
[T1] A;


would it be possible to write a JOIN command (perhaps using
some dummy field) which gives the same result ? Or is it impossible ?

-P
Click to expand...
 
Queries are based on records. What kind of data do you have in the three
tables?

In a well-normalized relational database, it is rare to need multiple tables
"with the same structure" ... actually, it's probably an indication that the
tables hold spreadsheet data!

If you'll describe a bit more about your underlying data, and what you want
to be able to do with it (you have described HOW you are trying to do
something), the 'group readers may be able to offer alternate approaches...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
If you'll describe a bit more about your underlying data, and what you want
to be able to do with it (you have described HOW you are trying to do
something), the 'group readers may be able to offer alternate approaches...
Click to expand...


Thanks Geoff, Douglas, Jeff , for the enlighting and prompt reply.

I wasn't actually working with real data. I was just curious about the
matter,
say as a theoretical question.

I was wondering whether, given T1, ..., TN with * identical
structures * , one could express their UNION using some JOIN syntax
instead (perhaps with some involved trick or dummy field) .

But so far it seems your general answer has been that it is NOT
possible.

Let me know if you find out some trick.

Thanks a lot,

-Pam
 
Thanks Geoff, Douglas, Jeff , for the enlighting and prompt reply.

I wasn't actually working with real data. I was just curious about the
matter,
say as a theoretical question.

I was wondering whether, given T1, ..., TN with * identical
structures * , one could express their UNION using some JOIN syntax
instead (perhaps with some involved trick or dummy field) .

But so far it seems your general answer has been that it is NOT
possible.
Click to expand...

It can't be done, and as Jeff pointed out, it doesn't really make sense to
want to, since you should never be in such a situation. With a properly
designed database, you would never have "T1, ..., TN with * identical
structures *"
 
Douglas J. Steele ha scritto:
It can't be done, and as Jeff pointed out, it doesn't really make sense to
want to, since you should never be in such a situation. With a properly
designed database, you would never have "T1, ..., TN with * identical
structures *"
Click to expand...

Thanks Douglas. You are right.
.. however, for each "never" there is always an exception ;)

In my case I had in mind the situation one uses SQL statement against
Excel,
where it is quite common to have several sheets all with same
structure.

Of course, that's not a "dbms", at least not relational :)

Thanks,

-P
 
Pamela,
In my case I had in mind the situation one uses SQL statement
against Excel, where it is quite common to have several sheets
all with same structure.
Click to expand...

In that case, you'd do better to transfer the data from the various Excel
spreadsheets into a single table in an Access database; then perform
whatever actions you need on that single table. If you had many Excel
spreadsheets, you'd automate the data transfer using VBA.

Geoff
 
Geoff ha scritto:
Pamela,


In that case, you'd do better to transfer the data from the various Excel
spreadsheets into a single table in an Access database; then perform
whatever actions you need on that single table. If you had many Excel
spreadsheets, you'd automate the data transfer using VBA.
Click to expand...

Right. Infact I was thinking to use the UNION (or join if possible)
just for the
purpose of importing the multiple sheets into one table. OleDB (and
hence simple SQL
agains Excel) seems a convenient and efficient way to do (that instead
of a lot coding).

-P
 
Pamela,
Right. Infact I was thinking to use the UNION (or join if possible)
just for the purpose of importing the multiple sheets into one table.
OleDB (and hence simple SQL agains Excel) seems a convenient
and efficient way to do (that instead of a lot coding).
Click to expand...

I don't agree. It does not involve a lot of coding to capture data from
multiple spreadsheets. In fact, it's very simple. VBA has a
DoCmd.TransferSpreadsheet method.

Below is a code example demonstrating how to import all the spreadsheets in
the root directory into a single table in Access. This example assumes that
all spreadsheets have the same format (as you've said) and that Table1
contains field names equivalent to the Excel column headings. It wouldn't
be much more trouble to re-write the code for a specific set of Excel file
names.

Public Sub GetExcelData()

Const CON_FOLDER As String = "C:\"
Dim strPathName As String
Dim strFileName As String

strPathName = CON_FOLDER & "*.xls"
strFileName = Dir(strPathName)
Do Until strFileName = ""
strPathName = CON_FOLDER & strFileName
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Table1", _
strPathName, True
strFileName = Dir()
DoEvents
Loop

MsgBox "Finished"

End Sub

Geoff
 
Hi Geoff,

Thanks. I was referring to mutiple sheets all contained within the
same
Excel file (that's why I am talking about a union done via oledb).

Btw, Is it possible to adjust you proc to achieve that ? :)

Geoff
 
Pamela,
Thanks. I was referring to mutiple sheets all contained
within the same Excel file (that's why I am talking about
a union done via oledb). Btw, Is it possible to adjust
you proc to achieve that?
Click to expand...

Yes.

Geoff
 
I am having a similar issue but have only one excel sheet and column names in
excel matchup exactly with access table. I tried copy and pasting previous
code then modifying to match database and excel file to no avail. My excel
file is C:\Documents and Settings\Owner\Desktop\Kevin's CMS Material\mod 2&3
CMS template.xls and my table name is [cmsreadingstable]. How would I modify
the previous code to get the sheet into the table?

thanks in advance,

Warren
 
Warren,
How would I modify the previous code to get the
sheet into the table?
Click to expand...

If you only have one Worksheet in one Workbook, it might be simpler to
import manually. Here are demonstrations of how to import manually and
programmatically.

(A) To import manually:

1. Open Access and load the database.
2. Open the File menu, point to Get External Data and click Import.
3. In the Import dialog that opens, select the XLS file containing the
data and click the Import button.
4. The Import Spreadsheet Wizard should start and guide you through the
rest of the Import process.


(B) To import programmatically the first Worksheet in one Excel Workbook:

Public Sub GetOneExcelWorksheet()

' Assumes that the data in the XLS file
' is in the first Worksheet - that has any
' name, like "Sheet1" (without an apostrophe).

' This subprocedure requires a Reference
' to the Excel object library (in VBA
' Tools > References > Select Microsoft Excel).

Const CON_WORKBOOK As String = _
"C:\Documents and Settings\Owner\" _
& "Desktop\Kevin's CMS Material\" _
& "mod 2&3 CMS template.xls"
Const CON_TABLE As String = "cmsreadingstable"


DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, CON_TABLE, _
CON_WORKBOOK, True

MsgBox "Finished"

End Sub

Geoff
 
Back
Top