I this small program is very simple, two database tables, a stock table, a table of shipment, two tables are the primary key (ID) the article number of queries sold what date period, which the article number of how many query inventory! Thank you

CodePudding user response:

 Private Sub Command13_Click () 
Set the CONN=New ADODB. Connection 
Dim the rs As New ADODB. You 
Lj=App. Path 
Mdbfilename=lj & amp; "\ T.m db 
"CONN. Open the provider="Microsoft. Jet. The OLEDB. 4.0; Data source="& amp; Mdbfilename 
SQL="SELECT stock table. ID, the Sum (stock table. Stock number) AS always carried out, the Sum (delivery table. Shipment number) AS shipments, total [were] - [shipments] AS summary of stock quantity" _ 
& "FROM stock table INNER JOIN table delivery ON stock table. ID=delivery table. ID GROUP BY stock table. ID;" 
Rs. The Open SQL, CONN, adOpenKeyset, adLockReadOnly, adCmdText 
The Set MSHFlexGrid1. 
 the DataSource=rsMSHFlexGrid1. Refresh 
With MSHFlexGrid1 
. AllowBigSelection=True 'set grid style 
The FillStyle=flexFillRepeat 
For I=0 To. The Rows 1 
. Row=I:. Col=. FixedCols 
. ColSel=. Cols () - FixedCols - 1 
If I Mod 2=0 Then 
. CellBackColor=& amp; HC0C0C0 'light grey 
End the If 
Next I 
End With 
MSHFlexGrid1. Visible=True 
End Sub 

CodePudding user response:

Statistical calculations again

 SELECT the IIF (ISNULL (stock statistics. ID), shipment statistics. ID, replenish onr's stock statistics. ID) AS ID, 
Stock statistics. Always carried, 
The IIF (ISNULL (shipment statistics. Shipments), 0, shipment statistics. Shipments) AS shipments, 
Stock statistics. Always carried out - the IIF (ISNULL (shipment statistics. Shipments), 0, shipment statistics. Shipments) AS summary of stock quantity 
The FROM (SELECT ID, the Sum (stock) AS always carried the FROM stock table GROUP BY ID) AS stock statistics 
LEFT the JOIN (SELECT ID, the Sum (delivery) AS the total shipments FROM shipment table GROUP BY ID) shipment AS statistical 
Shipment ON stock statistics. ID=statistics. ID;