SQL Table Updates in VBA

[smeghead]

I'm currently using a Query to update a table. The SQL looks like this:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");

Because I have many, many of these updates to do. (150) Instead of creating
a Query for each one, I'd like to use a module and VBA to do in more
efficiently.

So the multiple SQL updates would look something like:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");
UPDATE [Summary Table] SET [AP Totals] =
DCount("*","Combined_120804","[Region]='Asia Pacific'");
UPDATE [Summary Table] SET [Europe Totals] =
DCount("*","Combined_120804","[Region]='Europe'");
.....etc.

Can someone help me figure out how to do this in a VBA module?

 

[Tom Wickerath]

You will need a table that includes your regions, such as "Americas", "Asia Pacific", "Europe",
etc., along with the name of the field in your summary table that needs to be updated: [Americas
Totals], [AP Totals], [Europe Totals], etc. So, the table would have paired values, like this:

Region RegionField

Americas Americas Totals
Asia Pacific AP Totals
Europe Europe Totals
etc.

You would then open a recordset against this table and run a loop, one time for each record in
this table, where you substitute the names of the region and the regionfield into the update
query.

Now, the question arises.....why exactly are you doing this? You can certainly create sums in
grouped reports, without having to first store the results of a calculation in a table. It
appears as if you are trying to store a value that is calculated in a table named Summary Table.
Storing any calculated results violates 2nd and 3rd normal forms for database design. Further,
the design of Summary Table, with one field for each total, represents a multi-valued design,
which violates 1st normal form for database design.

Tom
___________________________________


I'm currently using a Query to update a table. The SQL looks like this:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");

Because I have many, many of these updates to do. (150) Instead of creating
a Query for each one, I'd like to use a module and VBA to do in more
efficiently.

So the multiple SQL updates would look something like:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");
UPDATE [Summary Table] SET [AP Totals] =
DCount("*","Combined_120804","[Region]='Asia Pacific'");
UPDATE [Summary Table] SET [Europe Totals] =
DCount("*","Combined_120804","[Region]='Europe'");
.....etc.

Can someone help me figure out how to do this in a VBA module?

 

[Marshall Barton]

I'm currently using a Query to update a table. The SQL looks like this:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");

Because I have many, many of these updates to do. (150) Instead of creating
a Query for each one, I'd like to use a module and VBA to do in more
efficiently.

So the multiple SQL updates would look something like:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");
UPDATE [Summary Table] SET [AP Totals] =
DCount("*","Combined_120804","[Region]='Asia Pacific'");
UPDATE [Summary Table] SET [Europe Totals] =
DCount("*","Combined_120804","[Region]='Europe'");
....etc.

Can someone help me figure out how to do this in a VBA module?

Oh the pain of working with unnormalized tables, uuggghhh.

Let's see, if you create a table named Regions with two
columns: RegionName and RegionColumn and populate it with
150 rows like this:

Americas Americas Totals
Asia Pacific AP Totals
Europe Europe Totals
. . .

Then you could use code like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Regions", dbOpenDynaset)
Do Until rs.EOF
db.Execute "UPDATE [Summary Table] SET [" _
& rs!RegionColumn &"] =" _
& DCount("*", "Combined_120804", _
& "[Region]='" & rs!RegionColumn & "'")
rs.MoveNext
Loop
rs.Close : Set rs = Nothing
Set db = Nothing

If you put that code behind a command button on a form, you
could also use a text box to get the current date part of
the summary table's name. Then you would not have to modify
the code every time you created another one of these
spreadsheets, err ahh, tables ;-)

 

[smeghead]

Thanks guys!!!

I'm currently using a Query to update a table. The SQL looks like this:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");

Because I have many, many of these updates to do. (150) Instead of creating
a Query for each one, I'd like to use a module and VBA to do in more
efficiently.

So the multiple SQL updates would look something like:

UPDATE [Summary Table] SET [Americas Totals] =
DCount("*","Combined_120804","[Region]='Americas'");
UPDATE [Summary Table] SET [AP Totals] =
DCount("*","Combined_120804","[Region]='Asia Pacific'");
UPDATE [Summary Table] SET [Europe Totals] =
DCount("*","Combined_120804","[Region]='Europe'");
....etc.

Can someone help me figure out how to do this in a VBA module?

Oh the pain of working with unnormalized tables, uuggghhh.

Let's see, if you create a table named Regions with two
columns: RegionName and RegionColumn and populate it with
150 rows like this:

Americas Americas Totals
Asia Pacific AP Totals
Europe Europe Totals
. . .

Then you could use code like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Regions", dbOpenDynaset)
Do Until rs.EOF
db.Execute "UPDATE [Summary Table] SET [" _
& rs!RegionColumn &"] =" _
& DCount("*", "Combined_120804", _
& "[Region]='" & rs!RegionColumn & "'")
rs.MoveNext
Loop
rs.Close : Set rs = Nothing
Set db = Nothing

If you put that code behind a command button on a form, you
could also use a text box to get the current date part of
the summary table's name. Then you would not have to modify
the code every time you created another one of these
spreadsheets, err ahh, tables ;-)