Calc Sales based on Stock Count

[neutrino]

Hi there!

I'm a complete novice to database programming and am currently working my
way through various online tutorials as I work on my application.

I operate an import and distribution company that specializes in
perishables. Many of our products carry best-by-dates counted in weeks. Since
in this country distributors are required to credit the retailers for any
product that either gets damaged or expires in the trade it is essential to
keep a handle on our customers' shrinkage. Unfortunately we are either not
granted access to the retailers sales databases or the systems do not contain
the information we require. My merchandisers are required to take weekly
stock counts at each of our customers, which is currently being entered into
an Excel spreadsheet.

What I'm working on is a database application that will work out weekly and
monthly sales per product AT each customer (not TO each customer) based on
the following input:
(The following table I call Tracker):

Date
Customer_ID (foreign key from tbl_Customers)
SalesRep_ID (foreign key from tbl_SalesReps)
Transaction#
Product_ID (foreign key from tbl_Products)
Movement_type (foreign key from tbl_Movement)
Quantity

In addition I have the following tables already set up:

tbl_Customers
tbl_SalesReps
tbl_Products
tbl_Movement (Delivered/Not Delivered/Off Condition/Stock)

I would like to get some assistance with the following:

To create a split form:

Form HEADER: (this information will be the same for a bunch of entries)
Date
Customer_ID
Customer_Name
SalesRep_ID
Transaction#

Form TABULAR section:
Product_ID
Product_Description
Movement_Type
Quantity

And to create queries/reports that will return the following information:
Any combination of sales reports by:
Week
Month
Product
Customer

Remember, I don't need sales TO my customers (retailers), but the sales FROM
each retailer to their end-consumers. I'll be inputting the following
information to calculate those sales: Delivered (to the customer), Not
Delivered (Billed but returned to our warehouse in good condition), Off
Condition (Credited for damages or expiry dates) and Stock (Weekly
shelf-stock taken at each customer).

I realize I'm probably asking a bag of questions at once and understand if
the answers cannot be posted in a reply on this board. I would therefore
appreciate some links to tutorials or examples that deal with specific
aspects of the database I'm working on.

Thanks in advance.

 

[Mark Andrews]

A little difficult to give you the exact advise you need, but in general:
It sounds like you need to grasp queries and query design a bit more to
accomplish your task.

If you need to "calculate sales the customer has" based on their stock
levels and how they change each month to get sales for July 2009 you would
take ((stock level for June 2009) - (stock level for July 2009)) +
(delivered July 2009) (I think, I read your post pretty quickly)

Example: they had 1000 in June, 900 in July but you delived 50 in July so
they really sold 150

In Access that would be something like multiple queries and/or dlookups to
get each number.
You would use a combo box that held the July 2009 date for reference in the
queries or dlookups to get the result. Also combobox to pick the customer.
Think multiple query to get each number (from the appropriate records) and
then doing math on those numbers in a query that ties everything together.

I could be missing some of the complexity. In general "a novice to database
programming" would need to become fairly good at queries to accomplish the
task.

I would be glad to help more if you want to contact me.
Here are some links to other sites that may help (start with Allen
Browne's):
http://www.rptsoftware.com/help/microsoft_access_links/

Mark Andrews
RPT Software
http://www.rptsoftware.com

 

[neutrino]

Hi Andrew,

Thank you for your reply. I figured I'd need to develop my skills somewhat
before getting any results with my application. Your formula was correct, in
Excel I calculate (Opening stock + Delivered) - (Off condition + Not
delivered + Closing stock).
I'll keep slogging through the tutorials until I can get the queries
formulated.
Thanks for the links and I'll post here if I get stuck.

Peter

 

[Mark Andrews]

You could always pay a bit for help in developing the application.
Something to think about. There are lots of books on how to write sql and
VBA code in Access but you have to decide how far down that path you want to
go.

Time vs. Money and "do you want to be a software developer/programmer"?

Wish you luck,
Mark Andrews
RPT Software
http://www.rptsoftware.com