Help with Access Forms/Reports and creating total fields on form and report

Question

Hello, I work in a large retail store with a lousy Point of sale system.  Each day, I might have approximately 10 registers and I need a way to reconcile the dollar amount on the POS report with how much cash is in the register.  I also consolidate all the smaller bills and coins so that I take the largest amount of large bills to the bank to eliminate change fees.  Anyway, I created a table that lists the date, register number, and there is a field labeled pennies, nickels, dimes, quarters, ones, fives, tens, twenties, fifties, and hundreds....

What I need to do is enter each register cash count in a form, and then have the form total the count of each currency or coin denomination and put a total at the bottom or top of form...so that i know the cash in the envelope should match what the POS system says. 

Anyway I need help with creating a field on the form that will multiply all those denominations and give me the total value of the cash. 

thanks

Answer 1

I'd agree with Scott as regards the flawed nature of the current design, but on the basis of the current model:

Firstly be sure that your pennies, dimes etc columns all have a DefaultValue property of 0 (zero) in the table design.  Also be sure that all the columns have the Required property set to True (Yes).  Then create a query, qryTotalSales, which groups by date and register number and adds the sum of the dollar values each cash column, e.g.

SELECT [SaleDate], [RegisterNumber],

(SUM([Pennies] / 100)) +

(SUM([Nickels] / 20)) +

(SUM([Dimes] / 10)) +

(SUM([Quarters] / 4)) +

(SUM([Ones])) +

(SUM([Fives] * 5)) +

(SUM([Tens] * 10)) +

(SUM([Fifties] * 50)) +

(SUM([Hundreds] * 100)) AS [TotalAmount]

FROM [YourTableName]

GROUP BY [SaleDate], [RegisterNumber];

Create a form based on a query which sorts the rows by date/register:

SELECT *

FROM [YourTableName]

ORDER BY [SaleDate], [RegisterNumber];

Add a text box to the form footer or header, txtTotalSales, to the form with a ControlSource property of:

=DLookup("TotalAmount","qryTotalSales","RegisterNumbr = " & [RegisterNumber] & " And SaleDate = #" & Format([SaleDate],"yyyy-mm-dd") & "#")

Add a 'Calculate Total' button to the form footer or header with the following in its Click event procedure:

Me.Dirty = False

Me.Recalc

The text box should then show the total amount for the register number and date of the record currently selected in the form.

You can of course use the first query above as the RecordSource of a report, which you can group by SaleDate to give you the daily totals per register.  In the Saledate group footer you can include a text box to give you the total for all registers per day, with ControlSource property of:

=Sum([TotalAmount])

You can open the report for all days or filter it to a specific day by means of the WhereCondition argument of the OpenReport method.  You can easily create a simple dialogue form in which you can enter the date and open the report filtered to the date entered.

BTW, note that I've used SaleDate as a column name.  Do not use Date, which is the name of a built in function and , as a 'reserved' word, should not be used as an object name.

You should also create a unique index on the SaleDate and RegisterNumber columns.  This should be a single index on both columns not individually.  The simplest way to do this is to make the two columns the composite primary key of the table.  Otherwise create an index in table design view by selecting Indexes from the View menu.  In the dialogue which opens enter a suitable name for the index in the first empty row of the Index name column, RegisterDate say.  In the Field Name column of the same row enter RegisterNumber, and with this row still selected, in the Index Properties below, select 'Yes' as the Unique property.  Then enter SaleDate in the Field Name column in the row immediately beneath RegisterNumber.  Don't enter anything in the Index name column for this row.  Close the dialogue and save the table design.  It is important that you do create this index as otherwise there would be nothing to stop two sets of data being entered for the same register/date.  Similar considerations apply to the normalized model suggested by Scott.

---

Ken Sheridan, Stafford, England

Answer 2

Your database is not normalized properly. You should have three tables:

tblDailyResult

DailyResultID (Primary Key Autonumber)

RegisterNumber

ResultDate

DailyTotal

Then have a second table:

tblCashBreakdown

CashBreakdownID (PK Autonumber)

DailyResultID (Foreign Key)

DenominationID

Quantity

Finally, you have a lookup table for demonitations

tluDenominations

DenominationID (PK Autonumber)

Denomination

Multiplier

You would use a form with a subform to enter the Daily Result info in the main form, and it the subform, you enter the counts and denominations for each denomination. From there its easy to produce a query that calculates the actual cash amount for each register for each day.

For example:

SELECT DailyResultID, Quantity, Denomination, Quantity*Multiplier AS Amount

INNER JOIN on tblCashBreakdown.DenominationID = tluDenominations.DenominationID

FROM tblCashBreakdown, tluDenominations;

Then use that query in a Group By query to get the total by register.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 3

Does a text box with an expression like:

   =Count(Pennies) + 5*Count(Nickles) + 10*Xount(Domes) + ...

do waht you want?

Remember that the totals will not include a value from a record that has not been saved to its table.