Counting Values in a Range of Columns

[Guest]

I have two tables
Table A has two columns. The first column which is the primary key contains
a three letter abbreviation for a type of contact, the second column is a
definition of the abbreviation.

Table B has 7 columns. They consist of
customer_ID 1stcontact 2ndcontact 3rdcontact ordered paid
returned
The values listed in the 1stcontact, 2ndcontact and 3rdcontact columns are
the three letter abbreviations from Table A.

I want to run a query that lists all the values of column one of Table A and
counts how many resulted in a order, how many paid, and how many returned
from Table B. It does not matter whether the value was the 1st contact, 2nd
contact, or 3rd contact.

 

[Guest]

I started to build the table and queries but got lost. Please post an
example of what you expect the output to look like. You want a complete list
from Table A and sums from the ordered, paid, returned fields.

 

[Guest]

This is a sample of the two tables and the output that I am trying to get.
Table A
ABB DEF
LTR Letter
PCD Postcard
FAX Fax
TEL Telephone
EMA Email

Table B
CUSTID 1STCONTACT 2NDCONTACT 3RDCONTACT ORDERED PAID RETURNED
1234 LTR Y
1345 PCD FAX EMA Y
Y Y
4567 FAX TEL
Y Y
4568 TEL
5671 LTR PCD
Y Y Y

Desired Output
ABB ORDERED PAID RETURNED
LTR 2 1 1
PCD 2 2 2
FAX 2 2 1
TEL 1 1 0
EMA 0 0 0

It is not necessary for 0 to appear when there is nothing to count but for
example purposes, I put them in. Thanks.

 

[Pat Hartman\(MVP\)]

Sorry but I can't make heads or tails out of your structure. The alignment
problems don't help either. Does the 1stContact go with Ordered and
2ndContact with Paid and 3rdContact with Returned? This table SHOULD be
restructured to be:

CustomerID
ContactCode
ContactType

That correctly normalized table will let you do simple count queries or
crosstabs to tabulate your data. Your given structure will either require
code or a number of queries.

 

[Guest]

One customer may be contacted three times in the course of getting one order.
I am trying to track what type of contact results in the most orders, paids,
and/or returns. You may be right, I might have to make several queries.

 

[Pat Hartman\(MVP\)]

But you didn't answer my question. Are the contact columns in any way
related to the status columns? When you made your example table, how did
you decide which contact column went with which status column?

This table really needs to be normalized if you want to get any reasonable
information out of it.

 

[Guest]

No, the contact columns are not related in any way to the status columns.
You could have someone that has no contact information.

 

[Pat Hartman\(MVP\)]

Sorry but you have not described the transformation between the stored data
and what you want as a report.