如果可以，请访问帮助。查询相关。 [英] Access assistance, if you can. Query related.
问题描述
我想知道你是否可以帮我解决一个问题。目前我有一个折扣计算，运行两个表：
1）供应商列表和与他们相关的回扣％年龄
2）A正在进行的发票明细清单，包括月/年期和发票总额
目前，我可以愉快地运行当月的折扣计算。我想做的是开始追踪供应商退税率随时间的变化。这意味着对折扣表中输入的每一行应用折扣适用日期的月/年期间。
理论上，当我运行查询时，它会进入，找到要从指定期间应用的正确回扣率，然后根据这些费率计算。这意味着我可以跟踪变化，但也可以在必要时运行计算（这是我计算的最终目的）。
我遇到的问题是，无论是什么我输入我的查询，似乎决定为供应商折扣表中存在的每一行提供结果，而不管该行的日期是什么。
例如，我可以获得如下数据：
供应商A - 2％ - 2007-03
供应商A - 3％ - 2007-08
供应商A - 5％ - 2008-01
退税表上的
。如果我运行一个运行日期为2007-10的计算，我希望它能够取消所有2007年10月的退款，然后使用3％的退税率。目前它给出了2％，3％和5％的结果，每个都有一个记录。
如果这没有意义，我可以进一步扩展！ />
如果您发现它有用，我现有的查询就是这个：
Hi,
I wondered if you could help me with an issue I''m having. Currently I have a rebate calculation that is running off two tables:
1) A list of suppliers and the rebate %ages relevant to them
2) A ongoing list of invoice details, including a month/year period and an invoice total
At the moment, I can happily run the rebate calculation for the current month. What I would like to do is begin to track changes in supplier rebate rates over time. This means applying a month/year period for the date which the rebate is applicable against each line entered on the rebate table.
The theory is that when I run my query, it goes in, finds the correct rebate rate to apply from a specifed period and then calculates based on those rates. This means I can track changes, but also run the calc historically if necessary (which is the end purpose of my calc).
The problem I''m having is, no matter what I enter in my query, it seems determined to give results for each and every line that exists in the supplier rebate table, regardless of the date that is against that line.
For example, I can have data that is like:
Supplier A - 2% - 2007-03
Supplier A - 3% - 2007-08
Supplier A - 5% - 2008-01
on the rebate table. If I run a calc with a run date of 2007-10, I want it to go and pull off all the October 2007 rebates, and then use the rebate rate of 3% against it. At the moment it gives me results for 2%, 3% and 5%, a record for each.
If this doesn''t make sense, I could expand further!
My existing query is this if you find it useful:
推荐答案
您将需要使用子查询从发票表中的每个记录返回一个来自回扣表的记录。
You''re going to need to use a subquery to return only one record from the rebates table per record in your invoice table.
Crikey，未使用之前的子查询。我会看看我是否能找到任何指南。
Crikey, not used subqueries before. I''ll see if I can find any guides.
哦我在说什么我之前使用过子查询。好的，我在这个问题上略有进步。我在VBA中使用此查询作为ADODB.Recordset的一部分。我目前所做的是从查询中设置一个完整的SQL字符串，包括一些表格中有用户输入的部分，并让它运行。在这种情况下我如何使用子查询？
而不是引用我的表，我可以参考查询吗？
Oh what am I talking about I''ve used a subquery before. Ok, I have a slight advancement on this problem. I''m using this query in VBA as part of a ADODB.Recordset. What I currently do is set a string full of my SQL from the query, including some sections where there is user input from a form, and have it run. How would I use a subquery in this instance?
Instead of referring to my tables, can I refer to a query instead?
这篇关于如果可以，请访问帮助。查询相关。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
