Shop database Help

[SeanDelere]

Hello All,

As a virgin member of the Access World Forums board and a new Access user I am trying to get started creating a database to run a stock control system for my recently opened jewellery shop.

I started off by recording all my data in Excel so I have several workbooks containing each item I have bought, the price paid for it, the suggested retail price, supplier code etc. and a unique code I have given to each item.

So far I have set up 3 tables, "Products" is the main table, "Suppliers" holds the wholesalers I buy from details and "Catagory" holds Rings, Bracelet, Necklace etc.
I have managed to import the data from Excel into the Products table so I don't need to enter everything all over again.

What I have not been able to get my head around is how to use a form (presumably) that I can type the unique code number into, enter the number of items sold and the price sold for. This should then update the stock level and also put the data into a fourth table so I can keep track of what is selling well.

Has anyone designed a similar database or knows of such an example so I can see how it is done.
I realise what I want to do is possible in Access - I just need an opening to get me going.

Many thanks for any help, examples or pointers

 

[sfreeman@co.mer]

template ??

Just curious, have you taken a good look at the stock Inventory Control database template that comes with Access?

 

[SeanDelere]

Yep

Now I am beginning to think I may be really stupid because I can't see a form that does what I require. Could you point me in the right direction please.

 

[Ashfaque]

Hello Sean,

I read almost all the threads that sent by you.

If I am not mistaken, you are a trader and you have to know about the current stock of your different kind of jwellery and items you sold.

So far you made 3 tables as follows:

1. Product Table --All your products are here
2. Supplier Table. ----------All your suppliers are here.
3. Category Table.----------All the categories of Jwellary are here.

Be patient with me. Let me know following things:

1. Do you wish to sale only on Cash basis OR you may have some Credit Customers too.?

2. Are you aware OR have you knowledge of VBA procedures, functions and modules that you may require in developing your db that will give you desired result ?

Its true that the sample db are not normally holds those tricks and techniques that somebody exactly needs. That is why they are called sample db.

I am not sure that how much I could help you but such kind of db I given to one my client which is for a supermarket. So I have little experience about db that are used for trading estabilshment.

Considering your requirements are same, I will try to help you in setting up your db. If possible send me your zipped db with your current 3 tables you prepared.

With kind regards,
Ashfaque

 

[SeanDelere]

Thanks for your help Ashfaque.

The shop is a cash only business.

I managed to set up the 3 tables with no problems and they work as I would expect as they are simple lookup tables.

I then set up 2 further tables ORDERS and SALES
ORDERS is for storing data of the orders I place with a supplier
SALES is for storing data when I sell an item

ORDERS has the following fields
OrderID (autonumber)
SupplierID (from the suppliers table)
ProductID (from the main Products table)
DateBought (The date the order arrives)
QuantityBought (the number of an item bought)
PriceBought (the price I paid for the item)
Ssp (the sugested selling price so I will be able calculate potential profit)

SALES has the following fields
SalesID (autonumber)
ProductID (from the main Products table)
DateSold (the date the item was sold)
QuantitySold (how many of the item I sold)
PriceSold (How much the item was actually sold for)

What I wanted to achieve was this -

When an order from one of my suppliers arrives I want to enter their product code and check whether that code exists in the Products table.

If it does exist only the ORDERS table should be updated.

If it does not exist then both the ORDERS and PRODUCTS tables should be updated.

At the end of the day I have a Daily Takings sheet showing the ProductID and the price it was sold for.
Those details would be added to the SALES table.

From what I have read I should then be able to use the Reports element of Access to show me what I needed to see such as the stock level (by adding up all the Orders and subtracting all the Sales) or which Suppliers gives me the best markup etc.

The only experience I have of VBA is pasting examples of code and modifying it meet my requirements.

I can post the zipped up database if it would help but to be honest I am begining to think this project is beyond my capabilities.

Sean

 

[smercer]

SeanDelere: Hi Sean,

I am designing a books database for a second hand bookstore and it works very well (except for problems mentioned here). the sales form is based around Temp tables (the tables is always there but the data is moved when I am finished with it) and action queries that copy data from inventory to the temp table, delete the record in inventory for current sale and same again when the user presses "sell books". Miscellaneous books are a bit different, they need a append and update query to change the quantity and a delete query when quantity reaches zero.

the process of setting this sales form up is very complex. make sure you have a lot of time up your sleeve.

This database will be copyrighted when I am finished.

hope this helped

Scott

 

[SeanDelere]

I think maybe I need a break from sitting in front of the computer and give my head time to clear itself.

Although I have made some progress from knowing nothing about Access to at least understanding some of the basics I can't see the light at the end of the tunnel.

 

[smercer]

I think maybe I need a break from sitting in front of the computer and give my head time to clear itself.

Sorry I made your head swirl. I didn't mean to speak the doom and gloom

Although I have made some progress from knowing nothing about Access to at least understanding some of the basics I can't see the light at the end of the tunnel.

You won't want to see my database then. It is full of visual basic programming

 

[Ashfaque]

Sean,

Sorry for the late reply.

As you said, can you send me the zip file in this forum and as well as on my email id ashfaque_online@yahoo.com ?

regards,

Ashfaque

 

[SeanDelere]

I have moved on quite a bit since the dark days of the time I wrote that post.

The database is coming along nicely (if slowly) with all the help I have been given in these forums