Date dependent element of query. (1 Viewer)

[Christopherusly]

SOLVED - Date dependent element of query.

This is probably a stupid question, but having searched the forum(s) and scoured my Access book i am damned if i can find an answer, so i turn to you good people for a hand.

What i think i need is some sort of IF statement, let me explain.

I have a query which is run from three tables

- tbl_finance
- tbl_graderates
- tbl_BI week

tbl grade rates has rates for 2009 and for 2010 which are used in calculating the cost of time spent on a job by any member of staff. Rather than having two separate databases for the different hourly costs, i want to have a single database which will look at the date of the record and determine which rate to use

i.e. between 01.04.2009 and 31.03.2010 USE 2009 RATE
between 01.04.2010 and 31.03.2011 USE 2010 RATE

So when i run the query it will look at the date of the record and use the correct rate and enable me to have some jobs that straddle the financial year AND use both 2009 AND 2010 rates.

HELP ? - please see attached sample database if i have not explained myself clearly enough please shoot with the questions.

THANKS GUYS

 

[Trevor G]

What you need is to build on the example below:

Expr1: IIf([Week] Between #01/04/2009# And #31/03/2010#,"2009 Rate","2010 Rate")

Note the date is wrapped around the hash (#) sign. after the first comma you can select your field and after the second comma select your other field.

You may want to ensure that 2008 data isn't shown.

 

[CraigDolphin]

Trevor's suggestion will work but I'm not sure I would take the same approach myself.

Let me paint a scenario to demonstrate the issue I see with it.

You implement the suggestion and everything goes swimmingly and the database is completed and put into production. However, the bean counters decide to change the rate to be applied again in 2011. And then again in 2012. And again in 2014.

Now your calculated field iif statement won't work and each time there's a change on the rate you'll need to remember to go back into the database and change the relevant query field calculation to allow for these new rates. NIf you go this route you will need to use either nested iif statements or (better yet) a switch statement.

Well, that's all well and good, but (sad to say) you get run over by a bus in 2011. Now, who's going to maintain this database? Your database legacy is in imminent threat of being useless if there's no one else in your org who knows what to do and remembers to do it before using the data produced by your now-inadequate iif statement logic.

A better approach (IMO) is to have a table that stores dates and rates to be used by your query. This table can be accessed by users via a form that you can setup for that task, and new rates etc can be entered as the need arises.

tblRates
RateID (auto, pk)
RateStartingDate
Rate_Value

Now, instead of using the iif approach to your calculated field, you'd use something like:

Expr1: Nz(Dlookup("Rate_Value","tblRates","[RateStartingDate]=#" & DMax("[RateStartingDate]","tblRates","[RateStartingDate]<=#" & [Week] & "#") & "#"),999)

Essentially, this equation looks up the rate where the starting date is equal to the highest starting date which is less than (or equal to) the date in the Week field. If no mathcing rate is found, then it applies a default rate (999 in this case)

Now, the value 999 in the code should be replaced with your actual default rate to use if there is no rate in the table applicable to the date in the [Week] field. (For example, this could occur if there was a date in the week field that is earlier than the starting date for the earliest rate in the rate table. You'll likely also need to trap for nulls in the [Week] field.

If you take this approach, then users can update the rate to be used through your form, and the calculated field need never be changed even if the rates change 100 times during the lifespan of the database. And if you get hit by a bus, your company can keep using your memorial database without skipping a beat.

Not that you'll care at that point I guess

Hope it helps.

 

[namliam]

How about using dateadd function to "jump back" 3 months in time... then use the year to see what rates to use?

Aircode:
Year(dateadd("YYYY",-1, Yourdate))

 

[boblarson]

Short post to just say I agree with Craig's assessment that you should NOT have a separate table for each year. That is not normalized and requires much more maintenance than if you set it up correctly.

 

[Trevor G]

I strongly agree with Craigs suggestion,

I responded to the question asked.

But would recommend using Craigs scenario and instruction.

 

[Christopherusly]

Oohhh quite a lot to think about here thank you guys for taking the time to reply. it is hugely appreciated.

 

[CraigDolphin]

Happy to help

 

[Christopherusly]

A better approach (IMO) is to have a table that stores dates and rates to be used by your query. This table can be accessed by users via a form that you can setup for that task, and new rates etc can be entered as the need arises.

tblRates
RateID (auto, pk)
RateStartingDate
Rate_Value

Now, instead of using the iif approach to your calculated field, you'd use something like:

Expr1: Nz(Dlookup("Rate_Value","tblRates","[RateStartingDate]=#" & DMax("[RateStartingDate]","tblRates","[RateStartingDate]<=#" & [Week] & "#") & "#"),999)

Essentially, this equation looks up the rate where the starting date is equal to the highest starting date which is less than (or equal to) the date in the Week field. If no mathcing rate is found, then it applies a default rate (999 in this case)

The only issue i see with this approach is that for a table - tblrates

RateID (auto, pk)
+ StaffMember
RateStartingDate
Rate_Value

I would have to repeat the list of staff each year when adding new rates, potentially there are up to 400 members of staff that i would need to add into the table each time - i also have some reservation about how this would work with my set up where i have a relationship linking username from tbl_finance and tbl_graderates

if i went with TrevorG's suggestion, whilst i would have to update the IIF statement each time the rates change (thats not a bad thing) what is the correct syntax to include more than one date range?

Thanks you guys

Christophe

 

[CraigDolphin]

Er...what? Where did this StaffMember field come into play? I don't see it referenced in your OP or in the If statement that TrevorG provided.

You would only include the staffmember field in your rates table if the rate depends on two things: the date and the specific staff member. And if that is so, then the if statement is even more problematic because you'll need to add in another clause to check for staff member as well as for date (which is all that TrevorG's example checks for). If the rate depends only on date and not staffmember then I question why you would add that field to the rates table at all?

I think you need to provide a bit more information about your intention, and likely provide us with a jpg of your table/relationships, or a sample database showing your data structure.

 

[The_Doc_Man]

It is possible to use a non-standard JOIN here.

Normally, you have

SELECT A.X, A.Y.A.Z, B.R,B.S,B.T FROM A INNER JOIN B ON A.W = B.Q ...;

But you can do a join on date fields and it is legal (but you cannot do this any other way than by editing the SQL).

SELECT A.XDate, A...., B.XRate, ...., FROM A INNER JOIN B ON A.XDATE BETWEEN B.StartDate AND B.EndDate ....

If the above is too much trouble, you can just do the Cartesian-product version and use a WHERE A.XDATE BETWEEN B.StartDate and B.EndDate .... to filter it. (WARNING - this will probably take longer to run because it has to do the Cartesian product first.)

Then you have a single-record entry that shows the right date automatically.

Once you have a query with your rate information and the other stuff you need, a simple formula in another column of the query gives you the rate-adjusted amount you need.

Another bizarre idea: Add an index to a given rate-table entry, perhaps nothing more than a PK by autonumber. Then write a VBA function that looks up the correct index for the date given it and stores that in the raw data record. Then JOIN the individual record to the rate-table record by using the index as a foreign key to the rate-table. Simple many/one case.

 

[Christopherusly]

See attached sample database (same as original post) which contains:

tbl_BIWeek

-Week
-BIWeek
-Billing Period

tbl_BIWeek comprises of the actual week ending dates and then the SAP key weeks to match

tbl_finance - this is the record of the transactions for each job

- Project Manager
- Field 2 (ignore this one)
- Job No
- Job Title
- Network activity
- Work Stage
- Employee Number
- Staff Member
- Amey fiscal Week
- HR
- £
- £/Hr

tbl_graderates

- Staff Member
- Grade
- 2009
- 2010

the query i am running -qry_costcalc

consists of

-Job No
-Job Title
-Network Activity
-Work Stage
-Staff member
-HR

from tbl_finance &

2009 from tbl_graderates

I then have a calculated field - Cost:

---

*[2009]

What i would like to have is an expression, function, statement, or something that i do not quite understand to do the following:

- look firstly at tbl_graderates and identify the person the rate is required for
- then look at the fiscal week of the record to determine the rate to be used
- return the rate as a field within the query for use within the expression to calculate cost

note: i would expect to use at least 2 different rates on jobs that span the financial year, and in some cases with long running jobs, 3 different rates.

Sorry i was not clearer in my original post, i appreciate all time you guys have taken to make your suggestions.

Christophe

 

[CraigDolphin]

Hmm. Well, not to be a jerk, but your table design is about as non-normalized as I've ever seen. Repeating information within fields, between fields, lack of primary keys, etc.

Relational databases do not function remotely well if they're not normalized. The way things stand right now, you're going to be pushing liquid feces up a steep hill with a straw to make this structure do anything that you would want to depend on (and billing is not something I would want to have the slightest doubt about for reliability).

A normalized database design models the real world system. There should be tables to represent each of your real world 'entities'.

For example: you should have a table of 'StaffMembers', a table of 'Jobs', A table of 'JobStages', A table of 'JobGrades' etc. You will likely need a table for Billing and perhaps BillingDetails as well.

Then, depending on the realworld situation, there may be the need for junction tables wherever there is a many to many situation between two of these entity tables.

And for the issue of rates....it looks to me as though the rate to be charged depends on the year and the JobGrade (?).

So what you need is a table to store this information

tblJobGradeRateHistory
AnnualJobGradeRatesID (auto, pk)
JobGradeID (fk)
RateStartingDate (int)
Rate (single)

then your calculated field would look something like:

Expr1: Nz(Dlookup("Rate_Value","tblJobGradeRateHistory","[JobGradeID]=" & Nz(JobGradeID,0) & " AND [RateStartingDate]=#" & DMax("[RateStartingDate]","tblJobGradeRateHistory","[JobGradeID]=" & Nz(JobGradeID,0) & " AND [RateStartingDate]<=#" & [Week] & "#") & "#"),999)

Which probably won't actually be of much use to you unless you fix the problems with your table design. And that's a bit beyond the scope of what I'm going to be able to provide you with in this thread. If this database is to be used for your billing etc for your business, I would strongly suggest you think about looking at off the shelf small business software.

If you really want to do it yourself then you're going to have a lot of reading and pondering to do. I would suggest reading up on table normalization. There's a lot of information on the web (google, this forum, etc) that will help you get started. As a starting point, there's loads of links from this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=100211&highlight=tutorial+normalization)

And lots of the gurus here love to help others get their table structure figured out if you have specific issues/questions. But it's going to take a lot of time, and a lot of back and forth, to get there.

Sorry I can't offer more positive feedback. I am also a biologist so I am somewhat lacking in experience designing small business databases that handle tasks like billing etc. If I were you I'd FIRST do a lot of reading on table normalization and trying to apply what you learn to your situation. THEN I'd create a separate thread in the tables subforum asking for guidance on table structure and normalization where you explain the fundamentals of your business operation, billing etc, what tasks you hope to achieve with your database, and what you've managed to come up with by yourself. Then hopefully someone who has been-there-done-that with this type of database will be able to provide you some guidance that will avoid the major pitfalls.

 

[Christopherusly]

What you need is to build on the example below:

Expr1: IIf([Week] Between #01/04/2009# And #31/03/2010#,"2009 Rate","2010 Rate")

Note the date is wrapped around the hash (#) sign. after the first comma you can select your field and after the second comma select your other field.

So with some minor tweaks of TrevorGs suggestion to:

Rate: IIf([Week] Between #01/04/2009# And #31/03/2010#,[2009],[2010])

I now have a working IIF statement in my database that quite correctly evaluates the date of the record and returns the correct rate. Thanks Trevor G !

 

[namliam]

But what of 2008, 2007 or 2011, 2012??

How about using dateadd function to "jump back" 3 months in time... then use the year to see what rates to use?

Aircode:
Year(dateadd("YYYY",-1, Yourdate))

Will do it all

 

[Christopherusly]

If i am honest, i am not sure i follow the logic in your approach ... would you be able to explain further ? Cheers namliam.

To evaluate dates pre 2009 and post 2010 i was going to look at a nested IIF statement, just trying to get my head round the syntax at the moment.

Christophe

 

[namliam]

The logic is this, .... -- I see I made a small mistake in my provided code working with YYYY instead of M for some reason --

Anyhow your (financial? or booking?) year runs from Apr 1 - Mar 31, while a real year runs from Jan 1 to Dec 31.

By taking away 3 months, you basicaly are reverting the Apr 1 to Jan 1 and the Mar 31 to Dec 31.

Year(Dateadd("M",-3 , Yourdate))

ie. 31/03/2010 will become 31/12/2009 ==> 2009
ie. 15/06/2010 will become 15/03/2010 ==> 2010
ie. 15/02/2011 will become 15/11/2010 ==> 2010

Its magic !