Results 1 to 7 of 7
  1. #1
    AlienV is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    4

    Should I create a table with empty placeholders to get full output.

    Hi Everyone,

    I am a noob so please be patient with me! My query needs to show weekly sales and targets by store and product class. I need to show all stores whether or not it has a target. My sales table will not have an entry if a store/week/class combo does not exist (i.e. Instead of a sale of $0). My Target table similarly has entries for which there are targets but not every store.

    Problem
    An outer join on either presents a problem. I cannot left Join on Sales because there is no entry for that store/class if its $0 sales. I cannot left join on Targets because it will eliminate all stores on the output for which there are no targets.



    Possible Solution?
    It seems I would have to create a placeholder of $0 entries for every week/store/class combo in my Target table. I am scrapping this together from ill-formatted spreadsheets (always fun!). Is this reasonable?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is one approach.

    Another is to have a dataset of every possible combination of store and week and class. Then join to the data tables.

    That dataset can possibly be generated with:
    SELECT Stores.*, Classes.*, Weeks.* FROM Stores, Classes, Weeks;

    Because there is no JOIN clause, every record in each table associates with every record of other table(s) - a Cartesian relationship.
    Last edited by June7; 09-23-2017 at 07:30 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.

  3. #3
    AlienV is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    4
    Thanks for the quick reply. I will process this at newbie speed and attempt to apply and see what happens. When I experiment I am always unsure that the results are what I expect (i.e. multiples of values).

  4. #4
    AlienV is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    4
    Thinking about this the cartesian would confound my data. I'm only using two tables at the moment which are built up from subqueries. I'm joining on week, class, store. How would a DBA typically build out a table when they need placeholders as for me below with the lack of class/target by week for every store?


    SALES
    Week
    Store
    Product Class
    Actual Sales


    Target
    Week
    Store
    Product Class
    Target Sales

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your options are:


    1. dummy records as you already identified


    2. Cartesian relations

    Dataset of all combinations of store/class/week (note correction to my earlier post). This dataset can then join to Sales and Targets tables on store/class/week fields.

    Join type of "All records from StoreClassWeek and only those from {Targets/Sales} that match".

    Do you have tables for stores, classes, weeks?


    3. expressions in report textbox using DSum domain aggregate function - I have done this to make sure a report had output for every month, this meant 12 textboxes arranged vertically


    4. VBA code writes data to a 'temp' table (table is permanent, data is temporary)
    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.

  6. #6
    AlienV is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    4
    Thanks!
    I created 3 queries on a single fields of each table: Store, Calendar (Week), Class
    Then put them all in a cartesian query
    Joined the result of the last with my existing table matching on week,class,store
    Shazam!

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So you do have 3 tables Store, Calendar (Week), Class? Glad you got it working.

    But possibly the 3 intermediate queries are not needed.
    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: 02-19-2017, 03:19 PM
  2. Replies: 8
    Last Post: 04-17-2012, 01:37 PM
  3. Replies: 4
    Last Post: 01-05-2012, 08:31 PM
  4. Full App - Create Your Own Training Quizzes
    By pkstormy in forum Sample Databases
    Replies: 0
    Last Post: 04-24-2011, 10:19 AM
  5. Replies: 4
    Last Post: 10-03-2010, 09:54 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