Hello Vanderghast
Many thanks for your reply.
Perhaps I should provide a little more info.
I had this, which works fine:
Set rs = CurrentDb.OpenRecordset( _
"SELECT staffs.*, [tax tables].*, [NI tables 2009].*, [SSP
tables].*, [SMP tables].* " & _
"FROM (([tax tables] RIGHT JOIN ([NI tables 2009] RIGHT JOIN staffs
" & _
"ON [NI tables 2009].[NI letter] = staffs.[NI code]) " & _
"ON [tax tables].set = staffs.taxset) LEFT JOIN [SSP tables] " & _
"ON staffs.[ssp set] = [SSP tables].[SSP set]) LEFT JOIN [SMP
tables] " & _
"ON staffs.[smp set] = [SMP tables].[SMP set];")
The problem is that I now need to pick up the data in [NI tables 2009] via a
join to the table [months] i.e. [NI tables 2010].niSet = months.nicset. This
is in addition to the join [NI tables 2009].[NI letter] = staffs.[NI code],
which is in the original recordset above. I still need the other fields from
[SSP tables], [SMP tables] and [tax tables] from the recordset above, but
thought it best to define a new recordset (rsNIC) for the [NI tables 2009]
values that are needed, and it's this new recordset that I'm now struggling
with. Would it be feasible/recommended instead to amend the original
recordset above, so that it picks up the the data in [NI tables 2009] using
the (new) link to the table [months]?
Hope this all makes sense!
Thanks again for your help.
Les
vanderghast said:
The part:
([NI tables 2010] RIGHT JOIN months" & _
" ON [NI tables 2010].[NI letter] = staffs.[NI code])"
sounds strange: you define a join on two tables... but ON another table, I
mean, why it is not:
([NI tables 2010] RIGHT JOIN months" & _
" ON [NI tables 2010].niSet = months.nicset)"
and then, introduces table staffs in another join:
...
" FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" & _
" ON [NI tables 2010].niSet = months.nicset)" & _
" ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
...
Such join is ambiguous, though, and cannot be used as it is, with Jet. Make
a first query with:
SELECT * FROM [NI tables 2010] RIGHT JOIN months ON [NI tables 2010].niSet
= months.nicset
and use that saved query:
....
" FROM staffs RIGHT JOIN savedQuery ON savedQuery.[NI letter] =
staffs.[NI code] " & _
....
if that is really what you want.
Vanderghast, Access MVP
PayeDoc said:
Hello All
I had some help with a recordset problem recently (many thanks Stefan and
Daryl!), but now have another. I just get "Error 3135 Syntax error in JOIN
operation" with the following:
Set rsNIC = CurrentDb.OpenRecordset( _
"SELECT staffs.*, months.*, [NI tables 2010].*" & _
" FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" & _
" ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
" ON [NI tables 2010].niSet = months.nicset)" & _
" WHERE (((months.[month name])='" & [Forms]![frm x main]![month
name] & "'));")
If anyone can help with this I'd be really grateful - as ever!!
Many thanks
Leslie Isacas
