Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that display all expense names and their totals. I have a uniquie scenario in expenses. I want to to subtract from the Admin expense Deffered Comp and intrest. Can I do this within the query.
 
Dear George:

This is pretty scanty information, but I'll tell you what I can.

In the query results you would have a column for TotalExpense. In the
case that the ExpenseName is Admin, you want to subtract the totals
for two other ExpenseName categories called 'Deferred Comp' and
'Interest'. You would probably need to retrieve the total of each of
these with subqueries. You also might want to omit these two from the
overall query, since you're already accounting for them.

Begin with a totals query that gives you just the basic totals for
each account. I may look like this:

SELECT ExpenseName, SUM(ExpenseAmount) AS TotalExpense
FROM YourTable
GROUP BY ExpenseName

The modification for your needs would then be:

SELECT ExpenseName,
IIf(ExpenseName = "Admin expense",
SUM(ExpenseAmount) - (SELECT SUM(ExpenseAmount) FROM YourTable
WHERE ExpenseName = IN("Deffered Comp", "interest")),
SUM(ExpenseAmount))
FROM YourTable
WHERE ExpenseName NOT IN ("Deffered Comp", "interest")
GROUP BY ExpenseName

You must fix this up for the actual names of your table and columns,
and the exact ExpenseName values required.

You may want to remove the next-to-last line if you do want to see the
Deffered Comp and interest rows in addition to subtracting them. I
put this in in case you would want to omit them, as it's easier to
take code out than to have to add it, expecially if you don't know
what it is!

A final note. In my experience with accounting, it is often said you
should subtract something when, in actuality, the value is already
negative and, in computer terms, you need to add it. So, you may want
to change the - to + in line 3.

I made enough assumptions here to fill a bucket or two. You may need
to post additional information here if I was far off.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I get a syntax error. Here is my SQL Cod

SELECT [Total Expenses].[Expense Name]
IIF([Total Expenses].[Expense Name]="Administrative"
SUM([Total Expenses].[Total Expense]) - (SELECT SUM([Total Expenses].[Total Expense]) FROM [Total Expenses
WHERE [Total Expenses].[Expense Name] = IN("Defferred Comp", "Intrest"))
SUM( [Total Expenses].[Total Expense]
FROM [Total Expenses
WHERE [Total Expenses].[Expense Name] NOT IN ("Defferred Comp", "Intrest"
GROUP BY [Total Expenses].[Expense Name];
 
Dear George:

SELECT [Expense Name],
IIf([Expense Name]="Administrative",
SUM([Total Expense]) - (SELECT SUM([Total Expense])
FROM [Total Expenses]
WHERE [Expense Name] = IN("Defferred Comp", "Intrest")),
SUM([Total Expense])) AS CalculatedExpense
FROM [Total Expenses]
WHERE [Expense Name] NOT IN ("Defferred Comp", "Intrest")
GROUP BY [Expense Name];

In line 6 above, I have added a closing paren (probably the cause of
the syntax error - sorry if that was my error) and given an Alias for
this column, which you can change as desired. The SQL looks good to
me now. Please let me know how you do with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I'm still getting a syntax error. Here's be SQL again.

SELECT [Total Expenses].[Expense Name]
IIF([Total Expenses].[Expense Name]="Administrative"
SUM([Total Expenses].[Total Expense]) - (SELECT SUM([Total Expenses].[Total Expense]) FROM [Total Expenses
WHERE [Total Expenses].[Expense Name] = IN("Defferred Comp", "Intrest"))
SUM([Total Expenses].[Total Expense])
FROM [Total Expenses
WHERE [Total Expenses].[Expense Name] NOT IN ("Defferred Comp", "Intrest"
GROUP BY [Total Expenses].[Expense Name]

TheTotal Expenses in this case is the name as the union query as well as a field name in the query. I don't think this should make a difference.
 
Dear George:

Silly me. Don't put an = before the IN("Defferred Comp" . . .

Hope that's the only error here!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
That took care of the syntax problem and the query ran. How can I get the Deferred Comp and Intrest to dispaly as well as being subtracted. Also the Admin. cost is bigger than what it was previouslly.
 
Sorry for the confusion on the l ast post. It was a typo on my part that was causing one of the problems. It seems to be subtracting Intrest from Administrative cost but not Intrest. Can you see any reason why this would be.
 
Back
Top