store the results of a form calculation in a table field

[kiwiblue]

I have read the replies to other similar queries and understand the 'whys' of
not storing calculated data. I may be too narrow in my thinking, but can't
see any other way of achieving what I need:

I am storing geographical (Latitude and Longitude) coordinate data, in the
format Degrees, Minutes, Seconds (DMS) with each in its own field. Later in
the process I am converting from DMS to decimal using a calculated field in a
form. All good so far.

For data integrity purposes, I need to be able to prove that each entry is
absolutely unique. What I was trying to do to achieve this, was to add the
sum of the decimal LAT and the decimal LON together, then check the result
against every other such sum in the database to ensure that each location is
totally unique -the sum of the decimal LAT and decimal LON should be totally
unique for any given location on the planet. The sum is the easy part... got
that in a form. I don't know how else to handle this but to store these
results in a table field, which is set to accept only unique values.

Would really appreciate any advice offered. I'm not particularly adept
within Access, but should be able to understand simple instructions as long
as you type slowly!

Thank you!

 

[Wayne-I-M]

Another way round this would be to check the decimal LAT and decimal LON
after update in the form (to see if it has been used elsewhere in the same
table)

Don't know your field names so I have used kjust lat and lon.
Note in this code
[LAT] and [LON] are table field names.
LAT and LON are form control names
I have assumed that each LAT and LON or in a record with an ID field ???


