Simple subquery throwing errors in subsequent queries. 1

[ulteriormotif]

The query below is used to bring my blocks and sub-blocks (of land) together, and define the season I want to look at, and is a base query for another query.


SELECT [DAT - Blocks].[Property ID], [DAT - Blocks].[Block ID], [DAT - Block Sub-blocks].[SubBlock ID], (select [currentyear] from [BAS - current year]) AS Currentseason
FROM [DAT - Blocks] LEFT JOIN [DAT - Block Sub-blocks] ON [DAT - Blocks].[Block ID] = [DAT - Block Sub-blocks].tBlockID;

The Currentyear table only has one record in it ever - it's a single record holding table that's used to control pretty much the whole database - the user can change the current year at various points and that limits all queries, input screens, etc, appropriately.

The query above runs fine on its own, but when I use it in subsequent queries, they either don’t run, or they do run but then throw the following error when I try to close the query.

"This action will reset the current code in break mode
Do you want to stop running the code?
To halt the execution of the program so the Module window can be closed, select Yes.
To leave the code in the current state, select No. "

The rest of the database then goes screwy and the above error appears when I’m running unrelated queries or trying to close forms, etc (the common denominator seems to be it happening on closing something). I have to close the database and reopen to go any further.

Example of subsequent query that throws the error:

SELECT [U - blocks subblocks currentseason].[Property ID], [U - blocks subblocks currentseason].[Block ID], [U - blocks subblocks currentseason].[SubBlock ID], [U - blocks subblocks currentseason].Currentseason, [DAT - production targets].blockid, IIf([dat - production targets]![blockid] Is Null,"NO","yes") AS [Target set]
FROM [U - blocks subblocks currentseason] LEFT JOIN [DAT - production targets] ON ([U - blocks subblocks currentseason].[SubBlock ID] = [DAT - production targets].blockid) AND ([U - blocks subblocks currentseason].Currentseason = [DAT - production targets].Season);

It does all seem to go back to the subquery. Can anyone give me a pointer on where I’m going wrong?

 

[PHV]

You may try this:
SELECT [DAT - Blocks].[Property ID], [DAT - Blocks].[Block ID], [DAT - Block Sub-blocks].[SubBlock ID], [BAS - current year].currentyear AS Currentseason
FROM [BAS - current year], [DAT - Blocks] LEFT JOIN [DAT - Block Sub-blocks] ON [DAT - Blocks].[Block ID] = [DAT - Block Sub-blocks].tBlockID;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ulteriormotif]

hmmm.. no luck- 'join expression not supported'.

I think that's why I had to go for the subquery in the first place - because it has to be a directional join from blocks to sub-blocks, but there's no join I can put on currentyear

 

[PHV]

hmmm.. works for me:
FROM table1, table2 INNER JOIN table3 ON table2.FK=table3.PK

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Ah, don't works for me (same error as you):
FROM table1, table2 LEFT JOIN table3 ON table2.FK=table3.PK
You may consider create a named query with the outer join and then another query with the cartesian product

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ulteriormotif]

I'm not sure what a 'cartesian product' is... but I did do that - a base query that just joins the currentyear to the topblock id, and then the next query adds the sub-block information.

Thanks for your help PHV.

 

[wendywam]

How do I convert this sql 6.5 query into Ms Access 97 query?

SELECT a.Rem,b.nam,c.Pro,d.Exp,e.Fligh
FROM
Table1 a
LEFT OUTER JOIN Table2 b ON b.Code = a.Cone
LEFT OUTER JOIN Table3 e ON e.AwbN = a.AwbN
LEFT OUTER JOIN Table4 c ON c.AwbN = a.AwbN
LEFT OUTER JOIN Table5 ON d.AwbN = a.AwbN
WHERE a.AwbN = '12345'

 

[PHV]

Have you tried this ?
SELECT a.Rem,b.nam,c.Pro,d.Exp,e.Fligh
FROM Table1 a
LEFT JOIN Table2 b ON b.Code = a.Cone
LEFT JOIN Table3 e ON e.AwbN = a.AwbN
LEFT JOIN Table4 c ON c.AwbN = a.AwbN
LEFT JOIN Table5 d ON d.AwbN = a.AwbN
WHERE a.AwbN = '12345'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I'm not sure what a 'cartesian product' is

A cartesian product (or cross join) is what you get when you say:

SELECT * from Table1, Table2

say Table1 has:

a1 a2
10 u
20 v
30 w
40 x
50 y
60 z

Table2 has:
b1 b2
10 p
20 q

when you run the query above, your results will be:

a1 a2 b1 b2
10 u 10 p
20 v 10 p
30 w 10 p
40 x 10 p
50 y 10 p
60 z 10 p
10 u 20 q
20 v 20 q
30 w 20 q
40 x 20 q
50 y 20 q
60 z 20 q

This gives you all possible combinations. The number of rows in the result set will be the number of rows from table1 * number of rows from table2 (6 * 2 = 12)






Leslie

 

[ulteriormotif]

aahhh.. thanks for that lespaul