[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Asset Inventory Summation

Posted on 2011-10-09
3
Medium Priority
?
338 Views
Last Modified: 2012-05-12
Hi,
I have an simple access database that I'm using the track inventory items.  I have a table with four fields, the first is an auto numbered key field, then I have a "From" location field, a "To" location Field and "Quantity" field that tracks the number of units moved from the "From" location to the "To" location.
I'd like to be able to choose a location and have the database add the quantity delivered to a location and then subtract the quantity taken From that location to give me a total number of items at that location.
I've done this in excel simply have a "From Qty" column and a "To Qty" column.  Then I multiply the From column by -1 to make it a negative number and add it to the To column which gives me the total number of items at any given location.  
Any help is greatly appreciated.
0
Comment
Question by:torttion
  • 2
3 Comments
 
LVL 21
ID: 36939841
In a properly normalized relational database there will be two records.
1) a record for the From   with a negativequantityy
2) a record  for the To with positivevquantityty.

transactiontion tawouldoudl look something like:

Trx_ID - auto number primary key
Trx_LocationID  (foreign key to Location table)
Trx_Date
Trx_TrxType  - (foreign key to transaction types)
Trx_Quantity  
0
 
LVL 21
ID: 36939851
Oops I forgot a field.

Trx_ID - auto number primary key
Trx_LocationID  (foreign key to Location table)
Trx_Date
Trx_TrxType  - (foreign key to transaction types)
Trx_ItemID - (foreign key to items)
Trx_Quantity  
 
With  the above structurer you simple sum the records to for a location and Item get the quantity.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 36958564
torttion,

...You have a lot of works ahead of you if you want this to be a real "Inventory DB", as TheHiTechCoach stated: ("a properly normalized relational database...")

One table simply won't cut it...

<I'd like to be able to choose a location and have the database add the quantity delivered to a location and then subtract the quantity taken From that location to give me a total number of items at that location.>

Doing this in Excel with a "Flat File" structure is easy, however with a true DB this would require a more complex design.

So I would suggest that you *First* create a design based on "a properly normalized relational database".
...Before ever worrying about "Interface"(form) aspects, like selecting locations and displaying totals
I would suggest you download the Access Inventory DB template, ...and examine it extensively, ...to get an idea of what is really required to move this up to a standard Database.
http://office.microsoft.com/en-us/templates/inventory-management-database-TC001018458.aspx

JeffCoachman

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

872 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