What's wrong with this multiple join?

[Bob]

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

 

[Joan Wild]

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.

 

[Bob]

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

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

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

 

[Joan Wild]

Perhaps I'm misunderstanding what you want, but I thought it was show
Contact ID and a count of WebComs for that ID.
i.e.
Contact 3 2 webcoms
Contact 5 1 webcom
etc.
Since the ContactWebComs contains the two field of interest, that is the
only table you need in your query, built as I outlined earlier.

If I have misunderstood your intent, please provide sample data and what you
want in the outcome of the query.

What do you want to do with the results in code? I don't understand the
relationship between the total and whether the form should show vertical
toolbars (what's that)? Also you say you don't want this shown to the user.

Perhaps explain what you want to do, rather than how you think it should be
done.


--
Joan Wild
Microsoft Access MVP

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

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

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

 

[Bob]

Hi Joan,

You have identified my objective correctly except that I want to show
the count for a single specified contactID (as opposed to a count for
each contactID in the database). What I want to do is use the number
of records returned as part of If ... Then statement to determine
whether or not vertical scrollbars should be shown on my form. If the
number of records is greater than a certain number, I want the form to
set the scrollbars to true, otherwise I don't want the scrollbars to
show up at all.

The vba for the scrollbars part works fine. After tinkering with your
original suggestion, I now have the following select statement for my
query:

SELECT Count(WebComs.WebComID)
FROM WebComs INNER JOIN (Contacts INNER JOIN ContactWebComs ON
Contacts.ContactID=ContactWebComs.ContactID) ON
WebComs.WebComID=ContactWebComs.WebComID
WHERE Contacts.ContactID=Me.ContactID;

The above code returns the number of WebComs that relate to a specified
ContactID. It appears to work in the query window - eg I double-click
on the query name, Access asks for the contactID number, I insert 3,
access shows a single column datasheet with "2" as the number of
relevant records. What I would like to now know is how to run this
query via vba so that the number of relevant records can be stored an a
variable.



Regards
Bob

Perhaps I'm misunderstanding what you want, but I thought it was show
Contact ID and a count of WebComs for that ID.
i.e.
Contact 3 2 webcoms
Contact 5 1 webcom
etc.
Since the ContactWebComs contains the two field of interest, that is the
only table you need in your query, built as I outlined earlier.

If I have misunderstood your intent, please provide sample data and what you
want in the outcome of the query.

What do you want to do with the results in code? I don't understand the
relationship between the total and whether the form should show vertical
toolbars (what's that)? Also you say you don't want this shown to the user.

Perhaps explain what you want to do, rather than how you think it should be
done.


--
Joan Wild
Microsoft Access MVP

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

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 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

 

[Joan Wild]

If you set the vertical scrollbar to true, Access will only display it
if/when it is needed.

You do not need any other tables other than WebComs:

SELECT Count(WebComs.WebComID)
FROM WebComs
WHERE (((WebComs.ContactID)=Forms!frmName.ContactID;

If you want to set the SQL statement in code

"SELECT Count(WebComs.WebComID) FROM WebComs WHERE WebComs.ContactID = " &
Me.ContactID

 

[Bob]

Hi Joan,

I have a continuous subform with scrollbars set to vertical only. When
the form is initially loaded, I also have allowadditions set to false.
The size of the subform is sufficient to allow six rows/records to be
visible at all times, but the scrollbars become visible as soon as I
set allowadditions to true (ie even if there are less than six records
in the table). Unless I am adding the seventh record, I don't want the
scrollbars to be visible when I set allowadditions to true.

In any event, I've managed to work this out for myself. Thanks for
pointing me in the right direction.


Regards
Bob