Hello,
I am working on an accounting database. There are many different trans codes that I work with. There are a few that cross between the financial ledgers that I am trying to work with. Due to them crossing the ledgers, depending on which account I am trying to work with they may need to be the opposite value.
I need to make it so that if the trans = 83, then the [amount] field for that record is -[amount].
Data
Basically I have my Cross-tab setup currently like this:
Thanks,
PROXI
I am working on an accounting database. There are many different trans codes that I work with. There are a few that cross between the financial ledgers that I am trying to work with. Due to them crossing the ledgers, depending on which account I am trying to work with they may need to be the opposite value.
I need to make it so that if the trans = 83, then the [amount] field for that record is -[amount].
Data
Code:
ID PPA Case Date Amount Trans Type
226996 063 83003 7/31/2009 1.65 83 PA
227010 063 83012 7/31/2009 32.34 83 PA
227009 063 83011 7/31/2009 25 83 PA
227008 063 83011 7/31/2009 5.61 83 PA
227007 063 83011 7/31/2009 13.5 83 PA
227006 063 83010 7/31/2009 25 83 PA
227005 063 83010 7/31/2009 4.62 83 PA
Basically I have my Cross-tab setup currently like this:
Code:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Sum(TblDailyfiles.Amount) AS SumOfAmount
SELECT TblDailyfiles.Date, Sum(TblDailyfiles.Amount) AS [Sum of Amount]
FROM TblDailyfiles
WHERE (((TblDailyfiles.Trans)=79 Or (TblDailyfiles.Trans)=80 Or (TblDailyfiles.Trans)=81 Or (TblDailyfiles.Trans)=83 Or (TblDailyfiles.Trans)=84 Or (TblDailyfiles.Trans)=85 Or (TblDailyfiles.Trans)=89) AND ((TblDailyfiles.Date)>=[Enter Start Date] And (TblDailyfiles.Date)<=[Enter End Date]))
GROUP BY TblDailyfiles.Date
ORDER BY TblDailyfiles.Date, [Trans] & " " & [Type] DESC
PIVOT [Trans] & " " & [Type];
Thanks,
PROXI