Hi,

I'm quite new to this and would like to thank you in advance for any help given. It will be greatly appreciated.

I'm making a database for my shoe store.

It needs to do the following:

1. Store suppliers (got it in a separate table)
2. Store Shoes - brands, styles, fittings, automated barcode etc (got it in separate table also)
3. Store customers (separate table)
4. Store multiple orders for each Shoe in different sizes (5 x 1, 5½ x 2, 6 x 1, 6½ x 2 ... etc)
5. Orders have to be current (incoming/on order) and archived (for the record).
6. When order received it needs to be archived by a push of a button and the stock allocated according to each size to the current in-store stock
7. I also need transaction/purchase menu to run sales using the barcode system (would also appreciate any help give on automated barcoding for each shoe), when sale is made the item have to deducted from the stock level.

I need to know how to best organize such database. At the moment I seem to have troubles to manage the stock levels for the different sizes.

Many thanks in advance!
Anton

Hi,

I'm quite new to this and would like to thank you in advance for any help given. It will be greatly appreciated.

I'm making a database for my shoe store.

It needs to do the following:

1. Store suppliers (got it in a separate table)
2. Store Shoes - brands, styles, fittings, automated barcode etc (got it in separate table also)
3. Store customers (separate table)
4. Store multiple orders for each Shoe in different sizes (5 x 1, 5½ x 2, 6 x 1, 6½ x 2 ... etc)
5. Orders have to be current (incoming/on order) and archived (for the record).
6. When order received it needs to be archived by a push of a button and the stock allocated according to each size to the current in-store stock
7. I also need transaction/purchase menu to run sales using the barcode system (would also appreciate any help give on automated barcoding for each shoe), when sale is made the item have to deducted from the stock level.

I need to know how to best organize such database. At the moment I seem to have troubles to manage the stock levels for the different sizes.

Many thanks in advance!
Anton

Anton,

Inventory control is probably one of the most difficult things to program in any development system, not just Access.

Here is a very good starting point:

Inventory Control: Quantity on Hand (http://allenbrowne.com/AppInventory.html)

TIP: Put all people/companies in a single table. There is no need for suppliers, customers, employees, and etc. to be in separate tables.

Anton,
Inventory Control: Quantity on Hand (http://allenbrowne.com/AppInventory.html)


That is awesome! Thank you - exactly what I was looking for! :D



TIP: Put all people/companies in a single table. There is no need for suppliers, customers, employees, and etc. to be in separate tables.

Hmmmmm. Lol ok. Here is the question then: how would I combine the following details of principally different (and not related) records into one table? :confused:

1. Customers: ID, Name, Contact details, Purchase history
2. Supplier: ID, Brand, Country of origin, Contact details, Agency details
3. Shoes: Brand, Style, Size, Color, Fitting, Size range

Thanks!
Anton

That is awesome! Thank you - exactly what I was looking for! :D


Glad that was helpful.




Hmmmmm. Lol ok. Here is the question then: how would I combine the following details of principally different (and not related) records into one table? :confused:

1. Customers: ID, Name, Contact details, Purchase history
2. Supplier: ID, Brand, Country of origin, Contact details, Agency details
3. Shoes: Brand, Style, Size, Color, Fitting, Size range

Anton,

I was referring to people/companies only. It does not matter is ther are a customer, epmployee or a supplier. The all have name, address, phone, email etc. Shoes would not be inlcuded.

I think you will need to look into properly normalizing your data.

Example:

1. Customers: ID, Name, Contact details, Purchase history

Purchase history is NOT part of the customer info. That would be sore in the Invoice/Sales tables.

Here are some liks to get you started:

Database - Planning/Normalization (http://www.utteraccess.com/wiki/index.php/Links#Database_-_Planning.2FNormalization)

Tutorials (http://www.databaseanswers.org/tutorials.htm)

About developing a system like this: it is realistic to expect a very experienced developer (10+ years deveoloping accounting systems with integrated Inventory control) to take a minimum of 7-9 months working full time to develope a system like this. For some3one starting out with databses, it ocoudl take years without some formal traing. If you are needing this withing a year, it is probably will be better for the business to purchase something.

About developing a system like this: it is realistic to expect a very experienced developer (10+ years deveoloping accounting systems with integrated Inventory control) to take a minimum of 7-9 months working full time to develope a system like this. For some3one starting out with databses, it ocoudl take years without some formal traing. If you are needing this withing a year, it is probably will be better for the business to purchase something.

And I'll second that statement!

I really appreciate all the answers and help guys. Just to be clear, im not THAT new to access, but last time i did anything was about 10 yrs ago.

also im creating a very simple database:

Products
Customers
Companies
Orders (stock in)
Purchases (stock out)

Relations are: Customers --> Products, Products --> Customers (so i can pull purchase history of a specific person as well as see who else purchased that shoe). Companies --> Products, Orders --> Products, Sales --> Products

I mean it can't be simpler than that can it? :)

that is all i need, just to look at the screen and see how many pairs of shoes i have in chosen size of a particular style.


1. Customers: ID, Name, Contact details, Purchase historyPurchase history is NOT part of the customer info. That would be sore in the Invoice/Sales tables.Purchase history is a field to which tblPurchases.ID related to. Customers query is used as a subform in form Products, so when i open Shoes i can see who actually purchased that style. Or this is not the way of doing that?

I do understand how tables should be related and have understanding of the principals of database structures. What im lacking is VBA knowledge really.

Everything is working perfectly as anticipated, but I did run onto this stock handling problem:

I have a Stock Management form where I see the current level of stock in all sizes for each selected shoe.

In the Orders subform I can also pull all the orders of that particular shoe.
In unbound txtBoxes I calculate current stock level + all current orders = stock including orders.

The problem begins when I have an order arrived. I need to do the following:
1. Allocate the order to the current stock
2. Delete the order from current orders
3. Save the order into the orders archive

Can this all be done by a single button click "Order received"?

I was trying to find VBA code to get the button working correctly, but had no luck.

Thanks in advance again