Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Reno Nevada
    Posts
    5

    Unanswered: Running Totals in Access 2000

    Hello,
    I am a noobie with Access and I have been asked by my boss to create a simple database for him to track salesmen's sales. The database needs to be able to be able to keep a runnign month to date total of sales dollars and then at the end of the year store the yearly total to be added to the next years total ie. At the end of Januaray the database reports the total for January only, but at the end of February the database reports the total for January and February, etc, etc. Then at the end of 12months the total figure needs to be kept as a running total so that the January number gets added to the Yearly total and so forth and so on. I have done this kind of thing in Exel, but it does not have the storage and flexability power that Access does. I would really appreciate any help that anyone can give me.

    Thank you,

    Joe

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Easy enough..

    This can be done with basic grouping levels and a bit of sql.

    What do your tables look like?

  3. #3
    Join Date
    Mar 2004
    Location
    Reno Nevada
    Posts
    5
    Teddy,
    Thanks for the reply. The tables are as follws:

    1) The budget table- This is used to see how close each month the saleman has come to reaching his sales goal

    BudgetID (autonumber)
    EmployeeID (linked to the REPS table)
    Budget Dollars (Currency)
    Budget Gross Margin (number)

    2) Customer Table

    CustomerID (autonumber)
    EmployeeID (linked to the REPS table)
    Customer Name (text)

    3) Monthly- Where all the sales dollar info is located

    MonthlyID (autonumber)
    EmployeeID (linked to the REPS table)
    CustomerID (linked to the CUSTOMER table)
    Date (date/time format - I have this in case Access needs it for month to date and year to date totals - just dont know how to use 'em)
    Dollars Sold (Currency - A monthly total)
    GMR - (Number - An entered number from reports)
    Margin Dollar - (Currency - An Entered number from reports)

    4) Reps - The list of sales people in the office

    EmployeeID (autonumber)
    Sales Rep Name (Text)

    Any Help you cna give me is greatly appreciated

    Thanks,

    Joe

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok... We're just looking at the sales figures here then so we can deal with Monthly and Reps. The first thing you need to do is pull your dataseurce, if I understand correctly you want to pull all of the sales figures prior to the current month?

    SELECT *, YEAR(Date) As Year, MONTH(Date) As Month
    FROM Monthly INNER JOIN Reps ON Reps.EmployeeID=Monthly.EmployeeID
    WHERE Date < #MONTH(date()) + "/1/" + YEAR(date())#

    That's going to give you a result set of everything EXCEPT the current, unfinished month.

    Then you need to set a few grouping levels.. First you want to group everything by salesmen if I understand correctly. This means your first grouping level should be by [EmployeeID]. Next you want to group and total by [Year], so that will be your second grouping level. The next grouping level will be [Month]. Set footers for both of these gruoping levels so you can pull the sums for each section using a basic "=Sum([Dollars Sold])" in a text box. You can set headers if you want to title the year/month too, that's up to you. Now to pull the total life-time sales of a given salesperson, simply add a footer to the EmployeeID grouping level and plop a sum text box in there.

    It's really not to tricky, the real quick rundown of that is I extracted the month and year of each record so I can group on them, then set the following group levels:

    EmployeeID
    ....Year
    ........Month

  5. #5
    Join Date
    Mar 2004
    Location
    Reno Nevada
    Posts
    5

    Unhappy

    Teddy,
    I am sorry for being such a noobie. I entered the code as you told me to and it is all in red and Access keeps giving me either compile errors around the astericks, end statement errors around the inner and join and reps and expression error on the number sign in the last line. I am entering the code exactly as you have it so I am not sure what I am doing wrong. I have no grounding in VB so I don't know what to modify to make it work. Sorry.

    Thanks,

    Joe

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uh.. vb?

    That was SQL for the recordsource for the report... where are you putting the code?

  7. #7
    Join Date
    Mar 2004
    Location
    Reno Nevada
    Posts
    5
    I put it in the module section. Where should I have entered it?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That should be in the recordsource for a report... you might want to get someone to physically walk you through this..

  9. #9
    Join Date
    Mar 2004
    Location
    Reno Nevada
    Posts
    5
    I found in queires where to enter SQL statements and have entered the statement and it give me a compile error ony the YEAR(Date) ad it Erases the *, Do you have an idea as to why Access would do that?

Posting Permissions

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