A help needed...!

[prabhakarpeje]

i have created an access database file in which i want to make pharmacy/medical stores bill , i have created a table called "pharmacy bill" in that i have made many calculated fields and have finally added a gross total field to sum all the fields. i have made a form named "pharmacy bill" the first record in this form is entered and calculated properly but when i add a 2nd record it does not works properly and then the print of first record comes correctly but second record does not get proper page setup arrangement. i have created a report format also but all in vain...!
I am attaching the link to the file for ur correction and help...

https://app.box.com/s/ksuc0jd8iy5xuzn6jlu3

[June7]

This db is not a normalized relational design. It might seem to meet your requirements but will cause a lot of problems down the road.

The "Gross Total" for the second form doesn't show anything because you are calculating with some fields that are Null. Calculations with Null result in Null. Calculated fields cannot use functions. So you will have to do the calculation to handle the Null in textbox ControlSource property:

=Nz([Total 1]) + ... + Nz([Total 22])

[prabhakarpeje]

This db is not a normalized relational design. It might seem to meet your requirements but will cause a lot of problems down the road.

The "Gross Total" for the second form doesn't show anything because you are calculating with some fields that are Null. Calculations with Null result in Null. Calculated fields cannot use functions. So you will have to do the calculation to handle the Null in textbox ControlSource property:

=Nz([Total 1]) + ... + Nz([Total 22])

Dear June7 ,
This is not working. please specify deeply stepwise pls or another options....!

[prabhakarpeje]

Please Advice or suggest the options for not having problems future ahead down the road...!

[June7]

The best solution is to normalize data structure.

Why didn't the expression work - what happens? Post what you attempted.

[prabhakarpeje]

Actually i did not get the option of textbox control source property .... meand in what ? Form, Table or else where..?

[June7]

You can do that expression in the report RecordSource sql statement to construct a field and bind textbox to that field or put the expression in textbox ControlSource property.

[prabhakarpeje]

sorry but can u pls explain me steps wise pls again sorry

[June7]

http://office.microsoft.com/en-us/ac...001181384.aspx

Form in Design view.

Select textbox.

Type expression in ControlSource property.

OR

Form in Design view.

Select the form if not already by clicking the box in upper left corner.

Select the RecordSource property.

Click the ellipses (...) to open the query builder.

Type expression in a cell of the Field row.

[prabhakarpeje]

Dear June7,

Thank u very much it finally worked but then i had another problem in printing the bill the grand total would not be shown, so i did as u had advised earlier, then it worked successfully . But still there is one problem , i checked the pharmacy bill table in which the records are saved , in this the record saves all the sub totals and the particulars but it does not show the Grand total here in table datasheet view . pls help or advice....!
Thanking you...!

[June7]

Don't understand. Should not be saving subtotals and grandtotals to a table.

[prabhakarpeje]

Yes its saving all sub totals value but not the grand total...!
what to do ...?
means MRP1xQTY1=TOTAL1's value is saved but the Grand total is not been Added...!

[June7]

Why are you saving these calculated data? There is no reason to, especially aggregate data (Grand Total). These should all be calculated when needed, not saved in table.

[prabhakarpeje]

Dear June7 Thanks for all the previous Kind help given by you..!

Now i have another need/help required .
I now actually want to auto deduct the product qty(specified in the pharmacy bill) from the main stock qty list. Means if i prepare a bill of some item(gloves) and qty is 5, this qty should be automatically deducted from the main gloves stock list 50 qty to 45. i have a combo box to select the product name .

[June7]

Ideally, balance on hand should be calculated when needed. Adjusting the quantity increases risk for error because it is disconnected from the raw data. Sum of purchases minus sum of sales equals balance on hand. Review http://allenbrowne.com/AppInventory.html