Complex left join within sql statement

T

tigger

Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with several rules
and criteria. The inner joins work perfectly but I am trying to do a left
join to show all records and I know it is simply a syntax problem - can't see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or tblNCRs if this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name], tblNCRs.[NCR ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
 
A

Alex Dybenko

hi,
perhaps something like this. But have you tried to use a query designer? you
can easy build such query there.You can also make one more subquery on
tblAuditor and tblAuditDetails, and then left join other table to it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
T

tigger

Hi Alex,

Thanks for your help.

I've tried using the query designer and I get an error message that says
ambiguous outer joins, so I'm guessing it's an order thing and I've got
something in the wrong order.

When I use your code I get error message 3296: Join expression not supported.

The aim is to show NCRs grouped by Audit ID, whether they have NCRActions or
not, and the name of the auditor (from tblAuditor) who performed the audit.
As I mentioned, the inner joins work perfectly but only show those NCRs with
NCRActions attached.

I'm a bit stuck!

Thanks
Amanda

Alex Dybenko said:
hi,
perhaps something like this. But have you tried to use a query designer? you
can easy build such query there.You can also make one more subquery on
tblAuditor and tblAuditDetails, and then left join other table to it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with several
rules
and criteria. The inner joins work perfectly but I am trying to do a left
join to show all records and I know it is simply a syntax problem - can't
see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or tblNCRs if this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name], tblNCRs.[NCR
ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
perhaps you have different field types?
ok, try to send me your DB with there 3 tables, this will be faster
alexdyb (~at~) hotmail (~dot~) com

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

Thanks for your help.

I've tried using the query designer and I get an error message that says
ambiguous outer joins, so I'm guessing it's an order thing and I've got
something in the wrong order.

When I use your code I get error message 3296: Join expression not
supported.

The aim is to show NCRs grouped by Audit ID, whether they have NCRActions
or
not, and the name of the auditor (from tblAuditor) who performed the
audit.
As I mentioned, the inner joins work perfectly but only show those NCRs
with
NCRActions attached.

I'm a bit stuck!

Thanks
Amanda

Alex Dybenko said:
hi,
perhaps something like this. But have you tried to use a query designer?
you
can easy build such query there.You can also make one more subquery on
tblAuditor and tblAuditDetails, and then left join other table to it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit
ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with several
rules
and criteria. The inner joins work perfectly but I am trying to do a
left
join to show all records and I know it is simply a syntax problem -
can't
see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or tblNCRs if
this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions
relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name], tblNCRs.[NCR
ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed
action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON
tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 
T

tigger

Hi Alex,

Thanks for the offer but I can't send it out as it is a live database with
confidential information in it. I'm working on some additional functionality.

All the IDs are Number(long integer) except Auditor ID, which is a long
integer autonumber.

Alex Dybenko said:
Hi,
perhaps you have different field types?
ok, try to send me your DB with there 3 tables, this will be faster
alexdyb (~at~) hotmail (~dot~) com

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

Thanks for your help.

I've tried using the query designer and I get an error message that says
ambiguous outer joins, so I'm guessing it's an order thing and I've got
something in the wrong order.

When I use your code I get error message 3296: Join expression not
supported.

The aim is to show NCRs grouped by Audit ID, whether they have NCRActions
or
not, and the name of the auditor (from tblAuditor) who performed the
audit.
As I mentioned, the inner joins work perfectly but only show those NCRs
with
NCRActions attached.

I'm a bit stuck!

Thanks
Amanda

Alex Dybenko said:
hi,
perhaps something like this. But have you tried to use a query designer?
you
can easy build such query there.You can also make one more subquery on
tblAuditor and tblAuditDetails, and then left join other table to it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit
ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with several
rules
and criteria. The inner joins work perfectly but I am trying to do a
left
join to show all records and I know it is simply a syntax problem -
can't
see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or tblNCRs if
this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions
relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name], tblNCRs.[NCR
ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed
action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON
tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 
A

Alex Dybenko

then you can delete all data (make a copy first and compact then), I need
table structure also

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

Thanks for the offer but I can't send it out as it is a live database with
confidential information in it. I'm working on some additional
functionality.

All the IDs are Number(long integer) except Auditor ID, which is a long
integer autonumber.

Alex Dybenko said:
Hi,
perhaps you have different field types?
ok, try to send me your DB with there 3 tables, this will be faster
alexdyb (~at~) hotmail (~dot~) com

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

Thanks for your help.

I've tried using the query designer and I get an error message that
says
ambiguous outer joins, so I'm guessing it's an order thing and I've got
something in the wrong order.

When I use your code I get error message 3296: Join expression not
supported.

The aim is to show NCRs grouped by Audit ID, whether they have
NCRActions
or
not, and the name of the auditor (from tblAuditor) who performed the
audit.
As I mentioned, the inner joins work perfectly but only show those NCRs
with
NCRActions attached.

I'm a bit stuck!

Thanks
Amanda

:

hi,
perhaps something like this. But have you tried to use a query
designer?
you
can easy build such query there.You can also make one more subquery on
tblAuditor and tblAuditDetails, and then left join other table to it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit
ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with
several
rules
and criteria. The inner joins work perfectly but I am trying to do a
left
join to show all records and I know it is simply a syntax problem -
can't
see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or tblNCRs
if
this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions
relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name],
tblNCRs.[NCR
ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed
action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON
tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 
T

tigger

Hi Alex,

I've sent the table structure over to you, do you think you would be able to
come back with a solution?

Thanks

Alex Dybenko said:
then you can delete all data (make a copy first and compact then), I need
table structure also

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

Thanks for the offer but I can't send it out as it is a live database with
confidential information in it. I'm working on some additional
functionality.

All the IDs are Number(long integer) except Auditor ID, which is a long
integer autonumber.

Alex Dybenko said:
Hi,
perhaps you have different field types?
ok, try to send me your DB with there 3 tables, this will be faster
alexdyb (~at~) hotmail (~dot~) com

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi Alex,

Thanks for your help.

I've tried using the query designer and I get an error message that
says
ambiguous outer joins, so I'm guessing it's an order thing and I've got
something in the wrong order.

When I use your code I get error message 3296: Join expression not
supported.

The aim is to show NCRs grouped by Audit ID, whether they have
NCRActions
or
not, and the name of the auditor (from tblAuditor) who performed the
audit.
As I mentioned, the inner joins work perfectly but only show those NCRs
with
NCRActions attached.

I'm a bit stuck!

Thanks
Amanda

:

hi,
perhaps something like this. But have you tried to use a query
designer?
you
can easy build such query there.You can also make one more subquery on
tblAuditor and tblAuditDetails, and then left join other table to it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit
ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with
several
rules
and criteria. The inner joins work perfectly but I am trying to do a
left
join to show all records and I know it is simply a syntax problem -
can't
see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or tblNCRs
if
this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions
relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name],
tblNCRs.[NCR
ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed
action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON
tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
did not get it, pls make sure that you compact the file and zip it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

I've sent the table structure over to you, do you think you would be able
to
come back with a solution?

Thanks

Alex Dybenko said:
then you can delete all data (make a copy first and compact then), I need
table structure also

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

Thanks for the offer but I can't send it out as it is a live database
with
confidential information in it. I'm working on some additional
functionality.

All the IDs are Number(long integer) except Auditor ID, which is a long
integer autonumber.

:

Hi,
perhaps you have different field types?
ok, try to send me your DB with there 3 tables, this will be faster
alexdyb (~at~) hotmail (~dot~) com

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi Alex,

Thanks for your help.

I've tried using the query designer and I get an error message that
says
ambiguous outer joins, so I'm guessing it's an order thing and I've
got
something in the wrong order.

When I use your code I get error message 3296: Join expression not
supported.

The aim is to show NCRs grouped by Audit ID, whether they have
NCRActions
or
not, and the name of the auditor (from tblAuditor) who performed the
audit.
As I mentioned, the inner joins work perfectly but only show those
NCRs
with
NCRActions attached.

I'm a bit stuck!

Thanks
Amanda

:

hi,
perhaps something like this. But have you tried to use a query
designer?
you
can easy build such query there.You can also make one more subquery
on
tblAuditor and tblAuditDetails, and then left join other table to
it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit
ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with
several
rules
and criteria. The inner joins work perfectly but I am trying to
do a
left
join to show all records and I know it is simply a syntax
problem -
can't
see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or
tblNCRs
if
this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions
relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name],
tblNCRs.[NCR
ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline
target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed
action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON
tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 
T

tigger

Hi Alex,

Don't worry - I've sorted it out!

I cheated a bit and used a query within another query to force the joins -
not very tidy but it works!

Thanks for your help.

Alex Dybenko said:
Hi,
did not get it, pls make sure that you compact the file and zip it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tigger said:
Hi Alex,

I've sent the table structure over to you, do you think you would be able
to
come back with a solution?

Thanks

Alex Dybenko said:
then you can delete all data (make a copy first and compact then), I need
table structure also

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi Alex,

Thanks for the offer but I can't send it out as it is a live database
with
confidential information in it. I'm working on some additional
functionality.

All the IDs are Number(long integer) except Auditor ID, which is a long
integer autonumber.

:

Hi,
perhaps you have different field types?
ok, try to send me your DB with there 3 tables, this will be faster
alexdyb (~at~) hotmail (~dot~) com

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi Alex,

Thanks for your help.

I've tried using the query designer and I get an error message that
says
ambiguous outer joins, so I'm guessing it's an order thing and I've
got
something in the wrong order.

When I use your code I get error message 3296: Join expression not
supported.

The aim is to show NCRs grouped by Audit ID, whether they have
NCRActions
or
not, and the name of the auditor (from tblAuditor) who performed the
audit.
As I mentioned, the inner joins work perfectly but only show those
NCRs
with
NCRActions attached.

I'm a bit stuck!

Thanks
Amanda

:

hi,
perhaps something like this. But have you tried to use a query
designer?
you
can easy build such query there.You can also make one more subquery
on
tblAuditor and tblAuditDetails, and then left join other table to
it

FROM (tblAuditor INNER JOIN
(tblAuditDetails INNER JOIN tblNCRs ON tblAuditDetails.[Audit
ID]=tblNCRs.[Audit ID]) ON tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor)
LEFT JOIN tblNCRActions ON tblAuditDetails.[Audit
ID]=tblNCRActions.[Audit
ID] AND tblNCRs.[NCR ID]=tblNCRActions.[NCR ID]

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi there, can anyone help?

I apologise in advance for hurting your eyes ...

I am trying to create a query which joins several tables with
several
rules
and criteria. The inner joins work perfectly but I am trying to
do a
left
join to show all records and I know it is simply a syntax
problem -
can't
see
where to put my brackets :)

