Query joins to the same table multiple times

[TonyF]

Hi,

I have created new expressions each for front_lab_stock warehouse and front_lab_stock_product , then done the same for rear_lab_stock warehouse and product in a new query


And then when I run the query it is fine and pulls all the correct wh/product combinations


I have then created a new query based on this and added a join from FR_LAB_STOCK_WH and FR_LAB_STOCK_PROD to Stockm wh and product and this works fine bringing through the descriptions from stockm


But then when I add the stockm table again and try and add a join to stockm for RR_LAB_STOCK_WH and RR_LAB_STOCK_PROD I get the Join expression not supported message


I cannot see why it won’t let me add it as the other,
this is the SQL of the query and the joins both look correct to me

SELECT qry_WDORDERM_STOCKM.order_number, qry_WDORDERM_STOCKM.customer, qry_WDORDERM_STOCKM.range_sheet_series, qry_WDORDERM_STOCKM.range, qry_WDORDERM_STOCKM.sheet, qry_WDORDERM_STOCKM.series, qry_WDORDERM_STOCKM.sage_so_stem, qry_WDORDERM_STOCKM.delivery_date, qry_WDORDERM_STOCKM.mc_style, qry_WDORDERM_STOCKM.card_code, qry_WDORDERM_STOCKM.job_desc, qry_WDORDERM_STOCKM.box_unit_desc, qry_WDORDERM_STOCKM.box_label_stock, qry_WDORDERM_STOCKM.pack_label_stock, qry_WDORDERM_STOCKM.caption_id, qry_WDORDERM_STOCKM.caption, qry_WDORDERM_STOCKM.pack_for_collation, qry_WDORDERM_STOCKM.new_prod, qry_WDORDERM_STOCKM.closure, qry_WDORDERM_STOCKM.works_order_1, qry_WDORDERM_STOCKM.works_order_2, qry_WDORDERM_STOCKM.works_order_3, qry_WDORDERM_STOCKM.works_order_4, qry_WDORDERM_STOCKM.env_stock_1, qry_WDORDERM_STOCKM.env_stock_2, qry_WDORDERM_STOCKM.env_stock_3, qry_WDORDERM_STOCKM.env_stock_4, qry_WDORDERM_STOCKM.env_desc_1, qry_WDORDERM_STOCKM.env_desc_2, qry_WDORDERM_STOCKM.env_desc_3, qry_WDORDERM_STOCKM.env_desc_4, qry_WDORDERM_STOCKM.card_ref_1, qry_WDORDERM_STOCKM.card_ref_2, qry_WDORDERM_STOCKM.card_ref_3, qry_WDORDERM_STOCKM.card_ref_4, qry_WDORDERM_STOCKM.env_page, qry_WDORDERM_STOCKM.box_stock, qry_WDORDERM_STOCKM.front_lab_stock, qry_WDORDERM_STOCKM.rear_lab_stock, qry_WDORDERM_STOCKM.fold_lab_stock, qry_WDORDERM_STOCKM.special_lab_stock, qry_WDORDERM_STOCKM.price_lab_stock, qry_WDORDERM_STOCKM.barcode_lab_stock, qry_WDORDERM_STOCKM.barcode, qry_WDORDERM_STOCKM.flap_closure, qry_WDORDERM_STOCKM.tape_stock, qry_WDORDERM_STOCKM.scoreline_opt, qry_WDORDERM_STOCKM.wrap_opt, qry_WDORDERM_STOCKM.film_inner_size, qry_WDORDERM_STOCKM.film_inner_stock, qry_WDORDERM_STOCKM.film_outer_opt, qry_WDORDERM_STOCKM.film_outer_size, qry_WDORDERM_STOCKM.film_outer_stock, qry_WDORDERM_STOCKM.sundries_type, qry_WDORDERM_STOCKM.sundries_stock, qry_WDORDERM_STOCKM.spec_sheet, qry_WDORDERM_STOCKM.card_prefix, qry_WDORDERM_STOCKM.envelope_type_1, qry_WDORDERM_STOCKM.envelope_type_2, qry_WDORDERM_STOCKM.envelope_type_3, qry_WDORDERM_STOCKM.envelope_type_4, qry_WDORDERM_STOCKM.free_text_1, qry_WDORDERM_STOCKM.free_text_2, qry_WDORDERM_STOCKM.free_text_3, qry_WDORDERM_STOCKM.free_text_4, qry_WDORDERM_STOCKM.design_count, qry_WDORDERM_STOCKM.pack_unit, qry_WDORDERM_STOCKM.box_unit, qry_WDORDERM_STOCKM.box_weight, qry_WDORDERM_STOCKM.collation_qty, qry_WDORDERM_STOCKM.card_sell_price, qry_WDORDERM_STOCKM.env_sell_price, qry_WDORDERM_STOCKM.box_sell_price, qry_WDORDERM_STOCKM.lab_sell_price, qry_WDORDERM_STOCKM.sundries_sell_pric, qry_WDORDERM_STOCKM.total_sell_price, qry_WDORDERM_STOCKM.card_cost, qry_WDORDERM_STOCKM.env_cost, qry_WDORDERM_STOCKM.box_cost, qry_WDORDERM_STOCKM.lab_cost, qry_WDORDERM_STOCKM.sundries_cost, qry_WDORDERM_STOCKM.total_cost, qry_WDORDERM_STOCKM.labour_value, qry_WDORDERM_STOCKM.overhead_value, qry_WDORDERM_STOCKM.qty_per_box, qry_WDORDERM_STOCKM.pack_weight, qry_WDORDERM_STOCKM.pack_depth, qry_WDORDERM_STOCKM.box_qty_reqd, qry_WDORDERM_STOCKM.env_price_1, qry_WDORDERM_STOCKM.env_price_2, qry_WDORDERM_STOCKM.env_price_3, qry_WDORDERM_STOCKM.env_price_4, qry_WDORDERM_STOCKM.box_price, qry_WDORDERM_STOCKM.tape_qty_m, qry_WDORDERM_STOCKM.film_inner_form, qry_WDORDERM_STOCKM.film_inner_waste, qry_WDORDERM_STOCKM.film_inner_qty, qry_WDORDERM_STOCKM.film_outer_form, qry_WDORDERM_STOCKM.film_outer_waste, qry_WDORDERM_STOCKM.film_outer_qty, qry_WDORDERM_STOCKM.price, qry_WDORDERM_STOCKM.labour, qry_WDORDERM_STOCKM.overhead, qry_WDORDERM_STOCKM.env_length_1, qry_WDORDERM_STOCKM.env_length_2, qry_WDORDERM_STOCKM.env_length_3, qry_WDORDERM_STOCKM.env_length_4, qry_WDORDERM_STOCKM.env_width_1, qry_WDORDERM_STOCKM.env_width_2, qry_WDORDERM_STOCKM.env_width_3, qry_WDORDERM_STOCKM.env_width_4, qry_WDORDERM_STOCKM.BUD, qry_WDORDERM_STOCKM.E1S, qry_WDORDERM_STOCKM.E2S, qry_WDORDERM_STOCKM.CARD_STOCK_1, qry_WDORDERM_STOCKM.CARD_STOCK_2, qry_WDORDERM_STOCKM.CARD_STOCK_3, qry_WDORDERM_STOCKM.CARD_STOCK_4, qry_WDORDERM_STOCKM.CR_WH, qry_WDORDERM_STOCKM.FR_LAB_STOCK_WH, qry_WDORDERM_STOCKM.FR_LAB_STOCK_PROD, qry_WDORDERM_STOCKM.REAR_LAB_STOCK_WH, qry_WDORDERM_STOCKM.REAR_LAB_STOCK_PROD, qry_WDORDERM_STOCKM.FOLD_LAB_STOCK_WH, qry_WDORDERM_STOCKM.FOLD_LAB_STOCK_PROD, qry_WDORDERM_STOCKM.SPECIAL_LAB_STOCK_WH, qry_WDORDERM_STOCKM.SPECIAL_LAB_STOCK_PROD, qry_WDORDERM_STOCKM.PRICE_LAB_STOCK_WH, qry_WDORDERM_STOCKM.PRICE_LAB_STOCK_PROD, qry_WDORDERM_STOCKM.BARCODE_LAB_STOCK_WH, qry_WDORDERM_STOCKM.BARCODE_LAB_STOCK_PROD, scheme_stockm.description AS CR_PROD_DESC, scheme_stockm.long_description AS CR_PROD_LONG_DESC, scheme_stockm_1.description AS FR_DESC, scheme_stockm_1.long_description AS FR_LONG_DESC, scheme_stockm_2.description, scheme_stockm_2.long_description

