Can you use an SQL statement as part of another in Having or Where conditions

Topic

New Reply

I have data base that at present I use a Docmd Run Sql to create a table based on a field not being Null and grouped by a batch number. I use that table in another Docmd RunSQL to filter all the records with the Batch numbers from the first table and make another table.

To me this seems very clumsy and wondered if the two procedures could be combined so as the first query became the Having or Where clause of the second. The last table is used to produce a report so if I could combine the two procedures I coal avoid creating the table altogether I would hope.

Thanks

Reply | Quote

Replies

You can do that, yes. On more technical terms, you can run a subquery in the Having or WHERE clauses. I guess it would be a matter of determining what each query is doing and find the best way to combine them.

Reply | Quote

Based on what you described, I don’t understand the reason for that first query, nor do I understand the need for all those intermediate tables! In your 2nd query, can’t you just join to the table containing that field in question? Then in your WHERE condition, you could start with “WHERE thatTable.ThatField Is Not Null…”.

Reply | Quote

Thank you both for taking the time to reply.

Mark, I will try and explain. The Batch # referred to in my earlier post groups records that may include both revenue and expenses. What I wanted to do was separate Batch’s that included revenue from those that did not. Hence by grouping on type ( the field I referred to as being null or not). This returned just the batch #’s for 1) revenue, 2)revenue and expenses or just 3)expenses. Using the batch numbers to return all of the records for the selected batch #’s produced the final result used to produce a report that was either Revenue or Expense based.

In the clumsy way I am presently doing this (hence the request for assistance) the first table which is just a list of batch numbers is joined to the main db to produce the second table which is the basis for the report.

Hope this makes sense thank you again.

Peter

Reply | Quote

Can you post the SQL for the queries you are using? Maybe using that we’ll be able to suggest something.

Reply | Quote

The start is a form that allows me to select a number of reports of which this sequence is one.

The Sub “ExpReport1Year” is called first that builds the two tables then it opens the report.

In this case it is the report for Expenses only. the field “Type” can be a revenue classification or it can be blank. If it is blank the batch is only for expenses. You can see the report is designed to be for a selected year.

Thanks again

Private Sub ExpOnly1PYear_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “rptSelOpforExp”
Call ExpREport1Year
stLinkCriteria = “[OperatorID]=” & Me![OperatorID]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
End Sub

Private Sub ExpREport1Year()
‘Creates a table of Batch numbers for the production year selected. 11192014
‘Remember the object is to get batch numbers that appear in the production year as it is possible that there may be items in prior years
Year1 = InputBox(“enter Production year”)

DoCmd.SetWarnings False

‘Note TYpe is Null to make sure no revenue entrys are included
DoCmd.RunSQL “SELECT tblIncome_Expenditure.BatchID ” _
& “INTO tblBatchNoExpSelYear ” _
& “FROM tblIncome_Expenditure ” _
& “GROUP BY tblIncome_Expenditure.BatchID, ” _
& “tblIncome_Expenditure.Year, ” _
& “tblIncome_Expenditure.Type ” _
& “HAVING (((tblIncome_Expenditure.Year)=” & Year1 & “) ” _
& “AND ((tblIncome_Expenditure.Type) Is Null));”

‘Creates a table of Data for the Selected year based on the Batch Numbersin the previous SQL statement
DoCmd.RunSQL “SELECT tblIncome_Expenditure.BatchID, ” _
& “tblIncome_Expenditure.OperatorID, ” _
& “tblIncome_Expenditure.Month, tblIncome_Expenditure.Year, ” _
& “Sum(tblIncome_Expenditure.Revenue) AS SumOfRevenue, ” _
& “Sum(tblIncome_Expenditure.Taxes) AS SumOfTaxes, ” _
& “Sum(tblIncome_Expenditure.GOthDedNothDeds) AS SumOfGOthDedNothDeds, ” _
& “Sum(tblIncome_Expenditure.Expenses) AS SumOfExpenses, ” _
& “Sum(tblIncome_Expenditure.NetThisEntry) AS SumOfNetThisEntry ” _
& “INTO tblExpOnlySelYear ” _
& “FROM tblBatchNoExpSelYear INNER JOIN tblIncome_Expenditure ” _
& “ON tblBatchNoExpSelYear.BatchID = tblIncome_Expenditure.BatchID ” _
& “GROUP BY tblIncome_Expenditure.BatchID, tblIncome_Expenditure.OperatorID, ” _
& “tblIncome_Expenditure.Month, tblIncome_Expenditure.Year ” _
& “HAVING (((tblIncome_Expenditure.Year)=” & Year1 – 1 & “)) ” _
& “OR (((tblIncome_Expenditure.Year)=” & Year1 & “)) ” _
& “OR (((tblIncome_Expenditure.Year)<[year]));"
DoCmd.SetWarnings True
End Sub

Reply | Quote