Results 1 to 6 of 6
  1. #1
    anunat is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    19

    Query Question

    i have a table for example:

    id sales filedate
    1 500 6/1/2012
    2 200 6/1/2012


    .
    .
    1 400 5/1/2012
    2 175 5/1/2012
    .
    .
    1 300 4/1/2012
    2 180 4/1/2012
    .
    .

    My table will not have any dates other than the first day of a month.
    I would like to find the difference between the sales for the same id numbers for the dates 5/1/2012 and 6/1/2012 and show the following: id sale diff

    How do I do this?

    Note: My actual table has 24 months of sales data as columns for each date.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You need to pull a value from another record. Requires subquery. For one method review http://allenbrowne.com/subquery-01.html

    Another way is query with a constructed field that calculates the filedate minus 1 month. Then join this query to the original table on the unique ID. This will put the the sales data on same row.

    However, with either method, complication with your data is unique ID. Looks like will require compound identifier of ID/filedate.
    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.

  3. #3
    anunat is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    19
    The subquery link you provided really helped. I was able to get the results I wanted. Thank you!

    I have another related question:

    I am trying to use a column derived in the select statement in the where clause. I gathered online that I should use a nested select statement.

    SELECT *
    FROM
    (SELECT Main.POD, Main.DwId, Main.Product, Main.ps02 AS May2012, Main.ps02 - (SELECT TOP 1 Dupe.ps01 FROM Main AS Dupe WHERE Dupe.POD = Main.POD And Dupe.DwId = Main.DwId And Dupe.Product = Main.Product And Dupe.Dte = Forms!RestatementForm!compdate ORDER BY Dupe.DwId) AS DiffMay2012 FROM Main WHERE (Main.Dte=DateValue(Forms!RestatementForm!Focusdat e)) AS data
    WHERE data.
    DiffMay2012=1;

    If I did not do the nested statement, my results show fine. When I include the nested select statement to check if data.DiffMay2012=1, it filters the data correctly. However, DiffMay2012 column is completely blank for all rows while all other columns are what they should be.

    I can't understand what I am doing wrong here. Please help!

    If this is not the right way, then please suggest a way to use a derived column name in the where clause.



  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I am surprised this works at all. I see an odd number of parens. Should be in pairs. Put another closing paren before AS data or remove the opening paren in front of Main.Dte=.
    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.

  5. #5
    anunat is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    19
    I did have another closing parenthesis. I have several other fields. While I was removing them to post the question, I must have removed the parenthesis.

    I am still not sure why the field DiffMay2012 column disappears when I do the nesting

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Could be because when a field constructed by expression is also used in WHERE clause, the expression must be in the clause, not the alias fieldname. Example:

    SELECT *, Year(DateRecd) & Month(DateRecd) AS YrMo FROM tablename WHERE Year(DateRecd) & Month(DateRecd)=[tbxYear] & [tbxMonth];

    Maybe this carries through the alias tablename. I don't recall ever trying this query structure. If you want to provide data for testing, follow instructions at bottom of my post.
    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. Sub-query question
    By hklein in forum Queries
    Replies: 1
    Last Post: 05-01-2012, 03:02 PM
  2. query question
    By John Elway in forum Queries
    Replies: 5
    Last Post: 11-09-2011, 09:58 PM
  3. vba query question
    By jscriptor09 in forum Programming
    Replies: 2
    Last Post: 10-08-2011, 07:22 PM
  4. Query question
    By j2curtis64 in forum Queries
    Replies: 8
    Last Post: 07-29-2011, 01:45 PM
  5. Query Question
    By starhannes in forum Queries
    Replies: 13
    Last Post: 05-06-2010, 04:05 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