Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: Creating report sections or repeating data based on a field value

    Hello, I have a question regarding how to structure my query and/or report.

    Assume I have a simple query that looks like this:

    Group Product Qty # of batches
    GroupA ProdA 100 3
    GroupA ProdB 200 3
    GroupA ProdC 300 3
    GroupB ProdA 10 2
    GroupB ProdB 20 2
    GroupB ProdC 30 2


    # of batches remains constant for each grouping. What I need is the Access Report to break the data up based on the number of batches and repeat the data that many times.

    For example, my output would look like:

    Group A ProdA 100
    Group A ProdB 200
    Group A ProdC 300

    Group A ProdA 100
    Group A ProdB 200
    Group A ProdC 300

    Group A ProdA 100
    Group A ProdB 200
    Group A ProdC 300

    Group B ProdA 10
    Group B ProdB 20
    Group B ProdC 30

    Group B ProdA 10
    Group B ProdB 20
    Group B ProdC 30


    So because Group A had 3 batches, you can see it is repeated 3 times. Because Group B has 2 batches it is repeated twice. Any ideas on how I would be able to do this? Obviously there is much more behind my example and I am simplifying it. I am intermediate with Access but have no VB or SQL skills. I am using Access 2007 and Windows 7. Any help is appreciated.

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    TBH I can't think of a way to do this without using VBA but if you wanted to use VBA you could use a temporary table and populate it with all the records you require before you run the report.
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Click image for larger version. 

Name:	Q3.png 
Views:	0 
Size:	7.0 KB 
ID:	15486

    make Q1 qry to get the values records. (like you did above)
    Q2 that has the # record in group (3 recs of GROUP A)
    Q3 that has both Q1 & Q2 but do NOT join. This will multiply the results! Group A will have 9 records! (see img)

Posting Permissions

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