How to concatenate two tables via query?

[G Lykos]

Have two tables, each with a particular field (named differently in each
table). Need to sum other data in each table by linking on those field to
one corresponding field in a master table. Currently am Y'ing the two
select queries into a third, and am using a calculated expression in the
third query to join the fields, but get a run-time parameter prompt.
<Enter> with no selection for the run-time prompt and the query works
correctly, but would like to get rid of the bogus prompt.

One solution would seem to be to create a single query comprising these two
tables stacked end to end and then tie that query into the master query.
Could perhaps manually execute two append queries, but would prefer an
automatic process where opening the final query automatically creates the
concatenated tables (as queries?) and then analyses them.

As is apparent, am fumbling with the structure for dealing with this
arrangement (one query accessing two others in parallel, while bringing to a
like field in each of the two under a common name). Thoughts?

Thanks,
George

 

[Ken Snell \(MVP\)]

Sounds like a Union query is what you seek? General syntax for such a query:

SELECT Field1, Field2, Field3
FROM Table1
UNION ALL
SELECT Field1, Field2, Field3
FROM Table2;


Note that you must have the same number of fields in both subqueries, the
corresponding fields from each table must be of the same data type, etc.

I have used UNION ALL above to select all records from both tables, even if
they are duplicates. If you want to show just one record of any duplicate
records (ones that exist in both tables), then use just UNION.

There are some idiosyncracies with UNION queries (truncation of memo fields
to 255 characters if you use UNION instead of UNION ALL, for example), but
start with this and then post back if you have questions.

 

[G Lykos]

Thanks, Ken, this is exactly what I was trying to do. Using SELECT *,
[UniqueFieldName] as [CommonFieldName] then allows stacking the two
uniquely-named fields in a common field.

Going from here - a problem for me is that the union structure isn't
supported by the query design GUI window, and I don't speak SQL - at best
mumble it a little, so would appreciate a little further guidance.

The union results in a table that has [pjUID] with some repeated values on
different records, each with its corresponding [Task Hours]. I now need to
sum [Task Hours] for each unique [pjUID]. I could read this query into a
second conventional query to do this, but would prefer to put it all in one.
What is a SQL syntax to do the Task Hour totals per pjUID?

Thanks for your further help with this!

George

 

[G Lykos]

Ken, having done a little reading and played with it a bit, it appears that
the union must first be created and then its resulting data processed by a
second SQL statement. I don't see how to do so in the Access query - Access
complains as soon as it sees text after the semi-colon concluding the UNION
statement. Maybe I'm missing the big picture on how the pieces fit
together - is each Access query one (and only one) SQL statement? Thanks
for any further ideas.

Thanks, Ken, this is exactly what I was trying to do. Using SELECT *,
[UniqueFieldName] as [CommonFieldName] then allows stacking the two
uniquely-named fields in a common field.

Going from here - a problem for me is that the union structure isn't
supported by the query design GUI window, and I don't speak SQL - at best
mumble it a little, so would appreciate a little further guidance.

The union results in a table that has [pjUID] with some repeated values on
different records, each with its corresponding [Task Hours]. I now need to
sum [Task Hours] for each unique [pjUID]. I could read this query into a
second conventional query to do this, but would prefer to put it all in one.
What is a SQL syntax to do the Task Hour totals per pjUID?

Thanks for your further help with this!

George

Sounds like a Union query is what you seek? General syntax for such a query:

SELECT Field1, Field2, Field3
FROM Table1
UNION ALL
SELECT Field1, Field2, Field3
FROM Table2;


Note that you must have the same number of fields in both subqueries, the
corresponding fields from each table must be of the same data type, etc.

I have used UNION ALL above to select all records from both tables, even if
they are duplicates. If you want to show just one record of any duplicate
records (ones that exist in both tables), then use just UNION.

There are some idiosyncracies with UNION queries (truncation of memo fields
to 255 characters if you use UNION instead of UNION ALL, for example), but
start with this and then post back if you have questions.

field

to

bringing

to

 

[Ken Snell \(MVP\)]

Creating the union query is easily done...

Create a new query, don't select any tables, close the table selection
window, and then click on the SQL View button at top left end of toolbar.
That will open a text editor that allows you to type the SQL language
directly into the query.

In that window, based on the info that you've provided, type the following
(replace my generic names with your real names for the tables and fields):

SELECT TableName1.*,
TablenName1.UniqueFieldName AS CommonFieldName
FROM TableName1
UNION ALL
SELECT TableName2.*, TableName2.UniqueFieldName
FROM TableName2;

Save this query and name it qryUnion. Close the query. You now have the
union query all set to be used as the "table" source in another query.

To get the sums that you wish, now create a second query that has this SQL
syntax:

SELECT Sum([Task Hours]) AS SumOfTaskHours, pjUID
FROM qryUnion
GROUP BY pjUID
ORDER BY pjUID;

The above is a "totals" query that can be created from the normal "design"
view of the query. Just add the qryUnion query to the grid, pull down the
two indicated fields, click on the Greek letter sigma icon on the toolbar
(looks like an E), select Sum in the "Totals:" row for Task Hours, and
you're all set.

