Select data from two tables where there is a match and sum columns

[blackbox]

Hi all,

Been trying to solve this for 3 days without a result.

I have two tables both of which have a column which holds the same data. what I am trying to do is produce a query result that displays all the data in table 1 where there is a match in table 2. Along with this I want to sum the content of the result from table 1.

This is waht I have:

Table1: SequenceNo
Table2: PH001_Hist

Table1: FieldNames: SeqID, SeqText
The field "SeqID in Table1 contains a list of codes starting "SeqID0101 to SeqID0106" and SeqText contains a description of the SeqID* code.

Table2: Fieldnames: SeqID0101, SeqID0102, SeqID0103, SeqID0104, SeqID0105, SeqID0106
The fields in Table2 conatin either a "0" or a "1".

What I am trying to do is:

SELECT * FROM Table2 WHERE any of the fields contains "1" and sum each field to giving me a total for each field and then lookup the correct code description form Table1.

I have this part done with some help:

SELECT
SUM(SeqID0101 = 1),
SUM(SeqID0102 = 1),
SUM(SeqID0103 = 1),
SUM(SeqID0104 = 1),
SUM(SeqID0105 = 1),
SUM(SeqID0106 = 1) 
FROM PH001_Hist

Is this possible.

After so many hours of playing with this any help would be great.

Regards,

Blackbox

[barand]

Your time over the last 3 days would have been better spent in normalizing your data into relational tables rather than a spreadsheet.

+---------------+
| SequenceNo    |
+---------------+               +-----------------+
|   SeqID       | ------+       |  PH001_Hist     |
|   SeqText     |       |       +-----------------+
+---------------+       |       |   id            |
                        +------<|   SeqID         |
                                |   Value         |
                                +-----------------+

Then the query becomes

SELECT s.SeqText, SUM(h.Value) as total
FROM SequenceNo s
INNER JOIN PH001_Hist h USING (SeqID)

As it stands, you need a subquery with UNIONS of six separate queries AND then a JOIN of the subquery to the SequenceNo table