MS Access: Column split

[Lmens]

Please-

I have a field with revenu and cost of goods sold amont called "Total Local Currency Amt" and another field called "P&L" with values "REV" and "COGS". I want to split the the "Total Local Currency Amt" into 2 columns; Revenu and cost of goods sold depending on the value (REV or COGS) in the "P&L" column. How do I go about this?

Here is what I have. How do I join the 2 select statement?

SELECT [Q1 YTD Rev & Cogs].[Total Local Currency Amt] AS Revenu, [Q1 YTD Rev & Cogs].CT, [Q1 YTD Rev & Cogs].Director, [Q1 YTD Rev & Cogs].Source
FROM [Q1 YTD Rev & Cogs]
WHERE ((([Q1 YTD Rev & Cogs].[P&L])="Rev"))
GROUP BY [Q1 YTD Rev & Cogs].CT, [Q1 YTD Rev & Cogs].Director;

and

SELECT [Q1 YTD Rev & Cogs].[Total Local Currency Amt] AS Cogs
FROM [Q1 YTD Rev & Cogs]
WHERE ((([Q1 YTD Rev & Cogs].[P&L])="COGS"));

Thanks!

 

[pbaldy]

Try something along the lines of

IIf([P&L] = "Rev", [Total Local Currency Amt], 0) AS Revenue

 

[DCrake]

word of warning do not use words such as Source for field names. Again using & characters in field names is also bad practice.