How would you...

[stonelady]

I've posted a few messages and have received really excellent responses
(THANKS!).

I'm trying to structure my database correctly the first time. Basically, I
want to track production and sales at various sites.

Iwant to see all inventory for a product across all sites, or for each site,
etc.

Many products can be produced & sold at different locations.

I was thinking of setting up the following tables:

SiteID
SiteID
Location

ProductMaster
ProductID
ProductDesc
SiteID

Production
SiteID
ProductID
ProdDate
ProdQty

Sales
SiteID
SoldDate
ProductID
SoldQty


Does this look right? My database programming skills haven't been used in a
while, and there are no programmers here to bounce ideas off.

All thoughts and suggestions are greatly appreciated!

 

[Jeff Boyce]

What does (your) "site" have to do with your [ProductMaster] table? It
appears you are including SiteID in both [ProductMaster] and [Production]
tables ... why?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

NOTE: I'm not saying it is necessarily incorrect, just that I don't know
enough about YOUR situation to tell if it is...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[stonelady]

I need to be able to report how much product is on-hand, produced and sold
for each site.

All also need to report how much of a certain product is on-hand, for all
locations.

I would love any suggestions on making this cleaner!

 

[Jeff Boyce]

I still don't understand your business. "How" depends on "what"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[stonelady]

Each of our sites produces and sells stone of varying sizes. I need to
record and track inventory for each site/product and at a higher level for
the region.

I'm trying to determine the best way to link each product to a site, so I
can pull out site-specific inventory, production and sales figures.

 

[Jeff Boyce]

So it sounds like you have Products, and Sites, and Inventory (?product @
site?).

It also sounds like your Sites belong in (one and only one?) region(s).

Are your Sites both "Production" sites and "Inventory" sites, or only
one/other?

Are your Sites "Sales" sites, in addition to the previous answer, or are
they solely "Sales" sites?

Do you care about to whom (i.e., Customer) you sell? Do you need to keep
track of to where (i.e., Delivery Address) and when you deliver?

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[stonelady]

Each site may produce, inventory and sell certain products. (not all sites
produce or sell all products)

I don't need to track to whom products were sold, we have another program
that tracks that information.

And, I'm only creating this database (for now anyway) for this region.

 

[Fred]

I don't want to interfere with the main excahnge, but it looks like it might
have died out, and just consider my comments to be a sidebar.

At the 30,000' view level, you have to decide how it is that you are going
to "know" inventory. You are implying that this will be done from
transactions. Jumping to this is probably a much bigger job than you
realize, and you will need to create operating procedures that capture data
for anything that will add or deduct from inventory, and then get your people
to follow them. You might want to start small on this.

Then, if you decide to "know" inventory based on transactions, you need to
decide between:

1. Record all transactions (plus enter initial inventory as a transaction)
and then have your computator just sume them up when the current
inventory value is needed. I think that Allen Brown's site has an example
that does it using this method.

2. Have a number in a field which represent current inventory, and then
have transactions modify that number. (once and only once for each
transaction)

Finally, your initial answer to aside, having "site" in a product table
doesn't look right unless there is a one-to-one relationship between those
two, which I doubt. But I can't suggest an alternative without really
knowoing what's in that table.

 

[stonelady]

Hi Fred,
I appreciate any input. What started out as a simple project has blossomed.
I think I'm going to call in a local company to provide a little one-on-one
training, to assist me. I've been reading the books, and I've looked at
Allen Brown's site, etc. and I know from all I've read that structing my
tables correctly at first is so critical.

As I've mentioned, I haven't worked on the actual programming side for a few
years, so my programmer's brain is a little rusty...

(Also, since this project started there are more aspects that they want me
to capture...)

Thank you again - it is so great to have this resource of knowledge! I will
be posting more in the future (I'm sure!)

 

[Fred]

Your "foundation" is creating an organized picture for yourself of the
nature, structure and relationships between your data. Your "first floor"
is your data structure in tables and in relationships between tables.

Transaction based inventory is a special case in that the mission
fundamentally affects the foundation and the first floor. It's also one
where, as I described, 2/3 of the work is in areas outside of the database.

Good Luck!