Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Rui
    -------
    R4

  3. The Following User Says Thank You to ruirib For This Useful Post:

    mitchbvi (2014-12-28)

  4. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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...".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    mitchbvi (2014-12-28)

  6. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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

  7. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Can you post the SQL for the queries you are using? Maybe using that we'll be able to suggest something.
    Rui
    -------
    R4

  8. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •