Results 1 to 4 of 4
  1. #1
    BillG is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    14

    Lightbulb Inconsistent data between two years - how to get both?

    I am having a problem when data is not consistent betweentwo years to get both years to show up.
    I have a database which I query by two principle fields in onetable.
    1. One field is store number – examples – store 238 and store 101
    a. Another field is date – mm/dd/yyyy
    2. Another field for Month
    3. Another field for Year
    Conditions:
    1. Store 238 has sales in 2011, but closed in 2012therefore no sales data in table for 2012.
    2. Store 101 has sales every Wednesday in bothyears 2011 and 2012.
    a. Example: 2011- 8/4/2011 is a Wednesday but in2012 8/6/2012 is a Wednesday.
    Problem:
    1. My query crosstab has column as store number andvalue as Sales for 2012.
    2. I have another field for 2011 sales in the samequery by rows
    Store 238 and 101 do not show up in theresults.



    Store 238 does not show up because it isnot in 2012
    Store 101 does not show up because dates don’tmatch in 2011 and 2012.

    I can make two different query crosstabsone for 2012 and one for 2011 and everything shows up fine
    But when I combine into a simple query thetwo query crosstabs
    Store 238 and 101 do not show up in theresults.
    It doesn’t matter which type of join I use. Neither show up.
    The join always rules both stores out in the results due today, week, month or full date or store numbers not consistent in both years.
    My date field in the query could include full date(mm/dd/yyyy) or Month (mm) or Year (yyyy).
    One situation I got both to show up but I needed to add afields for week which excluded both stores again.
    I feel like I am 98% there in finding the solution, but justcan’t get it to work.
    I really need to figure this out because these specific two problems keep coming up over again when I do a new query to do a new report.

    HELP!!!


    .

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What tables are you joining and on what fields?

    Show the SQL statements.

    Want to provide db for analysis? 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.

  3. #3
    BillG is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    14
    I am not looking for a specific solution. I am looking for a generic solution.
    I have 20 reports some with simple queries, but most with crosstab queries.
    But the problem is the same.

    If generic, I have one table with a date, store number, sales, year field.
    I use either one crosstab query by year or two crosstab queries by store number
    both would have sales = value

    if date + store number is not in both years it eliminates both
    if sales is null in one year it eliminates that year

    I need to get date + store number + sales in both years even if one year is missing???

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The generic solution might be:

    1. Joining to a 'master' list that has all possible year + store number combinations so that even if no sales for that combination a 'blank' record will show. The 'master' list could be generated by a query.

    2. UNION queries
    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. Comparing Two years of Sales Data
    By Eric@Gopher in forum Programming
    Replies: 2
    Last Post: 08-07-2012, 12:17 PM
  2. Month wise two years data comparison
    By waqas in forum Reports
    Replies: 2
    Last Post: 07-06-2012, 08:35 AM
  3. Compare data across years
    By lvh519 in forum Queries
    Replies: 4
    Last Post: 02-16-2012, 01:46 PM
  4. Replies: 11
    Last Post: 01-25-2012, 09:46 AM
  5. Inconsistent??
    By bginhb in forum Programming
    Replies: 3
    Last Post: 09-07-2011, 03:10 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