Complex Query and Select statement (1 Viewer)

R

richardw

Registered User.
Local time
Today, 05:39
Joined
Feb 18, 2016
Messages
48
Hello Dear All,

I have a multi-user database where each user has its own perimeter (Team).

I am tryin to run a select statement that runs on several times based on the teams assigned for the user.

Here is the select statement:

Code: 
OpSQL = " SELECT tbl_temp.type, sum(tbl_temp.value) FROM tbl_temp WHERE tbl_temp.gFilter<=" & Me.cboOpening.Column(0) & " AND tbl_temp.teamName='" & Me.lboTeamName.Value & "' GROUP BY tbl_tempGraph.typeActual ORDER BY tbl_tempGraph.typeActual ASC "


After that, I would like to run another query based on the results found before (Which means teamname and the corresponding selected sum and type).


Here are the query parameters:
Code: 
Set Qdf = db.QueryDefs("rupd_Temp")
    
    With Qdf
        .Parameters("var1") = 'Sum
        .Parameters("var2") = Me.cboOpening.Column(0)
        .Parameters("var3") = 'Team
        .Parameters("var4") = 'This is the type
        .Parameters("var5") = TempVars!struserid
        .Execute

I hope you could help me guys,
Thank you in advance,
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,233
so how can we help you?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 13:39
Joined
Feb 19, 2013
Messages
16,607
your group by query wont work as written

you have

Code: 
 ...SELECT tbl_temp.type, sum(tbl_temp.value) ....
 ...FROM tbl_temp WHERE ...
 ... GROUP BY tbl_tempGraph.typeActual ....
1. you need to group by tbl_temp.type
2. type is a reserved word and should not be used as a field name
3. tbl_temp.value implies the use of a multiselect field
4. your FROM part of the query does not include tbl_tempGraph

and finally
5. ...& Me.lboTeamName.Value .... .value not required - it is the default property for the control - the same may be said for ...& Me.cboOpening.Column(0) ... but depends on which is the bound column (it is usually the first column)

if these are typo's on your part, please copy and paste the actual code - typos just distract from the real issue
 
R

richardw

Registered User.
Local time
Today, 05:39
Joined
Feb 18, 2016
Messages
48
Hi dear All,

Thank you for advices, but that is not the issue :) I am using reserved words just in the forum to simplify.

Here is my code and it is working, it loops trough the assigned teams to each user.

Code: 
    i = CLng(TempVars!lboCount)

    
  Do Until i = -1
  
    
    OpSQL = " SELECT tbl_temp.type, sum(tbl_temp.value) FROM tbl_temp WHERE tbl_temp.GraphFilter<=" & Me.cboOpening.Column(0) & ""
    DoEvents

    OpSQL = OpSQL & " AND tbl_temp.team='" & Me.lboAssignedTeams.Column(0, i) & "' GROUP BY tbl_temp.type ORDER BY tbl_temp.type ASC "
    DoEvents

    Me.lboOpeningval.RowSource = OpSQL
    DoEvents
    
    With Qdf
        .Parameters("var1") = Me.lboOpeningval.Column(1, 0)
        .Parameters("var2") = Me.cboOpening.Column(0)
        .Parameters("var3") = Me.lboAssignedTeams.Column(0, i)
        .Parameters("var4") = "EXT"
        .Parameters("var5") = TempVars!struserid
        .Execute
    DoEvents
    

        .Parameters("var1") = Me.lboOpeningval.Column(1, 1)
        .Parameters("var2") = Me.cboOpening.Column(0)
        .Parameters("var3") = Me.lboAssignedTeams.Column(0, i)
        .Parameters("var4") = "INT"
        .Parameters("var5") = TempVars!struserid
        .Execute
    DoEvents
    

        .Parameters("var1") = Me.lboOpeningval.Column(1, 2)
        .Parameters("var2") = Me.cboOpening.Column(0)
        .Parameters("var3") = Me.lboAssignedTeams.Column(0, i)
        .Parameters("var4") = "OTH"
        .Parameters("var5") = TempVars!struserid
        .Execute
    DoEvents
    
    End With

    
    i = i - 1
    DoEvents
    
    Me.lboOpeningval.RowSource = ""
    DoEvents
    
  Loop
    
    Set db = Nothing
    Set Qdf = Nothing

But when I have a user with strictly more than 15 assigned teams, the counter (i) stops on 14.. It is driving me crazy.. And when it less than or equal to 15, it stops on -1 as wanted..

Is it a limit of Access or am I doing it wrong?

Thank you in advance.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,233
how about using a For..Next loop:

Dim iLoop As Integer
For iLoop = i To 1 Step -1
' your code here
...
...
Loop iLoop
 
R

richardw

Registered User.
Local time
Today, 05:39
Joined
Feb 18, 2016
Messages
48
I think that it is actually Next instead of Loop..

I tried it, but still have the same problem, it stops on 14 :(
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 13:39
Joined
Feb 19, 2013
Messages
16,607
I am using reserved words just in the forum to simplify
Click to expand...
OK, I prefer to help based on real code rather than pseudo code so I'll leave for arnelgp to respond. Good luck with your project
 
You must log in or register to reply here.

Similar threads

A
Solved VBA Code to run a series of queries and to check the record count and based on record count either continue or display message
Replies
12
Views
1,736
arnelgp
arnelgp
R
Nasty Quote issue in VBA Queries
Replies
4
Views
1,888
arnelgp
arnelgp
P
MS Access OpenRecordset parameters and too few parameters issue
Replies
7
Views
5,882
suzeg
S
W
Running Select Query in MS Access 2007 vba based on a value in a textbox
Replies
2
Views
527
wchitamb
W
S
Question Run SQL and export query to CSV
Replies
5
Views
5,922
sneuberg
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom