Link Conditionally so records with Null field included? 1

[hondurab]

I have a query that pulls from a couple of tables that are linked based on a Code field. As it turns out, the Code field is sometimes null in the Main table, but I'd like to see the record anyway, even if what I get from the link is not there.

I thought I could change the Join Properties in the design view to pull all from table Main and only matching in the Code lookup table. But I get an Ambiguous Join SQL error.

Can anyone help me get around this problem?

Thank you

 

[tlbroadbent]

Can you post the SQL statement for the query? It will be easier to determine the problem if we can see the SQL. Do you have other tables JOINed in the query? SQL will often reject a query with different kinds of JOINs unless you group JOINs using parentheses.

Example:

SELECT
m.code, m.col2, c.col3, c.col4, d.col5
FROM (Main as m
LEFT JOIN CodeLookup AS c
ON m.code=c.code)
INNER Detail as d
ON c.colX=d.ColZ Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[hondurab]

Ok, the Resolution-Code is the field that is sometimes null in TT:TroubleTicket.

This is as-is:
SELECT [TT:TroubleTicket].[Ticket-Number], [TT:TroubleTicket].[Resolution-Code], [TT:TroubleTicket].[Status-History-Closed-TIME]
FROM [Ticket Source Lookup], ([TT:TroubleTicket]
INNER JOIN [Maintenance Group - Qry] ON [TT:TroubleTicket].[Primary-Department] = [Maintenance Group - Qry].Group) INNER JOIN [Resolution Code Lookup] ON [TT:TroubleTicket].[Resolution-Code] = [Resolution Code Lookup].[Resolution Code]
WHERE ((([TT:TroubleTicket].[Status-History-Closed-TIME])>=(DateValue([Forms]![Main]![Start Date])) And ([TT:TroubleTicket].[Status-History-Closed-TIME])<DateAdd(&quot;d&quot;,1,(DateValue([Forms]![Main]![Stop Date])))))
GROUP BY [TT:TroubleTicket].[Ticket-Number], [TT:TroubleTicket].[Resolution-Code], [Maintenance Group - Qry].MaintOrg, [TT:TroubleTicket].[Status-History-Closed-TIME]
HAVING ((([Maintenance Group - Qry].MaintOrg)=&quot;Transport&quot);

With Left Join:
SELECT [TT:TroubleTicket].[Ticket-Number], [TT:TroubleTicket].[Resolution-Code], [TT:TroubleTicket].[Status-History-Closed-TIME]
FROM [Ticket Source Lookup], ([TT:TroubleTicket]
INNER JOIN [Maintenance Group - Qry] ON [TT:TroubleTicket].[Primary-Department] = [Maintenance Group - Qry].Group) LEFT JOIN [Resolution Code Lookup] ON [TT:TroubleTicket].[Resolution-Code] = [Resolution Code Lookup].[Resolution Code]
WHERE ((([TT:TroubleTicket].[Status-History-Closed-TIME])>=(DateValue([Forms]![Main]![Start Date])) And ([TT:TroubleTicket].[Status-History-Closed-TIME])<DateAdd(&quot;d&quot;,1,(DateValue([Forms]![Main]![Stop Date])))))
GROUP BY [TT:TroubleTicket].[Ticket-Number], [TT:TroubleTicket].[Resolution-Code], [TT:TroubleTicket].[Status-History-Closed-TIME], [Maintenance Group - Qry].MaintOrg
HAVING ((([Maintenance Group - Qry].MaintOrg)=&quot;Transport&quot);

I am new to SQL, so go easy. Thanks.

 

[tlbroadbent]

I have a some questions.

1) Why do the LEFT join if not selecting data from the [Resolution Code Lookup] table?
2) Why is the table [Ticket Source Lookup] included in the FROM clause. It is not JOINED to any tables and you select no columns from the table?
3) Why not include MaintOrg in the select list if you are grouping by that column? Or why group by that column if only selecting one value?

The following should work.

SELECT
tt.[Ticket-Number],
tt.[Resolution-Code],
tt.[Status-History-Closed-TIME]

FROM ([TT:TroubleTicket] As tt
INNER JOIN [Maintenance Group - Qry] As mg
ON tt.[Primary-Department]=mg.Group)
LEFT JOIN [Resolution Code Lookup] As rc
ON tt.[Resolution-Code]=rc.[Resolution Code]

WHERE
tt.[Status-History-Closed-TIME]>=
DateValue([Forms]![Main]![Start Date])
And tt.[Status-History-Closed-TIME]<
DateAdd(&quot;d&quot;,1,(DateValue([Forms]![Main]![Stop Date])
And mg.MaintOrg=&quot;Transport&quot;

GROUP BY
tt.[Ticket-Number],
tt.[Resolution-Code],
tt.[Status-History-Closed-TIME],
mg.MaintOrg;

The following should also work.

SELECT
tt.[Ticket-Number],
tt.[Resolution-Code],
tt.[Status-History-Closed-TIME]

FROM ([TT:TroubleTicket] As tt
INNER JOIN [Maintenance Group - Qry] As mg
ON tt.[Primary-Department]=mg.Group)

WHERE
tt.[Status-History-Closed-TIME]>=
DateValue([Forms]![Main]![Start Date])
And tt.[Status-History-Closed-TIME]<
DateAdd(&quot;d&quot;,1,(DateValue([Forms]![Main]![Stop Date])
And mg.MaintOrg=&quot;Transport&quot;

GROUP BY
tt.[Ticket-Number],
tt.[Resolution-Code],
tt.[Status-History-Closed-TIME];
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[hondurab]

Thank you! I tried the first of the two and it worked.

To answer your questions, I was trying to troubleshoot my query, so made a new one with just the basics, unjoining all the tables and starting with the one giving me trouble. What I was working on was just the troublesome part of a larger picture.
So...:
1) Why do the LEFT join if not selecting data from the [Resolution Code Lookup] table? Real query actually does pull data from this table.

2) Why is the table [Ticket Source Lookup] included in the FROM clause. It is not JOINED to any tables and you select no columns from the table? I unjoined this table for testing purposes, but left it visible in Design View.

3) Why not include MaintOrg in the select list if you are grouping by that column? Or why group by that column if only selecting one value? Um, my only defense here is that I was using the Design View, so the SQL was generated by Access. Not that this is really a defense.

Sorry for the confusion!