Results 1 to 2 of 2
  1. #1
    Fabdav is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    22

    difference between weekly sales

    Hi, I have this problem and I don't know what to do:
    In a table I have the sale of a department:

    week SP Value UNIT
    N John 100 8
    N-1 John 80 6


    N-2 John 120 10
    N-3 John 90 9
    (just the last 4 weeks and N sale professional)

    I would like to have a form with
    SP Value N-1 N-2 N-3 Units N-1 N-2 N-3
    JOHN 100 20 -40 30 8 2 -4 1


    And this go on for each SP

    Any suggestion?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Look at: http://allenbrowne.com/subquery-01.html#AnotherRecord

    SELECT ID, [Week], SP, [Value], Unit,
    (SELECT TOP 1 [Value] FROM Table1 As Dup WHERE Dup.ID<Table1.ID ORDER BY Dup.ID DESC) - [Value] As ValDifference,
    (SELECT TOP 1 [Unit] FROM Table1 As Dup WHERE Dup.ID<Table1.ID ORDER BY Dup.ID DESC) - [Unit] As UnitDifference
    FROM Table1;

    Note that there must be a unique value field in the table. Also must be a value or values that can be relied on to sort the records in correct order. In my example I use an autonumber field to serve for both.

    This will get the calculations. Then this query result can be the basis of rearranging the data into across page style you want. Here is one way http://allenbrowne.com/func-concat.html
    Last edited by June7; 09-11-2011 at 09:49 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 06-30-2011, 02:24 AM
  2. Is weekly / monthly automation possible?
    By 10 Gauge in forum Access
    Replies: 4
    Last Post: 03-17-2011, 07:23 AM
  3. Weekly reporting comparisons in query
    By Schon731 in forum Queries
    Replies: 3
    Last Post: 10-21-2010, 06:59 PM
  4. Weekly calculation query?
    By katie_88 in forum Queries
    Replies: 1
    Last Post: 07-13-2010, 10:54 AM
  5. weekly total
    By nkuebelbeck in forum Queries
    Replies: 2
    Last Post: 03-24-2010, 02:59 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