Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: Sales Report formatting

    Every week, I generate a sales report detailing sales by salesperson for the week-to-date, month-to-date and year-to-date.


    All sales contracts are entered in a field called “week_number”. Week to date is the current week, in this example, I’ll use Week 3. Month to Date would be Weeks 1, 2 and 3. Year to date would be all contract amounts entered for the current fiscal year, in this case it’s also Weeks 1, 2 and 3 (it’s the beginning of our fiscal year). I have the query currently formatted to and it's only providing a specific week and it looks like this:

    Week Salesperson ContractPrice
    TableName Table Name Sum of contracts


    The report should end up looking like this:

    Salesperson Week-To-Date Month-To-Date Year-To-Date
    Salesperson #1
    Salesperson #2
    TOTAL


    Is there a way to accomplish this goal? Thanks in advance for any responses.

    Angela

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yup
    you have two choices
    you either marhall the data using a query as a feedstock to the report, You woudl probably have to do the year to date bit as as a sub query

    or you issue a series of dlookups or dsum's in the report and set thise as part of the controls data source or call them as part of the report detail or group footer events.

    if you choose to use dsum /dlookups you will have performance issues.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Posts
    265
    I would create a Week Table so that each Week is assigned a Period in this case the Month and also the Year.

    Your Selection criteria can then look at the Year Month and Week.

    Simon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •