Sales Ledger

[Guest]

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

 

[Guest]

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

 

[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.