Query to show last records from and all date between records

[asoe209]

Hello I’m having some trouble trying to create a query that will show the last entry of and items location.
My main form (fleet) has a subform attached (PlantLocation), and within this form records are kept of that plans current location. As the move from place to place.
I need a query that will only show that plant that will need servicing for each month, which I have done with a date between but as a result It when the query is run it only shows plant that has a location with it.
I manage to get the query to show all plant for a given month so plant with an without locations records. But for plant that moves around often its showing all there movement address. Course doubles, triples etc. which I don’t need.
Can anyone help with getting the query to show everything that is due in a sent month with the date between, but also showing all plant but only there last record of movement.

[Dal Jeanis]

That can be done, but we'll need to know the structure of the tables.

Probably the easiest way is if you post the SQL for the query that's bringing you back the duplicate records.

Also, do you need the query to be updateable, or not? What fields might need to be updated?

[asoe209]

well the data will change on a daily basis with the address but the servicing dates are yearly.

ill have to uoload them tomorrow as the database is on my work computer. im new to access so u have to bear with me.
can I explain the table sturture u asked about if so what are yoy referring to.

[Dal Jeanis]

There's no way to talk you through it if you don't have the database in front of you. It's not worth trying to have you remember things that you're not totally sure about.

Basically, we're going to need to take your existing query, and add a subquery to it to select only the latest of the records being returned for each item. If you post the SQL, we'll be able to do that pretty quickly.

To get the SQL, you need to open the query in design view, then switch to SQL view. (There should be a dropdown at the top left of your screen to switch views) Copy all the words, and post them up here.

[asoe209]

ok ill have a got at posting that tomorrow morning thanks

[asoe209]

Moring, As promissed here is the SQL tex requested.

SELECT Fleet.FleetName, Fleet.FleetNumber, Fleet.[S/N], Fleet.NextLiftingTest, Fleet.HireStatus, PlantLocation.Address, PlantLocation.Area, PlantLocation.PostCode
FROM Fleet LEFT JOIN PlantLocation ON Fleet.FleetNumber = PlantLocation.FleetNumber
WHERE (((Fleet.NextLiftingTest) Between [Start Date] And [End Date]));

[Dal Jeanis]

Okay, you'll need to know the field in PlantLocation that tells the date of that location/move.
1) Copy your query to a test version,
2) Switch to SQL view,
3) Change the SQL to the following,
4) Replace "DateINeed" with the name of that date field, then
5) Switch back to data view to test the results.

SELECT 
  FL.FleetName, 
  FL.FleetNumber, 
  FL.[S/N], 
  FL.NextLiftingTest, 
  FL.HireStatus, 
  PL.Address, 
  PL.Area, 
  PL.PostCode
FROM Fleet AS FL
  LEFT JOIN 
     (  
     SELECT 
        PL.FleetNumber,
        MAX(PL.DateINeed) AS DateINeed, 
        PL.Address,  
        PL.Area, 
        PL.PostCode
     FROM 
        PlantLocation AS PL
     GROUP BY 
        PL.FleetNumber,
        PL.Address,  
        PL.Area, 
        PL.PostCode
     )
  ON FL.FleetNumber = PL.FleetNumber
WHERE (((Fleet.NextLiftingTest) Between [Start Date] And [End Date]));

What I've done is replace your PlantLocation table with a summary query on the same table that only gives the latest date for each FleetNumber. The left-join is left the same, so that you get a record for the FleetNumbers that have never had a PlantLocation record.

Let me know if there are any issues - like if DateINeed isn't on that particular table or something.

[asoe209]

many thanks will give that a try tomorrow. hopfully I can do it right.

[asoe209]

Hi I tryed what was suggested about and im getting this error.

"Syntax error in Join operation"

Wat I did was cope my m query.
opened the copy in SQL view.
Copied and pasted the code in above post
Chaned both dateineed to NextLiftingTest

when I try to switch view or run the query i get that error

[Dal Jeanis]

