Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009

    Unanswered: Sales By Customer By Item Including Zero Sales Values By Quarter Report Question

    I'm trying to create a report from sales data that has been imported into an Access MDB file. As stated in the posts title this report is to show sales by customer by item *including* zero sales values.

    So, basically I need to show all customers (from the customer table) and all items (from the item_inventory table) and I need to sum the sales (held in the invoice and invoice_line tables) of each item that has been sold for each customer and put a zero value for each item they did not buy in a given quarter.

    Simplified to show just the basic data I need the tables look like this:


    Customer_Ref_Full_Name_List_ID (related to List_ID in Customer table)
    ID_Key (links to List_ID in invoice_line table)

    Item_Name_Ref_List_ID (List_Id from item table)


    I need, for each customer, one entry in a temp table (to report off of with Crystal Reports) for each inventory item in the inventory table and for each inventory item either the number sold to that customer or a zero.

    The problem is that I want to do this in straight SQL and there appears to be while loop in MS Access's implementation of SQL like there is in transact SQL (and no I can't move the data to another database I have to do it in Access).

    I *think* the solution lies in what I hear referred to as set-based queries and I think I already do that to some extent (massage the data through a series of queries that build on one another) but I'm not quite sure how to progress with this query.

    How do I get all items for all customers (for *each* customer) without a while loop? is it even possible?

    I've been trying to think of an intermediate way of linking the customers tot he items but there is no real relationship and that seems kind of messy to me but I'm not sure how else to get from here to there.

    I hope I have explained this wll enough that it makes sense. (It is giving me a head ache I have been thinking about it so long already).

    So the output should look similar to this:
    Customer1 Name <this is group level one>
    Item1 Number sold <or 0 if none sold> dollar amount <for all sold>
    Item2 Number sold <or 0 if none sold> dollar amount <for all sold>
    Customer2 Name <this is group level one>
    Item1 Number sold <or 0 if none sold> dollar amount <for all sold>
    Item2 Number sold <or 0 if none sold> dollar amount <for all sold>

    This is simplified but if I can just get past the how do I include the items *not* sold (since they are not in the table witht he sales data) issue I can work the rest out.

    I tried to search to see if anyone had a similar issue posted already but the search here returns records for all the forums instead of just this one and I got tired of trying to sift through them all.

    TIA for anyone who can help me out on this one.


  2. #2
    Join Date
    Mar 2009


    I thought maybe I could do this with a Union but I don't think I can...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts