Sales Ledger

[Ernst Guckel]

Hello,

At work we have a few spreadsheets that we use on a daily basis...
'Monthly Cash Sheet' is one. It is a sales ledger that trackes everything
from Daily sales, to DIscounts and Pormos sold. There are 50 pieces of info
that is recorded each day at closing. Right now we use an excel spreadsheet
that tracks the data in ledger format. One tab for each month and a new file
for each year. We have already moved out inventory to a database but we want
to move the Sales ledger to a database as well but there seems to be so much
info that I do not know where to start as far as design. The employees do
not deal well with change so I am trying to keep the layout similar but it
just seems overwelming in complexity... The format of the ledger now is:

Dates down the left and data across the top. The catch is that each week
there is a week and month totals row.

It is just one big sheet... and moved to an access form is ugly by design I
guess is the best way to put it...

Any suggestions on this or examples of other projects like it would be
great...

Thanks,
Ernst.

 

[tina]

first, forget forms and data entry for the moment. the first step in
building a relational database is ALWAYS to define the tables/relationships
according to standard normalization rules, and then build those
tables/relationships in the database. then, and ONLY then, do you begin
building queries, forms, and reports on top of the tables structure.

next, in order to take that first step of defining your tables/relationships
correctly, you need to learn the basics of relational data modeling. one
good text to help you is Database Design for Mere Mortals by Michael
Hernandez. there are many helpful links at the following website
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with Starting Out and Database Design 101 links.
note that you can't transfer your current "flat file" Excel design into
Access. the two programs do not work at all alike, and trying to make Access
work like Excel will lead to a real mess, not to mention major frustration.

btw, learning to design your db correctly takes an investment of time and
patience. many people are tempted to "skip over" that step and jump right
in. the newsgroups are full of posts from these folks, asking how to fix or
circumvent a problem that is rooted in poor table design - and then the next
problem, and the next, and the next.

hth

 

[Ernst Guckel]

I have a pretty good understanding of relational database concepts... I have
been in access for a few years just never put a 'Sales Ledger' in access.
The core data works in a flat-file database better than any other concept I
have come accross...

Moving a ledger to access just has me stumpt as to how to setup the core
relationships inorder to make the venture into Access worth the trip. So
When do you leave it in excel and how do you know?

Ernst.

 

[tina]

well, if you understand relational data modeling principles, then you're
already ahead of the game. and if you've experience in working with both
Excel and Access, then you're in a better position than me to say which will
support your business process best.

i can't think of any process i've been required to house in Excel, that i
wouldn't have much preferred to put into a relational database. but that's
only my own personal experience, it hasn't much value for anyone else.

all i can suggest is that, if you decide to move the process to Access,
you'll need to put aside all thought of how you have it set up in Excel.
approach the process with a fresh eye, analyze it to define the business
needs, the entities, and the data elements, and design your
tables/relationships from there - just as you would any process that is not
already housed in Excel.

hth

 

[julie]

Ernst, keeping it simple your Access database can continue to be a wor
in progress. Realize that the Excel booklets have size limitations.
Relational aside, keep in mind the more you separate out the data int
little buckets the more options you will have to pull and view report
from-which will eventually make everyone happy and save time in th
long run. The good thing is that Excel and Access can interchang
easily. If you are looking for a starting point, keep the Excel shee
live as is and get the data into Access-most will agree that with th
interface design done right/forms it will actually be much prettier-i
someone complianed they could always switch to data sheet view whic
would look about the same as Excel anyways. Then you can star
separating out things and adding in data collection buckets t
centralize notes or other useful fields-think big, you can incorporat
other fields for business analysis and performance measurement withi
the database as you want/centralization but realize you can als
potentially control those views so that the indiv. users don't ge
overwhelmed/only see what they need to see with the forms eventually o
do that in advance-meaning switch to Access and tell them how to get th
same view they've had in Excel. Work from there to create th
customized queries and forms for them and they will probably prefer t
use those anyways and you will benefit by having added information i
one place for reporting. Go for it

 

[melspence]

Ernst,

mail me a copy of your ledger workbook, and I'll send you back a table
structure. I think I have been here or somewhere very like it before!

Mel

 

[melspence]

Ernst,

Think I've been down this road before. Mail me your work book, I'll
send back a table structure you can play with.