Results 1 to 2 of 2
  1. #1
    Join Date
    May 2013

    Unanswered: Performing a Calculation based on fields located in two seperate queries

    I am wondering how to perform calculations using data returned by a query.

    My program begins by prompting a user for their state and zip code

    After they use the command button on the form, a macro runs two queries, one to determine the city and corresponding latitude and longitude, and one to return the zip code, city, address, and latitude and longitude for all predetermined locations within the specified state.

    For example : User is prompted and enters 20850 as the Zip code and MD as the state.
    Once they hit the command button, the query UserLocation, will return 20850 Rockville MD and the corresponding coordinates.
    Additionally, the second query, Building Location, will return all of the locations for the company within that specific state, as well as their coordinates. In the case of this example, MD will return 51 entries.

    This is where i being to have problems

    The next step in my program needs to allow me to perform a calculation to determine the as the crow flies milage between the zip code for the customer and the zip code for the company locations in the specified state.

    I have the Function for distance written already, I just need some assistance with figuring out how to reference the queried data when trying to assign values to my variables.

    For example :

    How would i set LatitudeA and LongitudeA to be the coordinates returned by the UserLocation query?

    My second problem is that once i can assign the first entries field values to be the variables, how can perform the function for each entry returned by the building variable?

    For example :
    LatB and LongB would be assigned their values from query BuildingLocation. However, this query returns 51 results.

    I figure i need to have a looping structure do something to the effect of :

    Do Function Distance while counter <= entries

    Is there a way that i can automatically calculate the number of entries? and then assign this to a variable?

    Additionally, it is important to note that i've looked into possible ways to perform these actions using recordset and Itemselected, but i'm not entirely sure if i properly understand how to use these tools

    Just to reiterate: My question is how to assign a VBA variable its value based on data returned in specific query fields. I also am wondering if anyone has some insight into how to setup some type of loop/array to calculate the number of entries returned so that I can perform calculations for each record.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    providing there is some common piece of information then you can link queries as f they were tables using the join syntax

    Select CalculateDistance(Customer.sLatitude,Customers.Longitude, Depots,Latitude, Depots.Longitude) as Distance from Customers, Depots
    Join Depots on Customers.StateCode = Depots.StateCode
    ...the above is an example you would need to change the table names (Customers & Depots), the column names, the function name and so on but it shoudl give you the idea. providing there is a common piece of information you can JOIN data together in a raltional db using tables or queries (or in other db engines views)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts