Total Count Of Sources Query

[stoffer]

Hi everyone,
Managed to build an Access database with switchboard, forms, reports & queries but I'm left with two annoying problems:

1) I have two columns in my main table called "SOURCE" and "SOURCE 2". They both take their data from a table called "SOURCE". I run a weekly query so that the jefes (bosses) can see the fruits of their advertising so I get the the advertising source and the number of times it was used by a client, grouped according to number of times used. My first problem is knowing how to produce a TOTAL at the end of the report of ALL the sources as well as the individual count.
EG: GOOGLE 24
YAHOO 12
MSN 2
TOTAL 38
In design view I have the following:

FIELD: CONTACTID SOURCE WHENREGISTERED
TABLE: CONTACTS CONTACTS CONTACTS
TOTAL: COUNT GROUP BY WHERE
SHOW: Y Y N
CRITERIA: Between [Enter the first date:] And [Enter the last date:]

I haven't used the "SOURCE 2" column due to problem nº2:

2) How do I combine "SOURCE" and "SOURCE 2" columns in my main table in a query? Is it possible? EG on my form a client may have contacted us via GOOGLE the 1st time and then by YAHOO the 2nd time. I want to reflect that in the query, which at the end of the day uses the same table ("SOURCE") to get it's values and then store them in the main "CONTACTS" table.
Hope this isn't too complicated and that I'm explaining myself well. Well done to all those experts whose comments to others have already helped me make some great tweaks, especially with mail merging. Thanks. Chris.

 

[KenHigg]

After just a quick peek at your problem, I'd say you need to do some kind of subordinate 'union' query to get the data together...

 

[stoffer]

Thanks Ken, although I didn't realise this first post was actually submitted (webpage told me I needed to login again!). Remember that I'm a newbie to Access so you need to explain in basic, basic terms! I will look up "subordinate 'union' query" and see what I find. Thanks.

 

[KenHigg]

A union query is quite typical and there should be resources avail to help on them. But by subordinate I simple meant that you may need to create several queries where one may be the source for the next and where one of the first of the queries may need to be a union query...

 

[neileg]

Using a union query is often a pointer to a design flaw, and this case it is. You should not have 2 source fields. You have a many to many relationship between your main table and Source. To implement this you need another table which will hold the primary key of the client and the primary key of the source. There will be one record for each source used for each client. This means that you can support as many sources as you like (not just two per client) and counting is made very simple.

I'm not saying that Ken's advice is wrong, but it is a workaround to the problem caused by the design.