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

[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

 

[Aaron Codak]

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.

 

[John Spencer MVP]

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

 

[KARL DEWEY]

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

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

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

 

[BruceM]

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

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

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

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

 

[KARL DEWEY]

Correct.

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

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

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