We very much need to be concerned with that field. If there is not a field
to distinguish to which client these records belong, what you seek cannot be
done.
I will assume there is such a field, and that it is a number field named
ClientID. You should be able to use this expression:
=DSum("[SumOfActualAmt]","[YourTable]","([FunctionType] = 'E' OR
[FunctionType] = 'V') AND [ClientID] = " & [ClientID]) +
DSum("[SumOfBilledAmt]","[YourTable]","[FunctionType] = 'I' AND
[ClientID] = " & [ClientID])
Substitute the actual table name for YourTable.
Without sepcifying the client, the expression will add the values for all
records in the table.
A way you may be able to do this more efficiently is to use totals queries.
Construct a query that includes FunctionType, ClientID, and SumOfActualAmt.
Click the sideways "E" (sort of) on the query toolbar to make it a totals
query. Select Group By for ClientID and FunctionType, and Sum for
SumOfTotalAmt. For the top line in the SumOfActualAmt column, put something
like this:
SumActual: SumOfActualAmt
Set the criteria for FunctionType to:
"E" or "V". Set the criteria for ClientID to [Select Client ID]. Run the
query by switching to datasheet view. You will be prompted for a ClientID.
Type one in and view the result. Save the query.
Create another totals query except with SumOfBilledAmt (call it SumBilled
the same as you used SumActual in the previous query) instead of
SumOfActualAmt. Set the FunctionType criteria to "I". Use the same
criteria as before for ClientID. Run it, and save it.
Create a third query. Add the two totals queries, then select SumActual and
SumBilled. At the top of an empty column:
AddAll: SumActual + SumBilled
SumActual and SumBilled in the first two queries are called aliases, which
is to say a name you select for the field. They are optional. If you do
not use them the field will be named something like SumOfSumOfActualAmt.
If this works as intended we can find another way to get ClientID into the
query other than having to type it in each time.
Aaron Codak said:
Please show me a way we can accomplish what you mentioned in your previous
post. I believe there is another part of the query that distinguishes the
individual client fields so I don't believe we need to be concerned with
that
for combining this data. Please let me know what you are able to come up
with that I can build into the current query. Thank you!
BruceM said:
There should be a munged e-mail address in my sender information, but I
have
to say I cannot promise I will find time to look at the files any time
soon.
If my previous post describes what you wish to accomplish, I can show you
a
way to accomplish that. The only missing piece of information is whether
there is a field value the sample records have in common. You mention
that
these are the records for a Client. Is there a ClientID or some such
thing
in these records? If so, is it a text field or a number field?
Aaron Codak said:
Yes, I want to combine all into one column. The current report that I
am
working on with this data breaks the financial numbers into three
area's-one
section for the Function Type E financial data, the next section of the
report for the Type I financial data and the last section for the Type
V
financial data.
Yes, there is a field for Client name, account executive name and 34
other
fields in the design of the query the way that it is built right now.
Again, if I could get an email address, I could actually forward you
copies
of these files so I could show you exactly what I am trying to do. I
apologize if my descriptions are not as informative as you need them to
be
but I'm doing the best I can in describing in words what I'm trying to
do
(vs. sending you the files and showing you that way).
:
Please clarify what you mean by "combine in one column". Are you
saying
you
want to add together everything in SumOfActualAmt in records where
FunctionType is E or V, then add to that total the SumOfBilledAmt
total
for
records in which FunctionType is I?
66,343.78 (total of SumOfActualAmt for E and V function type)
+ 151,290.00
= 217,633.78
You say this is all for one client. Is there a field that identifies
that
client?
Bruce, can you please assist me with this?
:
Function Type SumOfActual Amt SumOfBilled Amt
E 63,720.00 0.00
E 450.00 0.00
E 1,650.00 0.00
I 0.00 151,290.00
V 284.80 454.64
V 29.74 0.00
V 209.24 765.77
I need combine into one column: All of the data under "SumOfActual
Amt"
and
the $151,290 that is under "SumOfBilled Amt" - 'I' Function code.
This
is
an example for one of our clients. All of the "SumOfActual Amt"
numbers
listed for the 'E' and 'V' Function codes + "SumOfBilled Amt"
numbers
listed
for the 'I' Function code - I need this combined into one column.
:
I meant a couple of lines like this in a newsgroup message:
ActualAmt BilledAmt FunctionCode
$100 $90 E
$200 0 I
Along with that, an explanation of how the pieces fit together,
which
is why
I asked the questions in the previous response. Data without
some
understanding of the underlying business need is not likely to
help.
message
I'll be happy to send you some sample data - is there an email
address I
can
forward an excel file to to show you exactly what I am trying
to
achieve?
Thanks.
:
I'm not following this. Does ActualAmt contain either
VendorCosts
and
TimeBillings, or could there be both? If both, they need to
be
separated
somehow, perhaps by using a related table. Further, are there
any
details
of VendorCosts and TimeBillings, or are do the amounts exist
only
as
lump
sums? Again, related tables seem to be called for.
You can combine several columns (fields) into one in a query,
but
to
what
end? Do you mean you want to add the values? But then you
say
you
can't.
Is there a business reason you do not wish to add the values,
or
do
you
mean
Access is somehow not letting you do what you want? Are you
saying
you
can
have Actual amount or Billed amount but not both? Every time
I
think I'm
close to understanding I see that I need to take something
else
into
consideration. Perhaps some sample data would help (the
equivalent
of
three
records or so).
message
I have vendor costs (V) and time billings (E) in one column
called
actuals.
In a second column is Billed Amount which contains fee's (I)
billed to
the
client. A third column carries the function codes - E, I, V
that
designates
what each dollar value item is in the Actual Amt and Billing
Amt
columns
are.
I'm trying to build all of the financial data into one
column
(the
vendor
costs, the time billings and the client fee's). Right now
the
Vendor
Costs
and Time billings are in one column and the Client Fee's are
in
another
column.
I can't just combine the two columns though because for the
Vendor
costs -
there is a $$ number listed in the Actual Amt column for
Vendor
costs
and
a
$$ number listed in the Billed Amt column also for vendor
costs -
these
two
numbers cannot be combined. For Time billings, there are
financial
numbers
in the one column but nothing in the second column -
likewise
for
the
client
fee's. I hope I made some sort of sense out of this, I
tried
to
describe
and
give you as clear of a picture as I could. Thank you!
:
It would be best if you describe exactly what you are
trying
to
do.
As a
guess seems you want to do something if the FunctionType is
E
or
I,
and
you
want to do another thing if FunctionType is V. Are these
the
only
choices
for FunctionType?
in
message
I am new to Access and am in the process of revising an
Accounting
report
presently setup in access. Can you assist me with
verifying/changing
the
statement below I am attempting to setup in the current
query?
Thank
you!
=(([SumOfActual Amt]+[SumOfBilled Amt]])IIF [Function
Type]='E'
or
'I')+([SumOfActual Amt]IIF [Function Type]='V')
