[harishdhir]

Need help on designing a access form with calculation controls which are evenly distributed in different rows which are transferred to a table for future use as finding total materials issued to a particular person / customer.

[ScottGem]

You need to explain this in much greater detail. I really don't have a clue what you are asking. The only thing that comes to mind is to look at Crosstab queries.

[harishdhir]

I Mean Like In Excel We Can Define A Formula For A Cell, Which Shows Result Calculations From The Desired Cells I.e. I Need To Calculate A Certain Percentage Of A Value Then Multiple With Specific Values To Acheve My Desired Result

[ScottGem]

OK, first, as a general rule, we don't store calculated values. They can be calculated on the fly as needed. Calculations can be done anyplace you can put an expression. On a form or report, the calculation is set as the controlsource of a textbox control. In a query, its placed in the File row of a blank column preceded by an alias.

For example, If you need to calculate a discount on a price then multiply by a quantity the expression might look like this in a query

ActualPrice: ([Price]*[Discount])*[Quantity]

In this case, the names are the names of fields in your table.

On a form it might look like:
=([txtPrice]*[txtDiscount])*[txtQuantity]

In this case, the names are the names of controls on the form.

If you have more complex calculations you can create a custom function to return your result.

[harishdhir]

how to create a custom function to return your result.

basically I have to calculate .67% of a cell then multiply it with a particular value / rates, this is done in 10 lines of a invoice in excel then make totals of all the three cells i.e. cell having original value, cell having .67% of original value and then total of cells having multiplied value

I will be greatfull if you continue to guide me

thanks

[ScottGem]

I would suggest getting a book on Access VBA before you start with custom functions. In the book I'm writing creating functions takes up a couple of chapters so it more than I can do here.

Again you would do most of this in a query. You would have a column in the query for the original value, then another like this:

Discount: [Valuefield] * .67
Total: [Valuefiled] + ([Valuefield]*.67) + [third value]

If you can be more specific about the calcs I might be able to help more.

[harishdhir]

Respected Sir,

I am very very thankful to you for considering my request / question if you think it is right I can send the excel invoice I am using that I want to make it in access

Or

Attach the excel file for your consideration.

Or

If you send me a email address so that I can directly tell you the details


Thanks

Edited to remove e-mail addresses-Scott<>

[ScottGem]

You can zip and attach the file to a post here and I will look at it.

[harishdhir]

Respected Sir,

I am sending you the required file in zip format along with this description.

In this excel file under description field there is C.No column which is number of truck and then there is Date column which tell us about the date on which truck was send, then there is Bags column which gives information about number of bags loaded in the truck, Weight column shows the weight of bags loaded in the truck, Q.cut column shows the percentage of moisture in the bags, Q.cut (Rs) column shows the amount to be deducted for the reason of excess moisture this is calculated by applying a formula i.e. ( Cell E4 * F4 *1142.83% )

In this manner we can make a invoice up to maximum 12 trucks i.e. from S.No 1 to 12 in downward order, after 12 rows there is Heading showing TOTAL which is used to calculate Total of Bags, Weight, Q.cut, Q.cut (Rs).

The Total of Bags, Weight is carried above ( Cell H4 and Cell I4 ) to packing and quantity column then value in quantity column ( Cell I4 ) is multiplied with Rate column ( Cell J4) and the result is stored in Amount column ( Cell K4), then Tax @ 4% is calculated, then Total is calculated ( Cell K7 = K4 + K5 ), then Total of Q.cut (Rs) is deducted from Total and Net Amount due from the customer is calculated.


This Invoice is made in Excel format this I want to make it Access database. I am finding it difficult to make calculation fields in Access database. I have designed the form in the Access make combo and other boxes for the but when I want to apply calculations in the boxes it does not perform the calculations in the multiple rows in downward order.
Then this result of calculation in the boxes in not transferred to the table fields which are linked with this form in Access.

I hope you can guide me to complete this task.

Thanking you,

Harish.

[ScottGem]

I would suggest that you look at the Northwinds sample database that comes with Access. Its an order entry system so it has a similar strucutre to what you need. I'm still not entirely clear of how this works for you, but I would imagine you would need at least three tables; a customers table, an invoice table and an invoice details table. The Invoice table will have the CustomerID as a foreign key, the Invoice date and any other info specific to the invoice. The Invoice details tables would list each truck that goes on that invoice, i.e. the InvoiceID as a FK, C.no, bags, weight and any other data specific to that truck for that day.

To create your invoice you would make a query that joins those three tables and adds your line calculations. You would then create a report that groups by Invoice. You can then use unbound controls to create the invoice totals.