Cross-Tab Query Needing opposite Value

[PROXI]

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

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:

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

 

[SkipVought]

Is it ONLY transaction 83 that needs Value * -1?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

IMO, you should have something stored in a table that identifies the grouping of Trans values 79, 80, 81, ... 89. I would expect to find a table in your system that has a primary key of the Trans field. Add a field that groups some together and possibly identifies which are to be negative. I don't care for queries that contain hard-coded values like 79, 80, etc.

Duane
Hook'D on Access
MS Access MVP

 

[PROXI]

I actually just figured this out. I am not a fan of hard coding queries either, but this is sort of a quick database that I don't want to spend much time on. Just for posterity, here is the SQL that I got to do what I needed in case someone else may use it later:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Sum(IIf([Trans]=83,[Amount]*-1,[Amount])) AS Amounts
SELECT TblDailyfiles.Date, Sum([Amounts]) 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

 

[dhookom]

You should be able to simply the SQL using "IN" and "BETWEEN". Something I detest even more than hard-coding values is parameter prompts in queries ;-)

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Sum(IIf([Trans]=83,[Amount]*-1,[Amount])) AS Amounts
SELECT TblDailyfiles.Date, Sum([Amounts]) AS [Sum of Amount]
FROM TblDailyfiles
WHERE TblDailyfiles.Trans IN (79,80,81,83,84,85,89) AND 
TblDailyfiles.Date Between [Enter Start Date] And  [Enter End Date]
GROUP BY TblDailyfiles.Date
ORDER BY TblDailyfiles.Date, [Trans] & " " & [Type] DESC 
PIVOT [Trans] & " " & [Type];

Duane
Hook'D on Access
MS Access MVP