Report Setup 1

Hi,

Need a little help with this report I'm trying to setup. Shouldn't be too hard I have a table with 3 fields: TrailerNumber, WashDate, WashType. What I need is to setup a report that users can run on a weekly basis to determine which trailers have NOT has an interior wash in a rolling 4 week time frame.

The WashType field only has 3 values, Interior, Exterior, or Both.

I guess i need help with the underlying query. For example, if they were to run a report today (August 1, 2002), it would list all trailers that have not been washed since July 1, 2002.

Each week new records get added.

Any help is much appreciated.

TN

 

[JPeters]

In the query, make the date criteria:

>=Date()-30 And <=Date()

I think that's what it is. Someone can correct me if I'm wrong.
This will show results between 30 days ago and the current date.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[nayfeh]

Hi JPeters,

What I need is to show which TRAILERS have NOT been washed in 30 days. I can retrieve which ones have, but I'd like to flag the ones that haven't.

Thanks,
TN

 

[JPeters]

Okay...

Well, if you know which one's HAVE been washed, then all other's haven't right? How do you know which one's have been washed.. is there a checkbox or something that they check? If there is you could just look for records where this checkbox is unchecked (or false) and run a query based on that criteria. If you only know which have been washed by some sort of date criteria, just look for the opposite ... How is this setup?

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[nayfeh]

We enter each trailer that has been washed into a table. the table has 3 fields; Date, TrailerNumber, and WashType. So, for each trailer, a new record gets added.

WashType just means if it's Interior or Exterior wash.

 

[JPeters]

Okay, now where are the vehicles that haven't been washed stored? And how are they flagged as unwashed?

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[nayfeh]

The vehicles that haven't been washed aren't stored at all. I have a table (tblTrailerMaster) that lists all the trailers, but we don't enter any info for the ones NOT washed. Is it possible of using the TrailerMaster table to cross-reference the vehicles that aren't washed? Or do I have to record each vehicle, because that would take a very long time. There are usually more vehicles NOT washed than washed.

 

[TomCologne]

Storing the unwashed trailers would literally mean dirty data. Take tblTrailerMaster and tblTrailerWash, josh's date criteria and set WashType Criteria to &quot;Is Null&quot;. This is from the top of my head and it may need some adjustments, perhaps in the Join Properties. But it could be a start.

You're not alone

TomCologne

 

[JPeters]

princeT ...

Are all vehicles stored in a certain table - unwashed or not... And are all Vehicles that are washed added to the Washed Table once they are washed? Is there a common field in both tables like identicalID numbers? If there is, just make a query and add both tables to it. Now find these identical fields and click/drag/drop from one table to the other on the query design area - from table1 IDfield over to table2 IdField. This will setup a query relationship. There are 3 types, you can double click the black line that appears and set it up appropriately. You'll probably leave it at the default option - somtehing like &quot;Show All records from tablewashed and only those from tableallvehicles that have identical blah blha&quot;... so it shows all the vehciles that have been washed from both tables.

Now add a field to your tblAllVehicles and call it a Yes/NO field - put this in our query... this is how we're gonna flag the vehicles as washed. Make this new query an update query. We don't need any criteria since the relationship will be our criteria. Have it Update the Washed field to True. Now just run that query before you run your report from a button.

Add the new Washed field to the Query your report is going to be built off of. Setup the UnWashed Report's Query criteria to look for 'false' as the criteria in the washed field. This will show all vehicles that are unwashed.

2 more more things you'll have to do:
-Have an update query that makes the Washed Section check to false on vehicles after a certain date.
-You will also need an update query for your Washed Vehicles table to Clear records after a certain date.

This just makes sure that they aren't forever marked as washed. I'd have these queries run in the Autoexec macro so that the vehicles that need cleaning are always current.
IF you need help setting those up, let me know.

Phew. I'm really tired, so I hope that I made some sense. To start this process, add the washed field to your TblAllVehicles (whatever table has them all listed). I hope that you have this table and the matching ID numbers.. heh.. Otherwise I just typed a whole lot of nothing. ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[nayfeh]

