Best way to populate calculated field into table

[matt beamish]

Hello people.

I need to populate a table with Grid reference values consisting of 6 figure integers, eastings and northings.
I am receiving/downloading the Grid Refs in a UK Grid Tile format, with 3, 4 or 5 figures preceded by letters.
eg SK 456 849, SP 6789 4356.

I have used some query functions and written a bit of VBA behind a form to convert the received coordinates into the full coords I want. The coordinates are now in unbound controls on a form, and I want to pass these back to a table so I can use the data for display purposes.

Ideally I want to link to the table from a GIS and autoupdate without having to open the database (my imported data is linked in to feed the database automatically).

I have done some searching and not found a method to pass the values into a table - What is my best way of doing this automatically?

As ever, thanks in advance

 

[plog]

You need to properly normalize your table structure, or set up queries that replicate that. I know you say this data comes from an external source, but that doesn't prevent you from storing it correctly.

What I would do is load that external data into a temporary table (source_Coordinates) and then set up a query based on it to break out the data into discrete fields (data_Coordinates). Sounds like you've already done the bulk of this work, but in a form--just move that code to a query and then you use that query (data_Coordinates) instead of the raw data.

Then when new data comes in, you either replace source_Coordinate or append the data there and the query "automatically" (one of my favorite words) has the data as you need it.

 

[matt beamish]

Thanks for reply.
In my form I have the following VBA triggered on Current to convert 3, or 4 figure refs to 5 figure. I can see that the form is the sticking point and that stacked queries are what I want, but where/how would I run this code in a query?
Fields: [5Easting] is 5 digit Easting, [EastingN] is a purely numeric Easting processed from the source coords.
Matt

Private Sub Form_Current()
If Me.EastingN < 100 Then
Me.[5Easting] = EastingN * 1000
ElseIf Me.EastingN < 1000 Then
Me.[5Easting] = EastingN * 100
ElseIf Me.EastingN < 10000 Then
Me.[5Easting] = EastingN * 10
ElseIf Me.EastingN < 100000 Then
Me.[5Easting] = EastingN * 1
End If
If Me.NorthingN < 100 Then
Me.[5Northing] = NorthingN * 1000
ElseIf Me.NorthingN < 1000 Then
Me.[5Northing] = NorthingN * 100
ElseIf Me.NorthingN < 10000 Then
Me.[5Northing] = NorthingN * 10
ElseIf Me.NorthingN < 100000 Then
Me.[5Northing] = NorthingN * 1
End If
End Sub

 

[plog]

You could create a function in a module and use the logic you've already built. Pass it the number and it would return the correct value.

 

[matt beamish]

Ah, yes, of course, a penny has dropped. I have used and adapted modules for what I have needed to do, but never created one. I better read up on what I need to do.
thanks

 

[matt beamish]

I am bit too much of a novice here.... I have packed my data into an .mdb - would anyone be kind enough to show me how a function in a module that includes my vba would look?
thanks

 

[plog]

Here's a function that multiplies 3 values:

Function getProduct(a, b, c) 
 
ret = a*b*c
 
 
getProduct = ret
 
End Funtion

Then to use it in a query, it would look like this:

BoxVolume: getProduct([height], [length], [width])

The above assumes your query's datasource has the fields 'height', 'length' and 'width' in it.

 

[matt beamish]

Thanks for that.
So how would I include the 'If' ,'then' and 'else' clauses I posted a couple of messages back?

My source coord is EastingN which may be 3,4, or 5 digits, and I need to make it 5 with the code.

 

[plog]

What did you try and how many people did you kill when it didn't work?

 

[matt beamish]

Couldn't see how to include what I needed in a function, did some more research and wrote what I needed into query grid design.

In query grid:

Easting5: IIf([EastingN]<100,[eastingN]*1000,IIf([EastingN]<1000,[eastingN]*100,IIf([EastingN]<10000,[eastingN]*10,IIf([EastingN]<100000,[EastingN]*1))))

 

[ButtonMoon]

Just a suggestion but have you considered using SQL Server spatial types? SQL Server has a built-in Geometry data type that supports industry standards for GIS data and comes with the functions and indexes you need to use it. The UK National Grid is flat so the Geometry type is probably what you want. The Geography type is the round-earth alternative.