AutoNumber based on Location Strings

[reslus]

Okay, so I'm still learning by trial and error with Access 2010 so please take this to heart before yelling at me for using incorrect terminology (i'll catch on quick, I promise).

I have two tables that are related: BUCKETS & LOCATION

BUCKETS has these fields:

1. ID (autonum)
2. Type (type of bucket)
3. Creation Date (when it was created)
4. Operational (if it is operational or not (Yes/No).
5. Location (Location of the bucket)
6. Notes (notes)

LOCATIONS has these fields:

• LocationAutoID (Autonumber)
• LocationCity
• LocationID
• BucketsOnSite (can have multiple entries)

What I'd like to have done is for LOCATIONS table to be aware of how many different cities there are. So for instance, if there is multiple location entries for the cities of Vancouver and Toronto in there, I want the LocationID to first know how many Torontos there are and in LocationID to combine the LocationCity and a count (starting from 1) to that field.

So for example, if there are three toronto locations and two vancouver locations, LocationID would have TO-01, TO-02, T0-03 for the Toronto locations and VAN-01, VAN-02 for Vancouver locations.

At first I had the expression of [LocationCity] & "-" & [LocationAutoID] in the Expression field in the Field Properties but I quickly realized that it wouldn't count the amount of cities but just add the autonumber and the city (which kind of works, but not ideally).

Any suggestions?

Much appreciated!

 

[mdlueck]

So for example, if there are three toronto locations and two vancouver locations, LocationID would have TO-01, TO-02, T0-03 for the Toronto locations and VAN-01, VAN-02 for Vancouver locations.

The built-in auto number data type may not be customized in this way... letters and punctuations.

You would need to keep track of your own ID system, check out from the master ID table the next available ID for XYZ counter, and build the custom ID string to return / get inserted into the table.

So the ID table would have a counter for Toronto, a counter for Vancouver, etc... When a request for new ID comes, passed in is which counter needs to be bumped, and returned would be the next available ID for that counter. Then you could compute your string from there.

 

[reslus]

So the ID table would have a counter for Toronto, a counter for Vancouver, etc... When a request for new ID comes, passed in is which counter needs to be bumped, and returned would be the next available ID for that counter. Then you could compute your string from there.

Oh wow!

Any suggestions or links on how I can build this? I'm a little overwhelmed with this solution.

 

[mdlueck]

Any suggestions or links on how I can build this? I'm a little overwhelmed with this solution.

Then perhaps it is not such a good idea to come up with unique counters based on category of the record's data. This scheme is rather elaborate.

And what if a location were to move... still the same organization, just operating in a different city. That would make you want to update the unique ID. Or retain their ID, and have it no longer be the correct type of ID for their new city. Thus, embedding meaning to unqiue ID's is not a good idea.

 

[John Big Booty]

The sample posted in this thread may help you to achieve your goal.

 

[reslus]

Much appreciated to everyone!

Why I'm trying to append it to a table rather than have it in a form is because I'd like for the functionality to be queryable— so for instance, if there are numerous buckets in a location (and the location DOES not move, btw, it can only be removed or created--and the buckets will be assigned to them).

So for example, a user can look up the statistics of items dropped off in these buckets by simply querying TO-01 or VAN-03 and we could see the bins attached to that specific location.

Perhaps I'm approaching the solution incorrectly?

Would it be possible to just count how many Vancouver locations there are and append a incremental number to it? I'm not sure if Access has that capability just yet.

So for instance, I could write an expression (can I?) formula that counts the cities then creates this sort of formula: (I've been tinkering with ideas, as you can see)

Count number = n
Then the formula would be somewhat of n-(n-1++) (++ is an increment every time that counts the specific city). But that may be flawed as well, now that I think of it. Damn it! Any ideas, ladies and gentlemen?

Frustrated yet motivated.

 

[reslus]

I guess maybe I shouldn't be messing with autonumbers if I'd like to be appending an actual count rather than a unique identifier (which the locations table already has).

 

[mdlueck]

You could still use autonumber, then have a second column which combines the autonumber with other data.

The down side to doing that is you need to INSERT, then look up the autonumber ID, finally perform an UPDATE to supply that custom ID which contains the autonumber value.

 

[reslus]

I think I've figured out how it's going to work! (The simplest way I could possibly think of)

Instead having the table do the heavy lifting (counting itself and appending an increment), I'm going to create a form that's a "Location 'Creater'". In this form, there will be a count of the specific city selected (to be added to the table) and this form will

1) count the amount of instances of the selected (drop down) city in the form
2) adds a 1 to that count and appends that number to a fragmented city code

this data will be stored to the correct column.

BAM! Problem solved, right? Right? Am I right guys? I need the confirmation. Love me. *whimpers*