MS Access 2013。我需要写一份报告,显示我们储物箱里的书的数量。
所有购买的书都有一个唯一的编号(SaleID)。我们每箱存放25本书,100本书需要4箱。当我们出售书籍时,它们会从特定的盒子中取出,随着时间的推移,假设售出了30本书。然后我们可以丢弃一个盒子,把剩下的70本书存放在三个盒子里。我们有数千本书和数百个储物箱。我需要运行一个报告,显示每100本书中有多少本书是库存的,这样我就可以减少盒子的数量(并节省存储空间)。
报告应该是这样的:
001 - 100 34(即有34本书的库存,编号范围为001到100)。
101 - 20035(库存35本)
201 - 30022(库存22本)
301 - 400 60(库存60本)等
下面的查询完成了我想要的,我在其中输入要查询的100本书的标准。但是我需要在报告上写一行,每一百本书,我们所有的书。
SELECT tblSale.BookInStock, Count(tblSale.BookInStock) AS [Total Books]
FROM tblSale
WHERE (((tblSale.SaleID) Between 4201 And 4300))
GROUP BY tblSale.BookInStock
HAVING (((tblSale.BookInStock)=Yes));
如果有人有任何想法,我会很感激你的想法干杯内华达州
由于您必须查询每一百个项目,因此需要对每一百个批次进行编码。因此,请考虑从VBA循环查询创建一个临时表。
循环的第一次迭代创建了取代前一个表的表,然后所有其他迭代都以100批(注意用于向前跳过迭代器的步骤)添加到数千个表中。顺便说一下,您的HAVING
条款条件被移到了WHERE
条款。
Dim i As Long
Dim sqlStr As String
Dim db As DAO.Database
Dim tbl As TableDef
Set db = CurrentDb
For Each tbl in db.TableDefs
If tbl.Name = "BoxBooksPerHundred" Then
db.TableDefs.Delete(tbl.Name)
End If
Next tbl
For i = 1 to 4300 Step 100 ' ADJUST AS NEEDED
If i = 1 Then
' MAKE-TABLE QUERY
strsql= "SELECT '" & i & " - " & i + 100 & "' AS [HundredRange], tblSale.BookInStock," _
& " Count(tblSale.BookInStock) AS [Total Books]" _
& " INTO BoxBooksPerHundred FROM tblSale" _
& " WHERE (((tblSale.BookInStock)=Yes)) And" _
& " (((tblSale.SaleID) Between " & i & " And " & i + 100 & "))" _
& " GROUP BY '" & i & " - " & i + 100 & "', tblSale.BookInStock;"
db.Execute strSQL, dbFailOnError
Else
' APPEND QUERY
strsql= "INSERT INTO BoxBooksPerHundred (HundredRange, BookInStock, [Total Books])"
& " SELECT '" & i & " - " & i + 100 & "' AS [HundredRange], tblSale.BookInStock," _
& " Count(tblSale.BookInStock) AS [Total Books]" _
& " FROM tblSale" _
& " WHERE (((tblSale.BookInStock)=Yes)) And" _
& " (((tblSale.SaleID) Between " & i & " And " & i + 100 & "))" _
& " GROUP BY '" & i & " - " & i + 100 & "', tblSale.BookInStock;"
db.Execute strSQL, dbFailOnError
End If
Next i
Set tbl = Nothing
Set db = Nothing
在打开报告的触发事件上运行此循环。但是由于相当多的数据,如果您希望数据不经常更改,请尝试触发数据库打开。最后,使用这个临时表BooksPerHundred作为报表的记录源。