Query within a Query - SQL in VBA

[Steve S]

I have 2 Queries that are used to update a table. Query 14 selects 3 records
from the table Fees that are then used to update 9 other records in table
Fees.

Currently Query14 is a saved query that is used in Query15 that is an SQL
string in VBA. Is there a way to ‘imbed’ Query 14 as a string in Query15 so
I do not have to save Query 14 as a saved query.

Or is there a way to rewrite Query15 so I can do the update in one pass.

Query14
SELECT Fees.[Contest ID], Fees.[Fee ID], Fees.Level
FROM Fees
WHERE (((Fees.[Contest ID])=42) AND ((Fees.Triathlon)=True));

Query 15
UPDATE Fees INNER JOIN Query14 ON Fees.Level = Query14.Level SET
Fees.TriAthID = [Query14].[fee id]
WHERE (((Fees.TriAthID)>0) AND ((Fees.[Contest ID])=42));

Contest ID Fee ID Level Triathlon TriAthID
42 1359 Advanced Yes
42 1370 Advanced No
42 1369 Advanced No
42 1368 Advanced No
42 1358 Beginner Yes
42 1373 Beginner No
42 1372 Beginner No
42 1371 Beginner No
42 1357 Novice Yes
42 1363 Novice No
42 1362 Novice No
42 1361 Novice No



Contest ID Fee ID Level Triathlon TriAthID
42 1359 Advanced Yes
42 1370 Advanced No 1359
42 1369 Advanced No 1359
42 1368 Advanced No 1359
42 1358 Beginner Yes
42 1373 Beginner No 1358
42 1372 Beginner No 1358
42 1371 Beginner No 1358
42 1357 Novice Yes
42 1363 Novice No 1357
42 1362 Novice No 1357
42 1361 Novice No 1357

 

[Bob Barrows [MVP]]

I have 2 Queries that are used to update a table. Query 14 selects 3
records from the table Fees that are then used to update 9 other
records in table Fees.

Currently Query14 is a saved query that is used in Query15 that is an
SQL string in VBA. Is there a way to 'imbed' Query 14 as a string in
Query15 so I do not have to save Query 14 as a saved query.

I do not understand your reluctance to save your query. there are several
advantages to using saved queries. However, instead of a saved query, you
can use a subquery:

FROM (sql of query) as q ...

 

[Steve S]

Thanks Bob,

And just what are the advantages of a saved query? Maybe I am missing
something that would help me in the future. Seems to me the less 'stuff' you
have to keep track of the better, specially if th equery is never changed (I
know - never say never).

 

[Bob Barrows [MVP]]

The main advantage is re-use.
A secondary advantage is improved performance since a saved query is parsed
and compiled as opposed to a string you are feeding to the database engine.

 

[Lord Kelvan]

that and you should give your queries more meaningful names so you
know what they are doing for the reuse and another advantage is if the
query is a one off thing and for some reason later off down the track
you happen to need to do something simmilar you can always refurre
back and see what you done if you dont happen to remember exatally

 

[Lord Kelvan]

it is not possible as far as i know because you are trying to update
data from a rown that is not the row you are trying to update what i
am questioning is why triathid is the same as feeid the mroe i look at
your data the more i begin to question your table structure and if it
is set out right from what it seems youa re trying to do a uniory join
connecting 1 something like a sub triathlon to the main triathlon
event.

Contest ID Fee ID Level Triathlon TriAthID
42 1359 Advanced Yes
42 1370 Advanced No 1359
42 1369 Advanced No 1359
42 1368 Advanced No 1359

problem is 1 access cannot handle uniory joins and 2 you dont really
do uniory joins like that you should use another table it will make
things eaiser to work with

ie

fees ----<feestofeeslink>----fees

so in the feestofeeslink table you will have

feestofeeslink
masterfeesid number
subfeesid number

and the data will looklike

feestofeeslink
masterfeesid subfeesid
1359 1368
1359 1369
1359 1370

and that data then tells me that fee 1368 1369 and 1370 are linked to
1359

and then that makes reporting so much eaiser

so when you are going your forms to insert these records you have the
user select the fee from the fees table which is the masterfee and
then insert the records into the feestofeeslink table

the reason i said access cannot handle uniory joins is that you cannot
do any referental intergertry on them or inner joins or things like
that in queries though there are work arounds for the latter

Hope this helps

Regards
Kelvan

 

[Steve S]

Thanks for the input. The following gives me axactly what I needed.

UPDATE Fees AS F1 INNER JOIN Fees AS F2 ON (F1.Level = F2.Level) AND
(F1.[Contest ID] = F2.[Contest ID]) SET F1.TriAthID = [F2].[Fee ID]
WHERE (((F1.TriAthID)>0) AND ((F1.[Contest ID])=42) AND
((F2.Triathlon)=True));

The subquery WHERE((..... was the key. The 42 has been replaced with a
variable, everything tested , and going into production Monday.

If I keep a query as a stored query I do give it a more discriptive name
than QueryX. since this query will only be executed about once a month and
is so unique it best fits as a SQL string in VBA.

thanks

 

[Bob Barrows [MVP]]

Thanks for the input. The following gives me axactly what I needed.

UPDATE Fees AS F1 INNER JOIN Fees AS F2 ON (F1.Level = F2.Level) AND
(F1.[Contest ID] = F2.[Contest ID]) SET F1.TriAthID = [F2].[Fee ID]
WHERE (((F1.TriAthID)>0) AND ((F1.[Contest ID])=42) AND
((F2.Triathlon)=True));

The subquery WHERE((..... was the key. The 42 has been replaced
with a variable, everything tested , and going into production Monday.

Huh? I do not see a subquery there. A subquery is an entire sql statement
enclosed by parentheses. This sql statement has no such thing.

Here is a silly example:

Select q.* FROM
(Select * FROM SomeTable WHERE ... ) as q
WHERE ...

Here is an example of a subquery used in a WHERE clause:
Select * from sometable as t
WHERE EXISTS (
select * from someother table where thisid= t.id)

Here is an example of a subquery used as a column expression (known as a
correlated subquery):
Select id,
(select max(cost) from costtable where thisid=t.id) as MaxCost,
<more fields>,
from sometable

Your statement is a simple inner join of two tables with a standard WHERE
clause.