FROM ((qry_WDORDERM_STOCKM
LEFT JOIN scheme_stockm ON (qry_WDORDERM_STOCKM.CARD_STOCK_1 = scheme_stockm.product) AND (qry_WDORDERM_STOCKM.CR_WH = scheme_stockm.warehouse))
LEFT JOIN scheme_stockm AS scheme_stockm_1 ON (qry_WDORDERM_STOCKM.FR_LAB_STOCK_PROD = scheme_stockm_1.product) AND (qry_WDORDERM_STOCKM.FR_LAB_STOCK_WH = scheme_stockm_1.warehouse))
LEFT JOIN scheme_stockm AS scheme_stockm_2 ON (qry_WDORDERM_STOCKM.REAR_LAB_STOCK_PROD = scheme_stockm_2.product) AND (qry_WDORDERM_STOCKM.REAR_LAB_STOCK_WH = scheme_stockm_2.warehouse);

I have screenshots if they help but the .doc is too big to put on here

Can anyone see anything I am doing wrong ?

Many thanks for your time

 

[jdraw]

Perhaps you could tell us in plain english what your "business" is and what you are trying to accomplish. We have no background/context of your situation.
It sounds like an Inventory Control situation, but that's just a guess.

 

[Galaxiom]

Your query would be a lot easier to read if you used aliases. Like this:

