Update multiple records in a field based on new entry and a 2nd field

[kawaisunn]

I need to create a form for entering Place, Name, and Metric data to one table. A Place can have more than one Name, and Metric is used to order an output of Name(s) in a list for each Place. For a Place, Metric should not be duplicated, but a new Name can be assigned an existing Metric and the other Metric values updated (or not) accordingly. My difficulty is with inserting the Metric attribute and updating the values that are further 'down' the list.



For instance, I have a Place named IdahoCapital, and two Name records for it: 'Boise' and 'Le Bois' with a Metric of 0 and 1 respectively. I want to input a new Name, 'City of Trees', and give it the second place on the list when the data are output, so Metric ==1 for Name == 'City of Trees'. When I input the Metric of 1 for that new Name record in the Metric field of the form, 'Boise' would retain a Metric of 0 while 'Le Bois' would be assigned a Metric of 2. If I were to make a csv list of IdahoCapital records ordered by Metric, it would be 'Boise,City of Trees,Le Bois.'

[Micron]

One would have to know the logic that dictates why 0 stays at 0 and 1 becomes 2. I figure I know, but if I had 0,1,2,3,4,5 and I wanted to insert something between 3 and 4, would 4 become 5 and 5 become 6? That's my assumption, as well as that you need or should have a composite index (Place and PlcName [not NAME - a reserved word that should not be used for db objects]). In that case, I think an update query would do the trick if you start with the position you want to insert at (e.g. 5) and increase all the rows by +1 WHERE Place = "somePlace" ( and maybe )AND PlcName = "someName". Then Append a record with the place/name combination with the metric value you wanted it to be (5).

[kawaisunn]

Thank you for the reply. 'Name' as a field is only for illustration, I'm happy to use the actual field names on the table but, well, they are a bit absurd. However if it helps getting to the root of sorting this out I'm game.

I think your interpretation of what I need is spot-on, but to clarify the metric would not be entered between two existing integers. 'City of Trees' is a moniker for Boise that is used more frequently than 'Le Bois.' My thinking is to supplant an existing metric if justifiable, then shove all the metric values above the chosen location in the metric records for the field of names per place up by one value, while leaving everything below the same. I hope that makes sense.

I would sincerely appreciate some ideas how I could implement this functionality in a form (I have yet to create). I'm using student help and would like entries to be from a form rather than directly into the table. The table is not overly large, under 20,000 records, and won't grow much, so computational economics is not much of a consideration.

Please let me know how I can help you help me!

[Micron]

shove all the metric values above the chosen location in the metric records for the field of names per place up by one value, while leaving everything below the same. I hope that makes sense.

No, not really. What is "up by one value"? Post a sample of what the data would look like before and after. Use Excel for an easy way of laying it out (make sure all columns are as wide as the data) then copy and paste into your post. You should get a decent html table as a result.

Or if you're somewhat capable with Access, create your form based on the table and use a command button's click event to update all the numeric values by +1 (update query) then right after, the append query to insert your new record, then requery the form. Unless I'm not understanding the goal...

[kawaisunn]

By 'up by one value' I mean increase the integer value of metric(s) > newly entered Name & Metric by +1. Here is a screenshot of the table itself:

And here are the data with SequenceNumber (Place in my text above), SeparatedNames_Deposit_FieldName (Name in my text above), and Metric adjusted for the new Name entry for AS0001, 'Cloud Mountain Mine' supplanting the Metric of AS0001's 'Unnamed Iron Prospect' of 1 and increasing the metric of that name to 2 :


I would also like the R_Index2_Deposit_FieldName field for AS0001 to be updated with the new entry, and on click is a good idea to make it happen, to be 'Thimbleberry Iron Prospect (Cloud Mountain Mine, Unnamed Iron Prospect). Not sure how the code would look to do this, but the order of the list of names returned would be based on the metric. Previously this was done by hand in the table.

The suggestion of a click to update all metrics by +1 for a SequenceNumber before entering a new name and assigning it a metric wouldn't work because it wouldn't allow for insertion of the newly entered name in it's user-defined place in the list other than the front of the list, if I'm understanding your suggestion.

[kawaisunn]

I apologize, these didn't paste in like they should have:

[orange]

Perhaps it's just me, but what exactly do you mean by metric, and what exactly are you trying to do in simple terms.

Definition of metric

1
plural : a part of prosody that deals with metrical structure

2
: a standard of measurement <no metric exists that can be applied directly to happiness — Scientific Monthly>

