Re: Sql query to count how many sales between dates

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@xxxxxxxxx> wrote in message
news:edc4d53e-abc8-4398-990a-32368d9511f0@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mar 24, 9:14 am, "tina" <nos...@xxxxxxxxxxx> wrote:
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

<joecosmi...@xxxxxxxxx> wrote in message


news:05b24761-ba59-4efd-a1f2-d44b80f1dfb1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx://mail.avatel.us/1.jpghttp://mail.avatel.us/2.jpghttp://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.

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?


.

Relevant Pages

  • Re: a query to produce sales activity totals
    ... In query design view, depress the Total button on the toolbar Access adds a Total row to the query design grid. ... number of contacts and the varous steps they have taken in the sales process. ... put these totals into a spread sheet that shows each salesperson's numbers... ...
    (microsoft.public.access.queries)
  • Re: a query to produce sales activity totals
    ... creating of the query to spit out my data that's my challenge. ... I'm not clear how your sales quantities actually relate to the steps of the ... I need a query or report of some sort to give me the ... I manually count each field so I can enter the totals on an excel ...
    (microsoft.public.access.queries)
  • Re: Sql query to count how many sales between dates
    ... StartDateControl with the correct name of the control where you enter the ... turn the query into a Totals query by clicking the toolbar button that ... each sale, Sum that field, for total sales value. ...
    (comp.databases.ms-access)
  • RE: Reduce # of date columns in crosstab query
    ... "Sales by Product temp" for that product, so it won't show up no matter how I ... it from the final calculation in the crosstab query. ... the criteria out of the crosstab and into something like the original query. ... You could use the column headings to limit the number of columns; ...
    (microsoft.public.access.queries)
  • RE: Totaling Issue for Group Non-Bound Value
    ... Your group totals should be a control in the ... That will present the sum of all invoices for a territory. ... I have a report that has sales data for both individual dealers and the ...
    (microsoft.public.access.reports)