Need Help with 3 tables in SQL & ACCESS 1

[memkam]

what i am trying to do is read entries from TransactionReport, if the IsItRes field is true, compare the entries in the TransactionReport table to the Res table otherwise compare to ComTable

Query doesn't work.. please help

SELECT CASE
WHEN IsIsRes = TRUE
THEN
SELECT [TransactionReport].[ServiceType], [TransactionReport].[Weight], [TransactionReport].[Zone], [TransactionReport].[Price], [ResPrice].[Contractprice], [ResPrice].[HistoricPrice], [ResPrice].[ServiceType], [ResPrice].[Weight], [ResPrice].[Zone]
FROM TransactionReport, ResPrice
WHERE ((([ResPrice].[ServiceType]=[TransactionReport].[ServiceType]) And ([ResPrice].[Weight]=[TransactionReport].[Weight]) And ([ResPrice].[Zone]=[TransactionReport].[Zone]))
ELSE
SELECT [TransactionReport].[ServiceType], [TransactionReport].[Weight], [TransactionReport].[Zone], [TransactionReport].[Price], [ComPrice].[Contractprice], [ComPrice].[HistoricPrice], [ComPrice].[ServiceType], [ComPrice].[Weight], [ComPrice].[Zone]
FROM TransactionReport, ComPrice
WHERE (([ComPrice].[ServiceType]=[TransactionReport].[ServiceType]) And ([ComPrice].[Weight]=[TransactionReport].[Weight]) And ([ComPrice].[Zone])=[TransactionReport].[Zone]))
END

 

[JohnDTampaBay]

I think this will do the trick. The idea is to OUTER JOIN to both Residential and Commericial tables, then use CASE to decide which table's columns to SELECT. HTH, Good luck!

SELECT [TransactionReport].[ServiceType],
  [TransactionReport].[Weight],
  [TransactionReport].[Zone],
  [TransactionReport].[Price],
  [TransactionReport].[IsIsRes],
  CASE IsItRes WHEN TRUE THEN [ResPrice].[Contractprice]
               ELSE [ComPrice].[Contractprice]
  END AS [ContractPrice],
  CASE IsItRes WHEN TRUE THEN [ResPrice].[HistoricPrice]
               ELSE [ComPrice].[HistoricPrice]
  END AS [HistoricPrice],
  CASE IsItRes WHEN TRUE THEN [ResPrice].[ServiceType]
               ELSE [ComPrice].[ServiceType]
  END AS [ServiceType],
  CASE IsItRes WHEN TRUE THEN [ResPrice].[Weight]
               ELSE [ComPrice].[Weight]
  END AS [Weight],
  CASE IsItRes WHEN TRUE THEN [ResPrice].[Zone]
               ELSE [ComPrice].[Zone]
  END AS [Zone],
FROM ResPrice
        RIGHT OUTER JOIN TransactionReport
          ON [ResPrice].[ServiceType]=[TransactionReport].[ServiceType]
          AND [ResPrice].[Weight]=[TransactionReport].[Weight]
          AND [ResPrice].[Zone]=[TransactionReport].[Zone]
            LEFT OUTER JOIN ComPrice
               ON [ComPrice].[ServiceType]=[TransactionReport].[ServiceType]
               AND [ComPrice].[Weight]=[TransactionReport].[Weight]
               AND [ComPrice].[Zone])=[TransactionReport].[Zone]

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[memkam]

John,

Thanks for the code (especially for the time you took to write it)..

I am getting an error though:

Syntax error (missing operator) in query 'CASE IsItRes WHEN TRUE THEN [ResPrice].[ContractPrice]
ELSE
[ResPrice].[ContractPrice]
END

 

[ESquared]

memkam,

Did you realize you double-posted?

http://www.tek-tips.com/viewthread.cfm?SQID=893842&SPID=183&page=1

-------------------------------------
A sacrifice is harder when no one knows you've made it.