3
: a mathematical function that associates a real nonnegative number analogous to distance with each pair of elements in a set such that the number is zero only if the two elements are identical, the number is the same regardless of the order in which the two elements are taken, and the number associated with one pair of elements plus that associated with one member of the pair and a third element is equal to or greater than the number associated with the other member of the pair and the third element

[Micron]

It's not making sense to me either. I though I understood, but I see that AS0001 has two metrics, 0 and 2. Where's the 1? There has to be some sort of logic behind anything you want to accomplish, and I can't see it. To me, it doesn't matter what a metric is. What matters is how the decision to make Cloud Mountain Mine as a 1 metric is arrived at. What if there was already a 1, like if you tried to insert a record for AS0002?

It shouldn't take 6 posts just to figure out what is and what the outcome should be.

[kawaisunn]

I had problems uploading the example tables and got the first one mixed up, and you are right Micron, in the first table AS0001 'Unnamed Iron Prospect' Metric should be 1. My apologies.

And from my original post: I need to create a form for entering Place, Name, and Metric data to one table. A Place can have more than one Name, and Metric is used to order an output of Name(s) in a list for each Place. For a Place, Metric should not be duplicated, but a new Name can be assigned an existing Metric and the other Metric values updated (or not) accordingly. My difficulty is with inserting the Metric attribute and updating the values that are further 'down' the list.

It may have been confusing that the higher the value of Metric, the further 'down' the list the name will be ordered.

Metric of 0 will be the first Name (SeparatedNames_Deposit_FieldNames in the tables above), Metric of 1 the second, Metric of 2 the third, etc when outputting an ordered list of names for the Place (SequenceNumber) AS0001.

The decision on how appropriate a name's place in the list, i.e. Metric value, is subjective.

For a new name entry that is not at the end of the list (1 integer value above the largest existing Metric value in the table for a given place, such as AS0001), the new name's Metric value should replace the previous name's Metric value. Metric values above the new name should be updated by adding 1 (integer value). Metric values below the new name's Metric value should remain unchanged. If a new name entry was given the Metric of 0 then all other names' Metrics would be updated by adding 1 to their value.

What I'm trying to achieve is, through a form, when I enter Cloud Mountain Mine and assign it a Metric of 1 the name Thimbleberry Iron Prospect retains it's Metric of 0, and the name Unnamed Iron Prospect's Metric is updated to 2. An output of ordered list of names originally would be 'Thimbleberry Iron Prospect, Unnamed Iron Prospect' and after the new entry would be 'Thimbleverry Iron Prospect, Cloud Mountain Mine, Unnamed Iron Prospect'.

[orange]

So metric is actually a sort order field or precedence that has meaning to you.
Stepping back, the proverbial 30,000 ft view, you want to add some records for Places and associated Names and a numeric value in another field to use for sorting those records. The value assigned to the "metric" must be numeric, should not be duplicated with a group of Places.
Could you step back and brief us on what this is all about --we're curious? Use simple terms as you would describe the "business" involved to a six year old. It adds context to the question/issue and provides some focus.

The other part of the issue, as I understand things, is you/someone may add new entries to an existing group and there is logic to deal with assigned "metrics".
All is possible, it's SMOP as a colleague once advised me.

smop ** small matter of programming.

[Micron]

Are you able to create your form, at least? Do you intend to free-form type the new values into form textboxes?
After so many posts, I don't see anything that suggests to me that the solution is any different than what I wrote in #2.

[kawaisunn]

So metric is actually a sort order field or precedence that has meaning to you.
Stepping back, the proverbial 30,000 ft view, you want to add some records for Places and associated Names and a numeric value in another field to use for sorting those records. The value assigned to the "metric" must be numeric, should not be duplicated with a group of Places.
Could you step back and brief us on what this is all about --we're curious? Use simple terms as you would describe the "business" involved to a six year old. It adds context to the question/issue and provides some focus.

The other part of the issue, as I understand things, is you/someone may add new entries to an existing group and there is logic to deal with assigned "metrics".
All is possible, it's SMOP as a colleague once advised me.

smop ** small matter of programming.

