Sales

[Ernst Guckel]

Hello,

I am looking to create a database that stores sales figures for a
restaurant. I have four tables. tblSales wich has sales by 1/4 hour. I add
the sales to get sales by whichever time slot I need.

tblReconsile
------------
reconsileID
pennies
nickles
etc...

tblPartials (deposits)
-----------
PartialID
PartialDate
PartialAmount

BreakID (diferent bank reconsiles for the same day)
BreakNumber
BreakDate

There can be many reconsiles in a day, many partials for a day and many
breaks for a day... I am at a loss as to how to setup the relationships
between the tables... Are they setup right??

Also... There has to be atleast two reconsiles for a day the Open and Close
reconsile. The others will fall inbetween...

Any help would be great...

Ernst.

 

[John W. Vinson]

Hello,

I am looking to create a database that stores sales figures for a
restaurant. I have four tables. tblSales wich has sales by 1/4 hour. I add
the sales to get sales by whichever time slot I need.

tblReconsile
------------
reconsileID
pennies
nickles
etc...

tblPartials (deposits)
-----------
PartialID
PartialDate
PartialAmount

BreakID (diferent bank reconsiles for the same day)
BreakNumber
BreakDate

There can be many reconsiles in a day, many partials for a day and many
breaks for a day... I am at a loss as to how to setup the relationships
between the tables... Are they setup right??

Also... There has to be atleast two reconsiles for a day the Open and Close
reconsile. The others will fall inbetween...

Any help would be great...

Ernst.

Why not store each sale as a record, and use a Totals query to get the totals
per quarter hour, per day, or whatever? I have NO idea what a "break" or a
"Partial" mean in your business logic - they're certainly not standard
database terms; and I don't see how either one relates directly to sales.

What is the PURPOSE of this database? What information do you want it to
provide you, and in what form?

 

[Ernst Guckel]

Ok... The POS system at "End Of Day" moved the sales data to PC. One of the
data files is 1/4 hour sales that I am going to import into the database. I
will use queries to get the data to the end user. The problem I am having is
that the end user needs to reconsile the "Cash" in the store a few times a
day to make sure there are no shortages (about 10K in cash sales a day).
This could happen 5 times a day or as little as two. But there is always an
open and close count. Partials are actual deposits made throughout the day.

I want the database to store each "Break" (this is a reconciliation of cash)
each day. The last break of the day will sum the actual sales and compare to
cash collected but the ones throughout the day will compare to sales up to
that point entered by the end user.

Partials(Deposits) must be stored so I can track weather they get to the
bank or not. Reconciles need to be stored to track potential over/shortages
of cash. And I didn't know how to handle multiple Bank Breaks during a day.
I could hard code this to 5 per day but that would be unnormalized and I did
not want to do this if I could normalize it somehow...

I think I covered it all...

 

[Evi]

You are using a jargon which means something to you but very little to most
of us. I'll have a go at translating it and then more of us can have a go at
answering your question.

Check if I've translated this correctly

The cash in your till needs to be counted at intervals throughout the day.
Reconsiled = counted
Breaks = those times when the cash is counted
Partials = Cash that has been put into the till? This is totally unclear. Do
you mean the cash which ought to be there according to the till's sales
totals or do you mean the cash that has been added to the till to provide
change?

You need to record how much cash is in the till when it is counted at those
periods throughout the day. That is why you need a column for nickles etc -
to make it easier to count up.
At the end of the day, you need to compare the cash that should be in the
till from sales with the cash that is actually in the till.

By using those counts throughout the day and recording them, you can see
who, if anyone has been dipping their fingers into the till or giving out
the wrong change. Is this right?

Evi

 

[Ernst Guckel]

For the most part its correct. Partials are deposits that are taken to the
bank. Partials are counted as cash for every Break throughout the day.

When we count the cash it is a collection of many "tills" or "Cash drawers"
during the day. A recap:

Partials: Bank Deposits (Total Deposits for a day = Total Cash Collected for
a day)
Breaks: A Counting event to verify cash

I guess a reconsile is the same as a break but there can be more than one in
a day. I guess reconsile can be renamed to SalesDay for a better
clarification...

SalesDay can have many Breaks
Breaks can have many partials but ALL partials from a previous break on the
same salesDay must be included on ALL future Breaks within the same salesday.


Ernst.