Query that can truncate date data a certain number of days ago

[MacG]

Hi all - I am a very recently self/google-taught Access user. To that end, I am creating a rather comprehensive database to track multiple aspects in an organization with over 300 people. One thing I am currently having a problem figuring out is how to track the time people have spent on business trips away from the main office.



I have a table that contains multiple (upwards of 30) Start/End Dates for trips and a calculation to show the number of days each trip. However, I need to have a report that shows a percentage of how long people were gone over the last 365 days (1 year) and also the last 730 days (2 years). This would be a rolling query based on Date() or Now() and presented in a percentage (##days)/365 or (##days)/730. The issue is I am not sure how to account for the times when the 365 or 730 date would fall in the middle of a prior trip. I keep a counter on the form to show the cutoff dates using this: =DateAdd("d",-365,Date()) or =DateAdd("d",-730,Date()) for my reference...it really serves no purpose otherwise...but I feel like it could play a role in this query.

For example, from the date I am writing this, 365 days ago would be 01 Nov 2018 and 730 days ago would be 01 Nov 2017. If a person was gone for a trip from 01 Aug 2018 through 15 Aug 2018 (15 days) and then gone again from 15 Oct 2018 through 01 Dec 2018 (47 days), but only 30 of those 62 days fall within the scope of the last 365 days. I can sort a formula/expression to get the average of about %16 for the 62 days, but don't now how to limit to a specific limiting date. Is there a way (that I am obviously oblivious to) to formulate a query which will truncate data at the 365 (or 730) day mark (which should provide about an %8 answer)?


Thanks!

[orange]

Mac,

Welcome to the forum.
I suggest you start with a description of the "business process" you are trying to automate/report. You might also take some sample data - a few records that can be used for testing any "algorithms"/calculations you consider.
I recommend that you work through 1 or 2 tutorials from RogersAccessLibrary mentioned in this link to assist in designing your database. Working through the tutorial will lead you to a normalized database design; and the process can be used with your own database. When you have the logical design, you have the blueprint to build your physical database.

Good luck with your project.

[Micron]

I thought I'd second the notion because

I have a table that contains multiple (upwards of 30) Start/End Dates for trips and a calculation

raises two red flags:
- you are designing tables like spreadsheets (wide) instead of 'tall' as they should be
- you have calculated fields in tables (not good)
Don't put the cart before the horse - i.e. make sure you understand database design principles & naming conventions and be sure you are aware of the pitfalls (calculated fields in tables, lookup fields in tables, etc.).

In the end, you'll probably need a custom function to pull out the part of a phase (e.g. trip) that begins in one period but ends in another. This function might make use of the DateDiff function, where you calculate the difference between a start date and the end of a period rather than using the end date of the phase. Conversely, you'd probably need to do the reverse if you want to capture the remainder in a search that begins in the next period

[MacG]

Thank you for the link to the training items...While I have been reviewing all I find, it is always good to have more, especially if they are recommended by someone who knows what they are talking about!

[MacG]

Micron - I have already run into issues with the calculation in tables. I corrected that and now all my calculations are done through a query based on the output I want. I will have to read up more on the tall/wide idea. I have just been putting in the fields and the program makes them into columns. The "30" trips is just because I have been unable to find a way to create an alternate way to log all the trips. Our max trip takers have only ever hit about 25 in a 3 year period. I just added the extra 5 to give a buffer. Thank you for the info...and verifying that calculations in tables actually isn't a good thing. I thought that was just something I discovered by accident. haha.

[orange]

I'd like to see some of the records you are dealing with --make up names if you must anonymize the data.

I see a table TblBusinessTrip
with fields along

TripID
PersonId
TripStart
TripEnd

you may have more details (but this would seem to be the essence).

There would be some calculations required to determine if the Trip was "in progress" at the time of your cut off date. I take it that in this case you measure from your cutoff date or some other rule.

It also seems your percentage numbers are determined by (untested and thinking as I type)

(BusinessTripDays in period/BusinessDays in Period) * 100

Some complications perhaps if multiple people go on same business trip, you may have to get unique PersonBusinessTrip data.

I do not see an issue with someone taking 40 BusinessTrips or 3 BusinessTrips in 1 or over many BusinessYears. A simple table structure should accommodate any number of BusinessTrips by a Person. You should not have Trip1, Trip2...Trip30 fields in your table.

Just some thoughts for consideration.

[ssanfu]

It goes without saying (but I'm going to say it anyway) that I am in agreement with orange and Micron. I've been thinking about the problem for a day and here is my 2 cents.....

My first inclination is to write a UDF to search through the records, but I decided to try to do this in a query.

What I think the steps should be -
The first problem/step is the be able to select only the records that fall between the date and the date-365.
The second problem/step is to do the calculation to get the amount of leave days. So, if the dates are Oct 15 - Nov 1, how many days should be returned? I think my calcs might be a day shy.
The third problem/step would be to calc the percentages..

Step 1 and 2 :
Using a table structure similar to what orange proposed, I threw together this dB. The date of Nov 1 (as in the OP first post) and the employee PK are hard coded in the query (for now - easier to get the calcs correct) .


Is this close??

[MacG]

Steve - Thanks for the mock up...I like how you did some of it and I may try (if I can sort it in my head) to use some of it in my final version. I have not yet figured out the pop-up dialogue entry boxes since the switchboard and using the split forms for data entry seemed to be sufficient for me.

All - Attached is my very edited down DB with sample entries for your review. It took me a while to get it working again once I ripped out all the stuff with actual name data...it broke more than one thing. But it is working in this basic format.

Again, my concern is if a previous trip lands in the middle of the cut off date of 365 or 730 days ago. In my sample, I made the "Test 5 Entry" (Key - 1324TE) have this very problem. The first trip overlaps the 730 day mark and the third trip overlaps the 365 day mark. This means that only 27 of the 61 days in trip one should get counted in the query to figure out the 2-year tempo. And only 28 days of the 62 in trip 3 should be counted for the 1-year tempo calculation. This is where I am getting stuck on the "date maths" in limiting the query in order to not count the full trip if/when it falls in the midst of the cut off.

I'm sure you are all smart enough to see the query formulas - but I will mention that the 100% mark is not 365/365 or 730/730. It is actually 220/365 and 400/730. The intent is to not let anyone be gone longer than 220 days out of 365 and also not more than 400 days out of 730.

I did have a thought of how to accomplish this....and I am still trying to sort the formula...but please tell me if any of you think this is a viable option. Create a query with a multiple condiction IIF to see if the dates entered are older than Date()-365 or 730 and if the end of the trip is not, but the start of the trip is, then use the layered IIF to replace the start date with Date()-365or730 as the "new" start date. This might be a very convoluted way to get thigns done and the statement could get squirrely but it seems doable to me (again still in "thinking mode" on this one). I give Micron credit for sparking this thinking in his post about needing to create a custom function... It just took me a while to wrap my head around it.

Thanks for all your continued assistance!!!

[MacG]

I have been typing out my query I wanted to try and use in the query for the date formula. In qryTripTempoAll in the 11th column where it currently figures out the days of the trip it has the following:
1 -- Days of Trip: IIf(IsNull([1 - Date Departed for Trip]),0,(IIf(IsNull([1 - Actual return date from Trip]),DateDiff("d",[1 - Date Departed for Trip],[1 - Anticipated return date from Trip]),DateDiff("d",[1 - Date Departed for Trip],[1 - Actual return date from Trip]))))




I attempted the following to try to limit the DateDiff to only within the current 365 days.


1 -- Days of Trip: IIf(IsNull([1 - Date Departed for Trip]),0,(IIf(IsNull([1 - Actual return date from Trip]),(IIF([1 - Date Departed for Trip]>(Date()-365)),DateDiff("d",[1 - Date Departed for Trip],[1 - Anticipated return date from Trip]),DateDiff("d",(Date()-365),[1 - Anticipated return date from Trip]),(IIF([1 - Date Departed for Trip]>(Date()-365)),DateDiff("d",[1 - Date Departed for Trip],[1 - Actual return date from Trip]),DateDiff("d",(Date()-365),[1 - Actual return date from Trip]))))


However, I get an error about wrong number of arguments. I haven't been able to sort it myself...maybe I'm staring at it too much. I can't seem to locate the issue. Or maybe this is too complex of an expression...?

[orange]

Please tell us about tblTripTempo. My first thought is that your table structure is an issue. I set out some broad parameters in post #6 that would seem to address your requirement.
Where does the source data originate?

[MacG]

Orange - For the tblTripTempo I simply make a table to contain the data based on each person's trips over their years at this location. It consists of the following:
name fields
a comment field
a field for each the 1-year and 2-years Trip Tempo percentages
and the rest of the fields are consisting of groups of 4 fields (date departed, anticipated return, actual return, total days of trip) for up to 30 trips.

The data for most fields is input by the person tracking everything (currently me since it isn't being used actively yet).

The only fields that are calculated are the percentages and the last of the 4-field groups. The query qryTripTempoAll contains the expressions which fill in the "# - days of trip" for each of the trips and also figures out the percentages for the Trip Tempos for 1- and 2-year intervals.

Everything seems to work...except that I cannot sort how to make the date for 365 or 730 days ago be the limiting factor. My attempt in post #9 is the closest I can sort to try and force a check on the start date for each trip and if it is older thna 365 (in that case) then it "should" replace the start date with date()-365 for the formula. But it won't work with the error I get for a wrong number of arguements. I'm willing to bet, provided the expression is correct, it is just a misplaced or missing punctuation of some type and I am just not seeing it.

[orange]

???Perhaps, but I think a structure similar to what Steve (ssanfu) provided is closer to what will make recording and calculations much easier.

[MacG]

Orange - I am sure that you and Steve are likely correct. I will have to reverse engineer the one Steve posted in order to figure out the inner workings. It will likely take a few days to sort it... I will post again when I have either sorted it or failed in my efforts. I really appreciate your guys' time and also that you are guiding me to success rather than "doing" it for me. This way helps me learn!

[orange]

Review any of the tutorials from RogersAccessLibrary mentioned in this link.
Take one and work through it (about 30-45 minutes) and you'll experience/learn a process to design a database.
You can use the same process with your own database to get the tables and relationships set up. You have to work through the tutorial, but you will learn. Once you get the design and tested it, you can then build the database. Getting the tables set up and normalized is the critical part. Don't try and solve everything at once with a physical Access database.

Break down your requirement into meaningful facts. Use pencil and paper. Make up some test data and make sure you can get the data you need from your model. Adjust as necessary. See this for ideas.

Good luck.

[ssanfu]

@Mac
I've been sitting here looking at your dB and, with the current design, I would say that you are going to have a struggle with your dB.
I am not trying to burn you, but there are a number of things that need to be fixed - otherwise you will be creating a workaround to "fix" something and will have to create a workaround to fix the 1st workaround. (been there, done that)

So to following are a few of the things I saw:

- With regards to tables "tblQuals" and "tblTripTempo", the table's design is like an Excel spreadsheet - short and wide - a RDBS is typically narrow and tall. This happens so often that it has the name "committing Spreadsheet". One of the things that indicates a "spreadsheet design" is that you have repeating fields. (Date Qual #1 Due, Date Qual #2 Due, Date Qual #3 Due)

- You have a 1-to-1 relationship between tables "tblRoster" and "tblQuals". 1-to-1 relationships are VERY, VERY rare (should be 1-to-many). Did you see that you have 2 "Personnel ID" values (1324TE and 1357TE) in table "tblTripTempo", that are not in table "tblRoster"?
- The PK field is a Text type. IMHO, it should be an Autonumber. See Microsoft Access Tables: Primary Key Tips and Techniques and Autonumbers--What they are NOT and What They Are. Virtually every table I create has an autonumber type field as the PK
- You have spaces in object names (bad) (Use only letters and numbers (exception is the underscore) for object names.)
- You began field names with a number (bad) (Do not begin an object name with a number)
- You have used punctuation or special characters in object names (bad)


I would suggest the first thing is to get the table structures correct.


BTW, in designing my first 3 databases, I "commited Spreadsheet". To my question "Why am I having so many problems getting my dB to work the way I want it to", a friend that programmed for a bank replied (to my face) "You have really bad table designs". (ouch!) Needless to say I, started researching how to design relational database tables.



I'm going to play around with your dB..........