Can I use a Variable In a SQL Statement

NIMBUS

Registered User.
Local time
Today, 01:25
Joined
Mar 7, 2007
Messages
15
This is my first posting on a forum so I hope it makes sense. I would appreciate any help available
I run the SQL shown below from a form button using DoCmd.RunSql without any trouble. Table A is a yearly rota for team A. I also have table B for team B, table C for team C etc.

INSERT INTO [All Teams] ( Name, [Tot Hrs] )
SELECT Namelist.Name, Sum(A.[Tot Hrs]) AS [SumOfTot Hrs]
FROM (A INNER JOIN Namelist ON A.Team = Namelist.Team) INNER JOIN Calendar ON A.Date = Calendar.Date
WHERE (((Calendar.Week)=[forms]![selection]![week]))
GROUP BY Namelist.Name;

Can I use a variable (in place of A in the SQL) and a For – Next loop so that after the SQL has appended the required records from table A into table All Teams it then loops and appends the same records from table B then table C etc. If this is possible could somebody please show me how to do it.
Many Thanks
St
 
There are a number of ways to do this.

You could take that script of yours that you're running using the RunSQL command and alter it like so:

Code:
Dim x as integer
Dim tbl as String

For x = 0 to [I]Number Of Teams Named A thru Z[/I] - 1
   tbl = Chr(x + 65) ' Will give you letters:  Chr(65) = A
   Docmd.RunSQL "INSERT INTO [All Teams] ( Name, [Tot Hrs] )
        SELECT Namelist.Name, Sum(" & tbl & ".[Tot Hrs]) AS [SumOfTot Hrs]
        FROM (" & tbl & " INNER JOIN Namelist ON " & tbl & ".Team = Namelist.Team) INNER JOIN Calendar ON " & tbl & ".Date = Calendar.Date
WHERE (((Calendar.Week)=[forms]![selection]![week]))
GROUP BY Namelist.Name;"

Next x

Alternatively, you could make a UNION query based on Table A, B, C etc, call it "AllTeams" then alter your query to be:

Code:
INSERT INTO [All Teams] ( Name, [Tot Hrs] )
SELECT Namelist.Name, Sum(AllTeams.[Tot Hrs]) AS [SumOfTot Hrs]
FROM (A INNER JOIN Namelist ON AllTeams.Team = Namelist.Team) INNER JOIN Calendar ON AllTeams.Date = Calendar.Date
WHERE (((Calendar.Week)=[forms]![selection]![week]))
GROUP BY Namelist.Name;

Regards,
Pete.
 
Pete
Many thanks for quick reply I will try your suggestions in the morning. Much appreciated.
St
 
Pete,

FYI from Access Help, not VBA help.
"Data can't be updated"
Query or query field Solution
Query based on three or more tables in which there is a many-to-one-to-many relationship Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
Crosstab query None
SQL pass-through query None
Query that calculates a sum, average, count or other type of total on the values in a field, or an update query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
Union query None
Query whose UniqueValues property is set to Yes None
In other words I'm pretty sure a UNION query is NOT updateable.
 
Pete
tried your suggested code, works perfectly :) Many thanks

Also thanks RuralGuy for the Quote
 

Users who are viewing this thread

Back
Top Bottom