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.
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.
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
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
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.
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.
Thank you.
How can I define a lookup table?
To me, both the location table and product table were master tables.
Regards,
Mike
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
Thanks!
So, here we go:
1.) The very basic crosstab looks like this:
From Left to right: Location Code, Product Code, Size Code, Stock
The SQL would look like this:
Result: Works as expected. Each size cell lists the correct stock value.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].[サイズコード];
2) Now the extension with the location table:
From left to right: Location Code, Product Code, Size Code, Stock, Location Name (ENG)
Code:
Result: Works as expected. Each size cell lists the correct stock value: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].[サイズコード];
3) Now the extension with the product table instead of the location one:
From left to right: Location Code, Product Code, Size Code, Stock, Product Name (ENG)
Code:
Result: Each size item has an incorrect value: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].[サイズコード];
Thanks and regards,
Mike
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
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.
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!