Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24

    Creating Report: Size Curve

    Hello,

    First, Iīm very new to Access - but it already took several hours of my life which I canīt get back ;-)
    I thought maybe someone in this community could give me a hand, as I am sure my problem is not a major one.

    I have a table which stock information - in particular size information for shoes. This information is comes from an ERP system in Excel format.

    What Iīd like to do is to display this information via a report. Of course, itīs very basic and not sophisticated, but this shall change in the future. More later.

    What Iīd like to achieve is basically this (I only included basic information):

    1. Letīs say, I got a product ABC which is displayed in the excel Sheet as follows:

    Product Name / Product Number / Size / Stock
    ABC / 12345 / 6 / 2
    ABC / 12345 / 7 / 3
    ABC / 12345 / 8 / 4

    2. This data is uploaded into a table in Access.
    3. Now, if Iīd just create a very basic query showing my all information from this table, the information are displayed as shown above.
    4. Next, if I create a report based on this query, the information are displayed top to bottom, of course (as I figured out) based on how you design/arrange your report.

    Question here: How can I generate a report which:

    a) Lists the items in horizontal fashion and not vertical
    b) and would add automatically new "fields" at the end as long as data is available?

    To make it clearer, it shall look like this:



    Header:
    Product Name: ABC
    Product Number: 12345

    Body:
    Size: 6 / 7 / 8
    Stock: 2 / 3 / 4

    In the end, Iīd like to have a quick overview of which sizes I currently have in stock for each product in horizontal fashion, not vertical as this would allow me to save a tremendous amount of paper ;-) Of course, for several products Iīd like to have it in this fashion:

    Header:
    Product Name: ABC
    Product Number: 12345

    Body:
    Size: 6 / 7 / 8
    Stock: 2 / 3 / 4

    Header:
    Product Name: DEF
    Product Number: 67890

    Body:
    Size: 6 / 7 / 8
    Stock: 1 / 2 / 3

    and so on...

    I hope my explanation was clear enough. If not, please donīt hesitate to ask.

    Thanks and regards,
    Mike

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    there are two possible solutions, both involve modifying your rowsource. One is to use the concatrelated function (see here http://allenbrowne.com/func-concat.html ) once for size and once for stock. The other is to use a crosstab query with name and number as row headings, size as a column heading and quantity as stock.

    If your data is

    Product sizes
    1...……...4,5,6,8
    2...……….4,5,7,9


    the former will present columns in a single field as above - i.e. looking much like above and as you can see the sizes will not line up beneath each other

    the latter will provide separate columns, 4,5,6,7,8,9 and the quantities displayed below - leaving sizes without stock blank

  3. #3
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Ajax, fantastic!
    I used the crosstab query and it was even too easy! Unbelievable!
    And it works - without any issue, exactly what I was looking for.

    You definitely made my day! Thanks!

    /Edit: Now, my next question would be: Letīs say I have two rows selected as my basis: Location and Product. How would it be possible to letīs say have a report for each location listing all products in the fashion via crosstab query? That would save my day!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I think Ajax meant 'recordsource' instead of 'rowsource'.

    Getting the sizes and quantities to line up with the ConcatRelated function can be done. Have to decide how many spaces to set fixed column widths. I used 10.

    Query calling the function:
    Code:
    SELECT Products.ProductName, Products.ProductNumber, ConcatRelated("String(10-Len([Size]),' ') & [Size]","Products","ProductNumber='" & [ProductNumber] & "'","Size"," /") AS Sizes, ConcatRelated("String(10-Len([Stock]),' ') & [Stock]","Products","ProductNumber='" & [ProductNumber] & "'","SIze"," /") AS Stocks
    FROM Products
    GROUP BY Products.ProductName, Products.ProductNumber, ConcatRelated("String(10-Len([Size]),' ') & [Size]","Products","ProductNumber='" & [ProductNumber] & "'","Size"," /"), ConcatRelated("String(10-Len([Stock]),' ') & [Stock]","Products","ProductNumber='" & [ProductNumber] & "'","SIze"," /");
    Be aware query can perform slowly with large dataset.

    Building a stable report based on CROSSTAB query is tricky. Another tutorial from Allen: http://allenbrowne.com/ser-67.html#ColHead

    In ConcatRelated query, Location would be another field for grouping. It would have to be in the SELECT and GROUP BY as well as in the function WHERE argument. In CROSSTAB, Location would be another row heading.
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I think Ajax meant 'recordsource' instead of 'rowsource'.
    oops

  6. #6
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Quote Originally Posted by June7 View Post
    ....
    Thanks for your input!

    Regarding the location, what Iīd like to achieve is the following:

    Now, the crosstab query works wonders and gives me exactly what I need.
    Next, my idea would be the following: The page header includes the location, the Detail section each product with sizes/stock based on the crosstab query.
    For each location, a new page should be created.

    Would this involve creating a sub-report or can it be done within one report?

    Thanks and regards,
    Mike

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can use the same report, just group by location and place the location details in the location header section.

  8. #8
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Quote Originally Posted by Ajax View Post
    you can use the same report, just group by location and place the location details in the location header section.
    Thank you - I tried that, but somehow the report wonīt make the switch to the next page when the location changed.

    Thatīs how it is currently:

    Click image for larger version. 

Name:	Tables.JPG 
Views:	18 
Size:	29.7 KB 
ID:	35782

    The report would look like this:

    Click image for larger version. 

Name:	Report.JPG 
Views:	18 
Size:	95.9 KB 
ID:	35783

    Thanks and regards,
    Mike

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Location must be a GROUP in report design, not just in query.

    You have not set up any GROUPING in report design. https://www.youtube.com/watch?v=oRRnyxfeQv4 and http://ms-access-tips.blogspot.com/2011/12/adding-group-and-sort-to-access-report.html

    Set ForceNewPage property to yes in the GROUP footer. Downside is the REPORT footer will end up on page by itself.
    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.

  10. #10
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Thanks - now it is working!

    Now, all my data for the report comes out of the crosstab query. What if Iīd like to a) use the location code from the crosstab query to b) lookup additional information in the location master table to c) add this information to the report (letīs say address of the location in the report header).
    Could I just "extend" the query?

    Many thanks,
    Mike

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Can:

    1. do a SELECT join of the two tables, then switch to CROSSTAB, every additional field associated with the location would have to be another RowHeading

    or

    2. build another SELECT query that joins the CROSSTAB to the other table and use that as the report RecordSource

    or

    3. DLookup in textboxes
    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.

  12. #12
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Many thanks - Iīll try that!

    Mike

  13. #13
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    First, thanks again for your help.

    I tried it with option 1 and it did work - so far so good.
    Now, each location lists the stock for all products available.
    I got another item which indicates the product group: Either ladies shoes or men shoes.
    My goal is to divide the report by each product group, but keep the location as the main indicator.
    It should look like this:

    Group_Header_1:
    -Location_1
    -Product_Group_1
    -Products
    Group_Header_2
    -Location_1
    -Product_Group_2
    -Products

    Any clue how someone might be able to achieve this?
    Define the "Product_Group" as an additional row?

    Thanks and regards,
    Mike

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Try it and let us know.
    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.

  15. #15
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    I did some testing, but encountered an other issue regarding the join.
    I spotted it while checking some stock information for one of the locations and discovered that the item count was much higher than it should be.

    If I do a very plain crosstab query, with 2 row headings, I get the desired results:

    Click image for larger version. 