--

Ken Snell
<MS ACCESS MVP>

Thanks, Ken, this is exactly what I was trying to do. Using SELECT *,
[UniqueFieldName] as [CommonFieldName] then allows stacking the two
uniquely-named fields in a common field.

Going from here - a problem for me is that the union structure isn't
supported by the query design GUI window, and I don't speak SQL - at best
mumble it a little, so would appreciate a little further guidance.

The union results in a table that has [pjUID] with some repeated values on
different records, each with its corresponding [Task Hours]. I now need
to
sum [Task Hours] for each unique [pjUID]. I could read this query into a
second conventional query to do this, but would prefer to put it all in
one.
What is a SQL syntax to do the Task Hour totals per pjUID?

Thanks for your further help with this!

George

Sounds like a Union query is what you seek? General syntax for such a query:

SELECT Field1, Field2, Field3
FROM Table1
UNION ALL
SELECT Field1, Field2, Field3
FROM Table2;


Note that you must have the same number of fields in both subqueries, the
corresponding fields from each table must be of the same data type, etc.

I have used UNION ALL above to select all records from both tables, even if
they are duplicates. If you want to show just one record of any duplicate
records (ones that exist in both tables), then use just UNION.

There are some idiosyncracies with UNION queries (truncation of memo fields
to 255 characters if you use UNION instead of UNION ALL, for example),
but
start with this and then post back if you have questions.

 

[G Lykos]

Ken, thanks for the further guidance. Is it inherent to Access that each
query can contain one (and only one) SQL statement?

George

Creating the union query is easily done...

Create a new query, don't select any tables, close the table selection
window, and then click on the SQL View button at top left end of toolbar.
That will open a text editor that allows you to type the SQL language
directly into the query.

In that window, based on the info that you've provided, type the following
(replace my generic names with your real names for the tables and fields):

SELECT TableName1.*,
TablenName1.UniqueFieldName AS CommonFieldName
FROM TableName1
UNION ALL
SELECT TableName2.*, TableName2.UniqueFieldName
FROM TableName2;

Save this query and name it qryUnion. Close the query. You now have the
union query all set to be used as the "table" source in another query.

To get the sums that you wish, now create a second query that has this SQL
syntax:

SELECT Sum([Task Hours]) AS SumOfTaskHours, pjUID
FROM qryUnion
GROUP BY pjUID
ORDER BY pjUID;

The above is a "totals" query that can be created from the normal "design"
view of the query. Just add the qryUnion query to the grid, pull down the
two indicated fields, click on the Greek letter sigma icon on the toolbar
(looks like an E), select Sum in the "Totals:" row for Task Hours, and
you're all set.

--

Ken Snell
<MS ACCESS MVP>

Thanks, Ken, this is exactly what I was trying to do. Using SELECT *,
[UniqueFieldName] as [CommonFieldName] then allows stacking the two
uniquely-named fields in a common field.

Going from here - a problem for me is that the union structure isn't
supported by the query design GUI window, and I don't speak SQL - at best
mumble it a little, so would appreciate a little further guidance.

The union results in a table that has [pjUID] with some repeated values on
different records, each with its corresponding [Task Hours]. I now need
to
sum [Task Hours] for each unique [pjUID]. I could read this query into a
second conventional query to do this, but would prefer to put it all in
one.
What is a SQL syntax to do the Task Hour totals per pjUID?

Thanks for your further help with this!

George

Sounds like a Union query is what you seek? General syntax for such a query:

SELECT Field1, Field2, Field3
FROM Table1
UNION ALL
SELECT Field1, Field2, Field3
FROM Table2;


Note that you must have the same number of fields in both subqueries, the
corresponding fields from each table must be of the same data type, etc.

I have used UNION ALL above to select all records from both tables,

even
if

they are duplicates. If you want to show just one record of any duplicate
records (ones that exist in both tables), then use just UNION.

There are some idiosyncracies with UNION queries (truncation of memo fields
to 255 characters if you use UNION instead of UNION ALL, for example),
but
start with this and then post back if you have questions.

--

Ken Snell
<MS ACCESS MVP>


Have two tables, each with a particular field (named differently in
each
table). Need to sum other data in each table by linking on those

field
to

one corresponding field in a master table. Currently am Y'ing the two
select queries into a third, and am using a calculated expression in
the
third query to join the fields, but get a run-time parameter prompt.
<Enter> with no selection for the run-time prompt and the query works
correctly, but would like to get rid of the bogus prompt.

One solution would seem to be to create a single query comprising these
two
tables stacked end to end and then tie that query into the master
query.
Could perhaps manually execute two append queries, but would prefer an
automatic process where opening the final query automatically creates the
concatenated tables (as queries?) and then analyses them.

As is apparent, am fumbling with the structure for dealing with this
arrangement (one query accessing two others in parallel, while

bringing
to

a
like field in each of the two under a common name). Thoughts?

Thanks,
George

 

[Ken Snell \(MVP\)]

