Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    How to create a Grouped report Like Excel

    Hi Guys,



    I am working on a simple stock database. Copy of the Database attached for immediate reference. Also Image of Excel Sheet Attached, Like which the data presentation is required.

    I would like to create a grouped report just like it we have in Excel. I have created two queries qryYarn & qryTani. Both queries have all the data I need to be displayed on Report. What I really Want is (hope I am able to explain this), Like for the first entry shown in qryYarn i.e.

    Date Supplier Product SumOfQty
    31-03-2018 ---Stock---5048 polyester---3758.78

    I want to display in report this as first line and it should be followed with entries from qryTani for the same product, but Total of TaniWeight should not exceed SumofQty. As soon as the total of TaniWeight is exceeding SumOfQty Next record from qryYarn should be displayed and again entries from qryTani should continue based on same criteria as above.

    Hope I have been able to explain my problem. Looking forward to your help and guidance.
    Thanks and Regards
    Deepak Gupta
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Did you use the report wizard?
    it will ask you to group items. You can put sums in the groups.

  3. #3
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Ranman256,

    As explained earlier, I have two queries which Have product as common but are not connected with each other in any way. Please refer my attached database.

    Also, yes tried to use the report wizard, when adding both reports it says the you are trying to add data which not connected. Something like that.

    Please, kindly look into the data and then I think you will be able to understand what i am trying to achieve.

    Thanks and Regards
    Deepak Gupta

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    This isn't the complete solution but it should help you get there

    1. Tick the Show checkbox in qryYarn
    2. Make a new aggregate query joing qryYarn & qryTarn as follows:
    Code:
    SELECT DISTINCT qryTani.Date, qryTani.Type, qryTani.PrdName, qryTani.BeamSize, qryTani.Rounds, qryTani.Ends, qryTani.NumberOfBeams, qryTani.BeamWeightLoaded, qryTani.BeamWeightEmpty, qryTani.BeamWeight, Sum(qryTani.TaniWeight) AS SumOfTaniWeight, qryYarn.SumOfQtyFROM qryTani INNER JOIN qryYarn ON (qryTani.Date = qryYarn.PurchaseDate) AND (qryTani.Type = qryYarn.Type)
    GROUP BY qryTani.Date, qryTani.Type, qryTani.PrdName, qryTani.BeamSize, qryTani.Rounds, qryTani.Ends, qryTani.NumberOfBeams, qryTani.BeamWeightLoaded, qryTani.BeamWeightEmpty, qryTani.BeamWeight, qryYarn.SumOfQty
    HAVING (((Sum(qryTani.TaniWeight))<=[SumOfQty]));
    Adjust as appropriate for your needs then use to create your report

    BTW As there are a lot of identical tables in your 2 queries you could probably get the same result in one query
    I'll leave that to you
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Ridders52,

    Thanks for the help, but sorry I am not able to get results out of this.

    1. Tick the Show checkbox in qryYarn - I presume you mean in qryYarn tick Show on Type. I did this.
    2. Secondly I created a new query with your SQL Statement (Copy Pasted it) - It gives me only 5 record hits, where as desired result should give 58 records.

    Hope I have been able to explain my point of view.
    Looking forward to your help.
    Thanks and Regards
    Deepak Gupta

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not at my computer at the moment. Try removing DISTINCT from the code I supplied

  7. #7
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    I don't know where I am making a mistake. PLease find attached copy of my atempt maybe that helps figuring out where I have made a mistake.

    Regards
    Deepak Gupta
    Attached Files Attached Files

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ok my guess didn't work but as I said I was away from my computer.
    I'm not clear why you say there should be 58 records and therefore am not sure what to suggest.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Because I counted the Records and that is the number , any way I could be wrong some where but Quantity needs to add up and that can add up & there could not be 5 records hence I am sure it surely not doing what is required. PLease help.

    Thanks and Regards
    Deepak Gupta

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry Deepak but as I don't understand where that number comes from, I can't suggest what the query should be.
    But its your database & you do understand your data so hopefully you can solve it yourself with my earlier prompt

    I'll drop out now & perhaps someone else will suggest something.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    thanks for your help

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Deepak,

    Are the dates in the two queries related? Meaning could we link the two queries by product and date?

    Cheers,
    Vlad

  13. #13
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Gicu,

    Thanks for checking out the post.

    No dates are not related. Product and quantity is the best criteria to make it work, but yes Date inward (stock or purchase) will always come first and then followed by the product issues (Outward). Sometime purchase comes before we sell, on similar principal I was wondering if we can use quantity in place of dates. Like FIFO basis, lot once completed then comes the next lot.

    Hope I have been able to explain.
    Thanks and Regards
    Deepak Gupta

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the attached and see if that is what you wanted. Note that the last entry didn't make it in the temp table because it is bigger than both individual purchases.

    Cheers,
    Vlad
    Attached Files Attached Files

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, I forgot to mention that I added a button on the frmPurchase form to run the code and open the report (you'll need to work on the report to make it look like you want).

    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-10-2016, 11:55 AM
  2. Replies: 7
    Last Post: 01-19-2016, 05:28 PM
  3. Replies: 11
    Last Post: 11-06-2014, 06:01 PM
  4. Replies: 2
    Last Post: 08-25-2010, 01:42 PM
  5. Create Report in a Matrix Format Like Excel
    By ortley77 in forum Reports
    Replies: 1
    Last Post: 08-24-2010, 09:56 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