Name:	Crosstab.JPG 
Views:	10 
Size:	35.7 KB 
ID:	35824
    From left to right (Sorry, in Japanese):
    Location Name, Product Code, Size Code, Stock, Total Stock

    The result looks like this (correct):

    Click image for larger version. 

Name:	Amazon.JPG 
Views:	10 
Size:	30.1 KB 
ID:	35825
    The location lists all the products + each stock for each size.
    Works as expected.

    But now, as explained before, Iīd like to extend this data.

    June7, what I did next was that I 1) created a basic query to get all my information I require from 3 different tables. 2) I did a crosstab based on the basic query:
    Click image for larger version. 

Name:	Standard Query.JPG 
Views:	10 
Size:	61.9 KB 
ID:	35828

    From left to right: Location name, Product Name, Size code, Product Code, Shop Manager, Stock, Total Stock, Link (to product picture),

    I checked the results in the report and got this:

    Click image for larger version. 

Name:	Amazon2.JPG 
Views:	10 
Size:	52.6 KB 
ID:	35829
    Somehow the total numbers as well as those for each size are not correct any more. For "MILA7400", we only got 4 in stock, not 36.

    I didnīt spot it in the fist place as we got items with higher stock amounts, but in this case the numbers just wonīt end up.

    So, my guess would be that somehow the initial query or the crosstab is not correct.

    Regards,
    Mike
    Attached Thumbnails Attached Thumbnails Standard Query.JPG  

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

Similar Threads

  1. Replies: 0
    Last Post: 07-27-2018, 02:17 PM
  2. Replies: 6
    Last Post: 05-25-2018, 09:53 AM
  3. Replies: 1
    Last Post: 08-16-2015, 01:41 PM
  4. Report Size
    By littleheart_Sony in forum Reports
    Replies: 1
    Last Post: 01-09-2015, 12:12 AM
  5. Max Report Size
    By Robert W in forum Reports
    Replies: 7
    Last Post: 09-05-2010, 12:13 PM

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