Returning null records from double join

[Billabong]

I have a query using multiple tables, effectively and order header, order detail and then several supporting tables. One of these is a text table with a field I need but only if it’s record type 1.



The join to the text table is based on two fields order_number and line_number and I need to return all records from order_header and order_detail and then insert null values into this data where there's no order_text of record type 1.

At the minute my query only returns data where the order_text record_type is 1 so omits a lot of data. Can anyone help me with the syntax for the correct join for this please?

SELECT DISTINCT ORDER.HEADER.ORDER_NUMBER, ORDER.DETAIL.LINE_NUMBER
FROM LIORDER_AUF_TEXT INNER JOIN (ORDER.HEADER INNER JOIN ORDER_DETAIL ON ORDER.HEADER.ORDER_NUMBER = ORDER_DETAIL.ORDER_NUMBER) ON (ORDER_TEXT.LINE_NUMBER = ORDER.DETAIL.LINE_NUMBER) AND (ORDER _TEXT.ORDER_NUMBER = ORDER_DETAIL.ORDER_NUMBER)
GROUP BY ORDER.HEADER.ORDER_NUMBER, ORDER.DETAIL.LINE_NUMBER, ORDER_TEXT.RECORD_TYPE, ORDER_TEXT.TEXT_DETAIL
HAVING (((ORDER_TEXT.RECORD_TYPE)=1))
ORDER BY ORDER.HEADER.ORDER_NUMBER, ORDER.DETAIL.LINE_NUMBER;

TIA.

[ssanfu]

I probably won't be able to help you. Some of the tables names I see in the SQL are not valid.
Access will not let you create a table name like ORDER.HEADER.

SELECT DISTINCT 
    ORDER.HEADER.ORDER_NUMBER, ORDER.DETAIL.LINE_NUMBER

FROM 
    LIORDER_AUF_TEXT 
       INNER JOIN (
          ORDER.HEADER INNER JOIN ORDER_DETAIL 
       ON 
          ORDER.HEADER.ORDER_NUMBER = ORDER_DETAIL.ORDER_NUMBER) 
       ON (
          ORDER_TEXT.LINE_NUMBER = ORDER.DETAIL.LINE_NUMBER) AND (ORDER_TEXT.ORDER_NUMBER = ORDER_DETAIL.ORDER_NUMBER)

GROUP BY 
    ORDER.HEADER.ORDER_NUMBER, ORDER.DETAIL.LINE_NUMBER, ORDER_TEXT.RECORD_TYPE, ORDER_TEXT.TEXT_DETAIL

HAVING 
    (((ORDER_TEXT.RECORD_TYPE)=1))

ORDER BY 
    ORDER.HEADER.ORDER_NUMBER, ORDER.DETAIL.LINE_NUMBER;

Sometimes the table name changes: "ORDER.DETAIL" vs "ORDER_DETAIL"


Not sure what you want to do.
First you say

One of these is a text table with a field I need but only if it’s record type 1

Then you say

return all records from order_header and order_detail and then insert null values into this data where there's no order_text of record type 1.

[NTC]

your wrote: "..my query only returns data where the order_text record_type is 1..."
that would be consistent with the clause: HAVING (((ORDER_TEXT.RECORD_TYPE)=1))

in general one cannot both select and insert as part of a single query; so I believe fundamentally you must make a query that selects the correct record set - save it with a name i.e. Q1 ....and then create an update or append query that begins with Q1 as part of its design sources

If you seek the SQL syntax; then create the query using the visual design view so that it runs correctly - and then select the 'SQL VIew' option in order to see the syntax