Help with relationship to show visual representation of warehouse

[tux]

[color:red]Click each link below each picture to see picture better.

See pictures below to understand file structure that I've built that works but is going to be terrible to replicate in mass, and doesn't really address my needs. I've achieved one goal - to see all locations on one layout and all the pallets that are in each location.

http://www.furniture-partner.com/picture0.jpg

http://www.furniture-partner.com/picture1.jpg

http://www.furniture-partner.com/picture2.jpg

To enter the data, the user creates a new pallet number and then the location is chosen based on what space the forklift driver decides to put the pallet. See here:

http://www.furniture-partner.com/picture3.jpg

I want to build a relationship that will show what pallet is in which location by status VISUALLY, notes in red are to you...-

http://www.furniture-partner.com/picture4.jpg

The list of locations will be HUGE, comprising of about 12 rows of pallet racking - each row has about 100 locations, and the only way I've figured to build it is by building a table for each location.

The structure that I've built as a test for 3 potential locations works - the display below shows location by status in 'list view', with each status on one layout.

http://www.furniture-partner.com/picture5.jpg

but I'm unhappy with it because it will require a lot of work to add all the locations. I've set up a warehouse view table that is related to each and every location table. My goal in planning for this build is to minimize the steps needed for additional locations.

Also, my above design is not practical because I have to enter all data in one field - this terrible design would require that I enter data in the proper table base on what the location will be. Not good.

Ideas on how to improve the design?

Thanks.

Tux

Edited September 8, 2007 by Guest

[Søren Dyhr]

Why do you need 3 TO's of the same table each related to it's own primary key, when an attribute could tell it's location??

and the only way I've figured to build it is by building a table for each location

Wrong in my humble opinion, take a look at:

http://www.fmforums.com/forum/attachment.php?attid/7281/

It's utterly fast compared to what you have in mind, the template as you see it is limited by the number of prefab'ed records in the sandwiching layer. So if you're willing to hog just a tiny bit of processor resources. Could the principle be changed into something that goes this way:

http://www.fmforums.com/forum/attachment.php?attid/7285/

Well the template shows both actually! Which suits your purpsoe best, is probably the prefab'ed, since endless numbers of warehouse allocations isn't likely to happen!

--sd

[tux]

Søren

Thanks for those links - I don't have much time today to build it, but the examples you sent definitely opened my eyes to the right way to do this. I'll work on it tonight or tomorrow and post back if I have further questions or get stuck.

Best,

Tux