W
Widge
I've got a table. Its full of rebate percentages. I then go and apply
these percentages to a table full of invoice data and amounts to
calculate rebates to raise for a given month per supplier.
This is fine, but what I'm attempting to do now is have a historical
trail of rebate rate changes throughout time. I want to run the query
and have it pull off the rebate for the appropriate date that the
query is running for. I can't figure for the life how I can set this
up.
I've tried entering loads of criteria, but it insists on pulling off
all the rebates on the table and gives a result for each one,
regardless of the date attached to them. I've tried entering in IIf
statement, but all I can do with that is isolate results greater than
or less than a certain date, not necessary the relevant one for that
period.
What I'm hoping to have is a rebate table which is:
Suppier 1% 01/01/2007
Suppier 3% 01/06/2007
Suppier 2.5% 01/01/2008
and if I run my calc where the data is to be pulled off for the month
of 2007-08, then I want the 3% rebate used.
For download here is my database. Inside are two table and query set,
one set which is running of my current spec where I don't use any
historical records.... runs fine. One where it is running using
historical entries for SuppB (as an example), as you can see when
running the query, 3 lines for SuppB show. I need the relevant one to
display only for Supp B.
Also I have to make sure the likes of Supp A are not affected.
Thanks for any help!
Database:
http://www.sendspace.com/file/nxjko3
these percentages to a table full of invoice data and amounts to
calculate rebates to raise for a given month per supplier.
This is fine, but what I'm attempting to do now is have a historical
trail of rebate rate changes throughout time. I want to run the query
and have it pull off the rebate for the appropriate date that the
query is running for. I can't figure for the life how I can set this
up.
I've tried entering loads of criteria, but it insists on pulling off
all the rebates on the table and gives a result for each one,
regardless of the date attached to them. I've tried entering in IIf
statement, but all I can do with that is isolate results greater than
or less than a certain date, not necessary the relevant one for that
period.
What I'm hoping to have is a rebate table which is:
Suppier 1% 01/01/2007
Suppier 3% 01/06/2007
Suppier 2.5% 01/01/2008
and if I run my calc where the data is to be pulled off for the month
of 2007-08, then I want the 3% rebate used.
For download here is my database. Inside are two table and query set,
one set which is running of my current spec where I don't use any
historical records.... runs fine. One where it is running using
historical entries for SuppB (as an example), as you can see when
running the query, 3 lines for SuppB show. I need the relevant one to
display only for Supp B.
Also I have to make sure the likes of Supp A are not affected.
Thanks for any help!
Database:
http://www.sendspace.com/file/nxjko3