Generating a numbering system dependant on other feilds

[IGrant]

I have used and maintained Access databases in the past but this is my first experience with building a new database. I started by borrowing every Access book my Tech department has on hand and going through them. I have gotten to the point where my tech department can not help me because my question is beyond their own knowledge. I have searched through many different website’s forums and have failed to find what I am looking for. It very well may be out there and I am using the wrong terminology in my searches. The most important piece of information to be stored in my entire database is of course the one that I am having the hardest time figuring out how to set up.

I have a table called “Document Index” which will be a master log of every document that passes through my department. This table has the three fields which are important to my problem/question.
1. The “LOC” field represents the location at which the document was logged. The “LOC” field has a working validation rule that only allows a “D” or “S”, representing the department head’s office or the site office.
2. The “DUO” field value represents the three digit unique number assigned to a project by another department (accounting)
3. The “ID Number” field is a unique number we will assign to each document that is this table’s primary key. It is also where my problem sits. The “ID Number” format has been decided upon by my boss and the rest of the office has already begun stamping these ID Numbers onto every document (which I will have to go through and enter once the database is complete). I am stuck with the format as it is shown in the example below.
“ID Number” format is: value of “LOC”- value of “DUO”-number

I know what I want the field to do but I have no idea how to turn my ideas into the proper code to see if it works. So here is my idea of a solution with example values entered.
A. Fill in form for “LOC” and “DUO”
B. At this point Access would run a behind the scenes search and return all ID #’s that had a that “LOC and “DUO”
C. At this point Access would run a behind the scenes search through only those records found in step B to find the highest “ID Number” previously assigned
D. At this point Access would make a calculation: adding one (+1) to the “ID Number” found in step C
E. At this point Access would insert the result found in step D into the “ID Number” field of my current entry (which until this step the record only contained the “LOC” and “DUO”)

For example: if my first 4 entries had this info:
LOC DUO ID Number Document Description
D 410 D-410-1 Wendy’s Ransom Note
S 410 S-410-1 Priate's Demands
D 415 D-415-1 Cinderella’s Birthday Invitation
D 410 D-410-2 Peter Pan’s Response to Ransom Note

Then I complete step A by typing into the form:
LOC DUO ID Number Document Description
D 410 D-410-1 Wendy’s Ransom Note
S 410 S-410-1 Priate's Demands
D 415 D-415-1 Cinderella’s Birthday Invitation
D 410 D-410-2 Peter's Response to Ransom Note
D 410

Access would execute step B finding:
LOC DUO ID Number Document Description
D 410 D-410-1 Wendy’s Ransom Note
D 410 D-410-2 Peter's Response to Ransom Note

Access would execute step C finding:
LOC DUO ID Number Document Description
D 410 D-410-2 Peter's Response to Ransom Note

Access would execute step D finding:
D-410-2 + 1 = D-410-3

Access would execute step E:
LOC DUO ID Number Document Description
D 410 D-410-1 Wendy’s Ransom Note
S 410 S-410-1 Priate's Demands
D 415 D-415-1 Cinderella’s Birthday Invitation
D 410 D-410-2 Peter's Response to Ransom Note
D 410 D-410-3
Then I would finish entering the information for that record.


I would appreciate any help you can offer, even if just pointing me in the right direction or correct terminology for my searches.

 

[The_Doc_Man]

Here is the issue I have with your idea. You are designing the fields from the bottom up and that is generally wrong with databases.

In any enterprise for which you are building something in Access, you must search your own personal knowledge base (the grey matter, if I was being oblique) to decide what THINGS - what ENTITIES you want to represent. Each class of things might either be in a separate table or might be in one larger table with a "discriminator" field. (Case in point - your LOC field is a location discriminator)

Once you have the entities firmly fixed in your noodle, work on how they fit together in the thing you are trying to do. From this you will determine issues in data relationships. Since Access is a relational database product, you might guess that this is important.

If you have read many books, I'll only briefly belabor an important issue that right now might seem a bit strained, but later will be impossible to ignore if your DB ever grows or becomes popular - normalization. If you have NOT read much on this topic, GOOGLE-search for it and screen your hits to see only .EDU domain or those vendors whose products are familiar to you.

You talked about building this thing or using it in a search, but are these the ONLY two things you would do with the data? (That's a practical and a rhetorical question at the same time.) If you haven't figured out all the things you wanted to do, you aren't ready to build anything yet.

Look through this forum for DESIGN topics to see how others approach DB design. Until you have an overall design, you aren't NEARLY ready to talk about ID keys or fields.

 

[Dreamweaver]

Ref: Look through this forum for DESIGN topics to see how others approach DB design. Until you have an overall design, you aren't NEARLY ready to talk about ID keys or fields.

If he looks in my sig there's a program there thats been designed to help with building up a stucture for a db I built it to help me but I would hope it would be useful to others learning how all the bits need to fit together.

IE A Phase could be Main Clients you can then "THINK" What Are my requirements or my clients for this part if the program so you start creating tasks for say Main Screen (Adding a desription maybe for things like field types ECT), Subforms ECT, ECT, ECT
If a person is able to spilt there project up into main and sub sections then they stand a much better chance of completeing in either how the client requires or saving themselfs masses of messing about but as with all things the goal posts are forever changing ESP where clients are added to the frame.

Mick

 

[Premy]

I think you have to create another field with just the incrementable digit in ID. Attach this to a routine which allows you to auto-increment it based on the last value entered, and then combine all the fields in a multiple index number which should not allow duplicates. I can supply you an auto-increment routine I concocted for myself and which has been working fine for me.

Hope this gets you in the direction, more or less

Regards,
Premy

 

[IGrant]

still a little lost

Thank you for your responses. I came up with something that almost does everything I want. I am sure there is a better/cleaner way to do it but since I have yet to figure out that way I have this...

I created a form with a subform to help accomplish what I need.
My subform is master/child linked to my main form by two fields with the: LOC (location) and DUO (project).

When I use the record navigation buttons on my main form it takes me between the 2 records for LOC (D or S). My subform populates with every entry made from the location chosen no matter what project and fills in the selected LOC on my subform.

After choosing the record for what location I want (D or S) I type in the DUO. My subform then populates with only those entries made from the selected location for that DUO and fills in the selected DUO on my subform.

On the subform I have a field called ID Number. I solved part of what I had been asking for help with here. I set my field [ID Number] control source as such:
=[Loc] & "-" & [DUO] & "-" & [SerialNumber]

That works wonderfully for populating the field [ID Number] with a number (ie.D-410-1) pulled from: the information selected in the main form([Loc] & [DUO]) and entered in the subform ([SerialNumber]) by the user.

This weekend I added a field called SerialNumber to the subform. At this point I am hand assigning this number but I want to find a way for it to auto assign...ugh still a problem. I noticed that when I have entered chosen the LOC and DUO on my main form and the subform pulls up just those records that the SerialNumber of the current record is the same as the Record number of the subforms Record navigation. Can this be used information be used to assign the SerialNumber? Like a record count for all entries that have matching LOC and DUO, then add 1 to that? Or after the subform has been updated with a LOC and DUO, pulling up all the matching records search for the highest [SerialNumber] value and add 1 to that?
Any help is still much appreciated.

 

[IGrant]

solution found

I figured out the code for what I need. Once I clean up all the useless code (ie. 'code) I will post my solution for anyone else to see whom it may help.