Private Sub LON_AfterUpdate()
If ((DLookup("[IDField]", "[TableName]", "[LAT] ='" & Form!LAT & "' AND
[LON] = '" & Form!LON & "'"))) Then
MsgBox "This LAT and LON have been used already", vbOKOnly, "Lat and
Lon checker"
Else
Use this space for the set value of your table field like Lat * Lon = some
other control bound to a field
End If
End Sub

I have not put in a cancel = true as not sure how you want to handle
duplicates (although you say you have disallowed this anway so should not be
a probelm)

Hope this helps

 

[Bob Quintal]

=?Utf-8?B?a2l3aWJsdWU=?= <[email protected]>
wrote in

I have read the replies to other similar queries and
understand the 'whys' of not storing calculated data. I may
be too narrow in my thinking, but can't see any other way of
achieving what I need:

I am storing geographical (Latitude and Longitude) coordinate
data, in the format Degrees, Minutes, Seconds (DMS) with each
in its own field. Later in the process I am converting from
DMS to decimal using a calculated field in a form. All good
so far.

For data integrity purposes, I need to be able to prove that
each entry is absolutely unique. What I was trying to do to
achieve this, was to add the sum of the decimal LAT and the
decimal LON together, then check the result against every
other such sum in the database to ensure that each location is
totally unique -the sum of the decimal LAT and decimal LON
should be totally unique for any given location on the planet.
The sum is the easy part... got that in a form. I don't know
how else to handle this but to store these results in a table
field, which is set to accept only unique values.

Would really appreciate any advice offered. I'm not
particularly adept within Access, but should be able to
understand simple instructions as long as you type slowly!

Thank you!

Firstly LAT 45degN LON 45degW will yeild the same sum as LAT
46degN LON 44degW.

All you need to do is set a single unique index on the combined
LAT and LON fields. Open the form in design view. Set a new
unique index on LAT. Click the Indexes icon in the toolbar.
change the name of the index from LAT to LATLON. In hte row
below this one add the field for LON, leaving the name blank.
save the table. you are now assured that nobody can enter the
same coordinates twice.

 

[Stefan Hoffmann]

hi,

I am storing geographical (Latitude and Longitude) coordinate data, in the
format Degrees, Minutes, Seconds (DMS) with each in its own field. Later in
the process I am converting from DMS to decimal using a calculated field in a
form. All good so far.
For data integrity purposes, I need to be able to prove that each entry is
absolutely unique.

Maybe you should consider storing it as two floats. But to solve your
problem, it is imho sufficent to create one unique index over all six
fields.


mfG
--> stefan <--

 

[John W. Vinson]

I have read the replies to other similar queries and understand the 'whys' of
not storing calculated data. I may be too narrow in my thinking, but can't
see any other way of achieving what I need:

I am storing geographical (Latitude and Longitude) coordinate data, in the
format Degrees, Minutes, Seconds (DMS) with each in its own field. Later in
the process I am converting from DMS to decimal using a calculated field in a
form. All good so far.

For data integrity purposes, I need to be able to prove that each entry is
absolutely unique. What I was trying to do to achieve this, was to add the
sum of the decimal LAT and the decimal LON together, then check the result
against every other such sum in the database to ensure that each location is
totally unique -the sum of the decimal LAT and decimal LON should be totally
unique for any given location on the planet.

Ummm!?

That's simply not true. Lat 60.00000, Long 60.00000; Lat 59.00000, Long
61.00000. The sum of both is 120.

You have a two-dimensional problem. It requires a two-dimensional solution!

The sum is the easy part... got
that in a form. I don't know how else to handle this but to store these
results in a table field, which is set to accept only unique values.

Would really appreciate any advice offered. I'm not particularly adept
within Access, but should be able to understand simple instructions as long
as you type slowly!

Don't assume that a unique index must be on only one field. You can create a
unique index on up to TEN fields!

I'd suggest not storing the degrees-minutes-seconds at all, or storing them
only temporarily; they can be easily reconstructed from the decimal
coordinates if needed. And if you have (decimal) Lat and Long fields in your
table, you can open the table in design view; ctrl-click Lat and then Long so
that both are highlighted; and click the key icon to make that coordinate pair
the Primary Key of the table. Access will then prohibit the entry of a second
record at that same location.

John W. Vinson [MVP]

 

[kiwiblue]

Thanks for the reply Wayne. I'm assuming the code goes in a VB routine I
append to the database? That code will check each entry against existing
entries to prevent duplicates? The 'Else' statement has me a little puzzled
-is it necessary? Could I make that statement report say, "Verified Unique"
to the Form?

Thanks for your help, forgive my lack of knowledge... still learning!

Another way round this would be to check the decimal LAT and decimal LON
after update in the form (to see if it has been used elsewhere in the same
table)

Don't know your field names so I have used kjust lat and lon.
Note in this code
[LAT] and [LON] are table field names.
LAT and LON are form control names
I have assumed that each LAT and LON or in a record with an ID field ???


Private Sub LON_AfterUpdate()
If ((DLookup("[IDField]", "[TableName]", "[LAT] ='" & Form!LAT & "' AND
[LON] = '" & Form!LON & "'"))) Then
MsgBox "This LAT and LON have been used already", vbOKOnly, "Lat and
Lon checker"
Else
Use this space for the set value of your table field like Lat * Lon = some
other control bound to a field
End If
End Sub

I have not put in a cancel = true as not sure how you want to handle
duplicates (although you say you have disallowed this anway so should not be
a probelm)

Hope this helps

--
Wayne
Manchester, England.

I have read the replies to other similar queries and understand the 'whys' of
not storing calculated data. I may be too narrow in my thinking, but can't
see any other way of achieving what I need:

I am storing geographical (Latitude and Longitude) coordinate data, in the
format Degrees, Minutes, Seconds (DMS) with each in its own field. Later in
the process I am converting from DMS to decimal using a calculated field in a
form. All good so far.

For data integrity purposes, I need to be able to prove that each entry is
absolutely unique. What I was trying to do to achieve this, was to add the
sum of the decimal LAT and the decimal LON together, then check the result
against every other such sum in the database to ensure that each location is
totally unique -the sum of the decimal LAT and decimal LON should be totally
unique for any given location on the planet. The sum is the easy part... got
that in a form. I don't know how else to handle this but to store these
results in a table field, which is set to accept only unique values.

Would really appreciate any advice offered. I'm not particularly adept
within Access, but should be able to understand simple instructions as long
as you type slowly!

Thank you!

 

[Wayne-I-M]

Hi

Sorry for not getting back you I thought the thread was over. The 'else'
section of an iif gives the result if something is not true

If something is true THEN
Do this
ELSE
Do something different
END IF

So in the code I sent
Private Sub LON_AfterUpdate()
If ((DLookup("[IDField]", "[TableName]", "[LAT] ='" & Form!LAT & "' AND
[LON] = '" & Form!LON & "'"))) Then
MsgBox "This LAT and LON have been used already", vbOKOnly, "Lat and
Lon checker"
Else
PUT YOUR Lat * Lon HERE and use the result to set the value of a control on
the form.
End If
End Sub

You would set the value of a control with something (in this case Lat * Lon)
BUT only if the statment above was NOT true (in this case that you have not
already stored the value Lat and Lon - both of them the same - in your table).

Note that I have not given the value the you want to store - as I don't
understna it. I have only given the process (code) that you could use to
"fill-in" the control and so store the calculation in your table - if the
control is bound to a field.

Hope this helps

--
Wayne
Manchester, England.

Thanks for the reply Wayne. I'm assuming the code goes in a VB routine I
append to the database? That code will check each entry against existing
entries to prevent duplicates? The 'Else' statement has me a little puzzled
-is it necessary? Could I make that statement report say, "Verified Unique"
to the Form?

Thanks for your help, forgive my lack of knowledge... still learning!

Another way round this would be to check the decimal LAT and decimal LON
after update in the form (to see if it has been used elsewhere in the same
table)

Don't know your field names so I have used kjust lat and lon.
Note in this code
[LAT] and [LON] are table field names.
LAT and LON are form control names
I have assumed that each LAT and LON or in a record with an ID field ???


Private Sub LON_AfterUpdate()
If ((DLookup("[IDField]", "[TableName]", "[LAT] ='" & Form!LAT & "' AND
[LON] = '" & Form!LON & "'"))) Then
MsgBox "This LAT and LON have been used already", vbOKOnly, "Lat and
Lon checker"
Else
Use this space for the set value of your table field like Lat * Lon = some
other control bound to a field
End If
End Sub

I have not put in a cancel = true as not sure how you want to handle
duplicates (although you say you have disallowed this anway so should not be
a probelm)

Hope this helps

--
Wayne
Manchester, England.

I have read the replies to other similar queries and understand the 'whys' of
not storing calculated data. I may be too narrow in my thinking, but can't
see any other way of achieving what I need:

I am storing geographical (Latitude and Longitude) coordinate data, in the
format Degrees, Minutes, Seconds (DMS) with each in its own field. Later in
the process I am converting from DMS to decimal using a calculated field in a
form. All good so far.

For data integrity purposes, I need to be able to prove that each entry is
absolutely unique. What I was trying to do to achieve this, was to add the
sum of the decimal LAT and the decimal LON together, then check the result
against every other such sum in the database to ensure that each location is
totally unique -the sum of the decimal LAT and decimal LON should be totally
unique for any given location on the planet. The sum is the easy part... got
that in a form. I don't know how else to handle this but to store these
results in a table field, which is set to accept only unique values.

Would really appreciate any advice offered. I'm not particularly adept
within Access, but should be able to understand simple instructions as long
as you type slowly!

Thank you!