Results 1 to 2 of 2
  1. #1
    rzawora is offline Novice
    Windows 7 Access 2007
    Join Date
    Oct 2009
    Posts
    1

    Query help for WTD and MTD values.

    What I have is a database with sales information and labor information. I have a simple calculation figuring out the daily labor cost % (labor $ / sales $). They would like to see a WTD (Week to Date) and MTD (Month to Date) labor cost on the same report. The company's week runs from Friday to Thursday. I'm stuck on what I need to do to get this to total the sales and labor for WTD or MTD so I can have it figure out the % and put it on the report. I'm pretty sure it will need to be done in VBA which I have limited experience in. I'm not a professional so please bear with me, I appreciate any help anyone can give me.



    WTD- this would need to figure out the sales $ total and labor $ total from previous Friday to current date and divide the labor by the sales to give me a percent

    MTD- this would need to figure out the sales $ total and labor $ total from the 1st of the month to current date and divide the labor by the sales to give me a percent

    Thanks again everyone.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    You first need to tell Access which day of the week is the first day. You can do this in your query when grouping by transaction date.

    Format([YourDate],"ww",vbFriday,vbFirstFourDays)

    This tells Access that the first day of your week is Friday. If you then group by this field you should get a sumarry breakdown. My only concern is when the month changes how do you propose to define what is current and what is not.

    David

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Null Values in query
    By LesleaOH in forum Queries
    Replies: 0
    Last Post: 10-19-2009, 04:45 PM
  2. Query multiple values in a field
    By JAYgarti in forum Access
    Replies: 0
    Last Post: 07-09-2009, 09:52 AM
  3. How to query boolean values from table
    By kevdmiller in forum Queries
    Replies: 2
    Last Post: 11-30-2006, 07:41 PM
  4. Null Values not showing up in a Query
    By Valli in forum Queries
    Replies: 0
    Last Post: 01-04-2006, 03:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums