[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


tracking boxes and pallets in a warehouse

Posted on 2011-02-15
Medium Priority
Last Modified: 2012-05-11
I was reading article "ID: 24339516" which has helped me figure out part of my question for "how will users easily search the data-tables... But that still leaves how to search broken...

I have to purchase, receive, transport, and deploy an entire office building of hardware.  So I have the DB setup to do all of that fairly well.

The issue I have is that I have parts coming into the warehouse, where I will assign a box #, pallet #, and a venue # (think bay #).  But, as we start to deploy the hardware I will have users asking for boxes from pallets (not the entire pallet) and wanting to be able to see "what's left" in the warehouse.  As boxes are confirmed to be delivered we will change the venue number which will allow the users to see only what is really in the warehouse.

The box table, pallet table, and location table are all ok, but box and pallet both have a field from venue table called "venue_ref" .  When I do an inner join it doesnt work.
"FROM (qry_Box_Detail INNER JOIN tbl_Venue_Ref ON qry_Box_Detail.Venue_Reference = tbl_Venue_Ref.Venue_Reference) INNER JOIN qry_Pallet_Detail ON tbl_Venue_Ref.Venue_Reference = qry_Pallet_Detail.[Venue_Ref]"

Instead I get repeating data, I get a line for the venue_ref in the box table and I get a line for venue_ref in the pallet table even though the venue_ref is the same location....
Venue_Reference      Part_ID      Box_ID      Pallet_ID      Pallet_Notes      Sum Of Quantity
1      test      b-1      p-1      test note      5
1      test      b-1      p-2            5
1      test      b-2      p-1      test note      7
1      test      b-2      p-2            7
2      test2      b-3      p-3            50

as you can see, the results show box_id "b-1" on both pallet p-1 and p-2 which the data tables do not have.   What I am looking for is "what boxes are on each pallet, in a specific venue number".

Any thoughts on whats wrong?

the code sample is my attempt to pull two queries and display the data in one query...

SELECT DISTINCTROW tbl_Venue_Ref.Venue_Reference, qry_Box_Detail.Part_ID, qry_Box_Detail.Box_ID, qry_Pallet_Detail.Pallet_ID, qry_Pallet_Detail.Pallet_Notes, Sum(qry_Box_Detail.Quantity) AS [Sum Of Quantity]
FROM (qry_Box_Detail INNER JOIN tbl_Venue_Ref ON qry_Box_Detail.Venue_Reference = tbl_Venue_Ref.Venue_Reference) INNER JOIN qry_Pallet_Detail ON tbl_Venue_Ref.Venue_Reference = qry_Pallet_Detail.[Venue_Ref]
GROUP BY tbl_Venue_Ref.Venue_Reference, qry_Box_Detail.Part_ID, qry_Box_Detail.Box_ID, qry_Pallet_Detail.Pallet_ID, qry_Pallet_Detail.Pallet_Notes;

I am going to use the other answered article to create a search form that will allow the user to drop down a box and select a venue (i.e. EastSide Warehouse) and hit a button asking what boxes and pallets are available (the users are required to request items based on pallet number).

Thanks for the help,
Question by:KollBrian
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 85
ID: 34901971
You'll have to give us a little more about your structure.

I'd think you'd have something like this:

etc etc



The tables above would allow you to store the contents of a Pallet.

From there, you can then store where the Pallet is located in your warehouse.

When you then "remove" a box from a Pallet, you then show that box as HasBeenRemoved = True

Do you have a structure similar to that? If not, can you post your relationshp diagram?

Author Comment

ID: 34905947

I have attached a copy of the file.  There is really no data in the db yet as I wanted to lock the design/function before putting real data in.  

That said I am running out of tinkering time :)  

LVL 85
ID: 34907363
For one thing, you've got circular references in several locations. For example, tbl_Parts_Detail is related back to tblVenueRef on at least 2 paths. In some cases this is okay, but I can't see how it would be relevant to your situation.

What's the point of tblBox? It's fine if that's your "Master" table in a Master-Detail setup, but that should be the table that contains the PalletID.

What's the point of tblDate and tblWeek?

If I'm not mistaken, your data structure is this:

A Venue contains many Pallets.

A Pallet contains many Boxes

