Mike,
What I suggested would give you three lines, one for each employee (without
having to select) with a total amount nect to their names (so all employees
in one move). This requires that the employee name field in the query uses a
Group by total function and the payment field uses a Sum total function,
while any other field in the query (for filtering purposes) uses a Where
total function (so it does neither afftect the grouping, nor appear in the
results).
If you still want to use a combo box (or list box) then you have to do this
on a form, not in the qury design. Start with a blank (unbound) form, add an
unbound combo box (although my personal view is that a list box would be
more "ergonomic" for just three values), and an unbound text box, and use
the On Change event of the combo box (or the on DblClick event of the list
box) to set the value of the text box to the calculated sum by means of
either a macro or a line of code.
If you use a macro, then it takes a SetValue action with arguments:
Item: the name of the text box
Expression: DSum("[field1]","qry","[field2]= ' " & Forms!Frm!Cbo & " ' ")
substituting:
field1 = payments field name in the query
qry = query name
field2 = employee name field name in the query
Frm = the form's name
Cbo = the combo box name (or list box name)
If you use code, then it would be something like:
Dim TotalPayments as Double
TotalPayments = DSum("[field1]","qry","[field2]= ' " & Me.Cbo & " ' ")
Me.TxtX = TotalPayments
(where TxtX = the name of the text box on the form)
In either case, the query need not be a totals one.
HTH,
Nikos
Mike said:
Thanks,
But I don't see this total in the View. I see Total,
Criteria, etc. I have all the feilds grouped by Total,
have a Sum for the Amount. I can enter:[enter employee
name:] in the criteria and it gives me amount only for
that employee which what I want, but I want to be able to
choose the employee name from a combo like box instead of
entering an employee name.
I hope that I have been able to explain this well.
Thanks again,
Mike
-----Original Message-----
Mike,
If I understand correctly what you're trying to do, then:
- remove the employee criterion altogether
- make your query a Totals one (in design view, View > Totals) with Group By
total function on employee and Sum total function on payment; leave out
non-required fields, and use total function Where on other criteria fields
(dates etc.)
This will return just one record per employee with the total amount of
payments.
HTH,
Nikos
Mike said:
Hi,
I have created this query to lookup some data from three
tabless and give a sum of all payments received by three
employees. In the criteria I have put:
[Enter Employee Name:] and it give the total payments
received by that particular empoyee. I was wonderig if
there is any way to have the prompt to be of a Combo form
to have the three employee listed so that we can choose
one of them to avoide running the same process three
times for each employee?
Thanks very much.
Regards,
Mike
.
