calculations between records

plenarts · #1 (**permalink**) December 28th, 2004, 04:36 AM

Goodmorning,

I have a question concerning calculation between records in a table or query in access.
I need to extract a value in column x of record 1 from a value in column y in record 2 and then a value in column x in record 2 from a value in column Y in record 3 etc. Is there a way to do this is access or in VBA (has my preference), and how to handle end of file(Last record)

Thanks Paul

jemacc · #2 (**permalink**) December 28th, 2004, 09:40 AM

create a query and do your calculation

use this as a start

Code:

SELECT [Order Details].OrderID, [Order Details].Quantity, [Order Details].UnitPrice, Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal, Count([Order Details].OrderID) AS [Count], Sum([UnitPrice]+[quantity]) AS total
FROM [Order Details]
GROUP BY [Order Details].OrderID, [Order Details].Quantity, [Order Details].UnitPrice;

Jaime E. Maccou

plenarts · #3 (**permalink**) December 30th, 2004, 03:19 AM

Jaime,
I don't exacty know what you mean.

this is the data i have to work with:
XY Start and XY Dest are the start and end coordinates of a location in the warehouse.
I need to calculate the distanse for a terminal and therefor I need to extract
(see example) 35-34 and 155-157, 67-69 and 165-165 etc

Terminal X-Start Y-Start X-Dest Y-Dest
2 29 155 35 155
2 34 157 67 165
2 69 165 26 166
etc

My data does't have and can't have a unique recordnumber.
Paul

Ray Pinnegar · #4 (**permalink**) December 30th, 2004, 07:01 AM

Your table needs some uniqueness as you cannot depend upon Access keeping the row order initially entered. Can't they be uniquely indexed on X-Start and Y-Start columns?

Cheers Ray

jemacc · #5 (**permalink**) December 30th, 2004, 07:56 AM

I asked GIS person about this. Here is a sample he shot at me.

With a little bit of Pythagoras, something like this might do the trick...

Code:

Private Sub cmdCalculateDistance_Click()
    Dim x1 As Double
    Dim x2 As Double
    Dim y1 As Double
    Dim y2 As Double
    Dim h As Double

    x1 = DLookup("[x-pos]", "Postal Codes", "[Postal Code] = " & cboStart)
    x2 = DLookup("[x-pos]", "Postal Codes", "[Postal Code] = " & cboEnd)
    y1 = DLookup("[y-pos]", "Postal Codes", "[Postal Code] = " & cboStart)
    y2 = DLookup("[y-pos]", "Postal Codes", "[Postal Code] = " & cboEnd)

    h = Sqr((x1 - x2) ^ 2 + (y1 - y2) ^ 2)

    MsgBox "The distance between " & cboStart & " and " & cboEnd & " is " & h & " kilometers."

End Sub

this is using a form with to combo box where you select the two coordinates and it will do the calculations

Jaime E. Maccou

plenarts · #6 (**permalink**) December 30th, 2004, 08:05 AM

Ray,

The data is from a query and in the underlaying table/query is also no uniqueness.
The data is daily updated from the main warehouse system and will be loaded into access.
I'm using maketable queries to create some tables. I don't know if there is an automated way
in this process to create an autonumber because I can't start doing it by 'hand'.

What may be the possibilities if I can make some uniquness in the query (Concatenate some fields maybe?),
because I got one extra field which i didn't mensioned in my example and that a field with a time element.

Paul

Need to download code?

Navigation