Create a Calcuated field in a query

[Rachel M.]

I'm creating a database to track commission. I have an Invoice Database that
records the amount of each sale. There can be multiple Sales Reps per
Invoice. So I've created a table called SalesPerson Invoice. There is a one
to many relationship between Invoice & SalesPersonInvoice.

I have a third table called Commision Plans. Each Sales Rep. has 2 plans per
year. There is a different quote for Existing clients & New Clients.

I want a field that gives me a total for sales up to that date.

I've started a query based on the SalesPersonInvoice table. The query needs
to add up all the sales between the beginning date (Which is the start date
in the Commission Plans table) and the invoice date. The query needs to add
up the right sales bases on employeeid, newclient.

What do I do next? My query contains the SalesPerson & Amount. I just need
the current total.

 

[[MVP] S.Clark]

I think it would be a rather straightforward inner join between several
tables with some criteria to handle the date ranges. Couple that with a
Group by for salesperson, then sum the totals and it should be soup.
At worst, 2-3 queries strung together can probably make it happen.

Go ahead and post your table structures,(relevant fields only is fine) and
we might be able to give more specific direction.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting