I'm doing an internship in the States and I have to complete the following task.
An advisor inputs all invoices from the salesmen into excel sheets. first of all to calculate the commission for each salesperson, second of all for reporting.
How much commission was summed up today, weekly etc.
so far the excel sheets are almost not automated and it takes a lot of time to calculate it by zourself. i want to design a database with ACCESS 2003. I already made a form to input the following information.
They do not want me to store any personal information about customers or the sale personnel. it is also not interesting what product was sold. all they care about are the numbers.
so far all these information are stored in ONE table. only those rates which might change, like credit card fees or commission rates, are stored in another table. the form i created accesses these data for their calculations. also i put the salespersonnel (id and name) in a table and a combobox on the form accesses this table. the last table contains thousands of customer (only company name) and a combobox also accesses this table. but none of these tables are referenced to each other.
so i know of normal forms and all that design rules, but i do not see a way to do that here. do you? i mean i certainly have the first normal form, but can i apply the third? or any other things that improve my db? can't wait to hear some advices for my design...
there are two kind of sales. parts they sell in stock(regular sale) and those they buy somewhere else and sell then to customers (special order sale). i have to differentiate. this column is just a checkbox. if checkbox clicked then special order sale true. so there would me maximum two rows with the same invoice number. all sums from special orders are added up, as those from regular sales. means if 4 items were sold, 2 spo's and 2 regular, i add up both spo's in 1 row, the same with regular
so technically there might be two rows per each invoice...