Results 1 to 11 of 11
  1. #1
    RAJESHKUMAR R is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    48

    Thumbs up Compare Two Crosstab Query and Find Delta Qty With Respect to Rows & Column Reference!

    Dear Experts,



    Need your valuable inputs on my subjected Query!

    I have two Crosstab Query 1. FirmOrder_Crosstab & 2. Forecast_Crosstab


    1. FirmOrder_Crosstab



    1. First Column Contains Product Details
    2. Consequent column contains Month Code (MMM-YY Format) in 1ST Row and Firm Requirement (Qty) by each product in Value Field respectively



    1. Forecast_Crosstab
      1. First Column Contains Product Details
      2. Consequent column contains Month Code (MMM-YY Format) in 1st Row and Tentative Requirement (Qty) by each product in Value Field respectively


    Concerns:


    1. Product & Month Code are not in Same Row & Column sequence in above Crosstab Queries, that is….
    2. Some of the Product which is not listed in 1. FirmOrder_Crosstab Query may be available in 2. Forecast_Crosstab Query and Vice versa
    3. Similarly, some of the Month Code which is not part of 1. FirmOrder_Crosstab Query may be available in 2. Forecast_Crosstab Query and Vice versa


    Support Required is: -


    1. From above two Quires, I would like to find out a delta Qty (Value Field) by Product & Month Code.
    2. Expected Result must be in same as above Crosstab Query Format i.e. Product in First column and month code in Column and Delta Qty in Value Field


    Note:
    I am a New User to MS Access, Hence; Requesting you to provide your suggestion with more details (which any New User like Me) can understand, so that it will help me a lot to implement in my file immediately!

    Looking forward your support!

    Thanks & Regards,
    Rajeshkumar R

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need a dataset of all possible product and month combinations. Does db have multi-year data? Do you have Date/Time fields you are extracting the month value from? Probably need to include year as well.

    This dataset can be created with a UNION query.

    SELECT Product, MonCode FROM ORDERS
    UNION SELECT Product, MonCode FROM Forecast;

    Build another query that joins the UNION query to the two CROSSTAB queries joining on the Product and MonCode fields in each table.

    Post samples of raw data if you need more help.
    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
    RAJESHKUMAR R is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    48
    Dear,

    Thanks for prompt response my Request!


    Need a dataset of all possible product and month combinations. ---> I have just attached a sample Data Base!

    Does db have multi-year data? --->Yes!

    Do you have Date/Time fields you are extracting the month value from? Probably need to include year as well --->Yes, I do have Date Field

    Post samples of raw data if you need more help.

    Attached DB has three table (Table1 is Outcome of my Firm Orders_Crosstab, Table 2 is Outcome of my Forecastorders_Crosstab and Table 3 is I just manually updated and this is the result which I am expecting)

    Note : Though attached lists contains only few product details, my live DB contains 2500+Products with last 3+ Years and future 1+ Year (Month Code) Data in it!

    Thanks & Regards,
    Rajeshkumar R
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you attempt anything like I suggested?
    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
    RAJESHKUMAR R is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    48
    Hi,

    I am Sorry, Due to my Lack of knowledge in Access (Since I am beginner Level), I could't able to proceed as per your suggestion!

    Need your extended support to accomplish my subjected issue!

    Thanks & Regards,
    Rajeshkumar R

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, UNION queries may be a bit daunting because there is no designer or wizard for them. Have to switch to SQL and just type them (or copy/paste). So do that and follow the example I provided in post 2 with a little adjustment to include field for year.

    Review https://www.w3schools.com/sql/sql_union.asp

    Your db does not include raw data. How can I design queries without raw data?
    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.

  7. #7
    RAJESHKUMAR R is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    48
    Hi,

    I created Union Query as per your suggestion, It worked well for Getting Product (Union Lists)!

    But, I am unable to get Union output of Different Months (MMM-YY) which is there in First Row, Multiple Column!

    Also, How to find Delta of Forecast Vs. Firm Orders, By Product and Month?

    Yes, I do understand; My db doesn't have Raw Data, Because, I am unable to load attachment which is more than 500KB!

    Thanks & Regards,
    Rajeshkumar R





  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Per instructions at bottom of my post, zip file of 2MB is allowed.

    At least post the SQL statement for analysis if you want help with it.

    Find Delta by joining both crosstabs to the union then expression subtracting fields.
    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.

  9. #9
    RAJESHKUMAR R is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    48
    Dear,

    Thanks for your clarification! Here, I have attached the complete file with Raw Data!

    Table: -

    FirmOrder & Forecast. Also “Expected Result” Only for Reference!

    Crosstab Query

    FirmOrder_Crosstab & Forecast_Crosstab

    Support Required on “Delta Forecast Vs. FirmOrders_Crsstab”. From your input I could able to get Union lists of “Product”,


    1. How to get a Union lists of CRD Month (Note: Here Month Code is I am extracting from Raw Data CRD Column with EOMONTH from Excel). So, Month Code is nothing but End of the Month CRD Date
    2. I would request to build the SQL code which refers Product, EOMONTH (Column) from both crosstab Query and make a delta by individual Product and EOMONTH.


    Looking forward your valuable Input and possible suggestion!

    Thanks & Regards,
    Rajeshkumar R
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why bother with the [CRD Month] field? The month/year can be calculated from CRD field. Setting format property for a field in table does not alter the data. Even though [CRD Month] is formatted for month/year the full value is still mm/dd/yyyy. I never set formatting in tables.

    Assume you want to calculate: Forecast - Firmorder

    Taking a different approach. Consider:

    Query: Fore_Firm_UNION

    SELECT Product, Qty, CRD, Format([CRD], "yyyy_mm") AS YrMo, "FO" AS Cat FROM Firmorder
    UNION ALL SELECT Product, Qty*-1, CRD, Format([CRD], "yyyy_mm"), "FC" FROM Forecast;

    Query:

    TRANSFORM Sum(Fore_Firm_UNION.Qty) AS SumOfQty
    SELECT Fore_Firm_UNION.Product
    FROM Fore_Firm_UNION
    GROUP BY Fore_Firm_UNION.Product
    PIVOT Fore_Firm_UNION.YrMo;
    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.

  11. #11
    RAJESHKUMAR R is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    48
    Thank You So Much for a Simple & Awesome solution! It works amazingly!Thanks & Regards,Rajeshkumar R

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Crosstab query showing duplicate rows
    By Aliasjeffturner in forum Queries
    Replies: 3
    Last Post: 04-18-2017, 07:10 AM
  2. Replies: 3
    Last Post: 12-21-2015, 06:55 PM
  3. Showing Columns & Rows in Crosstab query
    By coach32 in forum Queries
    Replies: 6
    Last Post: 09-11-2011, 07:01 PM
  4. Replies: 1
    Last Post: 01-24-2011, 03:03 PM
  5. Replies: 7
    Last Post: 09-24-2010, 07:23 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