subquery syntax question - sub query after FROM?

[olivero]

Hi again,

I'm trying to create a calculated field in a query that uses a sub query in the expression.

This is what I have:

tmpProvinceAbbr: (SELECT ProvinceAbbr FROM (SELECT ProvinceOrStateTable FROM tblCountry WHERE CountryID = 1) WHERE ProvinceID = 8;)

ProvinceOrStateTable is actually a field in another table.

The sub query works on it's own and produces the value of the ProvinceOrStateTable as it should.

When I insert the sub query after FROM in the primary query, I get promted for input for the ProvinceAbbr field (I'm prompted just as any parameter query would promt for missing information).

This is not what I expected! This expression should be returning the value of ProvinceAbbr, not asking for it!

What am I doing wrong?

Thanks!
Oliver

[Lysander]

Hi again,

I'm trying to create a calculated field in a query that uses a sub query in the expression.

This is what I have:

tmpProvinceAbbr: (SELECT ProvinceAbbr FROM (SELECT ProvinceOrStateTable FROM tblCountry WHERE CountryID = 1) WHERE ProvinceID = 8;)

ProvinceOrStateTable is actually a field in another table.

The sub query works on it's own and produces the value of the ProvinceOrStateTable as it should.

When I insert the sub query after FROM in the primary query, I get promted for input for the ProvinceAbbr field (I'm prompted just as any parameter query would promt for missing information).

This is not what I expected! This expression should be returning the value of ProvinceAbbr, not asking for it!

What am I doing wrong?

Thanks!
Oliver

tmpProvinceAbbr: (SELECT ProvinceAbbr FROM (SELECT ProvinceOrStateTable FROM tblCountry WHERE CountryID = 1) WHERE ProvinceID = 8;)

Where is ProvinceAbbr to be found, because you appear to be trying to select ProvinceAbbr FROM ProvinceOrStateTable, as that is all the sub query is returning? Can you give an example of how you could select ProvinceAbbr without a subquery, or can you list the fields in the two tables?

[olivero]

tmpProvinceAbbr: (SELECT ProvinceAbbr FROM (SELECT ProvinceOrStateTable FROM tblCountry WHERE CountryID = 1) WHERE ProvinceID = 8;)

Where is ProvinceAbbr to be found, because you appear to be trying to select ProvinceAbbr FROM ProvinceOrStateTable, as that is all the sub query is returning? Can you give an example of how you could select ProvinceAbbr without a subquery, or can you list the fields in the two tables?

Hi Lysander,

ProvinceAbbr is a field in a table called tblProvinces_CA. This table name is stored within a field called ProvinceOrStateTable that lives in a table called tblCountry.

I've hardcoded the WHERE statements with 1 and 8 to simplify locating the correct records during troubleshooting.

This is what it would look like without the sub query (this works as a query):

tmpProvinceAbbr: (SELECT ProvinceAbbr FROM tblProvinces_CA WHERE ProvinceID = 8;)

Thanks,
Oliver

[Lysander]

Hi Lysander,

ProvinceAbbr is a field in a table called tblProvinces_CA. This table name is stored within a field called ProvinceOrStateTable that lives in a table called tblCountry.

I've hardcoded the WHERE statements with 1 and 8 to simplify locating the correct records during troubleshooting.

This is what it would look like without the sub query (this works as a query):

tmpProvinceAbbr: (SELECT ProvinceAbbr FROM tblProvinces_CA WHERE ProvinceID = 8;)

Thanks,
Oliver

Ok, let me see if I understand this.

You want to select ProvinceAbbr from a table, but you don't know the table name until you get ProvinceOrStateTable from the country.

So what you need is (SELECT ProvinceAbbr FROM XXXXX WHERE ProvinceID = 8;)

and XXXX is (Select ProvinceOrStateTable from tblCountry where countrycode=1;)

I am still missing something. Is ProvinceOrStateTable only one value for each country?

I can't see how this can be done by one query alone, but I think it can be done by a query and a function in code to return the data you want.

What you need is a function to which you pass the ProvinceID and the CountryID, this function generates a SQL string to extract the ProvinceAbbr and returns it as a value to your query.

If what I think you are doing is correct, please confirm and I think I can come up with an answer. What you are trying to do is very similar to my old coding days of machine code, where you had 'Indirect Addressing'

You want to get info from ProvinceOrStateTable but ProvinceOrStateTable does not have the info, it has the name of the table that has the info.

Ok, I think this will do it. Bear in mind I am writing this in the forum, not testing it for real so I might not get it 100% correct.

Create a global module and create a public function as so.

public function getProvinceAbbr  (byval ProvinceID as integer, byval CountryID as integer) as integer
 
dim strTable as string
 
strTable=dlookup("ProvinceOrStateTable","tblCountry","CountryId=" & CountryID)
'now have the table name, test for null records maybe
 getProvinceAbbr =dlookup("ProvinceAbbr",strTable,"ProvinceID=" & ProvinceID)
'now have the value you want
exit function
 

Then in you query have

select getProvinceAbbr(8,1) as tmpProvinceAbbr, SOMEOTHERDATA from maintable;
 

[olivero]

Ok, let me see if I understand this.

You want to select ProvinceAbbr from a table, but you don't know the table name until you get ProvinceOrStateTable from the country.

So what you need is (SELECT ProvinceAbbr FROM XXXXX WHERE ProvinceID = 8;)

and XXXX is (Select ProvinceOrStateTable from tblCountry where countrycode=1;)

I am still missing something. Is ProvinceOrStateTable only one value for each country?

I can't see how this can be done by one query alone, but I think it can be done by a query and a function in code to return the data you want.

What you need is a function to which you pass the ProvinceID and the CountryID, this function generates a SQL string to extract the ProvinceAbbr and returns it as a value to your query.

If what I think you are doing is correct, please confirm and I think I can come up with an answer. What you are trying to do is very similar to my old coding days of machine code, where you had 'Indirect Addressing'

You want to get info from ProvinceOrStateTable but ProvinceOrStateTable does not have the info, it has the name of the table that has the info.

Ok, I think this will do it. Bear in mind I am writing this in the forum, not testing it for real so I might not get it 100% correct.

Create a global module and create a public function as so.

public function getProvinceAbbr  (byval ProvinceID as integer, byval CountryID as integer) as integer
 
dim strTable as string
 
strTable=dlookup("ProvinceOrStateTable","tblCountry","CountryId=" & CountryID)
'now have the table name, test for null records maybe
 getProvinceAbbr =dlookup("ProvinceAbbr",strTable,"ProvinceID=" & ProvinceID)
'now have the value you want
exit function
 

Then in you query have

select getProvinceAbbr(8,1) as tmpProvinceAbbr, SOMEOTHERDATA from maintable;
 

Yep, that's exactly what I'm trying to do! Indirect is right :)

Each Country only has one such table (a list of all the Provinces, States, or other for that country). The name of that table is stored in the ProvinceOrStateTable field in tblCountry.

A little complex, but I couldn't think of any other way to keep these things well organized. Each "province" table also stores tax information for that province. The tax information is almost always different from province or state to the other, and it's always different between countried. Sometimes rules need to be created for only on one country at a time.

From a long term point of view, it seems to me that it would be easier to manage a separate province table for each country rather than lumping all the provinces and states into one table, along with their respective (and very diverse) tax structures.

I'll give your code a shot. I kind of had a feeling that I wasn't going to be able to do this with just a straight query/subquery, but it never hurts to ask :)

Thanks for the help!

Oliver