CODEWITHSUNDEEP
ms-accessms-access-2007
user608676
user608676

Reputation: 1

microsoft access create forms and reports help

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))

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 = 10130 - 777 = 9,353

All the above fields and calculation remains same except comission for buyer invoice.

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:

CustomerID is primary key in customer table and foreign key in Price table (one-to-many relationship)

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));

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));

Upvotes: 0

Views: 810

Answers (1)

Fionnuala
Fionnuala

Reputation: 91366

It is not difficult to perform calculations in VBA. First, ensure that the user inputs KGs and probably price per KG, not man calculations, then you set up Events for various controls. For example, with such a set of calculations to be carried out, you may wish to include a calculate button and also calculate in the before update event for the form.

As for searching, if you have skilled users, and not too many of them, they can use the built in search functionality of Access.

You then come to reporting. A textbox on a report can contain a calculation:

= Me.Quantity * Me.Price

You might like to look at some sample databases to see how controls can be set up in forms and reports.

BTW The create table code you show looks more suitable for SQL Server than for Access. There is no reason why you should not have an SQL Server back-end, if you wish, in which case a lot of calculations might be performed by SQL Server through triggers or better, calculated columns (fields).

However, it may suit just as well to skip storing calculations, unless there is an historic necessity.

Upvotes: 0

Related Questions