SQL to add new table to query

[asoe209]

Good morning all, I had a thread going a few days ago on how to get my last record to show in a query which can be found here.

It was sorted by an SQL, I now need to add some extra details to the query from another table and I’m not quite sure what I need to put into the code to do that.

The details that need to come over are FirstName, Surname and Phone and the table name is SupervisiorDetails.
Here is the SQL that was provided to me which was great thanks again.

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;

[June7]

Use the Access query builder to help construct the SQL. Need to pull in the SupervisorDetails table and join it to primary table in the query. However, I don't see Supervisor ID field in the current query structure. What is the common field in both tables that they can be joined on?

[asoe209]

well the supervisior details is joined to plant location table in relationships

SupervisiorDetailsTeable (ID)
PlantLocation (supervisiorID)

[June7]

Okay, try it and if you need more help, let us know.

[asoe209]

I still have not been able to work this out?

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved in issue.

[asoe209]

Hi the db is atached, the querie to check is Copy Of newLiftingTest

many thanks for taking a look.

FMC_PLANT_MANAGMENT (2).zip

[June7]

Is this what you want:

SELECT Fleet.*, PlantLocation.Address, PlantLocation.Area, PlantLocation.PostCode, SupervisiorDetails.FirstName, SupervisiorDetails.Surname, SupervisiorDetails.Phone, SupervisiorDetails.Email
FROM SupervisiorDetails RIGHT JOIN (Fleet LEFT JOIN PlantLocation ON Fleet.FleetNumber = PlantLocation.FleetNumber) ON SupervisiorDetails.ID = PlantLocation.SupervisiorID
WHERE (((Fleet.LastLiftingTest) Between [Start Date] And [End Date]));

You have lookups set in tables, something I advise against, review: http://access.mvps.org/access/lookupfields.htm

I also don't like parameter input popups - too hard to validate input. Review:
http://datapigtechnologies.com/flash...tomfilter.html
http://datapigtechnologies.com/flash...earchform.html
http://datapigtechnologies.com/flash...mtoreport.html

[asoe209]

Hi sorry for the delay in responding, thanks for the SQL, that is close to what I need.
What you have done dose show the supervisor details on it which is what I needed adding. The only thing now is its pulling I some data that I don’t need. Well at least not for this one
Ideally I need the queries to be separate so for this one it only needs to show the next lifting test date.
So the results should be:
FleetName
FleetNumber
S/N
NextLiftingTest
Address
Area
PostCode
FirstName
Surname
Phone
So is there any way of changing in to show like that. What was there before worked just needed to and the extra details which I did not know how to do.
This same layout would be used for all queries just that lifting test would be changed to NextService or NextPMI these would also include service hours.
I have not had a chance to look at the links yet but ill have a good look at that when I get home, I never knew there were a problem with using parameters. I used it because we look at things by month.

[June7]

If you want supervisor info in your query, will have to bring in both PlantLocation and SupervisorDetails:

SELECT FL.FleetName, FL.FleetNumber, FL.[S/N], FL.NextLiftingTest, SupervisiorDetails.FirstName, SupervisiorDetails.Surname, SupervisiorDetails.Phone, SupervisiorDetails.Email, FL.HireStatus, PL2.Address, PL2.Area, PL2.PostCode
FROM SupervisiorDetails RIGHT JOIN ((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) LEFT JOIN PlantLocation ON FL.FleetNumber = PlantLocation.FleetNumber) ON SupervisiorDetails.ID = PlantLocation.SupervisiorID
WHERE (((FL.NextLiftingTest) Between [Start Date] And [End Date]));

Dynamic parameters in queries aren't the real issue, although I do avoid them and prefer applying filter to forms and reports with VBA, it is the popup inputs that I advise against.

[asoe209]

Thanks I shall give that a try tomorrow when I'm back in the office.

Right I see your point, as I'm new to access I'm still learning all this stuff, one day I will have a better understanding on how to build them and improve.

[asoe209]

Good morning, Just had a chance to look at that at that SQL, many thanks again it’s now working with supervisor details included which is great.

The one last thing that is not working, is its pulling all records from the plant location address again, where it only needs to pull the last location record, this being the record where the item currently is.

This is what the last SQL was made for to stop it doing that as it used to do the same thing before I was helped out on that. I have taken a look and can’t seem to see where or what would make it do that.

Any ideas on how to just get the last address to show.

[June7]

Do you know there is misspelling of field name SupervisiorID?

SELECT FL.FleetName, FL.FleetNumber, FL.[S/N], FL.NextLiftingTest, PL2.FirstName, PL2.Surname, PL2.Phone, PL2.Email, FL.HireStatus, PL2.Address, PL2.Area, PL2.PostCode FROM Fleet AS FL LEFT JOIN (SELECT PL.FleetNumber, PL.Address, PL.Area, PL.PostCode, FirstName, Surname, Phone, Email
FROM SupervisiorDetails RIGHT JOIN PlantLocation As PL ON SupervisiorDetails.ID = PL.SupervisiorID 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
WHERE (((FL.NextLiftingTest) Between [Start Date] And [End Date]));

[asoe209]

Thanks again for the code, it still does not seem to be forking for getting the last know address/ record or address. still seem to be choosing the 1st.

Oh and no I never noticed the spelling error.

any more ideas?

I may have to re think this DB.

[June7]

First post asked about including the supervisor info into your existing query. Think we've done that.

But the scope of your issue actually goes beyond the supervisor info? You want the most recent record for an item, try a nested subquery with TOP parameter, review http://allenbrowne.com/subquery-01.html#TopN

I see that no record has data in both OnHireDate and OffHireDate fields and that you use Nz to pull other date if one is blank. What do these fields represent?

SELECT WelfairUnitLocation.*, SupervisiorDetails.* FROM SupervisiorDetails RIGHT JOIN WelfairUnitLocation ON SupervisiorDetails.ID = WelfairUnitLocation.SupervisiorID WHERE WelfairUnitLocation.ID IN
(SELECT TOP 1 ID FROM WelfairUnitLocation AS Dupe WHERE Dupe.FleetNumber = WelfairUnitLocation.FleetNumber ORDER BY Nz(Dupe.OnHireDate,Dupe.OffHireDate) DESC, Dupe.FleetNumber) ORDER BY FleetNumber;

Those date fields could be combined then have another field (text or Yes/No) for type (On, Off). That would be true normalization. Or you can keep using Nz() every time you need to consider both fields in an evaluation.


Am curious, what is Welfair?