Solved

VBA SQL How to design an update query with a changing where clause

Posted on 2014-04-07
8
837 Views
Last Modified: 2014-04-13
Good Morning Guru's

I am hitting a wall with this.  Here's the deal.
Platform: Access 2010
Programming data model: DAO
issue: I have to update a table to based upon data in that table based on specific criteria.
For example, we have to get the "total's" for a number of groups
Each group will have a different number of subgroups
so, say there is Grp1, and Grp1 has Grp1sub1, Grp1sub2 - and the sub groups need some values totaled.
Now say, there is Grp2 and Grp2 has Grp2sub1, Grp2sub2, Grp2sub3, Grp2sub4 - and these sub groups need there values totaled also.

I am having an issue with creating a query in VBA and will adjust the query statement to handle the different number of subgroups for each Group.

Is this at all possible?
0
Comment
Question by:UserName935
  • 6
  • 2
8 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39983163
I have to update a table to based upon data in that table based on specific criteria
Storing totals is not normally done in a properly normalized database UNLESS the database is a data warehouse where the data is static.  Storing calculated values leads to data anomalies because as soon as one of the underlying records is updated, the total is invalid.  That is why we calculate totals in queries or reports rather than storing them.

If your table is properly normalized, it won't matter how many groups/subgroups there are.  All will be totaled.

You may want to look at a crosstab query if you want the groups spread across the top like a spreadsheet.  

To display the group/subgroups as a list:

Select group, subgroup, Sum(youramt) as SubGroupTotal
From yourtable
Group By group, subgroup;
0
 

Author Comment

by:UserName935
ID: 39983208
Good Morning,

Yes, I hear what you are saying and agree. And, the data in the table is coming as an imported spreadsheet, then, there are a number data manipulating activities, then, that table gets exported back to the excel sheet, then the table contents deleted until import the following month.
The main question is, how does one, if at all possible, create a query in VBA (DAO) that will allow the Where clause of the query to change in the number of where conditions.  I was going to use a Do while loop to catch each condition, or, variable from a table in order to match the Grp's and Subgroup's.

Your thoughts?
0
 

Author Comment

by:UserName935
ID: 39983254
Maybe this may help clarify.  The below structure is what I have so far.

Dim db1 As DAO.Database
Dim rs2 As DAO.Recordset
Dim strUpdateSQL As String
Dim ResultString As String
Dim ResultString2 As String
Dim HereIsYear As String
Dim Variable1 As String
Dim Variable2 As String
Dim Variable3 As String

Set db1 = CurrentDb()
HereIsYear = 2014
Variable1 = "calcHC" ' There are 12 other values in addition to CalcHC,
Variable2 = "Total"
Variable3 = "CE Prog Other" ' There are 22 other values in addition to CalcHC,

'Query that Sum's the CalcHC
strsql = "SELECT DISTINCT Sum(MOR_ChartDBSource_Template.Jan) AS SumOfJan, Sum(MOR_ChartDBSource_Template.Feb) AS SumOfFeb " & _
"FROM MOR_ChartDBSource_Template " & _
"WHERE (((MOR_ChartDBSource_Template.CHARTDB_BIN) Like ""CalcHC"") AND ((MOR_ChartDBSource_Template.MOR_GROUP) Like '*CE Prog B Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*CE Prog CD Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*CE Prog C Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*CE Prog Large Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*CE Prog Truck Other*'));"

'Open SQL Dynaset
Set rs2 = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)

'Resulting string values for Update process
ResultString = rs2!SumOfJan
ResultString2 = rs2!SumOfFeb

'Error control Check
MsgBox ResultString

'Completes the Totals update
strUpdateSQL = "UPDATE MOR_ChartDBSource_Template " & _
    "SET MOR_ChartDBSource_Template.Jan = '" & ResultString & "' " & _
    "WHERE MOR_ChartDBSource_Template.YEAR Like '2014' " & _
    "AND MOR_ChartDBSource_Template.CHARTDB_BIN Like 'calcHC' " & _
    "AND MOR_ChartDBSource_Template.ORG_ABBR Like 'Total' " & _
    "AND MOR_ChartDBSource_Template.MOR_GROUP Like 'CE Prog Other' "
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39983419
It is always better to do these things with tables.  You seem to be grouping values of a field named - MOR_GROUP into a different group.  Create a table to do this for you.  That will allow you to run a single query that calculates a value for each group of groups.  It is also easy to change because you can just add a new group/subgroup to the grouping table.

Because you are importing/exporting to Excel, you are thinking of this as a spreadsheet problem when it is actually a set problem that can easily be solved with simple SQL.

'Query that Sum's the CalcHC
strsql = "SELECT DISTINCT Sum(MOR_ChartDBSource_Template.Jan) AS SumOfJan, Sum(MOR_ChartDBSource_Template.Feb) AS SumOfFeb " & _
"FROM MOR_ChartDBSource_Template Inner Join tblGroups On  MOR_ChartDBSource_Template.MOR_GROUP = tblGroups.Group;"

