Creating specific numbers for a Shelving system - Media Inventory

[tinkertoy]

Sorry about the subject. I wasn't sure how to shorten it. What I have is a customer with a shelving system that will be used to store CD-Roms and DVD-Roms. There are 8 doors(numbered 01-08), within each door there is 14 shelves (1-14), on the shelf there are 17 slots, each slot holds 5 jewel cases(1-85). A total of 9,520 cases can be stored. I have a form that the user can scan a barcode, from the jewel case, into the record. There can be multiple related media, i.e. 1 of 4. The user would like to be able to create the record, and then be able to store the media in the next available "slot", and then the related media in the next slot, and so on. I have the created the many to many table that allows the user to create a record of what the specific media is, 1 of 3, 2 of 3, & 3 of 3, that works just fine, and then of course the user can then run a query with a barcode of case #, and see if there are additional related items. NO PROBLEM! On the form there is a place for the door number, shelf number, and space #. What I cannot figure out is how to set up so that the next available number(s) are there for the user to use, without going to the shelf to see what is available. Kind of an autonumber setup. I know how to set the autonumber setup, that will work if needed. I was thinking of 8 tables, with a shelf field, and a slot/space field. But, that seems like overkill. I need assistance, and ideas. Also, when the media is purged/removed and all related items if any, the spaces all become available again. The main form has a purged check box, with a date on it, so I could use that to "cause" the spaces to become available. But, how to get there, I am at a loss! I appreciate what you can give me in the way of assistance! ThankS


Rona/tinkertoy

 

[Lightning]

Some Suggestions:

Only use One Table to store this information
Door field
Shelf field
Slot field
Purged field (Yes/No field - default value = NO)
Include an entry in this table for all available slots in the system.

When you purge your main table and set a date, run a routine to set the Purged field in this table to Yes.

You can then use a select query to display all the Shelf slots that are marked Yes.

HTH
Lightning

 

[wemeier]

In the same vein as Lightning's reply, I'd suggest the following:

Make a table like Lightning's with Door, Shelf and Slot as Integer fields. Make this the primary key. Add a boolean field called Empty that has a default value of True.

When looking for the next available slot to put a CD in, create a function called NextOpenSlot that does a Seek on the table to find the first record where Empty is true. If NoMatch is False, assign the Door, Shelf and Slot on the record to the CD, change Empty to False and update the table. With this logic, if a previously-filled space is opened up again (because the CD there has been removed) it will be the first slot to be reused.

 

[tinkertoy]

Thanks! I will try the combination of both, tomorrow. And, let you know if this worked.

One more point to make, this is for a database app. that is going on a PocketPC. When the handheld is not synced up, I am guessing this could be a problem if two users tried to "assign" a space number for a media item? We haven't got to that part yet! The user(s) are expecting to 'always' be wireless, but I also expect that it isn't possible 100% of the time.