Inventory?

[Ms Kathy]

Hello. I'm stuck on how to accomplish this. We have material that we're selling by the container. The material is loaded into a labeled "container" and labeled "lid". We want to know which container and which lid is shipping to which customer. The customer then returns the container and lid and they are placed back in inventory (if they are in good condition). We also want to know the weight of the material being sold to each customer and which employee is responsible for the transaction (of the containers shipping and the containers being received back into inventory). I have looked at various table and relationship templates (desktop lending library and desktop inventory) but can't seem to get a handle on how to begin. Am I able to have an employee take "container 1" and "lid 29" out of inventory without that container and lid being able to be used until they are put back into inventory? Any help or direction is greatly appreciated. (I am a novice.) Thank you.

 

[plog]

Yes, you can limit what containers/lids are available, but it would have to be done programmatically (using a query/forms). First though, you need to set up your tables correctly. Read up on normalization (http://en.wikipedia.org/wiki/Database_normalization).

Give structuring your tables a shot and post back what you have.

 

[Ms Kathy]

Here's what I came up with. I was unsure of what to do with the containers at the table level. (This is a .bmp screen shot of the relationships, with table fields shown - I used the "manage attachments" button below and the image was shown as uploaded, I hope it worked.)

 

[plog]

It looks really good, here's what I see:

T_Trans_Types is unnecessary. Instead of it, you need an additional date field in T_Transactions to designate when the container/lid was returned (i.e. Trans_ReturnDate). With those fields you will be able to tell which contianers/lids are available for use.

However, I see no lids table, nor field for it in T_Transactions. I thought lids were a seperate entity? Where's that data being accounted for in your layout?

 

[Ms Kathy]

I didn't know if I should have containers separate from lids. I included both of them in the container table, entering them in the item field as (for example) Container_01, Container_02, Lid_01, etc. Shall I make a separate table for them?

 

[jdraw]

Can you tell us a little more about Container and Lid?
Perhaps a sample of what happens if CustomerA wants a Container of XXX's.
What happens from
- the time of Order--->To Customer-->returned to you?
The other thing I don't see is Material. Is that important?
What about hazardous Material?

 

[Ms Kathy]

We sell non-(human)edible food waste to local farmers for their livestock. We load it in containers which are numbered, and top with a lid which is numbered. They may load into container(s) #3, #2, #15, and top with lid #3, #8, #7. We want to know which containers were used to each customer because they need to return the container after they have unloaded the content. The "material" is unimportant. We just need to enter the weight (after the customer has taken their truck to a weigh station and provided us with the total weight); at this point they are billed.

 

[Ms Kathy]

Now the question - how do I make this work? Shall I enter all the containers and lids we have into the T_Containers table (or two tables if you think I should have both container and lid tables) - to create a list of specific inventory items to choose from when an employee enters/selects the exact container and exact lid when the customer picks up the material? At what point am I able to place restrictions on the containers and lids (not being able to enter container IDs or lid IDs of those containers and/or lids that are not actually in inventory; and then also being able to use them again after they have been returned into inventory); I also want to restrict the use of the Container/Lid ID to numbers we actually have on the containers and lids (rather than making up a number or duplicating a number that we don't actually have in house). Is this done with a query? form? I have been pondering this for some time and still can't wrap my brain around it. A point in the right direction would be most appreciated! Thank you again.

 

[jdraw]

Good stuff. Where does the Employee (from your jpg) fit?
So you have entities
Customer
Container
Lid
Employee

and since Containers and Lids can be "picked" from a "pile" of each, you have to identify the combination (junction) of Container and Lid (you might call this a completedContainer/FinalContainer.

It is the tblFinalContainer that would contain the "goods" and would be weighed.

I would suggest a table with these attributes as a start.
FinalContainerId PK
ContainerID FK from Container
LidId FK from Lid
DateOfUse
Weight


As for returned items, I'm going to point you to a free video (Access 2003) that will either apply, or give you some ideas, for dealing with available Containers and available Lids.
See this video

Good luck.



OOOps: I see you posted while I was typing.

You might want a field in tblContainers and tblLids, if you want to identify Containers or Lids that have been removed from Service.
These fields would be boolean eg IsActiveYN with default True. If Container or Lid is removed the appropriate field would be set to false. If you simple replace a "broken " container and /or lid
You could just reset the IsActiveYN field accordingly.

The autonumber Id's used to identify the Containers and Lids in the database, do NOT have to be meaningful to the user. Autonumbers are only unique, they are not necessarily sequential nor positive.

You could also assign a ContainerNumber for Employee/people use.
Similarly a LidNumber.

 

[Ms Kathy]

Thank you both. I will take this information and do further exploration. You both have given me some direction. I may, however, be back with a question(s) . . . Thanks again!

 

[Ms Kathy]

Any help would be appreciated. I need to be able to have an employee enter info on a form to include the employee name, customer, and where I'm stuck is how do they enter the containers and lids that are being picked up? A sub form? (I've added an "Active? Y/N" field, and also a "Condition" field to both the container and lid tables to indicate if they're available and the condition of them. I want this to appear on the form.) The employee could run the "which containers and lids are active" query from the form to see what they have to select from; but what do I create for the employee to select the different containers and lids for each load? And how do I then immediately make those selected items "N" for Active?? Thank you in advance!

 

[jdraw]

Did you watch the video I mentioned in previous post?
It has a simple method to deal with removing things from a list.

Typical use would be:
Seats at theatre
Available vs unavailable (sold already)

ContainersAvailable vs ContainersNotAvailable.

Maybe it doesn't apply to your set up, but well worth the 3-4 minutes to watch it.

As for Containers and Lids pickup, what is the process at the moment? Who does what, when?

Also:
Can you post a screen shot of your Tables and Relationships?
Do you have some test scenarios/data to test your model?

 

[Ms Kathy]

I did watch the video and I use the information in it to create lists (as described in the video) which takes containers(totes) out of inventory on one list and puts them back in in the other list. What I don't know how to do is create a form (or some tool) which our warehouse can use to make their selections from only the containers that are available (in inventory); which would then take them out of inventory because they are being shipped out. We typcially have more than one container/tote that is being shipped at the same time to one customer.

They're currently not doing much of anything which is the problem. We don't know for sure where the containers/totes are or which customer has them.

I hope I'm explaining this well enough to be understood . . . Thank you for your help!

Essentially what I need to know is HOW to use this list.

 

[Fran Lombard]

Hi Kathy
Did you solve your last issue?
Based on what I see - you are missing a key table - Shipments or Orders
this table would represent the shipment event. The customer and employee fields would be in this record.
Your transaction table would be the details to that event with a separate record for each bin sent out one one shipment
You would then create a shipment form for the warehouse guys to fill in that has a subform for the details.

Hope this helps

 

[plog]

First, I don't think Lid_Available nor Tote_Available is necessary. You will be able to discern this by looking at T_Transactions. If a Lid/Totes is assigned to a transaction which has no Trans_Return_Date then you will know its not available.

Now, in practice, the form the warehouse uses would be based on T_Transactions. The other 4 tables would most likely feed drop downs that populate their respective fields in T_Transactions.

The Row Source of the drop down for Trans_Employee and Trans_Customer would be based on their respective tables. The Row Source of the drop down for Trans_Lids and Trans_Totes would be queries that see if the Lid/Tote was available. That means the Row Source for the Trans_Lids drop down would be this query:

SELECT Lid_AutoID, Lid_Internal_ID, Iif(Isnull(Trans_Return_Date)=True AND IsNull(Trans_Pickup_Date)=False, "Unavailable", "Available" AS Lid_Availability 
FROM T_Lids
LEFT JOIN T_Transactions ON Trans_Lids=Lid_AutoID

The Tote Availability query would be similiar.

 

[Ms Kathy]

I am trying to create the query. And this is the error message I got.

I assume I'm creating a text box in the form and then use this query as the control source?

 

[plog]

I missed a closing parenthesis. This should do it:

SELECT Lid_AutoID, Lid_Internal_ID, Iif(Isnull(Trans_Return_Date)=True AND IsNull(Trans_Pickup_Date)=False, "Unavailable", "Available") AS Lid_Availability 
FROM T_Lids
LEFT JOIN T_Transactions ON Trans_Lids=Lid_AutoID

 

[Ms Kathy]

I'm getting this message "Join expression not supported".

I am creating this query as a "data definition" type in query design.

 

[plog]

Can you post your database?

 

[Ms Kathy]

Yes, I may, but how do I do that?