Multiple join types in one query, jet compatible, exec from Excel/

[John Mulford]

When trying to add an 'unjoined table' to a group of Left Outer Joined
tables, jet returns "join expression not supported", "syntax error in FROM
clause", Etc.

The 'unjoined table' (Date_Driver_Table) needs to be included in the
dataset, but can not be joined directly to any of the other table fields.

Purpose - Use the date from the Date_Driver_Table for calculation purposes
with fields created in the group of Left Outer Joined tables.

Parameters - Need to maintain Excel/MS Query - Refresh External Data
compatibility.


Goal - Perform Multiple join types in one query.

Undesired known solutions:
- Fake Out with Dummy field of same data type, however, this is not clean
Ex LEFT JOIN Date_Driver_Table ON DATA.DUMMYFIELD <>

Date_Driver_Table.DUMMYFIELD
- use WHERE clause for join, however, do not want the risk of data being
excluded if primary keys not in structural tables
- separate into 2 queries

Example of failed syntax:

FROM Date_Driver_Table,

((( DATA
LEFT JOIN PRODUCT_TABLE
ON DATA.PRODUCT_CODE = PRODUCT_TABLE.PRODUCT_CODE )

LEFT JOIN PORTFOLIO_TABLE
ON DATA.LENDER_BOND = PORTFOLIO_TABLE.LENDER_BOND )

LEFT JOIN STATUS_TABLE
ON DATA.STATUS_CODE = STATUS_TABLE.STATUS_CODE)

Hopefully, someone has past experience/examples.

Any suggestions welcome.

Thx,
J

 

[Jeff Boyce]

John

"How" really depends on "what", as in "what does your underlying data look
like?"

Please post a description of your tables/data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Mulford]

Jeff:

Thank you.

The table that I want to incorporate as 'unjoined', Date_Driver_Table,
Contains 1 Row and several desctiptive columns (Dates and Text). The Dates
from this table will be used in comparison to dates from table DATA. DATA
contains the raw data upon which I wish to evaluate, 1 row per record with
multiple columns. The other left join tables are structural in nature as
they contain matching codes to DATA, but contain greater descriptions/depth
to the field contained in DATA.

The desire is to keep the orphans in the dataset (left joins to avoid
excluding any new codes that pop up in DATA, but have not yet been added to
the structural tables) and just bring in the few dates from the 'unjoined'
table that drive other definitions.

Example: The date in Date_Driver_Table is used with the file creation date
of DATA to produce groupings. Usage is that something like "reporting
timeperiod end " can be stored in the Date_Driver_Table and any query
definitions can use that date to create groupings. When you want to change
reporting periods a single date in a table can be modified and any subsequent
procedssng of data files will be correcly altered.


Again, thank you for any suggestions.

J

 

[Bob Barrows [MVP]]

Create a saved query containing the legal joins, then do the cartesian
join in a separate sql statement:

select ... from Date_Driver_Table, Saved_Query_Name
WHERE ...