Results 1 to 7 of 7
  1. #1
    JamesP
    Guest

    Subquery Problem

    I am attempting to use a subquery to filter the return of another query. This is my first attempt [img]/w3timages/icons/baby.gif[/img] and I have not been successful. My sub query is:
    (SELECT RT.LineNumber, RT.Section, RT.Subsection, RT.Affix, RT.Footage, Max(RT.InspectionDate) AS MaxOfInspectionDate
    FROM ThicknessReadings AS RT
    WHERE (((RT.LineNumber)="1C") AND ((RT.Section)=1) AND ((RT.Subsection)=2) AND ((RT.Affix)=""))
    GROUP BY RT.LineNumber, RT.Section, RT.Subsection, RT.Affix, RT.Footage
    Now I was hoping to use this sub queries results as a "candidate list" for the main query, which includes all the fields from the table in question, by using:
    WHERE RT.LineNumber, RT.Section, RT.Subsection, RT.Affix, RT.InspectionDate IN
    Is this possible? Is there another way?

    Thanks!!!!!

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subquery Problem

    You can do what you are attempting but, Access will only let you have a single field in the SELECT for the sub query.

    SELECT RT.LineNumber FROM.....

    This subquery would then go in the WHERE for the RT.LineNumber column of the main query.


    Hope this helps

    Richard Aheron
    raheron@hotmail.com
    [img]/w3timages/icons/grin.gif[/img]
    Richard

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subquery Problem

    You need to return a single field from your subquery for comparison in your main query. Then you use either an "exists" or an "in" keyword to compare a field value in the main query. Exists is broader, In is faster in most cases. When you use negation (Not In), the Exists is usually faster.

    If your records in ThicknessReadings contain a unique key, return that from your subquery. Then you can compare the unique key in your main query to the subquery result based on the Where clause in the subquery.

    Does that help you at all?
    Charlotte

  4. #4
    JamesP
    Guest

    Re: Subquery Problem

    Thanks Richard and Charlotte!
    It does help in a way of letting me know what is stopping me.
    Charlotte I created a unique key in the ThicknessReadings table but, when I try to include it with my subquery I am no longer able to get the return I wanted. It is because of the aggregate field I am using MAX() in one field and GROUPED BY in the others. I can't seem to use the key field without including it in the GROUPED BY and when I do it ruins my return.[img]/w3timages/icons/sick.gif[/img]
    Any ideas?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subquery Problem

    Sorry about that. I slid right over the Max() in your SQL. The problem with using an aggregate like Max() (even if the query engine lets you) is that it doesn't necessarily return what you think you asked for. Can you explain what you're trying to accomplish with the query? Maybe there's another way of wording the whole thing to get what you want. Post the entire SQL, and we'll work on it.
    Charlotte

  6. #6
    JamesP
    Guest

    Re: Subquery Problem

    Thanks Charlotte!
    I have this large table that contains information about thickness readings taken at different footages on a pipe. The problem is that I need the latest thickness reading (InspectionDate) for each footage. I need that per location which is disrobed as Line, Section, Subsection and Affix.

    SELECT RT.EquipmentId, RT.DueDate, RT.InspectionDate, RT.Footage, RT.LineNumber, RT.Section, RT.Subsection, RT.Affix, RT.Length, RT.Diameter, RT.Material, RT.Descriptor, RT.Type, RT.DrawingNumber, RT.UsedHours, RT.InstallationDate, RT.LastRotationDate, RT.LastRotationNumber, RT.LastRotationAngle, RT.PMDate, RT.RepairDate, RT.ResetDate, RT.MonthsAtInspectionDate, RT.HoursAtInspectionDate, RT.EstimatedLife, RT.RemainingLife, RT.InspectionType, RT.Measurement1, RT.Measurement2, RT.Measurement3, RT.Measurement4, RT.Measurement5, RT.Measurement6, RT.Measurement7, RT.Measurement8, RT.Measurement9, RT.Measurement10, RT.Measurement11, RT.Measurement12, RT.InspectionComments, RT.SurveyType, RT.ThinknessKey
    FROM ThicknessReadings AS RT
    WHERE RT.LineNumber, RT.Section, RT.Subsection, RT.Affix IN
    (SELECT RT.LineNumber, RT.Section, RT.Subsection, RT.Affix, RT.Footage, Max(RT.InspectionDate) AS MaxOfInspectionDate
    FROM ThicknessReadings AS RT
    WHERE (((RT.LineNumber)="1C") AND ((RT.Section)=1) AND ((RT.Subsection)=2) AND ((RT.Affix)=""))
    GROUP BY RT.LineNumber, RT.Section, RT.Subsection, RT.Affix, RT.Footage

    Thanks again Charlotte. The person who was working on this data base before me was creating Temp tables then re-querying them. I was hoping there was another way.

    Thanks!

  7. #7
    JamesP
    Guest

    Re: Subquery Problem

    Thanks for the attempt Charlotte, but I think I found a way afound it.
    Thanks again for trying!!!

Posting Permissions

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