Yes, orange, the 30,000 ft view you outlined is exactly right. This is a database concerning recording mine attributes and locations (mineral extraction and exploration activity) for the State of Idaho. My goal is to create a form so our student hires can input these name data through a form, and depending upon the reference the name is based upon, I can assign a suitable listed order to the new name (it's metric attribute).

Often, when the ownership of a mine changed hands, the new owner would re-name the property. Sometimes neighboring properties would be absorbed, and conversely sections of a property might be sold, so the name of a place might also change through time as part of a larger or smaller group of point locations. Underground workings might tie together in tunnels etc. even for different mines. A lot of variables to consider when assigning name priority. Locations are point data in this database and usually associated with a significant feature, such as an Adit, tailing dump, or mill site. However, despite what a single site's name might be described in legal descriptions at a given time, the name the locals call it by might be different, therefore have more weight if someone wanted to query the mine's location(s) and attributes to, perhaps, 'stake' an interest in developing a new prospect. However, since the database is also used for reclamation purposes as well as land use modifications by government, NGO's etc., legal names also have weight, especially if there is scrutiny about liability. Therefore a database manager before me put the Metric attribute in place for printing out place names in a certain order. The order is often in flux, and new names as well as aberrations in spelling are tracked and given weight depending upon the reference source and it's credibility. Finally, though all names should be included in the list for query purposes, this database drives a web app. It is appropriate for the web app that the names for a mine be listed in a coherent order that makes sense and saves time finding a certain property since names are often duplicated for many mine locations. Familiar names should be more heavily weighted to the front of the list. Obscure names, though important for research, should be included as well but not necessarily a primary name.

I like SMOP, and I couldn't agree more. It seems like a fairly simple thing to implement, but is beyond my skill set.

[kawaisunn]

Are you able to create your form, at least? Do you intend to free-form type the new values into form textboxes?
After so many posts, I don't see anything that suggests to me that the solution is any different than what I wrote in #2.

I can create the form. New names spellings etc. could not be from a pick list or drop-down since there would be no previous record in the database to draw from. Certainly the Metric value for a name could be from a drop-down as long as the metric already was in use, but the question remains how to implement updating every Metric value above the one chosen for a new name entry, provided it supplants an existing name's Metric, by +1, while leaving every Metric value below the new name's Metric as it was already assigned.

[Micron]

tblSequence
SeqNum	Deposit	Metric
AS0001	Unamed Iron	1
AS0001	thimble iron	0
AS0001	clothes iron	2
AS0002	talc	1

If this is what you had for data and wanted to insert "new iron" row, I see it like this:

You would have to edit my made up names for your table, field and position identifier. txtOrdinalPosition is my name for your textbox where you type in the position number that the user wants to insert the new row into. So if your selected records showed as above and user wants to insert "new iron" at position 2, you'd run this query: UPDATE tblSequence SET Metric = Metric +1 WHERE (SeqNum) ="AS0001" AND Metric >= [txtOrdinalPosition]; That should increase all metric values for AS00001's throughout IF Metric is equal to or greater than value of txtOrdinalPosition (which is 2). Then APPEND a record to your table by using the text values user inputs into your form controls and use specified position from txtOrdinalPosition as the new metric value. What you should get:

tblSequence
SeqNum	Deposit	Metric
AS0001	Unamed Iron	1
AS0001	thimble iron	0
AS0001	clothes iron	3
AS0001	new iron	2
AS0002	talc	1

AS MY SIGNATURE SAYS, EXPERIMENT ON COPIES OF YOUR TABLE, FORMS, ETC.

[kawaisunn]

tblSequence
SeqNum	Deposit	Metric
AS0001	Unamed Iron	1
AS0001	thimble iron	0
AS0001	clothes iron	2
AS0002	talc	1

If this is what you had for data and wanted to insert "new iron" row, I see it like this:

You would have to edit my made up names for your table, field and position identifier. txtOrdinalPosition is my name for your textbox where you type in the position number that the user wants to insert the new row into. So if your selected records showed as above and user wants to insert "new iron" at position 2, you'd run this query: UPDATE tblSequence SET Metric = Metric +1 WHERE (SeqNum) ="AS0001" AND Metric >= [txtOrdinalPosition]; That should increase all metric values for AS00001's throughout IF Metric is equal to or greater than value of txtOrdinalPosition (which is 2). Then APPEND a record to your table by using the text values user inputs into your form controls and use specified position from txtOrdinalPosition as the new metric value. What you should get:

tblSequence
SeqNum	Deposit	Metric
AS0001	Unamed Iron	1
AS0001	thimble iron	0
AS0001	clothes iron	3
AS0001	new iron	2
AS0002	talc	1

AS MY SIGNATURE SAYS, EXPERIMENT ON COPIES OF YOUR TABLE, FORMS, ETC.

Thank you Micron, I'll work this approach out and post when I have results.