SELECT DISTINCTROW Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal AS SaleAmount
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) ON Employees.EmployeeID = Orders.EmployeeID ORDER by Orders.ShippedDate Desc
_________________________________________
各位麻烦看一下,这是何意?
(Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID)
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) ON Employees.EmployeeID = Orders.EmployeeID ORDER by Orders.ShippedDate Desc
_________________________________________
各位麻烦看一下,这是何意?
(Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID)
加[]是为了表明Order Subtotals 是个整体
Leverling Janet USA 1000
Laverling Janet UK 560
Davolio Nancy USA 2000
Davolio Nancy UK 1067
Sales For: 6/5/1996Country Last Name First Name Sales
USA Leverling Janet 1000
UK Leverling Janet 560
USA Davolio Nancy 2000
UK Davolio Nancy 1067
Total USA 3000
Total UK 1672是要生成这样的报表而进行的查询!哪位高手看得明白给我讲一下O吧?
下面这段是ACTIVEREPORT中的代码:
Dim iTotalUSA As Integer
Dim iTotalUK as IntegerPrivate Detail_Format()
If txtCountry = "USA" Then
iTotalUSA = iTotalUSA + txtSaleAmount.DataValue
ElseIf txtCountry = "UK" Then
iTotalUK = iTotalUK + txtSaleAmount.DataValue
End If
End SubPrivate Sub gfShippedDate_Format()
txtTotalUK.DataValue = iTotalUK
txtTotalUSA.DataValue = iTotalUSA
iTotalUK = 0
iTotalUSA = 0
End Sub
Leverling Janet USA 1000
Laverling Janet UK 560
Davolio Nancy USA 2000
Davolio Nancy UK 1067
Sales For: 6/5/1996Country Last Name First Name Sales
USA Leverling Janet 1000
UK Leverling Janet 560
USA Davolio Nancy 2000
UK Davolio Nancy 1067
Total USA 3000
Total UK 1672
This sample project demonstrates how to set up a report for conditional summaries by using variables to perform aggregate operations.1. Create a new Visual Basic EXE2. Add two command buttons to Form1.3. Name the command buttons cmdPrint and cmdPreview4. Add ActiveReports to the project.5. Place the following code under the command buttons on Form1Private Sub cmdPrint_Click()
ActiveReport1.printreport false
End Sub
Private Sub cmdPreview_Click()
ActiveReport1.show
End Sub6. Place an ADO data control on ActiveReport17. Connect to Nwind.mdb (see Chapter 3 for help with connecting)8. Set the data control's source property to the following SQL statementSELECT DISTINCTROW Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal AS SaleAmount
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) ON Employees.EmployeeID = Orders.EmployeeID ORDER by Orders.ShippedDate Desc9. Right-click and insert a GroupHeader/Footer.10. Click on the New Section "GroupHeader1" to select it11. Modify the section's properties as follows:Name ghShippedDate
DataField ShippedDate
Height 84012. Click on the new section "GroupFooter1" to select it13. Modify the section's properties as follows:Name gfShippedDate
Height 810
14. Add two text fields to gfShippedDate and modify their properties as follows:Name txtTotalUSA txtTotalUK
Height 270 270
Left 7740 7740Top 90 450
Width 1530 1530Alignment ddTXRight ddTXRight
OutputFormat $###0.00 $###0.0015. Add two labels to gfShippedDate and modify their properties as follows:Name lblTotalUSA lblTotalUK
Caption US Total Sales UK Total Sales
Height 270 270
Left 7740 7740
Top 90 450
Width 1530 1530
Alignment ddTXRight ddTXRight
Font.Bold True True16. Click and drag the following fields from the fields list into the detail section:Country LastName FirstName SaleAmount17. Set the field's properties as follows:Name txtCountry txtLastName txtFirstName txtSaleAmount
DataField Country LastName FirstName SaleAmount
Height 270 270 270 270
Left 0 1530 4140 7920
Top 0 0 0 0
Width 1440 2520 2790 1440
Alignment ddTXLeft ddTXLeft ddTXLeft ddTXRight
OutputFormat $###0.0018. Click and drag the following field from the field's list into ghShippedDateShippedDate19. Set the field's properties as followsName txtShippedDate
DataField ShippedDate
Height 360
Left 1620Top 0
Width 2790Font.Bold True
Font.Size 1620. Add five labels to ghShippedDate and modify their properties as follows:Name lblSales lblCountry lblLastName lblFirstName
Caption Sales: Country Last Name First Name
Height 360 270 270 270
Left 0 0 1530 4140
Top 0 540 540 540
Width 1530 1440 2520 2790
Font.Bold True True True TrueFont.Size 16Name lblSaleAmount
Caption Sales Amount
Height 270
Left 7830
Top 540
Width 1440
Font.Bold True
Alignment ddTXRight21. Set the Detail sections CanShrink property to True.22. Add the following code to the Detail_Format event to increment a integer variable for each country's SaleAmount detail item and then set the variable's total value to the separate fields in gfShippedDate_Format sub.Dim iTotalUSA As Integer
Dim iTotalUK as IntegerPrivate Detail_Format()
If txtCountry = "USA" Then
iTotalUSA = iTotalUSA + txtSaleAmount.DataValue
ElseIf txtCountry = "UK" Then
iTotalUK = iTotalUK + txtSaleAmount.DataValue
End If
End SubPrivate Sub gfShippedDate_Format()
txtTotalUK.DataValue = iTotalUK
txtTotalUSA.DataValue = iTotalUSA
iTotalUK = 0
iTotalUSA = 0
End Sub
全篇的!