Conditional Sum Question

  • Thread starter Thread starter idtjes3
  • Start date Start date
I

idtjes3

Hello

Im working on a form and i want to calculate a column on a sub form based
on a specific condition. I basically have 4 items a user can choose from in
a drop down box. The user selects which item it is, enters a unit quantity,
and the date it was purchased. Off to the side i want to create a section
where it will list: Item1, Item2, Item3, and Item4. Then, I want next to each
item the total number of units for that item taken from the subform.
This needs to be done because i need to know how much of each item was used
for a particular job and not just a complete total of all the units
reguardless of the item they are for. In the box next to the Item i want
something along the lines of : Sum([UnitQuantity]) Where([ItemID]="Item1")
and so on for each item. Ive tried this a few ways but all returned an error.
Any ideas? Is this the best way to approach this sort of thing? I prefer to
show it this way so a user has a quick reference for the items of the job.
Thanks.
 
Something like this set up could work - unfortunately I don't know how your
tables are setup, or indeed anything else about the app you are building.
Set the subform to continuous view. Each time the user wants to choose a new
item, the subform goes to a new record - a new line in the subform.
There is a text box to enter the number of units for each item. This would
give users instant visual of how many units of each item they chose.

Jeanette Cunningham
 
Hi,
Thanks for your response. ill try and give some more information. I actually
got it to work over the weekend however, instead of the box generating a
total for each individual job, its listing a running total of ALL the items.
For instance' i buy 7 units of item1 for job Test0001 and 15 units of
item2 for job Test0002, in the box for item1( the running total for that job)
its listing 22 for both rather than 7 units on Test001's form and 15 on
Test0002's form. The only way i got this to work was referencing the table
where my mix data is stored. it doesnt seem that it is associating each mix
to a particular job. Here is my code:
=DSum("[Unit(s)]","Job Mix Cost","[MixType]='A-#90'")

i think my problem above is that its tallying the units for itemA
reguardless of the job number they are associated to, becasue i dont specify
the job in my equation. Is there away to do that? Can i reference the form i
enter the item on rather than the table its stored in? Like i have a sub form
for my items that automatically associate themselves with what ever the job
number is on the form im working on. why does it do this and can i some how
use that auto association above?


Jeanette Cunningham said:
Something like this set up could work - unfortunately I don't know how your
tables are setup, or indeed anything else about the app you are building.
Set the subform to continuous view. Each time the user wants to choose a new
item, the subform goes to a new record - a new line in the subform.
There is a text box to enter the number of units for each item. This would
give users instant visual of how many units of each item they chose.

Jeanette Cunningham

idtjes3 said:
Hello

Im working on a form and i want to calculate a column on a sub form based
on a specific condition. I basically have 4 items a user can choose from
in
a drop down box. The user selects which item it is, enters a unit
quantity,
and the date it was purchased. Off to the side i want to create a section
where it will list: Item1, Item2, Item3, and Item4. Then, I want next to
each
item the total number of units for that item taken from the subform.
This needs to be done because i need to know how much of each item was
used
for a particular job and not just a complete total of all the units
reguardless of the item they are for. In the box next to the Item i want
something along the lines of : Sum([UnitQuantity]) Where([ItemID]="Item1")
and so on for each item. Ive tried this a few ways but all returned an
error.
Any ideas? Is this the best way to approach this sort of thing? I prefer
to
show it this way so a user has a quick reference for the items of the job.
Thanks.
Click to expand...
Click to expand...
 
You can specify more than one criteria for the DSum function.

=DSum("[Unit(s)]","Job Mix Cost","[MixType]='A-#90'")
instead of "[MixType]='A-#90'"

=DSum("[Unit(s)]","Job Mix Cost", "[txtID]=" & Me.txtID & " And
[MixType]='A-#90'")

Note: replace txtID with the name of the Primary Key field for your form

If ID is a text field then
=DSum("[Unit(s)]","Job Mix Cost", "[txtID]=""" & Me.txtID & """ And
[MixType]='A-#90'")

This assumes that the value of txtID from your form together with the value
of A-90# are enough to narrow it down to the job Test001.
The above is untested air code.

Jeanette Cunningham

idtjes3 said:
Hi,
Thanks for your response. ill try and give some more information. I
actually
got it to work over the weekend however, instead of the box generating a
total for each individual job, its listing a running total of ALL the
items.
For instance' i buy 7 units of item1 for job Test0001 and 15 units of
item2 for job Test0002, in the box for item1( the running total for that
job)
its listing 22 for both rather than 7 units on Test001's form and 15 on
Test0002's form. The only way i got this to work was referencing the table
where my mix data is stored. it doesnt seem that it is associating each
mix
to a particular job. Here is my code:
=DSum("[Unit(s)]","Job Mix Cost","[MixType]='A-#90'")

i think my problem above is that its tallying the units for itemA
reguardless of the job number they are associated to, becasue i dont
specify
the job in my equation. Is there away to do that? Can i reference the form
i
enter the item on rather than the table its stored in? Like i have a sub
form
for my items that automatically associate themselves with what ever the
job
number is on the form im working on. why does it do this and can i some
how
use that auto association above?


Jeanette Cunningham said:
Something like this set up could work - unfortunately I don't know how
your
tables are setup, or indeed anything else about the app you are building.
Set the subform to continuous view. Each time the user wants to choose a
new
item, the subform goes to a new record - a new line in the subform.
There is a text box to enter the number of units for each item. This
would
give users instant visual of how many units of each item they chose.

Jeanette Cunningham

idtjes3 said:
Hello

Im working on a form and i want to calculate a column on a sub form
based
on a specific condition. I basically have 4 items a user can choose
from
in
a drop down box. The user selects which item it is, enters a unit
quantity,
and the date it was purchased. Off to the side i want to create a
section
where it will list: Item1, Item2, Item3, and Item4. Then, I want next
to
each
item the total number of units for that item taken from the subform.
This needs to be done because i need to know how much of each item was
used
for a particular job and not just a complete total of all the units
reguardless of the item they are for. In the box next to the Item i
want
something along the lines of : Sum([UnitQuantity])
Where([ItemID]="Item1")
and so on for each item. Ive tried this a few ways but all returned an
error.
Any ideas? Is this the best way to approach this sort of thing? I
prefer
to
show it this way so a user has a quick reference for the items of the
job.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top