Microsoft Access Question....Pro User Help Plz???

[maddmaxx]

I have a pretty in-depth question about Microsoft Access (at least it's in-depth to me.....newbie at it and all). If anyone knows alot about access and would be willing to help me out, I would much appreciate it!!! PM me if you can help out, or if you'd rather I type out the whole thing here, let me know! TIA for any help!!!!

peace

 

[Celstar]

it doesnt hurt.

 

[EagleKeeper]

Typing out the problem will assist others in helping or understanding what you are going through.

That is what this forum is about - sharing the pain and wealth of info.

 

[maddmaxx]

Ok....here goes....I willl try my best to clearly explain what I am attempting to do.
I would like to create a movie database for my place of employment. I have already created a table with all of the info I want to have in the database, except one thing, I want to have a letter/number combination to shelf the tapes with, according to the catagory of the movie (which I have already created a lookup list for), and alphabetically withing each catagory.
For example, one of the catagories is "Original Movies". I would like to shelf all of the Original Movie tapes in alphabetical order and have Access assign them a shelf number such as "OM1, OM2, OM3," etc. And I would like to be able to go back as more tapes are aquired and have them added in the alphabetical order "OM1.1, OM3.5" etc.
Lastly, I want to create a form that, when the catagory is picked from the lookup list, will assign that "Shelf Number" automaticaly.

The Autonumber that is added to each entry won't do it....and I can't seem to figure out what I need to do, or if Access will even do such a thing?????

Hope that's not too poorly written to describe my situation!
TIA for any help!

peace

 

[EagleKeeper]

Originally posted by: maddmaxx
Ok....here goes....I willl try my best to clearly explain what I am attempting to do.
I would like to create a movie database for my place of employment. I have already created a table with all of the info I want to have in the database, except one thing, I want to have a letter/number combination to shelf the tapes with, according to the catagory of the movie (which I have already created a lookup list for), and alphabetically withing each catagory.
For example, one of the catagories is "Original Movies". I would like to shelf all of the Original Movie tapes in alphabetical order and have Access assign them a shelf number such as "OM1, OM2, OM3," etc. And I would like to be able to go back as more tapes are aquired and have them added in the alphabetical order "OM1.1, OM3.5" etc.
Lastly, I want to create a form that, when the catagory is picked from the lookup list, will assign that "Shelf Number" automaticaly.

The Autonumber that is added to each entry won't do it....and I can't seem to figure out what I need to do, or if Access will even do such a thing?????

Hope that's not too poorly written to describe my situation!
TIA for any help!

peace

This is one sequence of instructions that will work for you. Others here may know different shortcuts.

1) You will have to do some programming (in Access or externally and interface with the Access database)
2) You will have to know how many tapes can fit on a shelf (assuming the tapes are a standard physical storage container width)
3) When a tape is added to a collection, the tape info will have to be added to the master table. The master table can be designed to sort by name.
4) You will then have to read the sorted master table to determine which shelf (n tapes per shelf) the new tape should be placed on. Update your shelf location field with that info.
5) Then every tape in the sorted sequence that you inserted the tape for will have to have it's shelf location field recalculated and updated.
6) All tapes that then have their shelf location field changed will have to be physically moved to the next shelf.