total number of records

J

John C.

I have a query that is pulling records based on sales rep and date. The
total number of records determines a commission tier. I have the both tables
in my query (sales info and commission tiers). I would like to only show the
commission tier that is applicable based on the total number of sales. I
need to somehow total the number of records and display it in a column. Is
this possible?
 
J

Jackie L

Do a separate query with Totals where you can Group by the fields needed
(salesperson) and a Where on the Date field. Then do a field of Salesperson
but with a Count.

Bring this query into your current query and link by the Salesperson field
to the sales table and then by the count field to the commission table. If
your commission tier is based on a range, then join/criteria would be a
little different.

Hope this helps.
Jackie
 
J

John C.

Jackie, Thanks. I'm not sure how to do the Totals part. If I do a count
each record equals 1. How do i get the total?
 
S

Sam

--
Ato
I need to wrire a query to give me reccords of associated who are due for
90, 180, 270 and annual evaluation. The start date is what I am using as
bench mark. That means I need record of associated who are due for evaluation
depending on the month the information is retrieved
 
J

John Spencer

How do you know if they haven't had an evaluation on the expected dates?
Is there a field that records the evaluation? Is that field in a
separate record in a separate table?

The structure of your database makes a large difference on giving you
the correct answer.

One way to handle this would be to use a calculated field. The
following may be the simplest to implement, although it is going to be
inefficient with large numbers of records.

Field: Day90: Format(DateAdd("d",90,DateStart),"mmyyyy")
Criteria: Format(Date(),"mmyyyy")

Field: Day180: Format(DateAdd("d",180,DateStart),"mmyyyy")
Criteria: Format(Date(),"mmyyyy")



---
John Spencer
Access MVP 2001-2005, 2007


Sam wrote:
--
Ato
I need to wrire a query to give me reccords of associated who are due for
90, 180, 270 and annual evaluation. The start date is what I am using as
bench mark. That means I need record of associated who are due for
evaluation
depending on the month the information is retrieved
 
Top