Northwind Explanation

[SeanDelere]

I am still trying to get started making a database to control the stock for my new jewellery shop.

After spending 2 weeks getting nowhere fast I have gone back to the Northwind example database to see if I can pick up any ideas.

Can anyone explain why when I create a new order using the orders form and "sell" an item the amount held in stock is not reduced by the number I have sold?

I can't believe you have to go into the products table and manually reduce the stock level so I am sure I am missing something rather simple.

As ever, thanks for any help

 

[maxmangion]

did you put it as a calculated field in your query ?

 

[Rich]

Search here for inventory, there have been numerous posts on the subject

 

[SeanDelere]

I haven't modified anything in the database, so no.

Is that something I have to do? I would have thought as a sample database all those sort of things should have been done already.

Does anyone know of a database similar to the Northwind one but more to do with retail point-of-sales rather than taking orders that I could download to study?

 

[maxmangion]

as regards a sample database if you go to the Sample Databases section of this forum, there are some sample examples. However, keep in mind that any sample database is not tailored made to your specific needs, therefore for sure that you need to modify it to suit your needs.

 

[SeanDelere]

That is excatly what I want as I want to be able to understand the database that I build rather than just use one someone else has written so I can make it do (with time) exactly what I want.

Unfortunetly there are no suitable examples in the examples section.

 

[maxmangion]

in my opinion getting a sample database and try to understand it, is not an easy task. Imagine you get a sample and this database would consist of some 10 tables, 20 queries, 20 forms, 10 reports and a couple of macros and modules, do you think that you would be able to understand what the author had in mind, unless the sample will be well documented.

I would recommend you to make a plan of what you need on a piece of paper and then try to create the database yourself ... everytime you come across a problem there is always someone willing to help on this forum.

In this way, you will be able to understand exactly what your database is doing and any further modifications in the future would be a lot easier rather than trying to modify someone else's sample.

 

[Rich]

Microsoft has many more sample databases on it's website including using calculated fields and many more, Pat Hartman has posted links to them here, maybe oneday one of the Mods will add it as a sticky.

Search the MSKnowledge base if you can't find Pat's links here

 

[SeanDelere]

I have managed to accomplish quite a bit from my standing start. I have imported my products data from Excel and have 2 other table for catagories and suppliers.

The main stumbling block for me is getting a form to reduce my stock level by the quantity sold and to write to another table the transaction details. If I could get over this hurdle I am sure I would start to progress and add the other the things I want to do.

This would of course mean lots more questions to you experts at a later date ;-)

 

[Pat Hartman]

Can anyone explain why when I create a new order using the orders form and "sell" an item the amount held in stock is not reduced by the number I have sold?

- Microsoft never added this functionality to the database. Northwinds is by no means complete and managing inventory is quite complex so I'm not surprised that they omitted this little piece of functionality.

Take a look here for ideas:
http://www.access-programmers.co.uk/forums/showthread.php?t=68407&highlight=Inventory+Cycle+Count

I found this with a google search. It's not free but for $11.20, it may be a real bargin for you. You can download the associated database and look at it for free. I didn't look closely so it may not be fully functional.
http://elementkcourseware.com/products/ViewProduct.jsp?frmInventoryID=41632

 

[SeanDelere]

Thanks for the tips Pat.

Unfortunatly I can't look at the the site you found on Google as it asks me for a username and password.

 

[Oldsoftboss]

SeanDelere

I went to the site and downloaded the Db no worries. Went to post it here and found it was too big. (142k) So I deleted some embeded images on the forms. Not sure it will help much, but at least you can have a look.

Dave

 

[SeanDelere]

Thank you very much for taking the time to help me.

I will take a look at the file tonight and see if I can pick anything up.

I sense a trip to the Amazon site site to get some more reading material ;-)

 

[NeilC]

I didn't have a clue how to write my recruitment agency system until I bought "Access 2003 for Dummies". You might want to check it out. I'm no expert as I built mine just using the wizards and query builders etc - no actual programming. But what I found was that you need to make sure you understand relationships properly (especially how to create many-to-many relationships using two one-to-many relationsips and an extra table) and make sure it all works as Tables before jumping into Forms.

 

[DreamGenius]

I didn't have a clue how to write my recruitment agency system until I bought "Access 2003 for Dummies".

If you're reasonably au fait with Excel and have made a good standing start in Access, I'd recommend the Microsoft Press Step by Step series. I have both, but prefer this one, and it leans towards their MOUS qualification for Access too, if you're that way inclined.

 

[SeanDelere]

Following your advice I am off to the Amazon site today to order a book. I will look through the reveiws and decide what will work for me best.

After reading through all the advice, articles and threads people have sent me from this discussion I think I have the basis of a plan.

I will have one main table containing all the product information.
Two tables "feeding" the main table for suppliers and categories.
One table conaining ordering information including how many items and the price I pay for them.
One table containing transaction details which will be updated as items are sold.

My theory is that I should be able to calculate the stock level by deducting the transaction table from the ordering table in a query.

How dows that sound to everyone?

 

[NeilC]

So you're gonna have separate tables for:

Products
Suppliers
Product Categories
Orders
Sales Transactions

What info will each table hold?
Have a think about the relationships between them in terms of One-to-many or Many-to-many - which fields in which tables will need to look up information from which other tables?

 

[SeanDelere]

Products:

Code (every different item has a unique code I allocate)
SupplierCode (the ordering code of the supplier)
Category (Lookup from the Category table)
Supplier (Lookup from the Supplier table)
Description (A brief comment on the item)

Suppliers:

Details of the wholesalers I buy from.

Categories:

Ring, Bracelet, Necklace etc.

Orders:

DateOrdered (Entered when I order an item)
DateReceived (to check I receive what I order)
Cost (the price I pay for the item)

Sales:

DateSold (the dates the items are sold)
SellPrice ( How much I sell it for)

From what I can gather I should be able to work out the stock level in a query by adding the total number of items bought and taking away the total number of items sold.

I will read up more on relationships before getting under way, any comments would be most welcome as this is all new to me.