Calculating Totals

  • Thread starter Thread starter Greg Jesky
  • Start date Start date
G

Greg Jesky

I have a question concerning how you create a sum based on selected records.
For example I have a table with 3 fields:

Customer(text), ITEM(text), Cost(Money)

A, widget1, $1.00
B, widget2, $1.50
A, widget3, $1.75
C, widget4, $5.00
C, widget1, $1.00
B, widget3, $1.65

I would like to run a parameter type query where I input an ITEM. So far no
problem!!

However I now get stuck- If I would input "widget1"
I would like to get a result that looks like

A, widget1, $1.00
C, widget1, $1.00
Total $2.00

I get the first 2 lines above just fine...but I can not decipher Access Help
to determine how to get the $2.00 total on the dynaset.

Thank You,
Greg
 
Greg

Read that as "You *can't* have a total line in a query"

I am sure it is a typing mistake from MGFoster.
 
Van said:
Greg

Read that as "You *can't* have a total line in a query"

I am sure it is a typing mistake from MGFoster.
Click to expand...

Yes, indeed. I meant cannot have a total line in a query. The ole
fingers messed up.
 
Greg Jesky said:
I have a question concerning how you create a sum based on selected records.
For example I have a table with 3 fields:

Customer(text), ITEM(text), Cost(Money)

A, widget1, $1.00
B, widget2, $1.50
A, widget3, $1.75
C, widget4, $5.00
C, widget1, $1.00
B, widget3, $1.65

I would like to run a parameter type query where I input an ITEM. So far no
problem!!

However I now get stuck- If I would input "widget1"
I would like to get a result that looks like

A, widget1, $1.00
C, widget1, $1.00
Total $2.00

I get the first 2 lines above just fine...but I can not decipher Access Help
to determine how to get the $2.00 total on the dynaset.

Thank You,
Greg
Click to expand...

Do you want the totals on a Report or Form?

There is a workaround to get the total in the header/footer of a form.
 
Steve,
I would like to be able to get the total based upon a query. I saw a
function called "DSum" that may allow
me to get the information I need . I am going to be reviewing and trying to
get this function to work within the next several days, if I can get the
syntax correct. Do you know of any references or examples for this function
Thank You,
Greg
 
Greg Jesky said:
Steve,
I would like to be able to get the total based upon a query. I saw a
function called "DSum" that may allow
me to get the information I need . I am going to be reviewing and trying to
get this function to work within the next several days, if I can get the
syntax correct. Do you know of any references or examples for this function
Thank You,
Greg
Click to expand...


#1 - Using a Query:

Create a new query.
Add the "Widget" table.
Add "Item" and "Cost" to the grid.
In the Criteria row for "Item", enter "Forms!TheFormName.TheControlName"
(change "TheFormName" to your form name and "TheControlName" to your control
name.

Change the query a totals query.
For the Cost column, Change "GroupBy" to "SUM"
Save the query. Name it something like "qryTotCost"

Open your existing query and add the totals query you just created.
Join the table and the query by "Item"
Double click on the join line and select join type 2


table query
-----------------------
Item ------> Item
Cust
Cost
SumOfCost


Open the form and add SumOfCost to the form footer.
Save the form.

**********************
#2 - Using DSUM():

Add an unbound text box to the form footer.

Set the control source to:

=DSUM("[]",""," = '" & Forms!TheFormName.TheControlName & "'")

In the control "Forms!Forms!TheFormName.TheControlName" After Update event
add:

Me.UnboundControlName.Refresh



In both cases, be sure to use your form and control names.
 
That should be:

=DSUM("[fieldName]","TableOrQueryName"," = '" &
Forms!TheFormName.TheControlName & "'")
 
Back
Top