Simple Query Help 3

[nayfeh]

Hi,

I'm having a very hard time with this query I'm trying to setup. For some reason, the date field's value does not come up in this subquery.
I have a master table (tblTrailerMaster) that lists all trailers in our company. I also have tblTrailerWash which records each trailer #, wash date, and wash type. There can only be 3 types; INT, EXT, or BOTH.

I'm simply trying to list which trailers have NOT had a wash in 21 days. Also, I would like to include the Wash Date and Number of Days Since Last Wash for the units that have not had a wash. Can someone help me with this.

Thanks,
TN

 

[Torn39]

Is the Wash Date field a date/time field or is it a string field that uses some mask to look like a date...I'm hoping its a Date field, otherwise my little sample won't work...

Wouldn't it be something like

SELECT [TRAILER MASTER].Trailer, [TRAILER MASTER].WashType, max([TRAILER MASTER].WashDate)
FROM [TRAILER MASTER]
GROUP BY [TRAILER MASTER].Trailer
HAVING max([TRAILER MASTER].WashDate) < DATE() -21;

Honestly, using the max in conjunction with the &quot;having&quot; took me a minute to figure out. Note that I &quot;de-spaced&quot; your field names, just in case you &quot;cut and paste&quot;

Hope this helps...

Torn39

 

[benjamenus]

I'm working on the assumption that you only have 1 record in the wash table for each trailer in the trailer table (i.e. you do not keep a history of washes). If so then your SQL is:

SELECT Trailer.TrailerName, Washes.WashDate AS LastWash, DateDiff(&quot;y&quot;,[WashDate],Now()) AS DaysSince, Washes.WashType
FROM Trailer INNER JOIN Washes ON Trailer.TrailerID = Washes.TrailerID
WHERE (((Washes.WashDate)<Now()-21));

If you do keep a history of washes, then in design view use the sum function and select MAX for the WashDate so that you only get the most recent wash.

 

[Torn39]

PRINCET,

I messed up with my first post as I didn't read as thoroughly as I should have, with trailermaster and trailerwash existing as seperate tables.

With a footnote to benjamenus, look at this...

SELECT TRAILERMASTER.TRAILER, First(TRAILERWASH.WashType) AS WashType, Max(TRAILERWASH.WashDate) AS WashDate, FIRST(DateDiff(&quot;y&quot;,TRAILERWASH.WashDate,DATE())) AS DaysSince
FROM TRAILERMASTER LEFT OUTER JOIN TRAILERWASH ON TRAILERMASTER.TRAILER = TRAILERWASH.Trailer
GROUP BY TRAILERMASTER.TRAILER
HAVING MAX(TRAILERWASH.WASHDATE) IS NULL OR max([TRAILERWASH].WashDate)<DATE()-21;

Now, this is assuming you sort that &quot;Wash&quot; table by TRAILER, then by WASHDATE. This eliminates one little problem I was having with it...

It even shows the trailers that haven't been washed once, although there will be no values for the days since of course.

Hope this helps,

Torn

 

[nayfeh]

benjamenus,

It is a history table. For each trailer washed, I record date, trailer #, and wash type. WashDate is a Date/Time field and NOT a string.

Thanks,
TN

 

[nayfeh]

Torn39,

I tried your query. Unfortunately, I am getting incorrect values for the &quot;DaysSince&quot; field. Not sure why this is happening. It retrieves the correct records, and even the correct &quot;LastWashed&quot; value, but the caluclations for DaysSince are way off for some reason. I don't even see a pattern as to what it's calculating. For example, I figured Sept. 19 minus Aug 21 can't be 140 days

Again, here's my query:

SELECT [tblTRAILERMASTER].[TrailerNumber], [tblTRAILERWASH].[WashType] AS WashType, Max([tblTRAILERWASH].[WashDate]) AS WashDate, Last(DateDiff(&quot;y&quot;,[tblTRAILERWASH].[WashDate],Date())) AS DaysSince
FROM tblTRAILERMASTER LEFT JOIN tblTRAILERWASH ON [tblTRAILERMASTER].[TrailerNumber]=[tblTRAILERWASH].[TrailerNumber]
GROUP BY [tblTRAILERMASTER].[TrailerNumber], [tblTRAILERWASH].[WashType], [tblTRAILERMASTER].[Active]
HAVING (((tblTRAILERWASH.WashType)=&quot;Interior&quot; Or (tblTRAILERWASH.WashType)=&quot;Both&quot AND ((Max(tblTRAILERWASH.WashDate)) Is Null Or (Max(tblTRAILERWASH.WashDate))<Date()-21) AND ((tblTRAILERMASTER.Active)=True));

Thanks,
TN

 

[Torn39]

princeT,

Ughh...

I think the problem now lies in those other conditions that you are putting in the &quot;having&quot; part of the query. But try this first....

Change this part of the select statement...

Last(DateDiff(&quot;y&quot;,[tblTRAILERWASH].[WashDate],Date())) AS DaysSince

to First

This would compare today's date to the MOST RECENT wash, as opposed to the oldest wash in the database for that trailer.

Torn

 

[benjamenus]

Hi all

I've lost track - is this sorted yet? If not, how about this offering ...

QUERY1 (OutOfDate)

SELECT Trailer.TrailerName, Max(Washes.WashDate) AS LastWash, Washes.WashType
FROM Trailer INNER JOIN Washes ON Trailer.TrailerID = Washes.TrailerID
GROUP BY Trailer.TrailerName, Washes.WashType
HAVING (((Max(Washes.WashDate))<Now()-21));

QUERY2 (Based on OutOfDate)

SELECT OutOfDate.TrailerName, OutOfDate.LastWash, OutOfDate.WashType, DateDiff(&quot;y&quot;,[lastwash],Now()) AS DaysSince
FROM OutOfDate
ORDER BY DateDiff(&quot;y&quot;,[lastwash],Now()) DESC;

It seems that the problem occurred because you can calculate DateDiff when using Having and Max. All this does is gather the fields in one query and perform the calculation in the second.

Let me know if it helps

P.S. This is intended for a History of Washes table

 

[nayfeh]

Great, it's finally working!!! Thanks so much for you help guys.

TN