根据特定产品的日期和时间查找数量 [英] Find out a quantity based on date and time for specific products
问题描述
早上好，
我有一个名为"Décomptepundairepar produit"的表格，我有这些字段：
ID    
=
产品的唯一ID
Réfproduct= ;
产品描述
Quantité= ;   
我希望自动填写一个名为"Opérationsd'inventaire"的表
Décompte_Physique=员工在特定日期的库存数
DateDuDécompte= ; 
特定产品的计数日期。
注意：日期包括小时，时间和秒，计算数量时需要考虑
;                     
表：OPÉRATIONSD'INVENTAIRE
;                        &NBSP ;
在此表中，所有购买和销售记录如下：
                   
类型de transaction = 3种选择（Achat（购买）; Vendu（销售）; Ajustement（调整）
Date de modification = Date of of交易
;
Réfproduct=产品描述
Quantité=购买或出售的数量，请注意，数量始终为正数表，但我想要的数量（Achat减去Vendu加上Ajustement）因为
字段ajustement是正面还是负面。
                       
ID =产品的唯一ID
示例易于理解：
在表格"Opérationsd'sempirea"中我有7个特定产品的交易，1个购买和6个销售，但是，这些销售中有3个是在实际库存
完成后发生的，所以我在这里寻找的数量在表中的QUANTITÉ字段中Décompteporesairepar produit应为21
< p style ="margin：0cm 0cm 0pt 70.5pt; text-indent：-70.5pt">
图片：
你能告诉我实现这一目标的最佳方法是什么，我想我必须创建一个查询或SQL语句来检索信息然后创建更新查询以在第一个表格中输入
数量...
谢谢你，加拿大魁北克省的Claude
克劳德Larocque
在Opérationsd'missaire中的一个Ajustement值是一个手动输入的库存（décomptephysique）值和以库存盘点为目的的购买和销售计算的手头价值？
如果是这样，为什么Opérationsd'missaire中没有反映库存的行2018-01-18 13:15:17？ 有了这样一行，每次交易后每件产品的手头价值计算将是一个标准的"余额"查询，例如
SELECT T1。[Réfproduit]，T1。[修改日期]，T1。[Quantité]，
SUM（T2。[Quantité] * IIF（[类型de transaction] =" Vendu"， - 1,1））AS [Décompteinsrereire]
FROM [Opérationsd'inventaire] AS T1 INNER JOIN [Opérationsd'inventaire] A S T2
ON（T2。[ID]< = T1。[ID]或T2。[修改日期]<> T1。[修改日期]）
AND（T2。[修改日期]< = T1。[修改日期]）AND（T2。[Réfproduct] = T1。[Réfproduit]）
GROUP BY T1。[Réfproduct]，T1。[修改日期]，T1。[Quantité]，T1。[ID]
订购T1。[Réfproduct]，T1。[日期de modification] DESC，T1。[ID] DESC;
$
当然，你可以在一个AfterInsert事件过程中自动将Ajustement行插入到Opérationsd'inventaire中。形式绑定到Décompteporesairepar produit表：
Dim dtmLatestDate As Date
Dim intLatestCount As Integer
Dim intAjustment As String
Dim strCriteria As String
Dim strSQL As String
strCriteria =" [Réf produit] =""" &安培;我。[Réfproduit]& """并且[日期修改]< =＃" &安培; _
$
格式（Me。[DateDuDécompte]，" yyyy-mm-dd hh：nn：ss"）& "＃"
dtmLatestDate = DMax（" Date de modification"，"Opérationsd'inventaire"，strCriteria）
strCriteria =" [Réfproduct] =""" &安培;我。[Réfproduit]& """和[日期修改] =＃" &安培; _
$
格式（dtmLatestDate，"yyyy-mm-dd hh：nn：ss"）& "＃"
intLatestCount = DLookup（"Décompteinsrereire"，" NameOfAboveQueryGoesHere"，strCriteria）
intAjustment = intLatestCount - Me。[Décompte_Physique]
如果intAjustment<> 0然后
strSQL =" INSERT INTO [Opérationsd'sempaire]" &安培; _
"（[Réfproduit]，[Type de transaction]，[Date de modification]，[Quantité]）" &安培; _
"VALUES（""& Me。[Réfproduit]&"""，"" Ajustement""，"& _
格式（Me。[DateDuDécompte]，" yyyy-mm-dd hh：nn：ss"）&"＃，"& intAjustment&" ;）"
$
CurrentDb.Execute strSQL，dbFailOnError
结束如果
你会理解我无法测试上面的内容，所以，当我检查时SQL和代码仔细检查任何错误，我不能排除你需要进行一些调试。
另一方面，你可以完全省略任何Ajustement行形成Opérationsd'prepireire，并仅根据购买和销售数据以及库存数据来计算手头价值。 你可以在
我的公共数据库文件夹中的Inventory.zip中找到一个这样的例子：
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
请注意，如果您使用的是早期版本的Access，您可能会发现某些表单对象（如按钮）的颜色显示不正确，您需要 相应地修改表格设计。
$
如果您无法打开链接，请复制链接（NB，而不是链接位置）并将其粘贴到浏览器的地址栏中。
如需修改此小演示文件以计算任何给定日期的手头价值，请参阅
这个最近的帖子。
Good morning,
I have a table named "Décompte inventaire par produit" on which I have these fields:
ID = Unique ID of the product
Réf produit= Description of the product
Quantité= Field that I wish to be filled automatically from a table named "Opérations d’inventaire"
Décompte_Physique= Inventory count at a specific date by employees
DateDuDécompte= Date on which the count of that specific product was made.
Note: The date included the hour, time and second and need to be taken into account when calculating the quantity
Table: OPÉRATIONS D’INVENTAIRE
in this table, all purchases and sales are recorded as follow:
Type de transaction= 3 choices (Achat(Purchase); Vendu(Sale); Ajustement(Adjustment)
Date de modification = Date of the transaction
Réf produit= Description of the product
Quantité= Quantity purchased or sold, note that the quantité is always a positive number in that table, but the quantité I amlooking for (Achat minus Vendu plus Ajustement) because the field ajustement is either positive or negative.
ID= Unique ID of the product
EXAMPLE EASY TO UNDERSTAND:
In the table "Opérations d'inventaire" I have 7 transactions for a specific product, 1 purchase and 6 sales,however, 3 of these sales occured after the physical inventory was done, so the quantity I am looking for here to be in the field QUANTITÉ in the table Décompte inventaire par produit should be 21
Image:
Can you tell me what is the best way to achieve that, I guess I have to create a query or an SQL statement that will retrieve the informations then create an update query to enter the quantity into the first table...
Thank you, Claude from Quebec, Canada
Claude Larocque
Is an Ajustement value in Opérations d’inventaire a manually entered positive or negative difference between the stock-take (décompte physique) value and the stock-in-hand value computed on the basis of purchases and sales to date at the time of the stock-take? If so, why is there no row in Opérations d’inventaire to reflect the stock-take of 2018-01-18 13:15:17? With such a row the computation of the stock-in-hand value per product after each transaction would be a standard 'balances' query, e.g.
SELECT T1.[Réf produit], T1.[Date de modification], T1.[Quantité],
SUM(T2.[Quantité] * IIF([Type de transaction]="Vendu",-1,1)) AS [Décompte inventaire]
FROM [Opérations d'inventaire] AS T1 INNER JOIN [Opérations d'inventaire] AS T2
ON (T2.[ID]<=T1.[ID] OR T2.[Date de modification]<>T1.[Date de modification])
AND (T2.[Date de modification]<=T1.[Date de modification]) AND (T2.[Réf produit]=T1.[Réf produit])
GROUP BY T1.[Réf produit], T1.[Date de modification], T1.[Quantité], T1.[ID]
ORDER BY T1.[Réf produit], T1.[Date de modification] DESC , T1.[ID] DESC;
You can of course automate the insertion of Ajustement rows into Opérations d’inventaire in the AfterInsert event procedure of a form bound to the Décompte inventaire par produit table:
Dim dtmLatestDate As Date
Dim intLatestCount As Integer
Dim intAjustment As String
Dim strCriteria As String
Dim strSQL As String
strCriteria = "[Réf produit] = """ & Me.[Réf produit] & """ And [Date de modification] <= #" & _
Format(Me.[DateDuDécompte],"yyyy-mm-dd hh:nn:ss") & "#"
dtmLatestDate = DMax("Date de modification","Opérations d'inventaire",strCriteria)
strCriteria = "[Réf produit] = """ & Me.[Réf produit] & """ And [Date de modification] = #" & _
Format(dtmLatestDate ,"yyyy-mm-dd hh:nn:ss") & "#"
intLatestCount = DLookup("Décompte inventaire", "NameOfAboveQueryGoesHere", strCriteria)
intAjustment = intLatestCount - Me.[Décompte_Physique]
If intAjustment <> 0 Then
strSQL = "INSERT INTO [Opérations d’inventaire] " & _
"([Réf produit],[Type de transaction],[Date de modification],[Quantité]) " & _
"VALUES(""" & Me.[Réf produit] &""",""Ajustement"",#" & _
Format(Me.[DateDuDécompte] ,"yyyy-mm-dd hh:nn:ss") & "#," & intAjustment & ")"
CurrentDb.Execute strSQL, dbFailOnError
End If
You'll appreciate that I cannot test the above, so, while I've examined the SQL and code carefully for any errors, I cannot rule out the need for some debugging on your part.
On the other hand, you could omit any Ajustement rows completely form Opérations d’inventaire, and compute the stock-in-hand values solely from the purchases and sales data, and the stock-takes data. You'll find an example of this in Inventory.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.
For modifications to this little demo file to compute the stock-in-hand values at any given date see this recent thread.
这篇关于根据特定产品的日期和时间查找数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
