Date/Time relationships

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

Hi there:

I am trying to link two tables using date as the common field.

Table1 contains a chonology of approved commission rates, and thier
effective dates, for the past 10 years. This consists of 35 records. These
commisson rates are common to all salespersons, so it makes sense to have
them in a separate table.

Table2 contains a record of weekly sales by salesperson, and the sales
dollars for the date (representing the end of the week).

I want to create a relationship between these two tables such that table2
looks up table1 to determine the correct commission rate based on the "on or
after" effective date of the approved commission rate. An exact match, of
course, will not work.

I know that this kind of thing can be done in Excel, but I'm stumped as to
how to structure this in Access. Doing it in Excel is not an option, because
my database is much more complex than what I've summarized here.

Thanks to anyone who can help.
 
Bob,

In order to create a relationship between two tables, the values that you
use to link MUST be unique on at least one side of the relationship so what
you are trying to do will not work.

You are right to have the commissions in a separate table. There are a
couple of approaches on how to get the correct commission for the
salesperson depending on the details of your situation.

Are all of the salespeople getting the same commission or are some of them
on different schedules? Is it a flat % or a sliding scale? I think that we
would need a few more details to suggest how to best look the correct rates
up.

Gary Miller
 
One approach would be to create an intermediate query that links each weekly
sales date in your Table2 with the appropriate effective data from your
Table1.

Something like...
SELECT Table2.Week, Max(Table1.EffectiveDate) AS CommissionDate
FROM Table2, Table1
WHERE Table1.EffectiveDate <= Table2.Week
GROUP BY Table2.Week;

The WHERE clause causes all effective dates less than or equal to each week
to be returned, what you want is the biggest of these effectivedates for
each week, hence the Max, and the grouping by week. The query should return
the Max effective date (I've called it CommissionDate) for each week in
Table2 (Well that's the idea, I haven't tested it!).

You could then use this query as part of another query with joins
(relationships) linking the two tables via this query, and presumably
multipling the weekly sales by the commission rate.

HTH
Sam
 
I want to create a relationship between these two tables such that table2
looks up table1 to determine the correct commission rate based on the "on or
after" effective date of the approved commission rate. An exact match, of
course, will not work.
Click to expand...

You'll need what's called a "Non Equi Join". You can't do it in the
query grid, though you can start there. Create a Query joining the
table2 date to table1 joining to the StartingDate (which as you say
won't work); view the query in SQL view and edit

ON Table1.datefield = Table2.Startingdate

to

ON Table1.datefield >= Table2.Startingdate AND Table1.datefield <
Table2.endingdate

You'll want to set the endingdate for the *current* commission rate to
12/31/9999 to ensure that it gets picked up.
 
John:

This looks like a promising solution. You clearly grasped the nature of my
challenge.

Apart from having to set up an ENDDATE field (ie: the day before the next
record's STARTDATE, no big deal), this looks like a simple, eloquent
solution.

Thanks so much for contributing your expertise.

Bob.
 
Back
Top