Creating a combined query

[Klips]

Hi all,

Here is my situation. I have a table with some part informations. In
that table I have two fields: END1 and END2. These contain a connection
code. I also have a field QTY that contains the number of parts with
these particular discriptions. Here's an example:

TBL_PARTS :
---------------------------------------------------------
| PID | END1 | END2 | QTY |
---------------------------------------------------------
| 2424 | C353-35 | E34-3 | 3 |
| 2425 | D34 | E34-3 | 2 |
| 2426 | J34 | D34 | 12 |
| 2427 | J354-3 | J34 | 8 |

Now what I want is a combined list of the various connection codes. In
this case the result would be like this:

QRY_CONNECTIONS:
-----------------------------------------
| CONNECTION | QTY |
-----------------------------------------
| C353-35 | 3 |
| E34-3 | 5 |
| D34 | 14 |
| J34 | 20 |
| J354-3 | 8 |

As you see from the above result, I want the query to use both END1 and
END2 and group the results and give me a total number of times the
connection code appears.

I'm hoping this is a possible with Access. Any help with this problem
is greatly appreciated.

Thanks

 

[John Vinson]

Hi all,

Here is my situation. I have a table with some part informations. In
that table I have two fields: END1 and END2. These contain a connection
code. I also have a field QTY that contains the number of parts with
these particular discriptions. Here's an example:

TBL_PARTS :
---------------------------------------------------------
| PID | END1 | END2 | QTY |
---------------------------------------------------------
| 2424 | C353-35 | E34-3 | 3 |
| 2425 | D34 | E34-3 | 2 |
| 2426 | J34 | D34 | 12 |
| 2427 | J354-3 | J34 | 8 |

Now what I want is a combined list of the various connection codes. In
this case the result would be like this:

QRY_CONNECTIONS:
-----------------------------------------
| CONNECTION | QTY |
-----------------------------------------
| C353-35 | 3 |
| E34-3 | 5 |
| D34 | 14 |
| J34 | 20 |
| J354-3 | 8 |

As you see from the above result, I want the query to use both END1 and
END2 and group the results and give me a total number of times the
connection code appears.

I'm hoping this is a possible with Access. Any help with this problem
is greatly appreciated.

A UNION query will work:

SELECT [END1] AS CONNECTION, QTY
FROM TBL_PARTS
UNION ALL
SELECT [END2], QTY
FROM TBL_PARTS;

Save this query as uniBothEnds, and then do a totals query:

SELECT CONNECTION, Sum([QTY]) FROM uniBothEnds
GROUP BY CONNECTION;

John W. Vinson[MVP]