INNER JOIN questions

D

Darhl Thomason

DBType = Access 2003
Tables = tblType, tblOwners, tblStoreData
tblType has TypeID, TypeName
tblOwners has OwnerID, OwnerName
tblStoreData has TypeID, OwnerID, plus too many others to list, they're not
important here

I have an inner join that's giving me fits. It is:

strSQL = "SELECT * FROM (tblStoreData " _
& "INNER JOIN tblOwners ON tblStoreData.OwnerID = tblOwners.OwnerID)
" _
& "INNER JOIN tblType ON tblStoreData.TypeID = tblType.TypeID "

If I take the 2nd INNER JOIN out, it works fine, if I take out the 1st INNER
JOIN it breaks, so it's obviously the 2nd INNER JOIN that's the problem.
When I run the report that calls this SQL statement, it tells me "The
specified filed 'TypeID' could refer to more than one table listed in the
FROM clause of your SQL statement."

Does anyone have any ideas for me?

Thanks!

Darhl
 
M

Marshall Barton

Darhl said:
DBType = Access 2003
Tables = tblType, tblOwners, tblStoreData
tblType has TypeID, TypeName
tblOwners has OwnerID, OwnerName
tblStoreData has TypeID, OwnerID, plus too many others to list, they're not
important here

I have an inner join that's giving me fits. It is:

strSQL = "SELECT * FROM (tblStoreData " _
& "INNER JOIN tblOwners ON tblStoreData.OwnerID = tblOwners.OwnerID)
" _
& "INNER JOIN tblType ON tblStoreData.TypeID = tblType.TypeID "

If I take the 2nd INNER JOIN out, it works fine, if I take out the 1st INNER
JOIN it breaks, so it's obviously the 2nd INNER JOIN that's the problem.
When I run the report that calls this SQL statement, it tells me "The
specified filed 'TypeID' could refer to more than one table listed in the
FROM clause of your SQL statement."
Click to expand...


The report just uses the field name TypeID, but that name is
used in two tables so Access can not figure out whic one you
want. Since you are using it as a linking field, it doesn't
matter wich table it comes from so just modify the query's
field list to remove the duplicate fields:


"SELECT tblType.TypeName,
tblOwners.OwnerName,
tblStoreData .*
FROM . . .
 
D

Darhl Thomason

Hi Marshall,

Thanks for the suggestion, I'll take a look at that. Here's some more
information that may be pertinent... This query is built into a report, the
report is called by a form. The form has two option groups to apply filters.
It is only when the "type" option group is filtered that it gives me fits.
If the "type" filter is not applied then it works fine, it even works fine
when the other filter "include" is applied. I will copy the forms apply
filter sub.

Thanks!

d


Private Sub Apply_Filter()
strFilter = Null
Me.tglNonTraditional.Enabled = False
Me.tglUpcomingStores.Enabled = False
If Me.cboOwner <> "" Or Not IsNull(Me.cboOwner) Then
strFilter = "OwnerID = " & Me.cboOwner
End If
Select Case Me.ogType
Case 1
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
Case 2
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "TypeID = 1"
Case 3
Me.tglNonTraditional.Enabled = True
Me.ogReports = 4
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "TypeID <> 1"
End Select
Select Case Me.ogInclude
Case 1
Me.tglUpcomingStores.Enabled = False
If Me.ogType <> 3 Then Me.ogReports = 1 Else Me.ogReports = 4
Case 2
Me.tglUpcomingStores.Enabled = False
If Me.ogType <> 3 Then Me.ogReports = 1 Else Me.ogReports = 4
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "StoreOpen = True"
Case 3
Me.tglUpcomingStores.Enabled = True
Me.ogReports = 5
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "StoreOpen = False"
End Select
If IsNull(strFilter) Then
Me.FilterOn = False
Else
Me.Filter = strFilter
Me.FilterOn = True
End If

If IsNull(Me.cboOwner) Then
Me.cmdClear.Enabled = False
Else
Me.cmdClear.Enabled = (Me.cboOwner <> "")
End If
 
M

Marshall Barton

AFAICS, the code you posted would run into the issue my
earlier answer tried to address. I think you should try the
change and post back with a more specific question if
there's still a problem.
 
D

Darhl Thomason

Pardon my ignorance...what is AFAICS? I will also try your idea.

d
Marshall Barton said:
AFAICS, the code you posted would run into the issue my
earlier answer tried to address. I think you should try the
change and post back with a more specific question if
there's still a problem.
--
Marsh
MVP [MS Access]


Darhl said:
Thanks for the suggestion, I'll take a look at that. Here's some more
information that may be pertinent... This query is built into a report,
the
report is called by a form. The form has two option groups to apply
filters.
It is only when the "type" option group is filtered that it gives me fits.
If the "type" filter is not applied then it works fine, it even works fine
when the other filter "include" is applied. I will copy the forms apply
filter sub.


Private Sub Apply_Filter()
strFilter = Null
Me.tglNonTraditional.Enabled = False
Me.tglUpcomingStores.Enabled = False
If Me.cboOwner <> "" Or Not IsNull(Me.cboOwner) Then
strFilter = "OwnerID = " & Me.cboOwner
End If
Select Case Me.ogType
Case 1
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
Case 2
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "TypeID = 1"
Case 3
Me.tglNonTraditional.Enabled = True
Me.ogReports = 4
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "TypeID <> 1"
End Select
Select Case Me.ogInclude
Case 1
Me.tglUpcomingStores.Enabled = False
If Me.ogType <> 3 Then Me.ogReports = 1 Else Me.ogReports = 4
Case 2
Me.tglUpcomingStores.Enabled = False
If Me.ogType <> 3 Then Me.ogReports = 1 Else Me.ogReports = 4
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "StoreOpen = True"
Case 3
Me.tglUpcomingStores.Enabled = True
Me.ogReports = 5
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "StoreOpen = False"
End Select
If IsNull(strFilter) Then
Me.FilterOn = False
Else
Me.Filter = strFilter
Me.FilterOn = True
End If

If IsNull(Me.cboOwner) Then
Me.cmdClear.Enabled = False
Else
Me.cmdClear.Enabled = (Me.cboOwner <> "")
End If
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple INNER JOIN statements 2
Inner Join problem 0
Data from multiple tables on one report 4
QueryDef Help (Re-posted) 12
Nested JOIN clauses - order of precedence (Access 2007) 2
How do you ignore entire tables in an Inner Join Query? 16
Wildcard on an "INNER JOIN" 5
Need help creating temporary table from filter 11

Top