http://mail.avatel.us/1.jpg
http://mail.avatel.us/2.jpg
http://mail.avatel.us/3.jpg

I have provided links to 3 pictures which will hopefully help you
understand what I’m trying to do.

I need to be able to have a form that has each sales person’s name
with a total number of sales for the given date fields that I enter.
Since the form is in Continuous Forms mode, you can see that it lists
all of the sale’s people who made sales between the dates I specified
at the top. If you look at 2.jpg, you will see that I filtered the
form to show only Evelyn’s sales but there are a bunch. I’m trying to
get this form to show Evelyn only once (or any other user I filter)
and then the number of sales he/she had. I want to be able to show all
sales people as well but only show their names once and then the total
number of sales they did between the dates I chose. Each sale’s person
has their own unique ID and comes from a table called SalesEmployeeT.
The table that contains the sales is called LeadDetailT. Every time
someone creates a sale, it creates a single LeadDetail ID in that
table, so one sale means one new ID created. There are fields in that
LeadDetailT that reflect the total amount of sales and total amount of
maintenance called TotalSales and TotalMaint. I use a simple formula
to add both fields for a grand total.

Thanks in advance.

write a query to pull the fields you need for the form, and including the
date-of-sale field. set criteria on the date-of-sale field as

Between Forms!MyFormName!StartDateControl And
Forms!MyFormName!EndDateControl

the expression should be all on one line, regardless of linewrap in this
post. replace MyFormName with the correct name of the form, and replace
StartDateControl with the correct name of the control where you enter the
beginning date of the date range, and ditto EndDate control for the ending
date of the range.

still in query Design view, choose Query | Parameters from the menu bar, and
in the dialog box enter the two parameters, as each on separate lines, as

Forms!MyFormName!StartDateControl
Forms!MyFormName!EndDateControl

with the corrected form and control names, of course, and assign Date/Time
data type to each.

last, turn the query into a Totals query by clicking the toolbar button that
looks like a fancy capital E, or a capital M turned on its' side. Group By
the salepersonID field (that's the default grouping assignment), and Count
another field - that will be the total number of records in the query,
grouped by each salespersonID. if you have a field showing dollar value of
each sale, Sum that field, for total sales value. use the query as the
form's RecordSource.

hth


<joecosmides (AT) gmail (DOT) com> wrote

http://mail.avatel.us/1.jpg
http://mail.avatel.us/2.jpg
http://mail.avatel.us/3.jpg

I have provided links to 3 pictures which will hopefully help you
understand what I’m trying to do.

I need to be able to have a form that has each sales person’s name
with a total number of sales for the given date fields that I enter.
Since the form is in Continuous Forms mode, you can see that it lists
all of the sale’s people who made sales between the dates I specified
at the top. If you look at 2.jpg, you will see that I filtered the
form to show only Evelyn’s sales but there are a bunch. I’m trying to
get this form to show Evelyn only once (or any other user I filter)
and then the number of sales he/she had. I want to be able to show all
sales people as well but only show their names once and then the total
number of sales they did between the dates I chose. Each sale’s person
has their own unique ID and comes from a table called SalesEmployeeT.
The table that contains the sales is called LeadDetailT. Every time
someone creates a sale, it creates a single LeadDetail ID in that
table, so one sale means one new ID created. There are fields in that
LeadDetailT that reflect the total amount of sales and total amount of
maintenance called TotalSales and TotalMaint. I use a simple formula
to add both fields for a grand total.

Thanks in advance.

On Mar 24, 9:14*am, "tina" <nos... (AT) address (DOT) com> wrote:
I think what I'm after would be a form in continuous forms layout that
shows all of the sales users. I would create a simple query that pulls
down all of the sales people. There are 35 of them and they have their
own table called SalesEmployeeT.

I could create some unbound fields and use Dcount to look into the
SalesTable and count the number of sales between Me.BeginDate AND
Me.EndDate (which are unbound date fields on my form). I could use an
unbound field with a control source of:
=DCount("LeadDetailID","[LeadDetailT]","[SoldDate] Between #" & CLng
(Me.BeginDate) & " And " & CLng(Me.EndDate))

The problem is that I can get this to work until the BETWEEN part. The
code is broken and I'm not sure how to write it properly. I was
playing around while waiting for a response and it seems like that is
a good way to do it. What do you think?

i think running a domain function for every record in the form is going to
be less efficient than using a Totals query. if you're linking the employee
table to the sales table in the query, as you should be, then set up a LEFT
JOIN from the employee table to the sales table, rather than an INNER JOIN.
that should give you every employee, rather than only those who had a sale
in the time period.

hth


<joecosmides (AT) gmail (DOT) com> wrote

On Mar 24, 9:14 am, "tina" <nos... (AT) address (DOT) com> wrote:
I think what I'm after would be a form in continuous forms layout that
shows all of the sales users. I would create a simple query that pulls
down all of the sales people. There are 35 of them and they have their
own table called SalesEmployeeT.

I could create some unbound fields and use Dcount to look into the
SalesTable and count the number of sales between Me.BeginDate AND
Me.EndDate (which are unbound date fields on my form). I could use an
unbound field with a control source of:
=DCount("LeadDetailID","[LeadDetailT]","[SoldDate] Between #" & CLng
(Me.BeginDate) & " And " & CLng(Me.EndDate))

The problem is that I can get this to work until the BETWEEN part. The
code is broken and I'm not sure how to write it properly. I was
playing around while waiting for a response and it seems like that is
a good way to do it. What do you think?