DateINeed can't be NextLiftingTest because that's in the wrong table. You said you were getting multiple PlantLocation records. You need to use the date of the Plantlocation record - whatever date field shows *when* the unit with that FleetNumber was in that PlantLocation. (If there's no date on that record, then post the layout of the PlantLocation record and tell me how to tell which one is the latest one.)

[asoe209]

sorry for delay in responding to this been super busy.

Sorry think its starting to go over me head now so I have attached the data base to see if any one can make more sencse of it that way.

The query this is relating to is named Copy of LiftinigTestDue. (top one in querys)

FMC_PLANT_MANAGMENT 29.07.13.zip

[Dal Jeanis]

The first thing that I noticed in reviewing your data is that it appears you have the same fleet item hired out to multiple places at the same time. That would be a data issue, not a code issue. See fleet number 1958 for an example.

You need to review how the data gets into the database and gets updated about the hire status.

1) If the offhire date has been entered in PlantLocation, then is the fleet item possibly still in that location, or is it presumed to be back at a yard somewhere?

2) It might simplify your life to add a yes/no field to PlantLocation that indicates there is a later record.

3) At any case, there needs to be a certain way that you define to know which of the dup records is the correct one. If not, then you need to define an acceptable way to decide which one to use.

4) This SQL code will get you one valid version of the query you request:

SELECT 
  FL.FleetName, 
  FL.FleetNumber, 
  FL.[S/N], 
  FL.NextLiftingTest, 
  FL.HireStatus, 
  PL2.Address, 
  PL2.Area, 
  PL2.PostCode
FROM 
  Fleet AS FL
  LEFT JOIN 
  (SELECT 
      PL.FleetNumber,
      PL.Address,  
      PL.Area, 
      PL.PostCode
   FROM 
      PlantLocation AS PL
   WHERE PL.ID IN 
      (SELECT First(tP1.ID)
       FROM PlantLocation as tP1
       GROUP BY 
          TP1.FleetNumber, 
          NZ(tP1.OffHireDate, NZ(tP1.OnHireDate, Date())) 
       HAVING 
          NZ(tP1.OffHireDate, NZ(tP1.OnHireDate, Date())) = 
             (SELECT Max(NZ(tP0.OffHireDate, NZ(tP0.OnHireDate, Date())))
              FROM PlantLocation AS tP0
              Where tP0.FleetNumber = tP1.FleetNumber)
      )
  ) AS PL2
ON FL.FleetNumber = PL2.FleetNumber;

It's not particularly pretty, and it's based on these assumptions:

NZ(tP0.OffHireDate, NZ(tP0.OnHireDate, Date()))

For selecting the appropriate PlantLocation record, If the offhiredate is populated, use that as its date. Otherwise, if the onhiredate is populated, then use that. Otherwise use the current date. You'll note that, since the offhire and onhire dates are always past dates, that will give priority to the records that have no dates filled out. If you want to reverse that priority, then change "Date()" to "0" or #01/01/2000# or something low/old, everywhere it appears.

I also made the assumption that, if the dates are the same between different plantlocation records, then any one of them will do. If instead you want the record with the lowest or highest ID, then you can change "First(tP1.ID)" to "Min(tP1.ID)" or "Max(tP1.ID)", respectively.

[asoe209]

Good morning yes I see what ou mean by lookin at 1958 ad where this may have course some confusion.

Really I need to change the titles of on hire and off hire, as what seems to happen is althrough a bit of plant is on hire, it can move around to other locations while still being o the same hire contract. really this should be a transfer date or move date.

as for which record to pull it will alway be the last one entered as that will alway be the corect one as thats the last move it has done so there for its curent location.

thre will be time to build and make bette but for now it ust needs to be in a workable state to get the data to track plat for servicing. i really apprechate the help.....

[Dal Jeanis]

"The last one entered", unfortunately, isn't detectable from what you have in the database. If you add such a field, then that SQL can get a lot simpler.

[asoe209]

i added the code that you posted above, which when i looked seemed to e working and only pulling in the last record. and not dups