Hello,

I am newbie to microsoft access. I am creating sytem for agent works to connect seller and buyer. Currently, i am maintaining my billing system manually with two different
forms one for seller and one for buyer as follows:

In the first invoice example which is for seller agent earns by recieving comission from seller. In quantity field the first item at left
of dash sign represents mann (1 mann equals 40 kg) while item on right represents value in kilogram. Price column represents value per mann
basis and the last column represents agent comission on per mann.

Total qunatity is calculated through by adding kgs and convert it into mann unit if possible by adding kgs first and convert 40kg into 1 mann
e.g 2-30 + 2-32 from kg field, kgs are equal to 62kg convert 40kg into 1 man and put remaining kgs into kgs field as follows 5-22
Price is calculated in Total filed which is hidden column by coverting quantity value in kgs first (2*40+30) and also convert price value in
per kg (1800/40) and store result in Total field as ((2*40+30) * (1800/40)) = 4950
Finally for Seller deduct comission in another agent profit hidden field 140 per mann calcuated as convert agent value in kg first and multiply


total kgs by agent value as ((2*40+30) * (140/40))

Code:
Name: ABC
Address: Street # 02
Type: Seller


DATE        TIME        QUANTITY(mann-kg)    PRICE PER MANN    Total(Hidden Field)    AGENT COMISSION(per mann)    AGENT PROFIT(Hidden Field)
09-02-2011    Morning         2-30            1800            4950                   140                             385 
09-02-2011    Evening         2-32            1850            5180                   140                             392
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Total                           5-22            10130                              777                                
                                
Payable = 9,353
All the above fields and calculation remains same except comission for buyer invoice.

Code:
Name: DEF
Address: Street # 05
Type: Buyer


DATE        TIME        QUANTITY(mann-kg)    PRICE PER MANN
09-02-2011    Morning         2-30            1800          
09-02-2011    Evening         2-32            1850          
----------------------------------------------------------------------
Total                           5-22            10130         
                                
Recievable = 10,130
What i want i am looking to convert all that stuff to microsft access 2007 by creating forms in the same way for user to input data and generate
reports as above invoices example for seller and buyer. I am stuck how to input quantity field value as mentioned, how to calculate agent comission etc.
Also i want searching option in reports where end user can easily search invoice for specific seller or buyer within date criteria and with other filters.

I need help and asistance how to calculate and save quantity, price per mann, comission and the sub total in reports.

Database schema i designed is:

Code:
CREATE TABLE customer (
CustomerID SMALLINT(6) NOT NULL AUTO_INCREMENT,
Name VARCHAR(100),
Address VARCHAR(255),
Type ENUM('1','2') COMMENT 'Seller=1, Buyer=2',
PRIMARY KEY (CustomerID));
Code:
CREATE TABLE Price (
PriceID INT(11) NOT NULL AUTO_INCREMENT,
CustomerID SMALLINT(6),
Date Date,
PriceType ENUM('Morning','Evening'),
Price INT(11) NOT NULL,
Quantity INT(11) NOT NULL,
Comission INT(11) DEFAULT NULL,
PRIMARY KEY (PriceID));