库存控制表规范化 [英] Stock control table Normilization

问题描述

大家好......

我正在使用vb08创建一个数据库，因为后端将被访问（07）我决定在这里发布我的问题它更具相关性。

我正在制作我的第一个库存控制数据库.....我需要记录取出的内容以及放入的内容......以及当前的


我想要使用的基本字段是：

产品

日期

金额


我不知道如何使关系发生以及如何自动生成当前的instock值。


有人可以帮我吗

Hi guys......
I''m using vb08 to make a database how ever since the back end will be access (07) i have decided to post my question here since its more relevant.
Im making my first stock control database.....and i need to record what is taken out and what is put in..... and the current

The basic fields i want to use are:
Product
Date
Amount

I dont know how to make the relation ships and how to auto generate the instock value for the current.

Could someone please help me

推荐答案

嗨Daniel

这可以成为一项相当重要的任务！你需要收紧你的规格。


我应该首先看一下Access附带的名为库存控制的模板应用程序


因为它的复杂性只能解决你目标的三分之一，即它会提高供应商产品的采购订单，然后交付给库存。


但它没有涵盖销售订单给客户，我看不出你如何查看当前的库存水平，尽管它可能存在于某个地方。 （我只是想到它可能会在货物到货时更新产品表，但它并不是那么善良！）


即使你不想要一个完整的吹过PO / SO功能你仍然需要一些记录的加入/减去库存的方法，所以即使字段名称不同，表关系也会相似。


这是一个开始。祝你好运。


S7

Hi Daniel
This can become a fairly major assignment! You need to tighten up on your specification.

I should start by looking at the template application that ships with Access called "Inventory Control"

For it''s complexity it only tackles one third of your objectives i.e. it raises Purchase orders for Products on Suppliers who then deliver to stock.

But it doesn''t cover Sales orders to Customers and I can''t see how you view current stock levels, although it may be there somewhere. (I just had a thought that it probably updates the Products table when goods arrive, but it doesn''t so goodness knows !)

Even if you don''t want a full blown PO/SO function you will still want some ''documented'' method of adding/subtracting from stock, so the table relations will be similar even if the field names are different.

Its a start. Good luck.

S7

嗨...感谢收获......我不需要一个非常复杂的数据库吧dosent需要跟踪销售订单等...我的目标是简单地保持仓库内的库存水平......当然有多个供应商，但是只有一个提取器。


我认为有三个表是必要的：

Hi... thanks for the repply...i don''t need a very big complicated database it dosent need to track sale orders etc...my aim is to simply keep levels of stock within a warehouse ......there are of course multiple suppliers ,however there is only one extractor.

I thought Three tables would be neccesary:

1. 股票输入
2. 股票水平（和一般信息）例如价值
3. 库存输出

我主要担心的是使用其他两个表来控制库存水平......我不确定如何做到这一点。


我相信我的意图并不像我之前看到的那么复杂

My main concern is controlling the stock level using the other two tables...... I''m uncertain on how to do this.

I believe my intention was not as complicated as i made it seem before

你好再次Daniel

你当然可以创建一个系统描述。


你需要创建一个''产品'的概念或任何你正在放养的东西，系统的唯一ProductID代码（大多数uld为此使用自动编号字段。然后，您可以添加所需的所有其他属性，包括成本（值），如果您愿意的话。您可能希望将TotalAvailable保持在此级别。如果你想要生成报告，通常会有一些重新订购水平的指示（加上提前期？）。


但是，成本（价值）会随着时间而变化您可能需要考虑如何更新它。


您的库存输入文档需要ProductID才能与产品和数量相关联。您可以在此处保留成本并在添加新数量时更新[Products.Cost]，因此值保持最新，但会计师通常不希望您这样做。

其他字段可能是来源，到达日期，添加数据的人的姓名。添加数据后，您可能需要禁用/隐藏添加按钮，因此在没有单独表单的情况下，添加库存两次的额外布尔字段'StockAdded''将是最简单的方法。


对于''规范化'，您应该考虑源的附加表，并通过SourceID字段将它们链接到Input。


类似于Stock Out。您不希望该表格上有来源（ID），但包含TotalAvailable和检查例程可能会有用，因此您无法提供缺货的商品。


如上所述，您将需要三个单记录表单来创建产品和管理输入和输出。基于产品的连续多记录表单允许您滚动库存。基于输入或输出的多记录表单将允许您按日期等监视事务。取决于您想要的。


这在Access中非常简单，我不会考虑VB。


更新Products表上Quantity的优点是您可以清除输入或输出而无需担心库存水平。您可能需要配置输入以在库存拍摄后允许调整的负数量。


更复杂的方法是库存水平=输入之和减去输出之和但是我认为这超出了你现在考虑的范围。


希望这有所帮助。


S7

Hi again Daniel
You could certainly create a system as you described.

You need to create the notion of a ''Product'' or whatever you are stocking, with a unique ProductID code for the system (most would use an auto-number field for this) You could then add all the other attributes you need, including cost (value) if you wished. You might want to keep TotalAvailable at this level too. It''s usual to have some indication of Re-Order level too (plus lead time?) if you want to produce reports.

However, cost (value) varies over time and you may have to think how you are going to update it.

Your Stock Input document will need ProductID to tie in with Products, and Quantity. You may hold cost here and update [Products.Cost] as you add the new quantity, so value keeps current, although accountants usually don''t like you to do this.
Additional fields might be Source, Date of arrival, name of person adding the data. Once the data is added you might want to disable/hide the ''Add'' button, so an additional Boolean field ''StockAdded'' would be the simplest way to stop adding stock twice without separate forms.

For ''normalization'' you should consider an additional table for ''Sources'' and link them to Input via a SourceID field.

Similar holds for Stock Out. You won''t want Source(ID) on that form but it may be useful to include TotalAvailable, and a check routine so that you can''t supply items which are out of stock.

You will need three single-record forms, to create Products and manage the Input and Output as above. A continuous multi-record form based on Products would allow you to scroll through stock. Multi-record forms based on Input or Output would allow you to monitor transaction by date etc. Depends what you want.

This is all so easy in Access that I would not contemplate VB.

The advantage of updating Quantity on the Products table is that you could purge Input or Output without worrying about stock levels. You may need to configure Input to allow negative quantities for ''adjustments'' after Stock Take.

A more sophisticated approach is where Stock Level = sum of Input minus sum of Output but I think this is beyond what you are considering at the moment.

Hope this has helped.

S7

这篇关于库存控制表规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！