[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Inventory Database

Posted on 2006-05-01
7
Medium Priority
?
334 Views
Last Modified: 2012-05-05
Hello, I have a problem with the database I am creating.  I have a list of parts that I need to keep and inventory on.  My problem comes when I am trying to figure out how to show a transaction (issue a part to someone) and then get the qty of the equipment decreased by the qty issued. Also to throw another wrench into the mix, my Items table can have for example: 3 Monitors @ $150 each and then 2 Monitors @ $200. The difference would come from the monitors being purchased at different time and the amounts are different. So, when for example I issue 4 monitors to a dept. I need to know how much to charge them and then deduct the qtys from my inventory.  I Items will be distributed from oldest to new, so that will determine the order they will be issued in. I have two tables:

tblItems
-------------------
ID - autonumber
Item - text
qty - number
cost - currency
datepurchased - date/time
category - text (which is the type of item)

tblTransfer
-------------------
ID - autonumber
Dept - text (who was issued the item)
qty - number (the number recived)
datetransfered - date/time

I hope this is enough info the get you started.  Thanks for the help!!
0
Comment
Question by:BCUST
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Accepted Solution

by:
Marc333 earned 1200 total points
ID: 16582552
BCust,
I think there are a few different ways to go about this.  My first idea/suggestion is to take your transaction table and break it up into two tables.  The first will be to store the primary information, such as transaction ID, Transaction date and department.  The second table will house all of your detail.  Then you will link the two tables by the Transaction ID.  Additionally, you'll get much of the detail for the items from your tblItems table.  So for example:

tblTransfer
ID: 1234
Dept: Admin
DateTrans: 4/1/06


tblTransferDetails
ID: 1111 <- this ID is unique to this transaction Detail
TransID: 1234 <- this ID links this tranaction detail to the main transaction
Item: Moniter
InvID: 54321  <- ***
itemcost: $200

ID: 1112 <- this ID is unique to this transaction Detail
TransID: 1234 <- this ID links this tranaction detail to the main transaction
Item: Hard Drive
InvID: 54322  <- ***
itemcost: $150

The form that will be used to create your transactions should have the Transaction information up at the top with a subform in the middle to house all the detail.  

*** To make sure you can update your inventory, you'll need to link your detail table to the tblItems.  You'll want to possibly use a dropdown field for Item (in the subform), then use some code to find the oldest one of that kind of item, and then input the ID of that item into the InvID.  When you're user pushes the button to update the system, you'll use the InvID to go through your tblItems and delete that item from the Inventory.  That way your inventory stays up-to-date and you'll be able to use a query to compute the inventory totals.  





 
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 16583270
I write Point of Sale software and, as Marc333 states, you will require 2 tables.  A transaction Header table and transaction Detail table.  The header data is the date, time, department.  The detail is the item, price, etc.

I expect you have some sort of RECEIVING process.  If not, you need to keep track of what's being received in similar fashion.  A Receive Header (date, time, from where, etc) and a Receive Detail (item, your cost, etc.)

Simply query the Receiving Tables for what's been received (sum queries) and the same for Transaction tables (sum queries).  Using these 2 tables will help you track your inventory.

In the 'old days' (before super fast PCs) I'd suggest maintaining an 'Inventory Count' field in your inventory table.  This field would be updated after each 'receive' and 'sold' transaction.  But the programming is 'busy' since mistakes can cause this value to become unreliable - and you can 'sum query' large data pretty fast now days.


Scott C.
0
 
LVL 1

Author Comment

by:BCUST
ID: 16587009
Marc333
I do like the idea with the two tables. It does make the overall function better. I still have a problem, the way I have setup my received items is say when I get 2 monitors in at once I input that into one record with the qty = 2. Now I don't know if i will be able to leave it that way or not.  The only other way I can see the working is if I input each piece of equipment as a single record. What are your suggestions?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 3

Expert Comment

by:Marc333
ID: 16592511
Well, there question there is pretty simple.  In your details table, do you need to break down the cost of each monitor or can you just have a summary?  Put another way, after inputting the data, will you ever need to come back to a transaction and know, this transaction had two monitors, one that costs $150 and one that costs $200... OR is it enough to say, this transaction had two monitors and those totaled $350.  If you need to know the breakout, then I suggest having each item as a seperate line item.  And generally, I do prefer this in most cases unless a transaction and have large numbers, say 5, 10 or 20 monitors.  This could definately become frustrating to your users.  

In that case, I would use some recordset code to create a sorted query by date, filtered for the object you're adding, in this case, monitors, sorted by the oldest date.  (if you're not sure how to do this, let me know and I'll post some example code).  Then go through and add up the oldest monitors for the number you need and add this total to the subform for the total amount.  

The biggest concern with this is validating that you delete out the monitors you added up when you update.  But, you could probably validate this by making sure that the total costs of the monitors deleted matched the total in your subform.  





0
 
LVL 3

Expert Comment

by:Marc333
ID: 16592514
Sorry, first sentence should state, the question here is pretty simple.  
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 16594243
Even with a QTY field of more than 1 (2, 5, 25).... a sum query will add this value correctly.

(That's how my Point of Sale works)

Scott C.

0
 
LVL 1

Author Comment

by:BCUST
ID: 16595575
After some more looking into this database, I am going to have to put each new item in a seperate line. The reason for this is so I can track my serial numbers on the items to ensure that we are getting rid of the oldest items from the inventory and not just the database. By addind the items this way I think I should be able to get what I need accomplished. I am pretty much use what marc333 had to say the first time. Thanks for all the Help.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

834 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