苦苦挣扎的查询 [英] Struggling with a query

问题描述

为了把东西切成骨头我有3张桌子。

TblBoughtSold列出了购买或出售股票的日期，以及culpa mia，余额。

To cut things to the bone I have 3 tables.
TblBoughtSold which lists the date that stock was purchased or sold and, culpa mia, the balance.

展开 | 选择 | Wrap | 行号

推荐答案

只是在云雀上，是否可以在[BoughtSold]上添加一个字段来链接记录为该库存支付的股息？

just on a lark, would it be advisable to add a field to the [BoughtSold] that would link the record to the dividend paid for that inventory?

展开 | 选择 | Wrap | 行号

谢谢zmbd。


我认为你走在正确的轨道上，但它需要反过来。你建议的方法是为每个红利创建一个BoughtSold记录。


我的建议是：

Thanks zmbd.

I think you are on the right track, but it needs to be the other way round. The way you have suggested would create a BoughtSold record for each Dividend.

My suggestion is:

展开 | 选择 | Wrap | 行号

老实说，我猜这里是基于数据库，我跟踪了一些库存（非序列号消耗品）。


也许如果我解释我的思考过程可以帮助你决定我的建议如何，甚至是否可以为你的数据库工作

[t_supplier]

[t_products]

[t_POnum]

[t_invoice]

[t_inventory] ​​< br $> b $ b [t_discount]


- 所以我猜你的[BoughtSold] === [t_invoice]。


[t_discount]有关于供应商提供的折扣，特价促销或其他折扣（有些促销活动（@@））的信息。


+所以当我购买产品[t_invoice]。[fk_discount]有购买产品的折扣，金额等...（[t_POnum] - > [t_invoice]发票中的每个项目都可以有一个不同的折扣，每个采购订单有多个项目。）

因此，当物品到达时，它们会被标记为收到日期，谁收到它们和PO＃，[t_inventory]获得记录产品，收到的金额（我们可以订购6并一次性收到3个，一周后收到3个:-(所以我们可以查看[t_invoice]。[amordered]）和po号码。


当项目被移除时[t_inventory]收到类似的条目只是amo收到的是否定数字


这是我可以计算库存价值的原因，因为我们可能有六种不同PO＃中可能有不同折扣的商品。我们可以看看是否已经使用了最旧的PO中的所有项目（有些过期了！），我也可以在给定的时间范围内对供应商提起诉讼，看看我们是否从他们那里得到更好的定价，或者与另一个相比较供应商（我们多源一些关键任务项目），经常随着时间的推移，因为一些产品定价根据市场情况而变化。


这是我早期的数据库之一所以它的结构不是很好而且SQL非常糟糕（那时我的学习曲线陡峭！）但是它起作用并且自......起作用了...... 1995年 - 最初它是Lotus-Approach数据库 - V3.0 IIRC-在1994年或1995年运行在OS Win3.1上（太长了） - 从接近转换为访问很有趣！

Honestly I was guessing here based on a DB that I have tracking some inventory (non-serial numbered consumables).

Maybe if I explain my thought process here it may help you decide how, or even if, my suggestion could work for your DB
[t_supplier]
[t_products]
[t_POnum]
[t_invoice]
[t_inventory]
[t_discount]

- So I was guessing that your [BoughtSold] === [t_invoice].

[t_discount] has the information about the discounts offered by the supplier for quantity discount, special promotions, or other discounts (that gets to be a mess with some of the promotions (@@) )

+ so when I purchase a product [t_invoice].[fk_discount] has the discount for the product purchased, amount, etc... ([t_POnum]->[t_invoice] each line item in the invoice can have a different discount and there are multiple items per purchase order).

So when the items arrive, they get labeled with Date received, who received them and the PO#, [t_inventory] gets a record with the product, amount received (we may order 6 and receive 3 in one go and the other 3 a week later :-( so we can check against [t_invoice].[amtordered] ) , and the po number.

When items are removed [t_inventory] receives a similar entry just the amount received is a negative number

Where this is going is that I can calculate the value of inventory on hand because we may have items from six different PO# that may have different discounts. We can see if the all of items from the oldest PO have been used (some expire!), I can also pull against a supplier over a given time frame an see if we''re getting better pricing from them, or compare against another supplier (we multi-source some mission critical items), often over time as some of the product pricing is variable depending on market conditions.

It''s one of my earlier databases so it''s not very well structured and the SQL is quite the mess (steep learning curve for me then!) but it works and has worked since... 1995ish - originally it was a Lotus-Approach database - V3.0 IIRC-ran on OS Win3.1 in 1994 or 1995 (been too long) - that was fun converting from Approach to Access!

这篇关于苦苦挣扎的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！