The update will need to be changed programmatically each month since you have hard-coded the month into a column name.  You can probably solve this problem by using a crosstab query that creates a column for each month and a row for each group.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:UserName935
ID: 39983629
I agree with using tables as a query foundation is in best practice.

Wow, Umm, this is turning out to be way more complex than I thought it was going  to be.

ah, umm,  The above query would work, except, well, let me put it this way.

there is a 'spreadsheet' brought into a single record table.

The table is constructed like this.
table structure:
                                         month1  month2   month3   month4  etc.......
grp1   money                        5.00       6.23        52.3         54.21
grp1   something
grp1   something1
grp1   average
grp2   money                        5.36        8.21        4.14          5.98
grp2   something
grp2   something1
grp2   average
grp3   money                        6.00       9.23         2.3            5.21
grp3   something
grp3   something1
grp3   average
grpX   Total            
grpY   Total


Current # of records: 8562
Current number of grp's: about 150
Hook:  For the "total" a grp may consist of one or more grps.  For example, say grpX needs the totals for grp1&grp2
Yet, grpY would need the totals for grp2, grp5, grp7
The number of groups ie: grp1, grp2 etc... for each month will vary, so, one month there may be 150 groups, next month there will be 100 groups and so on.

The issue is how can the query 'auto adjust' for the Where condition when the number grp's vary from each group that calls for a total.

I absolutely see the value in creating a table that houses which "total group" houses which grp's.  the question is how can one change the where clause so that the appropriate totals are gathered from the correct "sub-groups"

I know I am probably doing a 'not so fine' job is describing the issue, so feel free to continue to ask questions, and, I DO appreciate your assistance, this issue has been going on for a while and I would really like to put it behind me.
0
 

Author Comment

by:UserName935
ID: 39983734
More Explaination:

So, in the query the variables that would have to change is 1. CalcHC and 2. the criteria.  In this case B Other, CD Other, C Other, Large Other, Truck Other

There are 10 additional variables to CalcHC and about 50 or so combonations of groups that create the 'groups' to which the totals are needed.

"WHERE (((MOR_ChartDBSource_Template.CHARTDB_BIN) Like ""CalcHC"") AND ((MOR_ChartDBSource_Template.MOR_GROUP) Like '*B Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*CD Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*C Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*Large Other*' Or (MOR_ChartDBSource_Template.MOR_GROUP) Like '*Truck Other*'));"
...
0
 

Accepted Solution

by:
UserName935 earned 0 total points
ID: 39984176
I changed most of all the code and am having issue with just one line

code:
Dim db1 As DAO.Database
Dim rs3 As DAO.Recordset ' ChartDB_BINN values ie: calcHC
Dim rs4 As DAO.Recordset ' TotalsLoopTable values ie: Groups to get values
Dim rs5 As DAO.Recordset ' Used to carry variables for update query
Dim strUpdateSQL As String
Dim ResultString As String
Dim ResultString2 As String
Dim HereIsYear As String
Dim Variable1 As String
Dim Variable2 As String
Dim Variable3 As String
Dim month As String

month = Me.MonthList.Value

MsgBox month

Set rs3 = CurrentDb.OpenRecordset("ChartDB_BinTable")
    Do Until rs3.EOF
        Variable1 = rs3!CHARTDB_BIN
       
               
            Set rs4 = CurrentDb.OpenRecordset("TotalsLoopTable")
                 Do Until rs4.EOF
                    Variable2 = rs4!SqlStringCode ' Where clause
                    Variable3 = rs4!TotalsGroupName
                   
                strsql = "SELECT DISTINCT Sum(MOR_ChartDBSource_Template.Jan) AS SumOfJan, Sum(MOR_ChartDBSource_Template.Feb) AS SumOfFeb " & _
                "FROM MOR_ChartDBSource_Template " & _
                "WHERE ((MOR_ChartDBSource_Template.CHARTDB_BIN) Like  '" & Variable1 & "' ) '" & Variable2 & "'"
                       
                        MsgBox strsql
                       
                    'Open SQL Dynaset
                    Set rs5 = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)

                    'Resulting string values for Update process
                        ResultString = rs2!SumOfJan
                        ResultString2 = rs2!SumOfFeb

                        'Error control Check
                        MsgBox ResultString

                            'Completes the Totals update
                                strUpdateSQL = "UPDATE MOR_ChartDBSource_Template " & _
                                "SET MOR_ChartDBSource_Template.Jan = '" & ResultString & "' " & _
                                "WHERE MOR_ChartDBSource_Template.YEAR Like '2014' " & _
                                " & ' & Variable2 & ' " & _
                                "AND MOR_ChartDBSource_Template.MOR_GROUP Like ' & TotalsGroup ' & "

                                DoCmd.RunSQL strUpdateSQL
                                rs5.MoveNext
                                Loop



rs3.MoveNext
    Loop
0
 

Author Closing Comment

by:UserName935
ID: 39997137
I do not know how to handle this case.  I have given up on this solution tactic and am moving on and wish for this question to be closed out.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now