Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58

    Unanswered: SQL mixing aggregate & non-aggr exprs

    How do I create a query that emulates a mix of aggregate & ‘non-aggregate’ expressions. I am using the query as the rowsource for a list box in Access.

    Here is my query:
    strSql = "SELECT tblTestHeader.TestHdrUniq, tblTestHeader.TestDate, " _
    & "'" & Forms("frmCompanySearch").[lstCompanySrch].Column(2) & "'" _
    & " + ' ' + tblLoadCell.Abbrev + ' ' + tblTestHeader.CertNumSequence AS CertNum, " _
    & "tblLoadCell.Description AS LoadCell, " _
    & "tblTestType.Description AS Test, " _
    & "tblTester.Sname + ', ' + tblTester.Gname AS Tester " _
    & "FROM tblTestHeader INNER JOIN tblLoadCell ON " _
    & "tblTestHeader.LoadCellUniq = tblLoadCell.LoadCellUniq INNER JOIN " _
    & "tblTester ON " _
    & "tblTestHeader.TesterUniq = tblTester.TesterUniq INNER JOIN " _
    & "tblTestType ON " _
    & "tblTestHeader.TestTypeUniq = tblTestType.TestTypeUniq " _
    & "WHERE CompanyUniq = " _
    & Forms("frmCompanySearch").[lstCompanySrch].Column(1) _
    & " ORDER BY tblTestHeader.TestDate, CertNum"

    I want to include another column:
    max(tblTestDetail.CertChar) as LastChar
    but must have all or no aggregate expr. What is work around for this ?
    Attached Thumbnails Attached Thumbnails db_diag.bmp  
    catkins

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One solution:

    strSql = "SELECT tblTestHeader.TestHdrUniq, tblTestHeader.TestDate, " _
    & "'" & Forms("frmCompanySearch").[lstCompanySrch].Column(2) & "'" _
    & " + ' ' + tblLoadCell.Abbrev + ' ' + tblTestHeader.CertNumSequence AS CertNum, " _
    & "tblLoadCell.Description AS LoadCell, " _
    & "tblTestType.Description AS Test, " _
    & "LastCharQuery.LastChar as LastChar, "
    & "tblTester.Sname + ', ' + tblTester.Gname AS Tester " _
    & "FROM tblTestHeader INNER JOIN tblLoadCell ON " _
    & "tblTestHeader.LoadCellUniq = tblLoadCell.LoadCellUniq INNER JOIN " _
    & "tblTester ON " _
    & "tblTestHeader.TesterUniq = tblTester.TesterUniq INNER JOIN " _
    & "tblTestType ON " _
    & "tblTestHeader.TestTypeUniq = tblTestType.TestTypeUniq " _
    & ", (select max(CertChar) as LastChar from tblTestDetail) LastCharQuery"
    & "WHERE CompanyUniq = " _
    & Forms("frmCompanySearch").[lstCompanySrch].Column(1) _
    & " ORDER BY tblTestHeader.TestDate, CertNum"

    blindman

Posting Permissions

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