SELECT A.order_number, B.whatever
FROM qry_WDORDERM_STOCKM AS A
LEFT JOIN scheme_stockm AS B
ON A.CARD_STOCK_1 = B.product

You can also alias with your own names instead of the appended _1 default style used by Access.

I would also recommend you change your naming style.

Keep full capitals for key words.
Use CamelCase for object and field names.

Underscores are are popular with developers for systems that only support uppercase but are quite unnecessary in Access. Moreover Access uses them in its own way in modules and they are best avoided in names.

Repeating fieldnames with appended numbers is usually a sign of normalization issues as are huge queries. I recommend you post more about your table strucures.

 

[TonyF]

Thanks for the reply GalaxiomAtHome,

I think I need to go and restructure the query as you suggest,

I am trying to create a Works order document via an ODBC link to two tables wdorderm and stockm on a Sage SQL server, most of the information I need is in the wdorderm table I just need to bring through 8 x descriptions of products from the stockm (Stock Master table) but don't seem to be able to get the joins right.

I will try omitting the underscores etc.

Thanks for your time

 

[jdraw]

Since the originating data is on a sage sql server, someone must have built/structured that data base. Can you get a data model/schema of that database?
It might be the most appropriate place to start.
And as Glaxiom has suggested you might want to start with simplification of your query.
I'd suggest aliases as well, but would also consider using just a few fields and build upon a query that gives the result you're looking for.

I think there may be a structure problem when I see (what appear to be repeated fields)
e.g.

qry_WDORDERM_STOCKM.CARD_[COLOR="Red"]STOCK_1[/COLOR], qry_WDORDERM_STOCKM.CARD_[COLOR="Red"]STOCK_2[/COLOR], 
qry_WDORDERM_STOCKM.CARD_[COLOR="Red"]STOCK_3[/COLOR], 
qry_WDORDERM_STOCKM.CARD_[COLOR="Red"]STOCK_4[/COLOR],

You may want to do some Goole searcing on "Normalization"
There are excellent references here
http://www.utteraccess.com/forum/Suggested-Readings-and-t373096.html