Not sure what you mean by "one (and only one) SQL statement"? By definition,
a query is defined by a single SQL statement, however you can have multiple
"SQL sentences" in that one statement. For example:

SELECT TableName.*,
(SELECT Max(TableN2.Fieldname) FROM TableN2
WHERE TableN2.DateField < Date()) AS MaxDateValue
FROM TableName;

or

SELECT TableName.*,
(SELECT Max(TableN2.Fieldname) FROM TableN2
WHERE TableN2.ForeignKeyField = TableName.PrimaryKeyField)
AS MaxDateValue
FROM TableName;

or

SELECT TableName.*
FROM TableName
WHERE TableName.OrderDateField =
(SELECT Max(TableN2.Fieldname) FROM TableN2
WHERE TableN2.DateField < Date());

and so on.

If your question is, "why did I recommend two separate queries", that
primarily is for ease of use. SQL syntax can get a bit complicated when you
try to use the SQL statement of a query as the table source -- at times,
ACCESS will "cough" and improperly save/delete the query's SQL sentence --
and making changes to an embedded or subquery can be a bit of "fun" (not to
mention the fun if the subquery is needed more than once in the one query
that is using it), so I find it much easier for maintenance (and for
remembering what I was doing when I revisit a database after being away for
a while) if I make some queries as saved queries and then write other
queries to use those as the table sources.

Note -- sometimes you can speed up the running of a query by using
subqueries and embedded queries instead of separately saved queries when the
first query returns tons of records and then the second query must filter
those records multiple times. If you do the filtering directly in the
subquery (see my example 2 above), the overall query will run much faster.
--

Ken Snell
<MS ACCESS MVP>

Ken, thanks for the further guidance. Is it inherent to Access that each
query can contain one (and only one) SQL statement?

George

Creating the union query is easily done...

Create a new query, don't select any tables, close the table selection
window, and then click on the SQL View button at top left end of toolbar.
That will open a text editor that allows you to type the SQL language
directly into the query.

In that window, based on the info that you've provided, type the
following
(replace my generic names with your real names for the tables and
fields):

SELECT TableName1.*,
TablenName1.UniqueFieldName AS CommonFieldName
FROM TableName1
UNION ALL
SELECT TableName2.*, TableName2.UniqueFieldName
FROM TableName2;

Save this query and name it qryUnion. Close the query. You now have the
union query all set to be used as the "table" source in another query.

To get the sums that you wish, now create a second query that has this
SQL
syntax:

SELECT Sum([Task Hours]) AS SumOfTaskHours, pjUID
FROM qryUnion
GROUP BY pjUID
ORDER BY pjUID;

The above is a "totals" query that can be created from the normal
"design"
view of the query. Just add the qryUnion query to the grid, pull down the
two indicated fields, click on the Greek letter sigma icon on the toolbar
(looks like an E), select Sum in the "Totals:" row for Task Hours, and
you're all set.

--

Ken Snell
<MS ACCESS MVP>

Thanks, Ken, this is exactly what I was trying to do. Using SELECT *,
[UniqueFieldName] as [CommonFieldName] then allows stacking the two
uniquely-named fields in a common field.

Going from here - a problem for me is that the union structure isn't
supported by the query design GUI window, and I don't speak SQL - at best
mumble it a little, so would appreciate a little further guidance.

The union results in a table that has [pjUID] with some repeated values on
different records, each with its corresponding [Task Hours]. I now
need
to
sum [Task Hours] for each unique [pjUID]. I could read this query into a
second conventional query to do this, but would prefer to put it all in
one.
What is a SQL syntax to do the Task Hour totals per pjUID?

Thanks for your further help with this!

George


Sounds like a Union query is what you seek? General syntax for such a
query:

SELECT Field1, Field2, Field3
FROM Table1
UNION ALL
SELECT Field1, Field2, Field3
FROM Table2;


Note that you must have the same number of fields in both subqueries, the
corresponding fields from each table must be of the same data type, etc.

I have used UNION ALL above to select all records from both tables, even
if
they are duplicates. If you want to show just one record of any duplicate
records (ones that exist in both tables), then use just UNION.

There are some idiosyncracies with UNION queries (truncation of memo
fields
to 255 characters if you use UNION instead of UNION ALL, for example),
but
start with this and then post back if you have questions.

--

Ken Snell
<MS ACCESS MVP>


Have two tables, each with a particular field (named differently in
each
table). Need to sum other data in each table by linking on those field
to
one corresponding field in a master table. Currently am Y'ing the two
select queries into a third, and am using a calculated expression in
the
third query to join the fields, but get a run-time parameter prompt.
<Enter> with no selection for the run-time prompt and the query
works
correctly, but would like to get rid of the bogus prompt.

One solution would seem to be to create a single query comprising these
two
tables stacked end to end and then tie that query into the master
query.
Could perhaps manually execute two append queries, but would prefer an
automatic process where opening the final query automatically
creates
the
concatenated tables (as queries?) and then analyses them.

As is apparent, am fumbling with the structure for dealing with this
arrangement (one query accessing two others in parallel, while bringing
to
a
like field in each of the two under a common name). Thoughts?

Thanks,
George