Combine 2 tables

[Guest]

I'm new to access and am struggling to do what should be a simple task.

I've got 2 tables. Both tables have the same 2 fields: ("Part Description"
and "Qty")

Some "Part Description" entries are the same. Others are different.

I want to combine them into one table with no duplicate part descriptions
and correct total Qty's. How do I do this?

Example:
Input
Table 1; Part Description (Bolt A) Qty (1)
Part Description (Bolt B) Qty (1)

Table 2; Part Description (Bolt A) Qty (1)
Part Description (Washer A) Qty (1)

Result

Table Combined; Part Description (Bolt A) Qty (2)
Part Description (Bolt B) Qty (1)
Part Description (Washer A) Qty (1)

Thanks for any help!

 

[Guest]

Combine the tables in a Union query and then sum in a totals query.

[Table 1_2] ----
SELECT [Table 1].[Part Description], [Table 1].QTY
FROM [Table 1]
UNION SELECT [Table 2].[Part Description], [Table 2].QTY
FROM [Table 2];

SELECT [Table 1_2].[Part Description], Sum([Table 1_2].QTY) AS SumOfQTY
FROM [Table 1_2]
GROUP BY [Table 1_2].[Part Description];

 

[Guest]

I'd start by tacking the rows from both tables together in a UNION query:

SELECT [Part Description], Qty
FROM [Table 1]
UNION ALL
SELECT [Part Description], Qty
FROM [Table 2];

Make sure you use a UNION ALL operation here as a UNION operation will
suppress any duplicated rows, which you don't want to do in this case.

Save this query as qryPartsUnion say, and then create another query which
sums the Qty values per part description:

SELECT [Part Description], SUM(Qty) AS TotalQty
FROM qryPartsUnion
GROUP BY [Part Description];

Once you are satisfied that this gives the correct results you can change it
to a Make Table query to create your new table. You can then change the
design of the new table, making the Part Description column the primary key
and changing the name of the TotalQty column to Qty.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks,

That worked.

Just out of curiosity, there isn't a way to do this all in 1 query rather
than 2 is there?

Combine the tables in a Union query and then sum in a totals query.

[Table 1_2] ----
SELECT [Table 1].[Part Description], [Table 1].QTY
FROM [Table 1]
UNION SELECT [Table 2].[Part Description], [Table 2].QTY
FROM [Table 2];

SELECT [Table 1_2].[Part Description], Sum([Table 1_2].QTY) AS SumOfQTY
FROM [Table 1_2]
GROUP BY [Table 1_2].[Part Description];

I'm new to access and am struggling to do what should be a simple task.

I've got 2 tables. Both tables have the same 2 fields: ("Part Description"
and "Qty")

Some "Part Description" entries are the same. Others are different.

I want to combine them into one table with no duplicate part descriptions
and correct total Qty's. How do I do this?

Example:
Input
Table 1; Part Description (Bolt A) Qty (1)
Part Description (Bolt B) Qty (1)

Table 2; Part Description (Bolt A) Qty (1)
Part Description (Washer A) Qty (1)

Result

Table Combined; Part Description (Bolt A) Qty (2)
Part Description (Bolt B) Qty (1)
Part Description (Washer A) Qty (1)

Thanks for any help!

 

[John Spencer]

Yes you can do it in one query, *** IF *** your table and field names don't
contain spaces or other characters that are not letters or numbers. Then
you could set up the UNION query as a sub-query in the FROM clause.

SELECT PartDescription, Sum(Qty) as Total
FROM
(SELECT PartDescription, Qty
FROM YourTable
UNION ALL
SELECT PartDescription, Qty
FROM YourOtherTable) as Combined
GROUP BY PartDescription

Thanks,

That worked.

Just out of curiosity, there isn't a way to do this all in 1 query rather
than 2 is there?

Combine the tables in a Union query and then sum in a totals query.

[Table 1_2] ----
SELECT [Table 1].[Part Description], [Table 1].QTY
FROM [Table 1]
UNION SELECT [Table 2].[Part Description], [Table 2].QTY
FROM [Table 2];

SELECT [Table 1_2].[Part Description], Sum([Table 1_2].QTY) AS SumOfQTY
FROM [Table 1_2]
GROUP BY [Table 1_2].[Part Description];

I'm new to access and am struggling to do what should be a simple
task.

I've got 2 tables. Both tables have the same 2 fields: ("Part
Description"
and "Qty")

Some "Part Description" entries are the same. Others are different.

I want to combine them into one table with no duplicate part
descriptions
and correct total Qty's. How do I do this?

Example:
Input
Table 1; Part Description (Bolt A) Qty (1)
Part Description (Bolt B) Qty (1)

Table 2; Part Description (Bolt A) Qty (1)
Part Description (Washer A) Qty (1)

Result

Table Combined; Part Description (Bolt A) Qty (2)
Part Description (Bolt B) Qty (1)
Part Description (Washer A) Qty (1)

Thanks for any help!