A Box contains many Parts.

IF that is correct, then you data relationships are not correct.

tblPalletDetail should contain a link to tblVenueRef - and there should be no direct relationship between BoxDetail and tblVenueRef, for example.

If you could define the overall intent of this database, we can probably help you to define the data structure more correctly.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 34910911
Hello LSM :),

ok lets tackle the questions:
--What's the point of tblBox? It's fine if that's your "Master" table in a Master-Detail setup, but that should be the table that contains the PalletID.
----The purpose of the tbl_box_id is to attempt to conform to 4th form rules which apparently direct all "repeative" information into a seperate table.  since many of the "boxes" will contain serveral different parts, I will have to repeat the box_ID several times in the tbl_box_detail.  This is also why I did not create the pallet ID and box ID fields in a master table, I have no way of knowing at this point "what or how many" boxes will end up on each pallet as all the supplies are different.

--What's the point of tblDate and tblWeek?
----your rignt to think this is an odd one, but the reason is that to me it was the easiest way to keep track of 2011 and 2012 week id numbers without having to learn complicated sql statements to gleen the information.  I needed a "simple" and quick way to know that if I am refering to week 2 the entry actually refers to 2012 week 2 not 2011 week 2.  So the data in this table will likely read something like 2_2012.  

--If I'm not mistaken, your data structure is this:
--A Venue contains many Pallets.
----venue is a generic term for essentially cubicle or office.  So that will populate with all the information about a particular office or space in the building.  Once I input the venue's and the parts, I will then use the tbl_deployment_plan to specify what parts go in what venue's.  Also an entire warehouse could be a venue in the DB so that I know the parts are still in Florida, or Maine, or even the server room of the intended building waiting for deployment to the individual cubicles or offices.

--A Pallet contains many Boxes
----correct, but once "deployment time" comes around the box to pallet link will be broken by users asking the warehouse for "5 monitors off pallet p-3"  So I will then take the box ID and knowing what box was delivered from the warehouse (no we cant ask for particular boxes) delete the pallet ID reference so we know "whats left" on the pallet.

--A Box contains many Parts.

--IF that is correct, then you data relationships are not correct.
----ummmm perhaps.

Basically I am using one database to track an entire deployment project from the initial concept (what venue's will there be, what will they be called, what hardware will they need...) all the way through to final deployment and handover of inventory information to the sustainment team.

The database design so far is a huge improvement upon what we used the first time around (ugh) but this time I am trying to write in ways to make the information easily accessible to "users".  i.e. point and click.

I agree the box and pallet tables might need to be redone, I just havent figured out a better way to do that yet, so far this is the best design I have thought up :)  During the first building of this two building project we shipped a little more than 210 pallets containing something around 4000-6000 boxes, and more parts than I can ponder.  And this one topic, knowing where a particular part was hiding, was the single largest pain in the neck to the deployment team.

I am attaching the most up to date version of the db, I have worked on the box/pallet search form since I uploaded last.  the form isnt 100% done yet but it's getting close.


LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 34911907
I'm going to just tackle the concept of tblBox and 4NF.

The overwhelming majority of database conform to 2NF, many attain 3NF, but a majority of them never attain 4NF. At some point you must diverge from the precepts of database normalization and into the realm of "the real world" :)

Simply put, there's no reason for your tblBox UNLESS you plan to pull parts from a Box but keep that box on the pallet. If you remove a box and all of it's contents, then you'll be fine with a structure like this:




Using this simply structure, you can tell exactly what Boxes are contained on a Pallet, and where your Pallet is located (i.e. the Bay and Slot number - you'd change that to suit your needs).

You can flesh this out with Parts details, for example.

You can then move to managing the "Venue" concept. Your Venue table would be your "dispersment" table, it seems. Is that correct?

The troubling aspect, however, is the circular refrences. YOu need to firm up your table structures and relationships before moving forward. For example, how is a Pallet related to a Venue? Do you ship a Pallet to a Venue, or do you ship Boxes to a Venue? Could you ship boxes from multiple Pallets to the same Venue?

Author Closing Comment

ID: 34928658
Thanks to the pointers from LSM I was able to achieve the goal and I am almost finished on the DB design :).

Tanks LSM

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question