tracking boxes and pallets in a warehouse

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,
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
KollBrianAuthor Commented:

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 :)  

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

KollBrianAuthor Commented:
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.


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

Tanks LSM
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.