Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820

    Sounds like you have included another table that is another n-to-many relationship. Sorry, don't know your database and language barrier doesn't 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.

  2. #17
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Thanks - I´ll try to investigate a bit more.
    Just one question: You mentioned the following step: "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"

    Would this also work for three tables?

    Regards,
    Mike

  3. #18
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    I did some testing: If I have only two tables, Crosstab works as expected. I add one more table, and it messes everything up.

    My overall goal is the following:

    I have stock information in one table and product as well as location information in two others.

    The stock information table is the basis for my crosstab, but I´d need to add some additional information in the report such as English Location name (from location master) or English product name (from product master).

    No solution yet, but I´ll keep testing.

    Regards,
    Mike

  4. #19
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    I did some more testing.
    If I only use my stock report table together with the location master table and put a Crosstab query on them, everything works as it should.

    Now I try the same with the stock report and product master. Again I created the Crosstabab but this time, my stock values are messed up.
    Somehow the relation between both tables seems not to work as it should. I did as June7 described: Do the join on two tables, switch to Crosstab and add each additional information via row heading.

  5. #20
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    It should work with additional tables if those are lookup tables (which was the case with location as you wanted more info about location such as address), not if they are dependent tables in n-to-many relationships with master.
    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.

  6. #21
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Thank you.
    How can I define a lookup table?
    To me, both the location table and product table were master tables.

    Regards,
    Mike

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest show your relationships for the relevant tables. Due to the language issue highlight the fields being used in the query. Then also show the sql to your query

  8. #23
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Thanks!
    So, here we go:

    1.) The very basic crosstab looks like this:
    Click image for larger version. 

Name:	Basic_Query1.JPG 
Views:	9 
Size:	28.5 KB 
ID:	35832
    From Left to right: Location Code, Product Code, Size Code, Stock
    The SQL would look like this:

    Code:
    TRANSFORM Sum([20181015_Stock_Report].前月末在庫) AS SumOf前月末在庫
    SELECT [20181015_Stock_Report].倉庫コード, [20181015_Stock_Report].商品コード
    FROM 20181015_Stock_Report
    GROUP BY [20181015_Stock_Report].倉庫コード, [20181015_Stock_Report].商品コード
    PIVOT [20181015_Stock_Report].[サイズコード];
    Result: Works as expected. Each size cell lists the correct stock value.

    2) Now the extension with the location table:
    Click image for larger version. 

Name:	Basic_Query2.JPG 
Views:	8 
Size:	64.7 KB 
ID:	35834
    From left to right: Location Code, Product Code, Size Code, Stock, Location Name (ENG)
    Code:
    Code:
    TRANSFORM Sum([20181015_Stock_Report].前月末在庫) AS SumOf前月末在庫
    SELECT [20181015_Stock_Report].倉庫コード, [20181015_Stock_Report].商品コード, [20181015_Location_Master].Name_ENG
    FROM 20181015_Stock_Report INNER JOIN 20181015_Location_Master ON [20181015_Stock_Report].倉庫コード = [20181015_Location_Master].Location_No
    GROUP BY [20181015_Stock_Report].倉庫コード, [20181015_Stock_Report].商品コード, [20181015_Location_Master].Name_ENG
    PIVOT [20181015_Stock_Report].[サイズコード];
    Result: Works as expected. Each size cell lists the correct stock value:
    Click image for larger version. 

Name:	Basic_Query5.JPG 
Views:	8 
Size:	31.7 KB 
ID:	35837

    3) Now the extension with the product table instead of the location one:
    Click image for larger version. 

Name:	Basic_Query3.JPG 
Views:	8 
Size:	73.1 KB 
ID:	35835
    From left to right: Location Code, Product Code, Size Code, Stock, Product Name (ENG)
    Code:
    Code:
    TRANSFORM Sum([20181015_Stock_Report].前月末在庫) AS SumOf前月末在庫
    SELECT [20181015_Stock_Report].倉庫コード, [20181015_Stock_Report].商品コード, [20181015_Product_Master].他品番
    FROM 20181015_Stock_Report INNER JOIN 20181015_Product_Master ON [20181015_Stock_Report].商品コード = [20181015_Product_Master].商品コード
    GROUP BY [20181015_Stock_Report].倉庫コード, [20181015_Stock_Report].商品コード, [20181015_Product_Master].他品番
    PIVOT [20181015_Stock_Report].[サイズコード];
    Result: Each size item has an incorrect value:
    Click image for larger version. 

Name:	Basic_Query4.JPG 
Views:	8 
Size:	33.6 KB 
ID:	35836

    Thanks and regards,
    Mike

  9. #24
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK - I can see a potential number of issues - your joins.

    for example

    FROM 20181015_Stock_Report INNER JOIN 20181015_Product_Master ON [20181015_Stock_Report].商品コード = [20181015_Product_Master].商品コード

    I would expect to be

    FROM 20181015_Stock_Report INNER JOIN 20181015_Product_Master ON [20181015_Stock_Report].商品コード = [20181015_Product_Master].ID

    The implication is your relationships are not set up correctly - i.e. your [20181015_Product_Master].商品コード field has duplicates

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Whether a table is master or lookup or dependent depends on context. You store locationCode as foreign key in a table. If you want to know the name or address associated with locationCode you need to 'look it up' from the source table. Each locationCode has only one record in the Locations table, therefore it can be included as a 'lookup' table in the query in order to retrieve the address info.
    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. #26
    Cronsen is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    24
    Ajax,
    you were absolutely right.
    In the end, the cause of the whole trouble was with a couple of duplicates in my master data.
    I fixed them and now it works perfectly.

    I was also able to add additional groups to the report, as desired.

    Again, thanks for your help!

    June7, same goes to you. Thank you!

Page 2 of 2 FirstFirst 12
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