Subquery on calculated field

[graveyard]

Dear all, i have a question on subquery on calculated field. i have extracted the first 4 letters from a field to form a lookup id [left(A,4)] = Y (end results of the left formula). in the same query in design value, i want to join Y to another table (let's called it TABLE2) that has the same reference id [Y]. however, i couldnt do it within the same query. i know i could create a query with the calculated field and then do another query to join but i would likle to do it in one query.

i am not sure whether can i achieve this using subquery in the criteria section of the design query - can someone point me to the syntax? someting like "select X from TABLE1 where TABLE2.Y=TABLE1.left(A,4)..". Sorry i am really noob in sql and i know i got the syntax messed up but i hope it somehow hints at what i was trying to achieve if my words aint clear enough

 

[G37Sam]

Can you upload what you have done so far?

What you're trying to do maybe easier with VBA

 

[plog]

It's going to take a sub-query, however, I can get it in all one SQL statement for you:

SELECT Table1Link.*, Table2.*
FROM (SELECT *, Mid([A],1,4) AS LinkData FROM Table1) AS Table1Link
INNER JOIN Table2 ON Table1Link.LinkData = Table2.Y;

Essentially you create your link field in a sub query and then link on that calculated field to your second table. You should be able to go into Design View and edit this query.

 

[graveyard]

It's going to take a sub-query, however, I can get it in all one SQL statement for you:

SELECT Table1Link.*, Table2.*
FROM (SELECT *, Mid([A],1,4) AS LinkData FROM Table1) AS Table1Link
INNER JOIN Table2 ON Table1Link.LinkData = Table2.Y;

Essentially you create your link field in a sub query and then link on that calculated field to your second table. You should be able to go into Design View and edit this query.

hi plog thanks! I entered below based on your statement.

SELECT TABLE1link.*, ConsolREF(2013AOP).*
FROM (select *,left([period],4) as linkData from ConsolAOP) AS TABLE1link INNER JOIN ConsolREF(2013AOP) ON TABLE1link.linkData = ConsolREF(2013AOP).Period1;


where ConsolREF(2013AOP) and ConsolAOP are tables and i am linking the field (Period1) and extracted form of Period based on 1st 4 letters from the left. but when i rahn the statement
there is any error msg saying "syntax error in FROM clause". Can you advise me whats wrong with the statement ive amended?

 

[graveyard]

Can you upload what you have done so far?

What you're trying to do maybe easier with VBA

hi Sam, thanks for responding. pls find attached database2.mdb. If you look at the query, i have a calculated field called "Extract" and this has to JOIN with [Year] field of table2 in a single query.
in the query, there was no join because i dont know how to create a join btw [Year] and [Extract] within the same query​

 

[plog]

It looks like you replaced everything correctly. It may be that ConsolREF(2013AOP) needs to have brackets ([ ]) around it in the query:

SELECT TABLE1link.*, [ConsolREF(2013AOP)].*
FROM (select *,left([period],4) as linkData from ConsolAOP) AS TABLE1link INNER JOIN [ConsolREF(2013AOP)] ON TABLE1link.linkData = [ConsolREF(2013AOP)].Period1;

See if that works. Non-alpha-numeric characters are usually poor choices to use in object names.

 

[G37Sam]

hi Sam, thanks for responding. pls find attached database2.mdb. If you look at the query, i have a calculated field called "Extract" and this has to JOIN with [Year] field of table2 in a single query.
in the query, there was no join because i dont know how to create a join btw [Year] and [Extract] within the same query​

Ok I get the picture now, not sure you can join tables on expressions however I found a way around it. Let me know if it does the trick for you:

View attachment Database2.accdb

 

[graveyard]

It looks like you replaced everything correctly. It may be that ConsolREF(2013AOP) needs to have brackets ([ ]) around it in the query:



See if that works. Non-alpha-numeric characters are usually poor choices to use in object names.

That does it! Thanks so much, plog for your help!

 

[graveyard]

Ok I get the picture now, not sure you can join tables on expressions however I found a way around it. Let me know if it does the trick for you:

View attachment 43052

Thanks Sam! Its resolved!