The problem that i am having is that i am trying to convert convetional access queires to code because they involve using a few "IIf" statements in each and they are so slow i might die before they finish running!
Using conventional queires it is just about impossible to extract and manipulate the data using a single query, so i have a main query that gets the "raw" data, and a second query based on the first which sums the results etc.
(i have to do this for 3 sets of data on the same form, which is 6 queries in total).
What i am trying to do is create a recordset conatining all the data that i need, then work through it summing the data etc, displaying the results in a list box with option buttons to display the results.
This is all fine so far, but the bit that has me stumped is this.
I have created the first select statement which creates the main recordset in memory, BUT basing the second SELECT statement on this recordset results in access telling me that it can't find the named query/table?
There has got to be a way of creating a recordset and then using a SELECT statement to extract the relevant data from this recordset?
As a side issue, i'm pretty sure i'm going to need to "tag" a couple of fields onto this recordset, is there an easy way of adding a couple of field names to a recordset created in memory, and do i do this before or after the recordset is created?
I haven't got much hair left as it is, so if anyone can help both me and my head will be very greatful!
Thanks
Vince
SAMPLES OF CODE BEING USED SO FAR
-----------------------------------------------------------------------------------
Query1 pulls the main data from the table
SELECT tblSOP.AccountOwner AS EmployeeID, tblSOP.CommissionPaid, (IIf(Format([CommissionPaid])<>"","yes","No")) AS Paid, tblSOP.Status, tblSOP.DateOrder, tblSOP.OrderID, tblSOP.CompanyID, tblCompany.CompanyName, tblSopItem.ItemNumber AS SOPItem, tblSopItem.Deleted, tblSopItem.ProductID, tblSopItem.ProductDescription, tblPurchasingDespatch.DespatchID, tblPurchasingHistory.PurchasingID, tblSopItem.Quantity AS QtySold, tblSopItem.PriceEach AS SOPPrice, tblSopItem.CostEach AS SysCost, tblPurchasingHistory.ActualQtyBought AS ActBought, tblPurchasingHistory.ActualCostEach AS ActCost, tblPurchasingDespatch.QtyDespatched, tblPurchasingHistory.POPOrderId, tblPOPItem.ActualCost, ([QtyDespatched]*[ActualCost]) AS SOPCost, [qtydespatched]*[sopprice] AS [Value], [qtydespatched]*[actualcost] AS Cost, [Value]-[Cost] AS Profit, Format((([Profit])*(DLookUp("Value","tblValue","Item = '" & 35 & "'")/100)),"Fixed") AS Commission
FROM (tblPurchasingHistory INNER JOIN tblPOPItem ON (tblPurchasingHistory.POPOrderId = tblPOPItem.OrderID) AND (tblPurchasingHistory.POPItemNumber = tblPOPItem.ItemNumber)) INNER JOIN ((tblCompany INNER JOIN tblSOP ON tblCompany.CompanyID = tblSOP.CompanyID) INNER JOIN (tblPurchasingDespatch INNER JOIN tblSopItem ON (tblPurchasingDespatch.SOPItemNumber = tblSopItem.ItemNumber) AND (tblPurchasingDespatch.SOPOrderID = tblSopItem.OrderID)) ON tblSOP.OrderID = tblSopItem.OrderID) ON tblPurchasingHistory.PurchasingID = tblPurchasingDespatch.PurchasingID
WHERE (((tblSOP.AccountOwner)=[Forms]![frmCommissionList]![cboEmployee]) AND (((IIf(Format([CommissionPaid])<>"","yes","No")))=[Forms]![FrmCommissionList]![cboPaid]) AND ((tblSOP.Status)="complete") AND ((tblSopItem.Deleted)=False));
------------------------------------------------------------------------------------
Query2 is then based on query 1 to sum and group the data
SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],"mmmyyyy") AS [Date], QryCommissionCompletesDetail.OrderID, IIf(DFirst("OrderID","QryCommissionOrders","CompanyID = '" & [CompanyID] & "'")=[OrderID] And (Sum([Profit]))>25 And Format(Sum(([Profit]))/Sum([Value])*100,"Fixed")>25,"Yes","") AS NewAcc, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Count(QryCommissionCompletesDetail.SOPItem) AS Lines, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Sum(QryCommissionCompletesDetail.Commission) AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,"Fixed") AS Margin, IIf([NewAcc]="Yes",([Commission]*2),[Commission]) AS Pay, QryCommissionCompletesDetail.DateOrder
FROM QryCommissionCompletesDetail
GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],"mmmyyyy"), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder
HAVING (((QryCommissionCompletesDetail.EmployeeID)=[Forms]![frmCommissionList]![cboEmployee]) AND ((Format([DateOrder],"mmmyyyy"))=[Forms]![frmCommissionList]![cboMonth] & [Forms]![frmCommissionList]![cboYear]))
ORDER BY QryCommissionCompletesDetail.DateOrder DESC;
------------------------------------------------------------------------------------
SQL string built behind form so far, gets the same results as query one, but I can’t base the second SQL string on the resultant recordset "rstData"
Private Sub cmdCompletesDetail_Click()
Dim cDetail As String
Dim cSummary As String
Dim rstData As DAO.Recordset
'Build the detailed recordset to get the bulk data from the tables...
cDetail = "SELECT tblSOP.EmployeeID, tblSOP.AccountOwner, tblSOP.CommissionPaid, tblSOP.Status, tblSOP.DateOrder, tblSOP.OrderID, tblSOP.CompanyID, tblCompany.CompanyName, tblSopItem.ItemNumber, tblSopItem.Deleted, tblSopItem.ProductID, tblSopItem.ProductDescription, tblPurchasingDespatch.DespatchID, tblPurchasingHistory.PurchasingID, tblSopItem.Quantity, tblSopItem.PriceEach, tblSopItem.CostEach, tblPurchasingDespatch.QtyDespatched, tblPurchasingHistory.POPOrderId,tblPOPItem.ActualCost "
cDetail = cDetail & "FROM (tblCompany INNER JOIN tblSOP ON tblCompany.CompanyID = tblSOP.CompanyID) INNER JOIN ((tblPurchasingHistory INNER JOIN tblPOPItem ON (tblPurchasingHistory.POPItemNumber = tblPOPItem.ItemNumber) AND (tblPurchasingHistory.POPOrderId = tblPOPItem.OrderID)) INNER JOIN (tblPurchasingDespatch INNER JOIN tblSopItem ON (tblPurchasingDespatch.SOPOrderID = tblSopItem.OrderID) AND (tblPurchasingDespatch.SOPItemNumber = tblSopItem.ItemNumber)) ON tblPurchasingHistory.PurchasingID = tblPurchasingDespatch.PurchasingID) ON tblSOP.OrderID = tblSopItem.OrderID "
cDetail = cDetail & "WHERE (((Format(tblSOP.DateOrder, 'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "') AND (((tblSOP.EmployeeID)) = '" & Me.cboEmployee & "') AND (((tblSOP.Status)) = '" & "Complete" & "')) OR (((Format(tblSOP.DateOrder, 'mmmyyyy')) = '" & Me.CboMonth & Me.CboYear & "') AND (((tblSOP.AccountOwner)) = '" & Me.cboEmployee & "') AND (((tblSOP.Status)) = '" & "Complete" & "') AND (((tblSOP.Deleted)) = False))ORDER BY tblSOP.OrderID DESC;"
Set rstData = CurrentDb.OpenRecordset(cSOPCompletes, dbOpenSnapshot)
If rstData.RecordCount > 0 Then
rstData.MoveFirst
'Second SELECT statement goes here selecting records FROM rstData
End If
'Me.LstCompletes.RowSource = cDetail (works fine so far with list box displaying the first recordset)
Me.LstCompletes.rowsource = cSummary
Me.LstCompletes.Requery
Me.txtOrderCount = Me.LstCompletes.ListCount
End Sub
Using conventional queires it is just about impossible to extract and manipulate the data using a single query, so i have a main query that gets the "raw" data, and a second query based on the first which sums the results etc.
(i have to do this for 3 sets of data on the same form, which is 6 queries in total).
What i am trying to do is create a recordset conatining all the data that i need, then work through it summing the data etc, displaying the results in a list box with option buttons to display the results.
This is all fine so far, but the bit that has me stumped is this.
I have created the first select statement which creates the main recordset in memory, BUT basing the second SELECT statement on this recordset results in access telling me that it can't find the named query/table?
There has got to be a way of creating a recordset and then using a SELECT statement to extract the relevant data from this recordset?
As a side issue, i'm pretty sure i'm going to need to "tag" a couple of fields onto this recordset, is there an easy way of adding a couple of field names to a recordset created in memory, and do i do this before or after the recordset is created?
I haven't got much hair left as it is, so if anyone can help both me and my head will be very greatful!
Thanks
Vince
SAMPLES OF CODE BEING USED SO FAR
-----------------------------------------------------------------------------------
Query1 pulls the main data from the table
SELECT tblSOP.AccountOwner AS EmployeeID, tblSOP.CommissionPaid, (IIf(Format([CommissionPaid])<>"","yes","No")) AS Paid, tblSOP.Status, tblSOP.DateOrder, tblSOP.OrderID, tblSOP.CompanyID, tblCompany.CompanyName, tblSopItem.ItemNumber AS SOPItem, tblSopItem.Deleted, tblSopItem.ProductID, tblSopItem.ProductDescription, tblPurchasingDespatch.DespatchID, tblPurchasingHistory.PurchasingID, tblSopItem.Quantity AS QtySold, tblSopItem.PriceEach AS SOPPrice, tblSopItem.CostEach AS SysCost, tblPurchasingHistory.ActualQtyBought AS ActBought, tblPurchasingHistory.ActualCostEach AS ActCost, tblPurchasingDespatch.QtyDespatched, tblPurchasingHistory.POPOrderId, tblPOPItem.ActualCost, ([QtyDespatched]*[ActualCost]) AS SOPCost, [qtydespatched]*[sopprice] AS [Value], [qtydespatched]*[actualcost] AS Cost, [Value]-[Cost] AS Profit, Format((([Profit])*(DLookUp("Value","tblValue","Item = '" & 35 & "'")/100)),"Fixed") AS Commission
FROM (tblPurchasingHistory INNER JOIN tblPOPItem ON (tblPurchasingHistory.POPOrderId = tblPOPItem.OrderID) AND (tblPurchasingHistory.POPItemNumber = tblPOPItem.ItemNumber)) INNER JOIN ((tblCompany INNER JOIN tblSOP ON tblCompany.CompanyID = tblSOP.CompanyID) INNER JOIN (tblPurchasingDespatch INNER JOIN tblSopItem ON (tblPurchasingDespatch.SOPItemNumber = tblSopItem.ItemNumber) AND (tblPurchasingDespatch.SOPOrderID = tblSopItem.OrderID)) ON tblSOP.OrderID = tblSopItem.OrderID) ON tblPurchasingHistory.PurchasingID = tblPurchasingDespatch.PurchasingID
WHERE (((tblSOP.AccountOwner)=[Forms]![frmCommissionList]![cboEmployee]) AND (((IIf(Format([CommissionPaid])<>"","yes","No")))=[Forms]![FrmCommissionList]![cboPaid]) AND ((tblSOP.Status)="complete") AND ((tblSopItem.Deleted)=False));
------------------------------------------------------------------------------------
Query2 is then based on query 1 to sum and group the data
SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],"mmmyyyy") AS [Date], QryCommissionCompletesDetail.OrderID, IIf(DFirst("OrderID","QryCommissionOrders","CompanyID = '" & [CompanyID] & "'")=[OrderID] And (Sum([Profit]))>25 And Format(Sum(([Profit]))/Sum([Value])*100,"Fixed")>25,"Yes","") AS NewAcc, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Count(QryCommissionCompletesDetail.SOPItem) AS Lines, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Sum(QryCommissionCompletesDetail.Commission) AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,"Fixed") AS Margin, IIf([NewAcc]="Yes",([Commission]*2),[Commission]) AS Pay, QryCommissionCompletesDetail.DateOrder
FROM QryCommissionCompletesDetail
GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],"mmmyyyy"), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder
HAVING (((QryCommissionCompletesDetail.EmployeeID)=[Forms]![frmCommissionList]![cboEmployee]) AND ((Format([DateOrder],"mmmyyyy"))=[Forms]![frmCommissionList]![cboMonth] & [Forms]![frmCommissionList]![cboYear]))
ORDER BY QryCommissionCompletesDetail.DateOrder DESC;
------------------------------------------------------------------------------------
SQL string built behind form so far, gets the same results as query one, but I can’t base the second SQL string on the resultant recordset "rstData"
Private Sub cmdCompletesDetail_Click()
Dim cDetail As String
Dim cSummary As String
Dim rstData As DAO.Recordset
'Build the detailed recordset to get the bulk data from the tables...
cDetail = "SELECT tblSOP.EmployeeID, tblSOP.AccountOwner, tblSOP.CommissionPaid, tblSOP.Status, tblSOP.DateOrder, tblSOP.OrderID, tblSOP.CompanyID, tblCompany.CompanyName, tblSopItem.ItemNumber, tblSopItem.Deleted, tblSopItem.ProductID, tblSopItem.ProductDescription, tblPurchasingDespatch.DespatchID, tblPurchasingHistory.PurchasingID, tblSopItem.Quantity, tblSopItem.PriceEach, tblSopItem.CostEach, tblPurchasingDespatch.QtyDespatched, tblPurchasingHistory.POPOrderId,tblPOPItem.ActualCost "
cDetail = cDetail & "FROM (tblCompany INNER JOIN tblSOP ON tblCompany.CompanyID = tblSOP.CompanyID) INNER JOIN ((tblPurchasingHistory INNER JOIN tblPOPItem ON (tblPurchasingHistory.POPItemNumber = tblPOPItem.ItemNumber) AND (tblPurchasingHistory.POPOrderId = tblPOPItem.OrderID)) INNER JOIN (tblPurchasingDespatch INNER JOIN tblSopItem ON (tblPurchasingDespatch.SOPOrderID = tblSopItem.OrderID) AND (tblPurchasingDespatch.SOPItemNumber = tblSopItem.ItemNumber)) ON tblPurchasingHistory.PurchasingID = tblPurchasingDespatch.PurchasingID) ON tblSOP.OrderID = tblSopItem.OrderID "
cDetail = cDetail & "WHERE (((Format(tblSOP.DateOrder, 'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "') AND (((tblSOP.EmployeeID)) = '" & Me.cboEmployee & "') AND (((tblSOP.Status)) = '" & "Complete" & "')) OR (((Format(tblSOP.DateOrder, 'mmmyyyy')) = '" & Me.CboMonth & Me.CboYear & "') AND (((tblSOP.AccountOwner)) = '" & Me.cboEmployee & "') AND (((tblSOP.Status)) = '" & "Complete" & "') AND (((tblSOP.Deleted)) = False))ORDER BY tblSOP.OrderID DESC;"
Set rstData = CurrentDb.OpenRecordset(cSOPCompletes, dbOpenSnapshot)
If rstData.RecordCount > 0 Then
rstData.MoveFirst
'Second SELECT statement goes here selecting records FROM rstData
End If
'Me.LstCompletes.RowSource = cDetail (works fine so far with list box displaying the first recordset)
Me.LstCompletes.rowsource = cSummary
Me.LstCompletes.Requery
Me.txtOrderCount = Me.LstCompletes.ListCount
End Sub
Last edited:
