Solved

tracking boxes and pallets in a warehouse

Posted on 2011-02-15
6
622 Views
Last Modified: 2012-05-11
Hello,
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,
Brian
0
Comment
Question by:KollBrian
  • 3
  • 3
6 Comments
 
LVL 84
Comment Utility
You'll have to give us a little more about your structure.

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

tBox
----------
BoxID
Details
etc etc

tPallet
------------
PalletID
Description

tPallet_Boxes
------------------
PalletID
BoxID
HasBeenRemoved

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?
0
 

Author Comment

by:KollBrian
Comment Utility
Brian-DB.accdb

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

Brian
0
 
LVL 84
Comment Utility
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.



0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:KollBrian
Comment Utility
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.
----correct

--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.

Brian

 brian-DB.accdb
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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:

tPallet
-----------
PalletID
PalletDesc
BayNum
SlotNum
Etc

tBox
--------
BoxID
BoxDesc

tPallet_Box
----------------
PalletID
BoxID

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?
0
 

Author Closing Comment

by:KollBrian
Comment Utility
Thanks to the pointers from LSM I was able to achieve the goal and I am almost finished on the DB design :).

Tanks LSM
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now