SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Query Help

  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Help

    I've been pushed into handling a couple of issues and am in need of some assistance.

    I'm attempting to create a query in Access to combine 2 fields of a record. Additionally I need to take that value and return the most recent record for that value (as well as list the records for each other unique value).

    I've taken a few cracks at it but I'm missing something. I initially used this post as reference: Combining Fields and tried to crank through it but I'm stuck.

    Here's my current attempt:
    Code:
    SELECT ReportData.SubDate, ReportData.VendorCode, ReportData.VendorName, ReportData.Contract & "_" & ReportData.Release AS ContractRelease, ReportData.MngHeadCount, ReportData.BusHeadcount, ReportData.AdminHeadcount
    FROM ReportData INNER JOIN (SELECT ReportData.ContractRelease, Max(SubDate) AS LastDate 
    FROM ReportData GROUP BY ReportData.ContractRelease)  AS B 
    ON (ReportData.ContractRelease=B.ContractRelease) AND (ReportData.SubDate=B.LastDate)
    ORDER BY ReportData.ContractRelease;
    Fields and Data Types:

    SubDate=Date/Time
    VendorName=Text
    VendorCode=Text
    Contract=Text
    Release=Text
    All Headcount Fields=Number(Double)


    Thanks for any help!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ReportData.SubDate
         , ReportData.VendorCode
         , ReportData.VendorName
         , ReportData.Contract & "_" & ReportData.Release AS ContractRelease
         , ReportData.MngHeadCount
         , ReportData.BusHeadcount
         , ReportData.AdminHeadcount
      FROM ( SELECT Contract
                  , Release
                  , MAX(SubDate) AS LastDate 
               FROM ReportData 
             GROUP 
                 BY Contract
                  , Release ) AS B 
    INNER
      JOIN ReportData 
        ON ReportData.Contract = B.Contract
       AND ReportData.Release = B.Release
       AND ReportData.SubDate = B.LastDate
    ORDER 
        BY ReportData.Contract
         , ReportData.Release
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rock on! Thanks.


Bookmarks

Posting Permissions

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