Can These 2 Queries be Combined into 1? 1

[GGleason]

I would like to combine the 2 queries into 1 query. An example of what I would like to do is based on 4 tables.

Table 1 called FRUIT w/ 1 field called TYPE. This table has 2 records: APPLE; ORANGE.

Table 2 called STATE w/ 1 field called ST. This table has 2 records: OKLAHOMA; TEXAS.

Table 3 called OKLAHOMA_APPLE (fields and records don't matter).

Table 4 called TEXAS_ORANGE (fields and records don't matter).

Query1 SQL Statement:
SELECT STATE.ST, FRUIT.TYPE, [ST]+"_"+[TYPE] AS

FROM STATE, FRUIT
ORDER BY STATE.ST, FRUIT.TYPE;

Query1 SQL Results:
ST TYPE TABLE
OKLAHOMA APPLE OKLAHOMA_APPLE
OKLAHOMA ORANGE OKLAHOMA_ORANGE
TEXAS APPLE TEXAS_APPLE
TEXAS ORANGE TEXAS_ORANGE

Query2 SQL Statement:
SELECT Query1.TABLE, IIf(Len([Name])>0,"Yes","No&quot AS TableExist
FROM Query1 LEFT JOIN MSysObjects ON Query1.TABLE = MSysObjects.Name
ORDER BY Query1.TABLE;

Query2 SQL Results:
TABLE TableExist
OKLAHOMA_APPLE Yes
OKLAHOMA_ORANGE No
TEXAS_APPLE No
TEXAS_ORANGE Yes

Can anyone tell me if it possible to combine these 2 queries into 1?

TIA
GGleason

 

[tlbroadbent]

You should be able to use the following query. I question why you want to do that. In my mind, two simple queries are easier to maintain and understand. One query probably won't run any faster or perform any better than two.

SELECT
q1.TABLE,
IIf(Len([o].[Name])>0,"Yes","No&quot AS TableExist
FROM
[SELECT
STATE.ST, FRUIT.TYPE,
[ST]+"_"+[TYPE] AS

FROM STATE, FRUIT
ORDER BY STATE.ST, FRUIT.TYPE]. As q1
LEFT JOIN MSysObjects As o
ON q1.TABLE = o.Name
ORDER BY q1.TABLE;
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Terry,

You bring up a good question. The rationale for having one query is that I sometimes need to build these kinds of queries dynamically within querydefs because there are a lot of permutations possible with the actual data. Therefore I don't want to have a lot of queries lying around that I only need to use once, if at all. So my request is motivated to keep the queries tab as uncluttered as possible.

I copied the above SQL statement and tried to run it but I got an error:

[red]Invalid bracketing of name 'SELECT
STATE.ST, FTUIT.TYPE,
[ST'.[/color]

What did I do wrong?

TIA for you help.
GGleason

 

[tlbroadbent]

You did nothing wrong. I just forgot that some versions of Access don't like the brackets on the column names in the sub query. Remove the brackets around this clause

[ST]+"_"+[TYPE] AS

Should be

ST + "_" + TYPE AS TABLE

Hopefully that will work. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Success!

Terry,

I made some minor modifications to the last code. Here it is:


SELECT
q1.MyTable,
IIf(Len([o].[Name])>0,"Yes","No&quot AS TableExist
FROM
[SELECT
STATE.ST, FRUIT.TYPE,
ST+"_"+TYPE AS MyTable
FROM STATE, FRUIT
ORDER BY STATE.ST, FRUIT.TYPE]. As q1
LEFT JOIN MSysObjects As o
ON q1.MyTable = o.Name
ORDER BY q1.MyTable;


Thanks much!
GGleason

 

[Guest_imported]

The Excel functions are rich and, it seems, many of them are included/available for use in Access queries (e.g., left and mid). One function I need is "mod" (modulo arithmentic. Specifically, I want to create a calculated result of E(ven) or O(dd) from a house number stored in a field of a record. I prefer to do this using a query instead of in the report (although, I suppose, I don't really have any compelling reason for doing so.

Also, when I searched for "mod" in Access' help, I got nothing of value. I thought I would at least get something on VBA's use of mod. Have I got the help system set up incorrectly?

Thanks,

John

 

[tlbroadbent]

John,

First, I have two recommendations. 1) Join Tek-Tips. Then you get the benefits that members enjoy. 2) Start a new thread for new questions.

Second, to answer your question about the MOD function. It is available in Access and can be used in a query as well as VBA. An example follows.

Select Address Mod 2 As OddEven
From Table

Third, Access Help is very poor. I get frustrated all the time. Search and the Index are terrible. If you know the books, you can go to the contents and find stuff but otherwise it is hard. Usually web searches are easier and faster. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Terry,

Is it possible to do another nested query that can call a function? I tried the following syntax and it fails:

SELECT q2.*, TableRecCo(q2.MyTable,"&quot AS Records
FROM
[SELECT q1.*, IIf(Len(o.Name)>0,"Yes","No&quot AS TableExist
FROM [SELECT
STATE.ST, FRUIT.TYPE,
ST+"_"+TYPE AS MyTable
FROM STATE, FRUIT
ORDER BY STATE.ST, FRUIT.TYPE]. AS q1 LEFT JOIN MSysObjects AS o ON q1.MyTable = o.Name
ORDER BY q1.MyTable]. AS q2
WHERE (((q2.TableExist)="Yes&quot);

The error message is as follows:

Syntax error in query expression 'q1.MyTable]. AS q2
WHERE (((q2.TableExist)="Yes&quot)'.

Is this possible?

TIA,
GGleason

 

[tlbroadbent]

I can't see anything wrong. I'm not able to test at the moment. You may need to replace the brackets around q2 with parenteheses. (Or maybe q1??)

SELECT q2.*, TableRecCo(q2.MyTable,"&quot AS Records
FROM
(SELECT q1.*,
IIf(Len(o.Name)>0,"Yes","No&quot AS TableExist
FROM
[SELECT
STATE.ST, FRUIT.TYPE,
ST+"_"+TYPE AS MyTable
FROM STATE, FRUIT
ORDER BY STATE.ST, FRUIT.TYPE]. AS q1
LEFT JOIN MSysObjects AS o
ON q1.MyTable = o.Name
ORDER BY q1.MyTable) AS q2
WHERE q2.TableExist="Yes";

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Terry,

I tried your suggestion and I got a different error message:

Syntax error in FROM clause

It seems like one or the other would work. Maybe Access has a limitation on the number of brackets for nested queries it can handle (in this case one).

Can you think of anything else?

TIA,
GGleason

 

[tlbroadbent]

Sometimes It is hard to see the obvious. Remove the ORDER BY clause from the sub-query. Add the ORDER BY clause to the final query after you get the selection to work.

SELECT q2.*, TableRecCo(q2.MyTable,"&quot AS Records
FROM
(SELECT q1.*,
IIf(Len(o.Name)>0,"Yes","No&quot AS TableExist
FROM
[SELECT
STATE.ST, FRUIT.TYPE,
ST+"_"+TYPE AS MyTable
FROM STATE, FRUIT
ORDER BY STATE.ST, FRUIT.TYPE]. AS q1
LEFT JOIN MSysObjects AS o
ON q1.MyTable = o.Name) AS q2
WHERE q2.TableExist="Yes"; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Terry,

I tried to make the query as basic as I could by doing the following:

SELECT q2.*
FROM
[SELECT q1.*,
IIf(Len(o.Name)>0,"Yes","No&quot AS TableExist
FROM
[SELECT
STATE.ST, FRUIT.TYPE,
ST+"_"+TYPE AS MyTable
FROM STATE, FRUIT]. AS q1
LEFT JOIN MSysObjects AS o
ON q1.MyTable = o.Name]. AS q2

I then tried different permutations of brackets and parenthesis w/ the following errors:

[[ ]] Syntax error in query expression 'q1.MyTable = o.Name]. AS q2'.
[( )] Syntax error in FROM clause
([ ]) Syntax error in FROM clause
(( )) Syntax error in FROM clause

Any other ideas?

TIA,
GGleason

 

[tlbroadbent]

This query now passes the syntax check. Want to try again?

SELECT q2.*
FROM
[SELECT q1.*,
IIf(Len(Name)>0,"Yes","No&quot AS TableExist
FROM
(SELECT
STATE.ST, FRUIT.TYPE,
ST+"_"+TYPE AS MyTable
FROM STATE, FRUIT) AS q1
LEFT JOIN MSysObjects AS o
ON q1.MyTable = o.Name]. AS q2
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Terry,

I am still getting an error (Syntax error in FROM clause). So if you have had success running the query then there may be another issue at work. I am running Access 97 SR-2. Could this be a problem related to the version of Access? I do appreciate your help.

GGleason

 

[tlbroadbent]

The query looks OK but having created the tables I get the same error in Access 97 SR2. The only solution I've found is to save the first query and use it in a second query as follows. It's too bad the combined query won't work. At least I can't get it to work.

1st Query: saved as qrySelectStateFruit
SELECT STATE.St, FRUIT.Type, ST & "_" & TYPE AS MyTable
FROM STATE, FRUIT;

2nd Query:
SELECT q1.*, iif(o.name is null, "No", "Yes&quot As TableExist
FROM qrySelectStateFruit as q1 Left Join MSysObjects AS o
ON q1.MyTable=o.Name; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GGleason]

Terry,

Thanks for the help! At least the error is consistent.

GGleason