Keeping Track Of Daily Sales

[dhstein]

I want to keep an inventory Database. One of the inputs will be daily sales.
I can either keep track of the amount on hand (simple solution) or try to
keep track of daily sales as well (more difficult - but of more use). Can
Access be used to create a table that will have columns of sales items by
date - and clearly this is an always increasing table? Another approach
would be to limit it to 1 year so there would be a limit to the number of
columns. Any ideas or suggestions would be appreciated. Thanks.

 

[Rick Brandt]

I want to keep an inventory Database. One of the inputs will be
daily sales. I can either keep track of the amount on hand (simple
solution) or try to keep track of daily sales as well (more difficult
- but of more use). Can Access be used to create a table that will
have columns of sales items by date - and clearly this is an always
increasing table? Another approach would be to limit it to 1 year so
there would be a limit to the number of columns. Any ideas or
suggestions would be appreciated. Thanks.

A Daily Sales table would have a new ROW per date, not a column per date.
Access is not a spreadsheet.

 

[dhstein]

Rick, Thank you for your response. That makes sense to keep a row per day.
I'm thinking that would be a separate table - for daily sales with the SKU
number as the column heading. But in the other table(s), the SKU number is
the key for each row. So the question is, can I relate Daily sales from the
daily sales table to the product table, since they are organized differently?
Thanks for any help.

 

[Klatuu]

To get full sales information, what you really need is a sales order system.
It would not only provide all the information you need for sales, but also
for product activity.
It involves two table. A sales order table that provides information about
the sale. Date, who to, etc. and an order detail table that would be
related to the order table that defines each product, quantiy, price, etc.

It also involves an order form and a order detail sub form. There is a good
example in the Northwind database that ships with Access you can use as a
model.

 

[Fred]

Reading between the lines, I think that you are envisioning an application
where your transactions on individual products automatically make adjustments
to inventory levels of those products. This is a typically large
undertaking, with most of the work creating lots of new operational
procedures for the entire business so that anything that affects the
inventory level of an item gets recorded as a transaction. (purchasing,
creation/destruction by using one itne to "build" another item, sales,
thefts, scrapping, etc. The Northwind example does not include this,
and you may want to ( at least temporarily) set that idea aside and start
with something along the lines of the Northwind example.

 

[Klatuu]

A full blown inventory system would be as you described.
I did not intend to imply that level of detail. That is why I recommended
NorthWind.

It is a good place to start as strictly a sales order system.

 

[Fred]

Sorry for the confusion I created. I was responding the the original
poster, not commenting on your post.

Sincerely,

Fred