Don't think that'll work. There will be no &quot;IS NULL&quot; values since I don't enter records for trailers not washed.

Like I said, is there a way of doing this without entering any records for trailers NOT washed?

Thanks guys!
TN

 

[JPeters]

TN,
It will work if you have a table that stores all the vehicles that your company owns, washed or not. You've got to have something like this or your Unwashed report won't have any data to build off of and this is a moot point. Did I explain the previous post decently enough?

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[nayfeh]

Great. Thanks for the advice Josh. Will let you know how I do

I'll setup a little spreadsheet like form so users can check off which trailers receive a wash. The problem is that there are three different types of washes (INTERIOR, EXTERIOR OR BOTH) so does that mean I need a different form for each type? Guess that would be the best.

TN

 

[JPeters]

Well, ... It's always up to you how you want to set it up. I guess I was basing my advice off of a single &quot;Washed or Not&quot; field. Since there are 3 different types - just elaborate on what I suggested. I'm just here to give you a nudge in the right direction. You may not do anything I suggest, Who knows. But maybe my advice triggered a better idea for you. ;-) Goodluck. Repost if you need more help.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[TomCologne]

Before you start adding fields or whatever, I just created two test tbls &quot;tblTrailerMaster&quot; and &quot;tblTrailerWash&quot;, Wash having TrailerMasterID as foreign key, entered 6 trailers in Master and gave No 1&4 a wash. The qry from left to right:
TrailerMasterID/tblTrailerWash||TrailerWashServ/tblTrailerWash - Criteria &quot;Is Null&quot;, Visible: No (=uncheck)-||TrailerMasterID/tblTrailerMaster||TrailerMasterDesc/tblTrailerMaster. The qry &quot;Join Properties&quot; set to return all records from &quot;tblTrailerMaster&quot; and the matching ones from &quot;tblTrailerWash&quot; and it came up with 4 dirty trailers, Nos 2,3,5,6. The relationship wasn't defined but in the qry and the Date filter was not applied, either.
You may want to give it a shot. A clean qry is much more convenient and much less work than adding flds and most of all, it's a tool that hardly ever will cause any harm, even action qrys
will make you click before they proceed, so if it doesn't work, try something else. Delete your &quot;Prototypes&quot; from time to time if you don't need them anymore and use the &quot;Compact db &quot; regularly. Let me know how it works, please.

TomCologne

 

[sko]

First find all trailers that have a wash type of &quot;Interior&quot; or &quot;Both&quot; where the date is between date() and date()-30

----------qryWashed--------------
SELECT DISTINCT tblWashed.TrailerNumber
FROM tblWashed
WHERE (((tblWashed.WashType)=&quot;Interior&quot; Or (tblWashed.WashType)=&quot;Both&quot AND ((tblWashed.WashDate) Between Date() And Date()-30));

Then create another query using tblTrailerMaster.TrailerNumber left-joined to qryWashed.TrailerNumber where qryWashed.TrailerNumber is null

---------qryNeedsInteriorWashed-------------

SELECT tblTrailerMaster.TrailerNumber, qryWashed.TrailerNumber
FROM tblTrailerMaster LEFT JOIN qryWashed ON tblTrailerMaster.TrailerNumber = qryWashed.TrailerNumber
WHERE (((qryWashed.TrailerNumber) Is Null));


Hope that helps

 

[rccline]

Is there a way to query for year? or month? Let's say I want to return a date query for those dates that include 1914? Or March 1914? ...Rather than the last 30 days as in the question posted above.

>=Date()-30 And <=Date()

Thank you.

Robert

 

[grnzbra]

If you add a calculated field using the datediff function, you can then use the criteria to select on the basis of number of days you want

The field in the query would look like:

DaySinceWash: =DateDiff(&quot;d&quot;,Date(),WashDate).

Set your criteria for >= however many days you want.