Matrix lookup

[mattc66 via AccessMonster.com]

Hi All,

I may have to pay someone to help me with this one.

Here is the situation:

Our customers have a customer Type (number). Each Item has an Item Type
(number). Using a matrix to look up the customer type and the item type tells
you what discount to give them for the item ordered.

I built a query and linked it to the Order Detail table, but it doesn't work.
Once linked I cannot add or make changes to the OrderDetail it looks it.

So I need to do some sort of look up in code. Does anyone have any suggested
help?

This is what the Matrix looks like:
Item Type>> 1 2 3
4 5
Customer Type
20 .50 .45 .
50 .425 .55
30 .45 .55 .
50 .425 .55
40 etc

I have put the matrix into a table

C-Type I-Type Disc
20 1 .50
20 2 .45
20 3 .50
20 4 .425
20 5 .55
30 1 .50
etc....

Thanks

 

[Jeff Boyce]

Matt

"... but it doesn't work" doesn't give us much to go on. Do you get an
error message? Do you get data returned, but the wrong data?

Consider posting the SQL statement of your query.

If I can paraphrase, it sounds like you want to provide a customer type and
an item type and see the applicable discount. This sounds like a query to
me.

You'd need customers, customer types, items, items types, an "order", an
"order detail" and your "matrix" ... 7 tables. Is that what you have?

I can envision a query that has tblCustomer joined to tlkpCustomerType and
to tblOrder ... the query also has tblItem joined to tlkpItemType and to
trelOrderDetail (which is joined to tblOrder). Finally, your "matrix" table
is joined to the tlkpCustomerType and tlkpItemType on the respective fields.
You'd add any fields you need to see in your output (perhaps only the
discount percentage?!).

Hopefully that gets you a bit further along...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mattc66 via AccessMonster.com]

What I had tried to do at first was setup the OrderDetail Table would contain
both Codes per item. However when I then took the tblOrderDetail and tried to
Join it to the tblDiscMatrix >>Join ItemType and >>Join CustType then having
the Discount from tblDiscMatrix in the Query I see all the records, but I
can't add or delete anything. The query is locked.

Why can't it just be simple..

Matt

"... but it doesn't work" doesn't give us much to go on. Do you get an
error message? Do you get data returned, but the wrong data?

Consider posting the SQL statement of your query.

If I can paraphrase, it sounds like you want to provide a customer type and
an item type and see the applicable discount. This sounds like a query to
me.

You'd need customers, customer types, items, items types, an "order", an
"order detail" and your "matrix" ... 7 tables. Is that what you have?

I can envision a query that has tblCustomer joined to tlkpCustomerType and
to tblOrder ... the query also has tblItem joined to tlkpItemType and to
trelOrderDetail (which is joined to tblOrder). Finally, your "matrix" table
is joined to the tlkpCustomerType and tlkpItemType on the respective fields.
You'd add any fields you need to see in your output (perhaps only the
discount percentage?!).

Hopefully that gets you a bit further along...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi All,

[quoted text clipped - 39 lines]

Thanks

 

[mattc66 via AccessMonster.com]

Okay let me make this example a little simplier. Let use 2 tables.

I need to do a lookup from the tblOrderDetail and find the correct Discount
in the tblDiscMatrix using the ItemType and CustType flds. From those 2 types
we determine the discount for the item.

Table1
tblOrderDetail
OrderNum Item ItemType CustType
1000 Wid1 5 60
1000 Wid2 4 60
etc

Table2
tblDiscMatrix
ItemType CustType Discount
5 60 .50
5 63 .55
4 60 .55
4 63 .50
Etc

So as I see it I’d need to Join the following tables:
tblOrderDetail tblDiscMatrix
ItemType >>> ItemType
CustType >>> CustType

Placing the fld Discount then in a Query with the tblOrderDetail to then
perform the rest of the calculation on the sales price.

This is not working. When I join these 2 tables, it shows me the records, but
is locked. I cannot add or make changes to the query.

What I had tried to do at first was setup the OrderDetail Table would contain
both Codes per item. However when I then took the tblOrderDetail and tried to
Join it to the tblDiscMatrix >>Join ItemType and >>Join CustType then having
the Discount from tblDiscMatrix in the Query I see all the records, but I
can't add or delete anything. The query is locked.

Why can't it just be simple..

Matt

[quoted text clipped - 29 lines]

 

[Jeff Boyce]

Matt

If you are using a query joining an OrderDetail record and a DiscountMatrix
record (on ItemType and CustomerType) and returning a Discount, why do you
need to update anything?

Have you looked into Access HELP on the topic of "updateable queries"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay let me make this example a little simplier. Let use 2 tables.

I need to do a lookup from the tblOrderDetail and find the correct
Discount
in the tblDiscMatrix using the ItemType and CustType flds. From those 2
types
we determine the discount for the item.

Table1
tblOrderDetail
OrderNum Item ItemType CustType
1000 Wid1 5 60
1000 Wid2 4 60
etc

Table2
tblDiscMatrix
ItemType CustType Discount
5 60 .50
5 63 .55
4 60 .55
4 63 .50
Etc

So as I see it I'd need to Join the following tables:
tblOrderDetail tblDiscMatrix
ItemType >>> ItemType
CustType >>> CustType

Placing the fld Discount then in a Query with the tblOrderDetail to then
perform the rest of the calculation on the sales price.

This is not working. When I join these 2 tables, it shows me the records,
but
is locked. I cannot add or make changes to the query.

What I had tried to do at first was setup the OrderDetail Table would
contain
both Codes per item. However when I then took the tblOrderDetail and tried
to
Join it to the tblDiscMatrix >>Join ItemType and >>Join CustType then
having
the Discount from tblDiscMatrix in the Query I see all the records, but I
can't add or delete anything. The query is locked.

Why can't it just be simple..

Matt

[quoted text clipped - 29 lines]

Thanks

 

[mattc66 via AccessMonster.com]

If the discount of 50% off list comes from the Discount Matrix and the List
price is in the OrderDetails how would I perform the calculation of discount
in the orderDetail if it will not allow me to make changes?

Matt

If you are using a query joining an OrderDetail record and a DiscountMatrix
record (on ItemType and CustomerType) and returning a Discount, why do you
need to update anything?

Have you looked into Access HELP on the topic of "updateable queries"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay let me make this example a little simplier. Let use 2 tables.

[quoted text clipped - 48 lines]

 

[Jeff Boyce]

You are assuming that you need to STORE the calculated amount. It is rarely
necessary to store calculated values, particularly so because doing this
then requires that you also add in all the coding needed to keep calculated
values and the pieces used to do the calculation in synch.

If you are working in a form, and your query can return both ListPrice and
Discount, add an unbound text box control and calculate the price there.

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the discount of 50% off list comes from the Discount Matrix and the
List
price is in the OrderDetails how would I perform the calculation of
discount
in the orderDetail if it will not allow me to make changes?

Matt

If you are using a query joining an OrderDetail record and a
DiscountMatrix
record (on ItemType and CustomerType) and returning a Discount, why do you
need to update anything?

Have you looked into Access HELP on the topic of "updateable queries"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay let me make this example a little simplier. Let use 2 tables.

[quoted text clipped - 48 lines]

Thanks