A newby question to create a query from 2 tables

I have 3 tables, all with the same field names.

I need to create a query which,
- Pulls 2 field from table 1, but also adds a new field called "Category",
and populates it with "X"
- Does the same thing with table 2, but puts "Y" in the "Category" field
- Append table 2 at the end of table 1

What would my SQL statements look like?

Also, is anyone aware of an on-line MS-Access SQL training for MS-Access
2002.

Regards,

Android.

Hi Android,

Not sure why you need 3 tables but this query will do what you want.

Select Field1 As [F1], Field2 As [F2], 'X' As [Category] From Tbl1
Union
Select Field1 As [F1], Field2 As [F2], 'Y' As [Category] From Tbl2
Union
Select Field1 As [F1], Field2 As [F2], 'Z' As [Category] From Tbl3

Jay

Hi,

Thanks. The following part works well:
SELECT 'Confirmations' AS Category, [DONE confirmations].From AS [From],
[DONE confirmations].Received AS Received
FROM [DONE confirmations]

However, when I add the 2nd part, as in:
SELECT 'Confirmations' AS Category, [DONE confirmations].From AS [From],
[DONE confirmations].Received AS Received
FROM [DONE confirmations]
UNION
SELECT 'Repeats' AS Category, [DONE repeats].From AS [From], [DONE
repeats].Received AS Received
FROM [DONE repeats]

It gives the following error: "Reserved error (-3034); .." Unfortunately no
explanations are given. What am I missing?




"Jay Vinton" wrote in message
...
Hi Android,

Not sure why you need 3 tables but this query will do what you want.

Select Field1 As [F1], Field2 As [F2], 'X' As [Category] From Tbl1
Union
Select Field1 As [F1], Field2 As [F2], 'Y' As [Category] From Tbl2
Union
Select Field1 As [F1], Field2 As [F2], 'Z' As [Category] From Tbl3

Jay

SELECT 'Confirmations' AS Category, [DONE confirmations].From AS [From],
[DONE confirmations].Received AS Received
FROM [DONE confirmations]

Your query shouldn't run. You should get a circular reference error because you have 2 aliases that are the same as the column names, and a general SELECT error because "From" is a reserved word in SQL and shouldn't/can't be used as a column name.

I think you should review your design. Why not have one table with a column that indicates the status (confirmation, repeat, etc.)? It would help if we had more info about the bigger picture.

Jay

A roundabout way to solve this, would be to update your 3
tables with the new category column and set the values
using 3 update queries, then a simple append query to
combine them into one.

Or did I miss the boat?

-----Original Message-----
I have 3 tables, all with the same field names.

I need to create a query which,
- Pulls 2 field from table 1, but also adds a new field
called "Category",
and populates it with "X"
- Does the same thing with table 2, but puts "Y" in
the "Category" field
- Append table 2 at the end of table 1

What would my SQL statements look like?

Also, is anyone aware of an on-line MS-Access SQL
training for MS-Access
2002.

Regards,

Android.


.

Jay, Ernie,

The bigger picture is...

I am looking at the email messages saved in 7 Outlook folders named "DONE
xxxx" etc.. I mentioned 3 only as an example because if I can do it for 3, I
can do it for 7.

In MS-Access, I have created linked tables of data from these Outlook
folders via Insert -- Table -- Link Table, and then selecting
Exchange()...

What I want to do next is, pull selected columns (From, Received) into Excel
vis a MS-Query on Excel. However before I do this, I need to add a column so
I can distinguish the 7 folders from each other -- Hence the "Category"
column I wish to add.

To add the "Category" column, I though the best way was to create a query
which pulls 1 table, adds the Category column with a fixed value, appends
the next one in the same way,.... .. until the 7 tables are joined in one
Query.

My Excel sheet would link to this query, and provide live stats on how many
emails were received each day in each category.

This was the simplest method I could think of. If there is a simpler method,
I would appreciate it.

Once I have done the above, I'll edit my Excel query to somehow append only
the new data as the Outlook folders get bigger.

Regards.

Android.




"Jay Vinton" wrote in message
...
SELECT 'Confirmations' AS Category, [DONE confirmations].From AS [From],
[DONE confirmations].Received AS Received
FROM [DONE confirmations]

Your query shouldn't run. You should get a circular reference error
because you have 2 aliases that are the same as the column names, and a
general SELECT error because "From" is a reserved word in SQL and
shouldn't/can't be used as a column name.

