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" <vanderghast@com> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:e8c%(E-Mail Removed)...
> > 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
> >
> >
> >
>
|
Similar Threads
