Tricky calculation to perform

[leeli67]

Well i hope someone can help because then I would have nearly completed this thing.



Problem :

I have about 30 customers in my Customer table each of which get a different discount depending on the product they buy below.

I have about 300 products in my products table which all fall into 4 category's BREAD - ROLLS - CAKE - OTHER.


for example:

customer A gets 20% off cakes but no discount on anything else.

customer B gets 10% on all of the Category's

my problem is I just don't know how i might factor this in.???
June7 helped me before but having trouble getting to grips with this.

would appreciate any help.

[jzwp11]

I would probably create a table that relates customers to the applicable product category for which they get a discount


tblCustomerCategoryDiscounts
-pkCustCatDiscID primary key, autonumber
-fkCustomerID foreign key to customer table
-fkCategoryID foreign key to category table
-spDiscount (single precision number field)

With the above table you can use a DLookup() function to pull in the applicable discount.

[leeli67]

Ok I will try that one , many thanks for the help.

PS are you saying I need a Category table too?.

[jzwp11]

PS are you saying I need a Category table too?.

Yes, because it provides the link between the product and the customer discount.

It would be a very simple table

tblCategory
-pkCategoryID primary key, autonumber
-txtCategoryName

You would then replace the category text field in your product table with a foreign key the product table.

tblProducts
-pkProductID
-txtProductName
-fkCategoryID foreign key to tblCategory

If you have a bunch of records in your product table, you can add the foreign key field (do not delete the category text field yet) to your table and then run an update query to pull the foreign key value from the product table and copy it into the fkCategoryID field of the product table. The update query would have to join the product table to the category table via the two category text fields in both tables.

[leeli67]

Thanks for getting back , Does the fkcategoryID in tblProducts have to be a number format so as to create an entegrity link, also i might need some help with the Query you mentioned.

Attachment 6694

[jzwp11]

If the pkCategoryID in tblCategory is an autonumber datatype, then the corresponding foreign key field in the product table must be a long integer number. (the autonumber is just a special case of a long integer number).

First, make a backup copy of the database.

You will have to populate the tblCategory prior to updating the product table. The category names in tblCategory must match those in the product table exactly, so you may want to check for extra spaces, misspellings etc.

Alternatively, you could create a query based on the product table that selects the unique category names. That query would look like this

SELECT DISTINCT category
FROM Products

You can then, if you want, change the above to an APPEND query and select the tblCategory as the destination table. You will need to map the category field of the product table (first row in the grid) to the categoryname field of the tblCategory.

For updating the product table, start with a SELECT query. Include the product table and the category table in the upper part of the query design grid. Access will automatically carry your join line between the categoryID fields, remove the join between the ID fields. Make a new join between the category field of the product table and the categoryname field of the category table. In the lower grid, select the CategoryID from the product table. Now change the query type from SELECT to UPDATE. In the CategoryID column you added, there should be a row that says update to. You will want to put [Category].[CategoryID]. So the SQL text should look like this:

UPDATE Category INNER JOIN Products ON Category.CategoryName = Products.Category SET Products.CategoryID = [Category].[CategoryID];

Now run the query.

Looking at your structure, you should not have a total field in the order detail table. The total would be a calculated value (QTY * PRICE) and in general, calculated values are not stored in a table. The same would hold for the total field in the order table. You would calculate the total of the order from the detail records whenever you need them via a query

[leeli67]

You My Friend Know your Onions , Thank you very much for your detailed answer. Its going to take me some time to digest the info

thanks again.

PS: Your right about the totals fields in the Order and order Details table, I included these fields so as to give the person entering an order a line total and perhaps an order total I.E customer on phone wants to know total there and then, BUT looking back now this could probably just be added through a form text box or calculated query , would that be correct ?

Also when i go to create the SELECT query I only get the options of
Simple
Crosstab
Find Dupes
Find Unmatched

or am i in the wrong place? UPDATE , found it its simple !!!!!!

[jzwp11]

You're welcome. Please post back with any questions.

[leeli67]

damn i got this :

UPDATE Products INNER JOIN Category ON Products.CategoryID = Category.CategoryName SET Products.CategoryID = [Category].[CategoryID];
not
UPDATE Category INNER JOIN Products ON Category.CategoryName = Products.Category SET Products.CategoryID = [Category].[CategoryID];


oooppss


Attachment 6697

[jzwp11]

You can just go to SQL view and put in the correct text and run it (just make sure all of the table and field names are spelled correctly--in case I typed something wrong)

UPDATE Category INNER JOIN Products ON Category.CategoryName = Products.Category SET Products.CategoryID = [Category].[CategoryID];

[leeli67]

My last image just keeps returning the before posted Sql code, and when I try and Paste your sample in it returns an error . possible mis match on names or something , Im going to try and crack it by checking all the names that are referred to in the sql code.

once again thank you JZWP11

[jzwp11]

This should be the correct SQL statement not what I had posted earlier (sorry for the error)

UPDATE Products INNER JOIN Category ON Products.CategoryID = Category.CategoryName SET Products.CategoryID = [Category].[CategoryID];

[leeli67]

type mismatch in expression when I run the query, I will look again, Dont suppose there's any chance if I upload the database you or someone could maybe show me where im going wrong , I find it easier to look at the correct way and break it down.?

[jzwp11]

I can take a look at it. It would be best to run Access' compact & repair utility (to get the size down) and the zip and post it. Please make sure to remove or alter any sensitive data.

[leeli67]

Heres that Database , if you do get a chance jzwp11, its very basic so should be easy to look at , many thanks