New To The Access World

  • Thread starter Thread starter SantaUK
  • Start date Start date
S

SantaUK

I have just picked up Access as I have never needed it before, I've got some
screens up and running, but now realise that this is too complex a task for
me. I can understand basic database concepts.

I need to create for my day job a database which does the following. It
would save me a mountain of time:

Scenario is checkout tills, multiple operators on them, final readings show
over or shortages on the tills.

I have approx 50 tills, with sometimes as many as 12, but usually about 4
operators on them in a day, all identifiable by unique operator numbers. I
want to be able to input on one screen the till number, and the cash over or
shortage. Once this is done, I want to be able to input the till number,
and the unique operator numbers that have been on that till. Each shortage
on the till should be marked against each operator, and most importantly
with a running total of shortages.

I want a report that shows by descending order, the operators with the
highest shortage running total. Can anyone help with such a thing?

Thanks for reading

--
Regards


M Millar
(e-mail address removed)
 
You need to start with the things you're interested in; they're called
"entities" in traditional database modeling. Sounds like you have tills,
operators, and shortages (or under/overs, or maybe discrepancies, you'll
need to think about what to call them.)

Each of those entities will become a table. The fields in the table are the
things you want to track -- till number, operator name and id, date and
amount of a discrepancy. And each table must also have a uniquely
identifier which becomes its primary key. That would be till number and
operator number for those tables, and you can either use an autonumber of
the combination of date/till/operator for the discrepancy (I'd use an
autonumber myself; it will be simpler).

At a minimum, I think you'll have something like:

Till
- TillNumber
- <other fields as desired>

Operator
- OperatorName
- OperatorNumber
- <other fields as desired>

Discrepancy
- Date
- TillNumber
- OperatorNumber
- Amount

One thing you haven't said is whether it's possible that an operator might
clear out their till (and register an over/under) more than once on a given
calendar date. If that's the case, you'll probably need to add a "shift"
field.

Notice that there's no need to store the total values; you can calculate
them on demand using a query.


--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...
 
Back
Top