Assign multiple values from querey to variables

Posted on 2009-04-18
Last Modified: 2013-12-25
I have a query that retrieves 16 lines

the result would be like this


12345, 1, 50
45678, 2, 100
23456, 3, 25

What I need to do is assign the quantity to a Global variable for each Location

I have these Global Variables


How can I query the DB and assign the appropriate value to the appropriate Global Variable

GIndicator1 = 50
GIndicator2 = 100
GIndicator3 = 25
Question by:rmardis
    LVL 15

    Expert Comment

    I don't understand what you mean about "Global Variables", as SQL has no variables.

     I recommend you to execute this SELECT:

    SELECT Location, SUM(Quantity) as TotalQuantity
    FROM (your original table or query here)
    GROUP BY Location;

    This would display the result you want.
    LVL 11

    Expert Comment

    What are you going to be consuming this with? A VB6 app? A Classic ASP web application? Need more details.

    Author Comment

    I'm sorry I wasn't very clear on the question

    i need to populate vb Global Variables with the returned values from the sql query

    My sql table has 3 fields

    i need to take the quantity value from the returned query and place it in a vb global variable where
    the variable represents the location

    My sql query returns what i need but it have 16 lines of data

    in VB6 i have the Global Variables that need to be filled with the data returned from the DB

    such that

    select * from sometable would return

    Item       Location       Quantity
    12345     1                     50
    34567     2                     75
    65768     3                     35

    in the record set

    I need to match the location value with the vb global variable with its corresponding name

    I need to assign the value for location 1 to Gindicator1

    GIndicator1 = 50
    GIndicator2 = 75
    GIndicator3 = 35

    Author Comment

    If my query were to return only 1 record like the following

    select * from sometable where location = 1

    then i would only have to assign the value like this

    GIndicator1 = rsSomeRecordSet!Quantity

    It's more of a how to loop through the recordset returned and tie the values
    for the location to the appropriate Global Variable

    GIndicatorX = LocationX    the X value indicates the relationship

    GIndicator1 would be Location 1 in the DB
    GIndicator2 would be Location 2 in the DB

    since the query will return the values for all 16 locations how can it assign the appropriate value to the appropriate Global Variable
    LVL 15

    Expert Comment

    Use the SELECT I said before with an ORDER BY:

    SELECT Location, SUM(Quantity) as TotalQuantity
    FROM (your original table or query here)
    GROUP BY Location;

    Put this SELECT on a cursor

    Instead of using 12 global variables, use a single array variable with 12 elements.

    Fetch every row of the cursor and assign values from cursor the n element of the array.
    LVL 15

    Expert Comment

    Sorry, the SELECT would be:

    SELECT SUM(Quantity) as TotalQuantity
    FROM (your original table or query here)
    GROUP BY Location
    ORDER BY Location;

    Author Comment

    i don't want to sum the values
    the values are going into the variables so they can then be written to 16 different digital indicators

    there are time the RS would only return a few lines and not always the 16 records in the table

    LVL 4

    Expert Comment

    If you post your code we can better see what you are doing and better understand what you are trying to do.
    LVL 11

    Accepted Solution

    Here you go... I don't have the ability to run VB6 where I am (nor do I have a Classic ASP site to test on)... so try the code below and post any errors that you run into so that we can debug it. Please let me know if my example wasn't clear.
    Dim GIndicator1
    Dim GIndicator16
    While Not rs.EOF
      If rs(1) = 1 Then
        GIndicator1 = rs(2)
      Else If rs(1) = 2 Then
        GIndicator2 = rs(2)
      Else If rs(1) = 16 Then
        GIndicator16 = rs(2)
      End If

    Open in new window


    Author Closing Comment

    Your nailed it
    Thank you so much!!!

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    755 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

    24 Experts available now in Live!

    Get 1:1 Help Now