ASP.NET, SQL Server, WPF, C#, VB.NET

Hi folks, 
 
I have three tables: 
 
Table1: Contacts (PK = ContactID) 
 
Table2: WebComs (PK = WebComID) 
 
Table3: ContactWebComs (junction table - multi-field PK = ContactID, 
 
WebComID) 
 
I want to get a count of how many WebComs relate specifically to the 
 
current Contact. 
 
I am trying to use the following select statement but it keeps 
 
returning an error 3075 - syntax error, missing operator in query 
 
expression: 
 
strSQL = "SELECT * " & _ 
 
"FROM WebComs INNER JOIN ContactWebComs " & _ 
 
"ON [WebComs].[WebComID]=[ContactWebComs].[WebComID] " & 
 
_ 
 
"INNER JOIN Contacts " & _ 
 
"ON [ContactWebComs].[ContactID]=[Contacts].[ContactID] " 
 
& _ 
 
"WHERE [Contacts].[ContactID]=" & Me.[ContactID] & _ 
 
";" 
 
I'm new at this, and I can't really tell where I'm going wrong. 
 
I've tried varying the select statement by replacing the second Inner 
 
Join with Left Join, but this doesn't seem to resolve my problem. 
 
The first three lines of the query on their own work fine - but they 
 
seem to return the total number of WebComs in the junction table as 
 
opposed to just those which belong to the existing Contact.  For 
 
example, if my dummy data shows two WebComs relating to Contact 3, 
 
where one of these also relates to Contact 5, the first three lines 
 
will return 3 - but I want it to return only 2. 
 
TIA 
 
Bob

A simple 'Totals Query' will do it.  Just create a query based on 
 
ContactWebComs.  Add ContactID and WebComID to the grid.  View menu, Totals; 
 
in the Total row of the grid, leave Group By under ContactID and  choose 
 
Count under WebComID. 
 
-- 
 
Joan Wild 
 
Microsoft Access MVP 
 
Bob wrote:

Hi Joan. 
 
I have opened a query window and added my three tables to the top.  In 
 
the grid section below, I have added one column which refers to 
 
WebComID from my WebComs table - I have set the totals row below this 
 
to read "Count".  I also have a second column which refers to ContactID 
 
in my Contacts table. 
 
Is the second column supposed to refer to ContactID in the junction 
 
table (ie ContactWebComs) instead? 
 
Also, how do I use the results of this query in my vba code?  Do I use 
 
the sql string to open a new recordset, and go from there? 
 
Just in case its relevant, I should point out that I may not have been 
 
entirely clear in my first post.  The WebComs table does not contain a 
 
ContactID field.  However, ContactWebComs acts as a junction table 
 
between the WebComs and Contacts tables. 
 
I want the total so that I can determine whether or not the form should 
 
show vertical toolbars.  I don't require the total as part of the a 
 
report, or to be displayed to the user. 
 
Just out of curiosity, what was wrong my original inner join?  I take 
 
it that the count query is far more efficient so no problem there.  I'm 
 
just wondering what was wrong with the actual syntax of my original sql 
 
string. 
 
The string for the query that I have just created is as follows: 
 
SELECT Count(WebComs.WebComID) AS CountOfWebComID, Contacts.ContactID 
 
FROM WebComs INNER JOIN (Contacts INNER JOIN ContactWebComs ON 
 
Contacts.ContactID = ContactWebComs.ContactID) ON WebComs.WebComID = 
 
ContactWebComs.WebComID 
 
GROUP BY Contacts.ContactID; 
 
This seems to still involve multiple joins, but the syntax is 
 
completely different (I think) to the instructions that I have been 
 
trying to follow and which are outlined here 
 
http://uk.builder.com/architecture/db/0,39026552,20282916,00.htm: 
 
"When joining more than two tables, use the following syntax: 
 
FROM table1 join table2 
 
ON table1.primarykey = table2.foreignkey join table3 
 
ON table2.primarykey = table3.foreignkey." 
 
Regards 
 
Bob 
 
Joan Wild wrote: