How to Make More than one Inner Join on Crosstab Query

[RAJESHKUMAR R]

Dear Experts,

Please find below SQL Query.

All Green Highlighted Part of SQL Statement is working well and it gives an expected Result to me Perfectly.

Now I am trying to lookup (Inner Join) one more Column i.e. “Shipped_Qty” from “FR_TO_SHIPMENT_Crosstab” Query. Red highlighted is the SQL statement which I am trying to Link. It gives multiple Error!

I am sure, I am missing some basic steps due to my lack of Knowledge on this.

Could you please correct below SQL Statement which gives desired results (Basically right now there are 8 Different Column in my Result ie Part_Number, CRD_Qty, CRD_Date, CRD_Month, Cat, Group, Planner & Lead_Time in addition to this I want to lookup “Shipped_Qty” from “FR_TO_SHIPMENT_Crosstab” Query)

SELECT TO_ZVRR.Part_Number, CRD_Qty, CRD_Date, Format([CRD_Date], "MM.YYYY") AS CRD_Month, "To_Ref." AS Cat, Group, Planner, Lead_Time, Shipped_Qty FROM TO_ZVRR INNER JOIN FG_PN_LISTS ON TO_ZVRR.Part_Number=FG_PN_LISTS.Part_Number INNER JOIN FR_TO_SHIPMENT_Crosstab As Shipped_Qty ON FR_TO_SHIPMENT_Crosstab.Part_Number=TO_ZVRR.Part_Number
UNION ALL SELECT FR_ZVRR.Part_Number, CRD_Qty*-1, CRD_Date, Format([CRD_Date], "MM.YYYY"), "From_Ref", Group, Planner, Lead_Time FROM FR_ZVRR INNER JOIN FG_PN_LISTS ON FR_ZVRR.Part_Number=FG_PN_LISTS.Part_Number INNER JOIN FR_TO_SHIPMENT_Crosstab As Shipped_Qty ON FR_TO_SHIPMENT_Crosstab.Part_Number=TO_ZVRR.Part_Number;

Thanks & Regards,
Rajeshkumar R

[isladogs]

I thought this was answered in a previous thread

Create your UNION query without the crosstab and save it as e.g qryUNION
Make a new query where qryUNION is joined to your CROSSTAB query

[RAJESHKUMAR R]

Dear ridders52,

Yes, with help of Previous thread only I could able to accomplish green highlighted Statement!

With the same logic, I am trying to Inner Join one more Column (From a Crosstab Query) to my Final Output Crosstab Query and I am getting “Syntax Error in FROM Clause” Error or some time, Syntax Error (Missing Operator) in Query Expression.

And, I am struck here and seeking for a Help!

Thanks & Regards,
Rajeshkumar R

[isladogs]

Yes I understand that and suggested a solution. Have you tried what I suggested?

[RAJESHKUMAR R]

Dear ridders52,

I tried with too many trial and Error but I am not able to be success full on this!

Here I have attached my Database. Basically, I want Shipped_Qty Column value from “SHIPMENT_Crosstab” to be looked up on “Fore_Firm_UNION” with respect to Product=Part_Number in SHIPMENT CrossTab Query

Could you please help to accomplish the same?

Thanks & Regards,
Rajeshkumar R

[isladogs]

Thank you for providing the expected result - very helpful
The attached db has a query qrySummaryResult which has the same fields - the dates are different because the data you provided was for Oct 2017

There are several important issues you need to deal with in your database design

1. All tables need a primary key field in order to ensure each record can be uniquely identified.
Without this action queries such as update queries will fail as Access doesn't know what to update
I've added a PK field to each table (except your z_Expected_Result) using either Product or PartNumber where possible or an autonumber field where duplicates prevented use of those fields.

2. I've also added a relationship joining Shipment & Forecast tables. Have a look at your other tables to see if you can/should add a similar relationship

3. Why have you used two different names for the same thing - Product/PartNumber?

4. The table FG_PN_Lists had 2584 records but only 24 were populated! How did that happen?
I've deleted the empty records

Anyway the query qrySummaryResults was created exactly as I said to do
I just linked the union & crosstab queries together using the Product/PartNumber fields

Suggest before you do any more that you spend time learning more about the basics of database design
e.g. watch some of the excellent access tutorial videos by Steve Bishop or Richard Rost on You Tube

[RAJESHKUMAR R]

Dear ridders52,,

Thanks for your Extended Support! Also for Tutorial Suggestions Too!

I am sure it will help me a lot to improvise my knowledge in MS Access!

Thanks & Regards,
Rajeshkumar R