I need to:

Left join tblNCRActions to tblNCRs on NCR ID
then
Left join tblNCRActions to tblAuditDetails by Audit ID (or
tblNCRs
if
this
is better?)
Inner join tblAuditDetails to tblNCRs by Audit ID
then
Inner join tblAuditor to tblAuditDetails by Auditor ID

So that I can show all audits, corresponding NCRs and any actions
relating
to the NCRs in a report.

Code:


SELECT tblAuditDetails.[Audit ID] AS [tblAuditDetails_Audit ID],
tblAuditDetails.[Audit title], tblAuditor.[Auditor name],
tblNCRs.[NCR
ID],
tblNCRs.NCRTitle, tblNCRs.[Agreed date], tblNCRs.[Baseline
target],
tblNCRs.[Forecast target], tblNCRs.Status, tblNCRActions.[Agreed
action],
tblNCRActions.Actionee, tblNCRActions.Status
FROM (tblAuditor INNER JOIN (tblAuditDetails INNER JOIN tblNCRs
ON tblAuditDetails.[Audit ID]=tblNCRs.[Audit ID]) ON
tblAuditor.[Auditor
ID]=tblAuditDetails.Auditor) LEFT JOIN tblNCRActions
ON (tblAuditDetails.[Audit ID]=tblNCRActions.[Audit ID])
AND (tblNCRs.[NCR ID]=tblNCRActions.[NCR ID])
WHERE tblAuditDetails.[Audit ID]= " & varAuditRef & "

Thanks for the help!
Click to expand...
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

SQL Upsizing Problem with Left Join Queries 19
SQL statement for left outer join for ranges in the same file 0

Top