Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates on Report dilemma (Access 97)

    Here is my dilemma. I have a report that lists multiple entries for each record and I want it to only list the most recent date for each record. Basically, I have a report that has an inspection date and a test date and what I want it to do is to look up the most recent inspection date, and if there are multiple entries with the same inspection date for a particular record, then I want it to look at the most recent test date. Only if there are multiple entries for the inspection date should it look at the test date. Therefore, I will have the most recent information for each record and no multiple entries. Does this make sense? The report is based off a query that contains three reports that are joined by the same primary key. I'm having trouble figuring out how to do this since the max function is not producing the results that I want.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates on Report dilemma (Access 97)

    I think that you will have to use nested queries or queries based on queries. Nested queries are hard to debug, so try queries based on queries first.

    We're going to start by getting the most recent inspection date for each Primary Key (if there is only one, that will be returned). Create a new query based on the query you already have (the record source of the report). Add only the primary key field and the inspection date to the query grid. Make the query into a Totals query, and set the Totals option for the primary key to Group By and for the inspection date to Max. Save this query. I'll call it qryMostRecentInspection to have a handle for it in what follows.

    Next, we're going to get the most recent test date belonging to the most recent inspection date (if there is only one, that will be returned). Create a new query based on the original query and on qryMostRecentInspection. Join them on the primary key and on inspection date vs. MaxOfInspectionDate (or whatever name the maximum has). Add the primary key, inspection date and test date (all from the original query) to the query grid. Make this query into a Totals query too, Group By on primary key and inspection date, Max on test date. Save this query, I'll use qryMostRecentTest.

    Finally, we're going to create a query that will return the desired records. Create a new query based on the original query and on qryMostRecentTest. Join them on the primary key, inspection date and on test date vs. MaxOfTestDate (or whatever name you have). Add * from the original query to the query grid. You can switch to datasheet view to check that the correct records are returned. The query will not be updatable, but since it will be used for a report, that doesn't matter. Save this query and use it as the new record source of the report.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Can you post the basic table structure, with a little sample data showing what is in there, and what you want to have 'pulled'? I think you could use a sub-query or union query to pull of what you want. (Or a combo of both.)

  4. #4
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    This actually worked. The only thing that I had to change was in the last paragraph when you told me to join the test date on the MaxOfTestDate. When I had did that, it didn't include the places where there was a null/blank test date. I needed those dates also b/c they still would have an inspection date. You were very helpful, thank you. I really appreciate it.

  5. #5
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Thanks for responding. I am going to send you a sample of what should appear on the report and an example of the access tables.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Okay, I am attaching that same Sample.mdb file, but there are two changes. First, Table2's ID field is changed to a Number field type. That is needed to link the data. Next, there is a qryFinalResults query. This query pulls the data up exactly as you requested (both by the criteria you posted...and it shows the same data as your excel spreedsheet. Here's the SQL for it:

    SELECT T1.Permit_Id, Table1.Est_Name, Table1.Est_Address, Table1.Est_City, Table1.Est_State, Table1.Est_Zip, Max(T1.Inspection_Date) AS MaxOfInspection_Date, IIf(Count([Inspection_Date])>1,(SELECT Max(T2.Test_Date) AS MaxOfTest_Date FROM Table2 AS T2 WHERE (((T2.Inspection_Date)=(SELECT Max(Table2.Inspection_Date) AS MaxOfInspection_Date FROM Table2 WHERE (((Table2.Permit_Id)=T2.Permit_ID))[img]/forums/images/smilies/wink.gif[/img]) AND ((T2.Permit_Id)=T1.Permit_Id))[img]/forums/images/smilies/wink.gif[/img],Null) AS TestDate
    FROM Table2 AS T1 INNER JOIN Table1 ON T1.Permit_Id = Table1.Permit_Id
    GROUP BY T1.Permit_Id, Table1.Est_Name, Table1.Est_Address, Table1.Est_City, Table1.Est_State, Table1.Est_Zip;

    This query actually uses a Query within a Query. The trick was the test date. Getting it to show the Max Inspection date is easy, it's in the 'primary' query, just as a Max. However, to pull the right Test date, it has a subquery, which also has a subquery. The 'first' or top level subquery does a count on the Inspection Date field based on the ID of the primary query. If this count is NOT greater then 1, then it simply returns Null....therefore, even if there is a test date, for that ID, you get a null field. (You can change that to return whatever you want of course....). If it IS greater then 1, then it uses another subquery, to get the Max test date that has the same ID and Inspection Date as your top level subquery. Thus, the top level subquery returns to the primary query with either Null, or the appropriate Test date value.

    You will want the ID field to be indexed in both tables, and you will want both the Inspection Date and Test Date fields to be indexed in the second table. Without indexes, a lot of data may take a while to run. With indexes, this process should run fairly quickly.

    Hope this all makes sense. Have fun! <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    I appreciate your help and your method definitely does what I've set out to do. As you explained the solution, I understood. But, looking at the code I get a little confused, by no fault of our own. Would the query become too complicated if criteria needs to be added, for example, where city = Bloomington and where would I stick that in the SQL?

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    SELECT T1.Permit_Id, Table1.Est_Name, Table1.Est_Address, Table1.Est_City, Table1.Est_State, Table1.Est_Zip, Max(T1.Inspection_Date) AS MaxOfInspection_Date, IIf(Count([Inspection_Date])>1,(SELECT Max(T2.Test_Date) AS MaxOfTest_Date FROM Table2 AS T2 WHERE (((T2.Inspection_Date)=(SELECT Max(Table2.Inspection_Date) AS MaxOfInspection_Date FROM Table2 WHERE (((Table2.Permit_Id)=T2.Permit_ID))[img]/forums/images/smilies/wink.gif[/img]) AND ((T2.Permit_Id)=T1.Permit_Id))[img]/forums/images/smilies/wink.gif[/img],Null) AS TestDate
    FROM Table2 AS T1 INNER JOIN Table1 ON T1.Permit_Id = Table1.Permit_Id
    GROUP BY T1.Permit_Id, Table1.Est_Name, Table1.Est_Address, Table1.Est_City, Table1.Est_State, Table1.Est_Zip
    HAVING (((Table1.Est_City)="Bloomington"));

    Or, in design view, just put the criteria beneath the appropriate field. You could put criteria in the subquery, but I believe having it in the main query, prevents the subquery from running on those records. (I'm guessing there....)

  9. #9
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Thanks a billion. You're awesome.

  10. #10
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Sorry to bother you, but I have some other fields that I added to the query and it is producing duplicates again. Is there something that I did wrong? I am attaching the Sample.mdb that you sent me with two new sample fields in them. If you're still able to help, I would appreciate it. Thanks.
    Attached Files Attached Files

  11. #11
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Okay, I am attaching the sample, with a new query. That query should do what you are asking to do. Adding more fields, into that particular query, is going to 'groupby' those new fields, and thus give you multiple records. From the Table1, that's not a problem, because there is only one record a peice in there. However, From Table2, where there are multiple records per ID in Table1, you can't just 'toss' fields into that query, because you will get duplicates.

    I have added the two fields you added. To get the query to return the 'correct' information for those fields (and I am assuming you want to get the values from the record with the correct Inspection and Test date), I had to use some subquerries to return those fields. The good news is, you can copy those fields. (I actually only did the 'GTrap' field. Then I copied that whole thing into a new field, gave it a new alias, then I replaced the two spots that had 'GTrap' with 'DisposalSite'. You can use the same process for addition fields down the road.

    I have NO IDEA how well this thing is going to perform under a heavy record load though! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  12. #12
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Hey, the database didn't attach to the message. It must not have went through when you sent it. Can you please send it again?

  13. #13
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    No, just a brain fart, I forgot to attach it! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Here it is. Sorry about that.
    Attached Files Attached Files

  14. #14
    New Lounger
    Join Date
    Mar 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    Sorry that I haven't responded to this lately, but I've been consumed with other projects. I just wanted to thank you for your help. Your solution worked well for me. You're the greatest! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  15. #15
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates on Report dilemma (Access 97)

    You're quite welcome. Glad to help. Just pay it forward! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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