Combining Columns - disregard previous post - sent before I was fi

  • Thread starter Thread starter Aaron Codak
  • Start date Start date
A

Aaron Codak

I"m trying to combine two columns of data, a sample of my table is shown
below. Is there an expression that can be built that will combine the two
columns of data: SumofActual Amt and SumOfBilled Amt?

Criteria: Combine all of data listed under SumOfActual Amt + SumOfBilled Amt
(but only where Function Type = I for SumOfBilled Amt column). Meaning for
the example below, combine into one column all of the data listed under
'SumOfActual Amt' plus the $151,290 amount listed under 'SumOfBilled Amt'
column. Thanks!

Currently using MS Access 2003.

Function Function Type SumOfActual Amt SumOfBilled Amt
esup E 63720 0
esuprt E 450 0
etrvl E 1650 0
feemgt I 0 151290
vmeal V 284.8 454.64
vmess V 29.74 0
vmisc V 702.54 765.77
 
If you hit reply, the spacing should come up properly for the sample table
below that I pasted in. I had it spaced correctly prior to posting but it
didn't carry through that way for some reason. Thank you.
 
For what you have displayed and what you have said the query would look like:

SELECT Sum([SumOfActual Amt]+ IIF([Function Type]="I",[SumOfBilled Amt],0))
FROM [Some Table or Query]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Maybe like this --
SELECT Sum([SumOfActual Amt]+ Sum(IIF([Function Type]="I",[SumOfBilled
Amt],0)))
FROM [Some Table or Query];

John Spencer MVP said:
For what you have displayed and what you have said the query would look like:

SELECT Sum([SumOfActual Amt]+ IIF([Function Type]="I",[SumOfBilled Amt],0))
FROM [Some Table or Query]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Aaron said:
I"m trying to combine two columns of data, a sample of my table is shown
below. Is there an expression that can be built that will combine the two
columns of data: SumofActual Amt and SumOfBilled Amt?

Criteria: Combine all of data listed under SumOfActual Amt + SumOfBilled Amt
(but only where Function Type = I for SumOfBilled Amt column). Meaning for
the example below, combine into one column all of the data listed under
'SumOfActual Amt' plus the $151,290 amount listed under 'SumOfBilled Amt'
column. Thanks!

Currently using MS Access 2003.

Function Function Type SumOfActual Amt SumOfBilled Amt
esup E 63720 0
esuprt E 450 0
etrvl E 1650 0
feemgt I 0 151290
vmeal V 284.8 454.64
vmess V 29.74 0
vmisc V 702.54 765.77
Click to expand...
Click to expand...
 
Or maybe the missing parentheses goes after SumOfActualAmt?
SELECT Sum([SumOfActual Amt]) + etc.

KARL DEWEY said:
Maybe like this --
SELECT Sum([SumOfActual Amt]+ Sum(IIF([Function Type]="I",[SumOfBilled
Amt],0)))
FROM [Some Table or Query];

John Spencer MVP said:
For what you have displayed and what you have said the query would look
like:

SELECT Sum([SumOfActual Amt]+ IIF([Function Type]="I",[SumOfBilled
Amt],0))
FROM [Some Table or Query]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Aaron said:
I"m trying to combine two columns of data, a sample of my table is
shown
below. Is there an expression that can be built that will combine the
two
columns of data: SumofActual Amt and SumOfBilled Amt?

Criteria: Combine all of data listed under SumOfActual Amt +
SumOfBilled Amt
(but only where Function Type = I for SumOfBilled Amt column). Meaning
for
the example below, combine into one column all of the data listed under
'SumOfActual Amt' plus the $151,290 amount listed under 'SumOfBilled
Amt'
column. Thanks!

Currently using MS Access 2003.

Function Function Type SumOfActual Amt SumOfBilled Amt
esup E 63720 0
esuprt E 450 0
etrvl E 1650 0
feemgt I 0 151290
vmeal V 284.8 454.64
vmess V 29.74 0
vmisc V 702.54 765.77
Click to expand...
Click to expand...
Click to expand...
 
Correct.

BruceM said:
Or maybe the missing parentheses goes after SumOfActualAmt?
SELECT Sum([SumOfActual Amt]) + etc.

KARL DEWEY said:
Maybe like this --
SELECT Sum([SumOfActual Amt]+ Sum(IIF([Function Type]="I",[SumOfBilled
Amt],0)))
FROM [Some Table or Query];

John Spencer MVP said:
For what you have displayed and what you have said the query would look
like:

SELECT Sum([SumOfActual Amt]+ IIF([Function Type]="I",[SumOfBilled
Amt],0))
FROM [Some Table or Query]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Aaron Codak wrote:
I"m trying to combine two columns of data, a sample of my table is
shown
below. Is there an expression that can be built that will combine the
two
columns of data: SumofActual Amt and SumOfBilled Amt?

Criteria: Combine all of data listed under SumOfActual Amt +
SumOfBilled Amt
(but only where Function Type = I for SumOfBilled Amt column). Meaning
for
the example below, combine into one column all of the data listed under
'SumOfActual Amt' plus the $151,290 amount listed under 'SumOfBilled
Amt'
column. Thanks!

Currently using MS Access 2003.

Function Function Type SumOfActual Amt SumOfBilled Amt
esup E 63720 0
esuprt E 450 0
etrvl E 1650 0
feemgt I 0 151290
vmeal V 284.8 454.64
vmess V 29.74 0
vmisc V 702.54 765.77
Click to expand...
Click to expand...
Click to expand...
 
Back
Top