M
Michael
iMy goal is to generate a query or report that shows potential margin for an
item with a customer specific price base on either a standard cost or a
rebate cost.
I have 4 tables that contain the data but I am having difficulty designing
the correct queries and / or relationships to generate the correct output.
tbl_item_cost has 3 fields
unique ID (generated / auto-number)
item_num (text value for item number)
last_cost (standard cost)
tbl_rebate_cust has 3 fields
unique ID (generated / auto-number)
rebate_id (text value for rebate id)
cust_num (number value for each customer that is eligible for that rebate_id)
Customers can belong to more than one rebate_id and each rebate_id will have
multiple customers.
tbl_rebate_items has 4 fields
unique ID (generated / auto-number)
rebate_id (text value for rebate id)
item_num (text value for item number)
contract_cost (double value for the contract_cost associated with the
item_num and rebate_id)
Each rebate_id will contain many item_num's and item_num's will be in
multiple rebate_id's.
tbl_special_prices has 4 fields
unique ID (generated / auto-number)
cust_num (number value for each customer that has a special item price)
item_num (text value for item number)
spec_price (double number value for special price)
Each customer can have many item_num's with only one spec_price for an
item_num.
Here is what I want to get to:
A customer(cust_num) with an Item(item_num) a rebate_id(if applicable) a
contract_cost(if applicable) a last_cost(standard cost there is always a
value here) a price(spec_price). From here I can use an expression{
iif([contract_cost] Is
Null,1-([last_cost]/[spec_price]),1-([contract_cost]/[spec_price])) } to
generate my margin.
There will be a few instances where a customer belongs to seperate
rebate_id's that have the same item_num and will therefore generate unique
lines and to me that is ok. The problem I am having is querying the tables
properly so that I don't get incorrect data output.
I have tried several differnt ways to no avail. I am getting a bit
frustrated with my lack of ability and would appreciate any advice or help in
getting this solved.
thanks
item with a customer specific price base on either a standard cost or a
rebate cost.
I have 4 tables that contain the data but I am having difficulty designing
the correct queries and / or relationships to generate the correct output.
tbl_item_cost has 3 fields
unique ID (generated / auto-number)
item_num (text value for item number)
last_cost (standard cost)
tbl_rebate_cust has 3 fields
unique ID (generated / auto-number)
rebate_id (text value for rebate id)
cust_num (number value for each customer that is eligible for that rebate_id)
Customers can belong to more than one rebate_id and each rebate_id will have
multiple customers.
tbl_rebate_items has 4 fields
unique ID (generated / auto-number)
rebate_id (text value for rebate id)
item_num (text value for item number)
contract_cost (double value for the contract_cost associated with the
item_num and rebate_id)
Each rebate_id will contain many item_num's and item_num's will be in
multiple rebate_id's.
tbl_special_prices has 4 fields
unique ID (generated / auto-number)
cust_num (number value for each customer that has a special item price)
item_num (text value for item number)
spec_price (double number value for special price)
Each customer can have many item_num's with only one spec_price for an
item_num.
Here is what I want to get to:
A customer(cust_num) with an Item(item_num) a rebate_id(if applicable) a
contract_cost(if applicable) a last_cost(standard cost there is always a
value here) a price(spec_price). From here I can use an expression{
iif([contract_cost] Is
Null,1-([last_cost]/[spec_price]),1-([contract_cost]/[spec_price])) } to
generate my margin.
There will be a few instances where a customer belongs to seperate
rebate_id's that have the same item_num and will therefore generate unique
lines and to me that is ok. The problem I am having is querying the tables
properly so that I don't get incorrect data output.
I have tried several differnt ways to no avail. I am getting a bit
frustrated with my lack of ability and would appreciate any advice or help in
getting this solved.
thanks