I think you should review your design. Why not have one table with a
column that indicates the status (confirmation, repeat, etc.)? It would help
if we had more info about the bigger picture.

Jay

I'm now using
SELECT 'Confirmations' AS dCategory, [DONE confirmations].From AS [dFrom],
[DONE confirmations].Received AS dReceived, 1 as dAllOnes
FROM [DONE confirmations]
UNION SELECT 'General' AS dCategory, [DONE general].From AS [dFrom], [DONE
general].Received AS dReceived, 1 as dAllOnes
FROM [DONE general]
union
SELECT 'Payments' AS dCategory, [DONE payments].From AS [dFrom], [DONE
payments].Received AS dReceived, 1 as dAllOnes
FROM [DONE payments]
........

Although teh query now works, it still shows the query icon as a circular
reference (two intersecting circle -- i assume that means circular
reference). Also some of the dates seem corrupted (show as 12/17/7827
9:16:41 AM).

How can I get rid of the circular reference? I don't quite see what is
circular.

Android.

"Android" wrote in message
...
Jay, Ernie,

The bigger picture is...

I am looking at the email messages saved in 7 Outlook folders named "DONE
xxxx" etc.. I mentioned 3 only as an example because if I can do it for 3,
I
can do it for 7.

In MS-Access, I have created linked tables of data from these Outlook
folders via Insert -- Table -- Link Table, and then selecting
Exchange()...

What I want to do next is, pull selected columns (From, Received) into
Excel
vis a MS-Query on Excel. However before I do this, I need to add a column
so
I can distinguish the 7 folders from each other -- Hence the "Category"
column I wish to add.

To add the "Category" column, I though the best way was to create a query
which pulls 1 table, adds the Category column with a fixed value, appends
the next one in the same way,.... .. until the 7 tables are joined in one
Query.

My Excel sheet would link to this query, and provide live stats on how
many
emails were received each day in each category.

This was the simplest method I could think of. If there is a simpler
method,
I would appreciate it.

Once I have done the above, I'll edit my Excel query to somehow append
only
the new data as the Outlook folders get bigger.

Regards.

Android.




"Jay Vinton" wrote in message
...
SELECT 'Confirmations' AS Category, [DONE confirmations].From AS
[From],
[DONE confirmations].Received AS Received
FROM [DONE confirmations]

Your query shouldn't run. You should get a circular reference error
because you have 2 aliases that are the same as the column names, and a
general SELECT error because "From" is a reserved word in SQL and
shouldn't/can't be used as a column name.

I think you should review your design. Why not have one table with a
column that indicates the status (confirmation, repeat, etc.)? It would
help
if we had more info about the bigger picture.

Jay

...provide live stats on how many
emails were received each day in each category.

If this is your ultimate objective, you are barking up the wrong tree. Maybe there are other objectives that I don't know about but, if this is what it is, you don't need Access or Excel. You can do this in Outlook alone. Click Tools - Macros - Visual Basic Editor.

I haven't tested this but a quick look at the Outlook object model suggests that the the general idea would be to iterate through the Folders collection and get references to the folders of interest.

Then do the same with the MailItems collections in those folders. Accumulate counts based on each item's ReceivedTime property and there are your stats.

You might get a more enlightened suggestion if you posted this in one of the Outlook forums.

Jay

I need Excel, because I am capturing other stats as well. I have found Excel
to be the best common tool to present in 1 spot, data from from several
sources.

I'll look into going directly to Outlook and avoiding Access.

However I am still curious what is the the circular reference in my SQL
code. If this is easily seen and explained, I would appreciate learning what
it is.

Thanks for your help in pointing to Outlook VBA as a possible solution.

Regards,

Android.

"Jay Vinton" wrote in message
...
...provide live stats on how many
emails were received each day in each category.

If this is your ultimate objective, you are barking up the wrong tree.
Maybe there are other objectives that I don't know about but, if this is
what it is, you don't need Access or Excel. You can do this in Outlook
alone. Click Tools - Macros - Visual Basic Editor.

I haven't tested this but a quick look at the Outlook object model
suggests that the the general idea would be to iterate through the Folders
collection and get references to the folders of interest.

Then do the same with the MailItems collections in those folders.
Accumulate counts based on each item's ReceivedTime property and there are
your stats.

You might get a more enlightened suggestion if you posted this in one of
the Outlook forums.

Jay