Results 1 to 7 of 7
  1. #1
    Acid1979 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    4

    Pivot Chart to show Number of competitor Products by month based on last updated record

    Dear Community,



    Lets say I want to monitor the number of competitor products at different customers and will update the information whenever I come home from a customer visit. This can be for some customers every few weeks, for others every few month or quarters.

    I created a table (simplified) with "ID", "Customer ", "Competitor", "Product", "Amount of Products", "last update"

    I was able to build a query showing me always the latest Record for each Customer and Product combination.

    Now I would like to create a pivot chart showing me the number of competitor products (filter options on Customer, Competitor and Product) for each month. The rule should be: always take the latest record available up until end of the specific month.

    Example 1:

    The following table
    Customer Competitor Product Amount of Products last update
    A Z Z1 5 20.02.2015
    A Z Z2 10 10.04.2015
    A Z Z1 10 01.06.2015

    Should bring the following result in the pivot Chart:

    January = 0
    February = 5
    March = 5
    April = 15
    May = 15
    June = 20
    July = 20
    ...


    In a future step I also want to add a table with a price list by competitor product. Here I would also have a "last Update" field.
    A second pivot chart should be created to show the estimated competitor revenues using the number of products in each month and the always valid competitor price in this month (following the same logic as above). Always taking the last valid price up to the last date of the month and multiplying it with the number of products

    Example 2:
    Product Price last update
    Z1 5$ 20.02.2015
    Z2 10$ 10.04.2015
    Z2 20$ 01.05.2015

    Should produce the following Pivot Chart:
    January = 0$
    February = 25$
    March = 25$
    April = 125$
    May = 225$
    June = 250$
    July = 250$
    ...


    Any ideas on how to build the pivot chart (or any queries as basis for the pivot chart) for this problems. Your help would be very much appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If there are no records for a month, no data will show, not even 0.

    By pivot chart you mean a graph, not a table? I've never used pivot chart/table. I build graph object on form or report.

    Be aware Microsoft has removed creating pivot table/chart capability from Access 2013.

    Perhaps a CROSSTAB query will get the data output you want and use that as the RowSource of a graph.

    You show dates in non-US format. Review http://allenbrowne.com/ser-36.html
    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
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you need 3 fields for pivot. Then use the query wizard to build it.

  4. #4
    Acid1979 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    4
    Dear Jun7,

    thanks for your feedback and the hint, that Microsoft removed the Pivot Table / Chart in Access 2013.Will look into the Graph option in Forms.
    The date and time in my db is in the US Format, I am just used to the other format and that is why I wrote it here, sorry for that misleading info.

    I am however still not sure / still stuck on how to build a query on my table to get the query results I want, so that it shows for every month always the latest valid information up to this point.

    Was thinking about creating another table (called "Period")with text fields for month and years as well as a Date field with the Date of the last day in this month.
    Then Building a query which links both tables with a where condition. This where condition should look for each record in the table Period for the latest record (unique in Customer, Competitor, Product combination) which is not bigger than the Date field in the Period table. The result would be a query that would have as a result something like this (still based on the example given before:

    Year Month Product Amount
    2015 January Z1 0
    2015 February Z1 5
    2015 March Z1 5
    2015 April Z1 5
    2015 May Z1 5
    2015 June Z1 10
    ... ... ... ...
    2015 January Z2 0
    2015 February Z2 0
    2015 March Z2 0
    2015 April Z2 10
    ... ... ... ...

    In the same way I could create a query with the product prices by month and year and then just multiply it out.

    Any idea on how to create this kind of query and how to use the where condition?

  5. #5
    Acid1979 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    4
    Dear Ranman256,

    thanks, I am a bit familiar with pivots and played around with them. The challenge I am facing is to prepare the data in the right way (query) so they show they way I want them to show.

  6. #6
    Acid1979 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    4

    Thumbs up

    Found a solution but maybe you still have some ideas on how to do it better or more sofisticated:

    1. I created a Query "AllCombinations" based on the Table "Products" the table "Period" (multiplying out all combinations of both tables).
    2. I created a Query "MaxAllCombinations" based on the Query "AllCombinations" grouping by "Product", "Month" and Max of "LastUpdate". Without "Amount"
    3. I created a Query "Final" based on the previous two queries "AllCombinations" and "MaxAllCombinations" creating relations between "Product", "Month" and "Last Update", now also showing the field "Amount"

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Other approach I can see would involve domain aggregate functions. So you found probably the best approach - one I have frequently suggested but often find hard to describe. Congratulations on your ingenuity!
    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: 0
    Last Post: 09-22-2014, 07:07 AM
  2. Replies: 9
    Last Post: 01-29-2013, 06:44 PM
  3. Replies: 14
    Last Post: 02-23-2012, 06:32 PM
  4. Replies: 2
    Last Post: 03-26-2011, 02:43 PM
  5. Replies: 2
    Last Post: 07-12-2010, 05:39 AM

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