Incomplete capture in query

[Larnu]

Hi All,

I have a query which exports to a excel file afterwards, however, it's not capturing all the data I need. I can see why it's not working as intended, but can't work out how to resolve it.

My query is at the bottom, and I think the problem is the "(([tbl Master].ID)=[tblRequest].[Cost Centre]))" statement, as this means that it'll never pick up a Cost Centre starting with "Z", as they don't exist in [tbl Master] (they're exceptions to the rule basically).

What I need to do is only parse the statement "(([tbl Master].ID)=[tblRequest].[Cost Centre]))" if the Cost Centre doesn't start with "Z", if it does, then it'll find it in the table, as there's validation on the point of entry that don't start with it.

It anyone able to help at all?

Many thanks for help in advance.

Kind regards,

Thomas

SELECT tblRequest.RequestID, 'N/A' AS [Week No], tblRequest.[Refund Date] AS [Date],
tblRequest.Requester, tblRequest.Authoriser, tblRequest.Refunder AS Processor,
tblRequest.[CRIS Reference] AS [CRIS Ref No], tblRequest.[Customer Title] & ' ' & [Customer Name] AS [Customer's Name],
tblRequest.[Reason Type] AS [Transaction Type], Format(tblRequest.Value,'£0.00') AS Amount,
tblRequest.DCRef AS [Gift Card No], tblGCProc.PPTAuth AS [PPT Auth No],
IIf(Left(tblRequest.[Cost Centre],1)='Z','WEBSTORE',[tbl Master].NAME) AS Store
FROM tblRequest, tblGCProc, [tbl Master]
WHERE (((tblRequest.[Refund Date])>=#7/9/2013# And (tblRequest.[Refund Date])<=#7/10/2013#)
AND ((tblGCProc.RequestID)=[tblRequest].[RequestID]) AND ((tblRequest.Currency)='Sterling')
AND ((tblRequest.Brand)<>'BHS') AND (([tbl Master].ID)=[tblRequest].[Cost Centre]))
ORDER BY tblGCProc.ID;

 

[Pat Hartman]

Your join is incorrect. It is a Cartesian Product. You need to specify the join type explicitly. Open the query in the QBE and draw the join lines. The join between the tblMaster and tblRequest should be a Left Join rather than an Inner Join if you want records from tblMaster even if they don't have matches in tblRequest.

Switching to proper joins will eliminate parts of the Where clause and you should end up with:
WHERE (((tblRequest.[Refund Date])>=#7/9/2013# And (tblRequest.[Refund Date])<=#7/10/2013#)
AND ((tblRequest.Currency)='Sterling')
AND ((tblRequest.Brand)<>'BHS')

Check the parens. I may have messed them up.

 

[Larnu]

Thanks Pat, replaced it with joins (as follows), and works great.

SELECT tblRequest.RequestID, 'N/A' AS [Week No], tblRequest.[Refund Date] AS [Date],
tblRequest.Requester, tblRequest.Authoriser, tblRequest.Refunder AS Processor,
tblRequest.[CRIS Reference] AS [CRIS Ref No], tblRequest.[Customer Title] & ' ' & [Customer Name] AS [Customer's Name],
tblRequest.[Reason Type] AS [Transaction Type], Format(tblRequest.Value,'£0.00') AS Amount,
tblRequest.DCRef AS [Gift Card No], tblGCProc.PPTAuth AS [PPT Auth No],
IIf(Left(tblRequest.[Cost Centre],1)='Z','WEBSTORE',IIF(Left(tblRequest.[Cost Centre],3)='EXP','EXPIRED GIFT CARD',[tbl Master].NAME)) AS Store
FROM (tblRequest Left JOIN [tbl Master] ON tblRequest.[Cost Centre] = [tbl Master].ID)
INNER JOIN tblGCProc ON tblRequest.RequestID = tblGCProc.RequestID
WHERE ((tblRequest.[Refund Date])>=#7/9/2013# And (tblRequest.[Refund Date])<=#7/10/2013#)
AND ((tblRequest.Currency)='Sterling')
AND ((tblRequest.Brand)<>'BHS')
ORDER BY tblGCProc.ID;