i need help with stock database query

[Goons]

Hi All,
I'm building a stock database for a mobile retailer. I've got following tables.
1)Manufacturer (Nokia, Apple....
2)Mobile Models (Lumia 900, E75, Ip5, IP4, Ipad)
3)LOcation (i.e warehouse, in Transit or in order)
4)Purchases
5)Sales
6)Quantity
7)Transit
8)In order

Query - Stock in hand (Purchases & sales table)

I can't seem to get Stock in hand figures right. I have added purchases for all models in PURCHASE field & sales of models that are Sold in SOLD field. After running the stock in hand query I get results for only the ones which have been sold and not for rest of models that have been sold i.e entire stock list.
The other issue is that if I've added multiple sales for one model that does not record it on stock in hand either.
I'll appreciate if someone can give me their expertise.

Many Thanks
G

[zmbd]

We'll need to see a little more information.
Which version of Access/
How are the tables related?
We'll also need to see the SQL/Queries that you currently have.

> Please do not attach your database at this time.

You should also take a look at the older MS "northwind" example database

The Location of the Northwind sample files in Access 2003 you used to be able to find this in the online templates; however, as of late it's difficult to locate.

You can find the 2007 online and this is the 2010 version:
Access 2010: northwind-sales-web-database
I'm not really fond of either version as it uses a lot of macros instead of VBA and the newer "Navigation Control" is not very nice to the novice programmer to implement; however, with a little looking you should be able to find an example or two that can help you out... in fact, the template may even be suitable for your immediate needs.

[neelsfer]

Don't forget to have a table for stock count totals. I use this table as a "base" and deduct sales and add stock received from/to it. I also use this data to reset the quantities on the day of the count, to the actual stock on hand.

[Goons]

Hi ZMBD,

I'm using access 2007.
Purchase table has got data from Manufacturer & Models table via lookup column and purchase table has a quantity column as well.
Same applies to Sales table as well.
In both tables I'm inputting Quantity figures.
Stock in hand query which relates to manufacturer/Model/Purchase & Sales tables to derive stock in hand figure.
The problem is that if there are multiple sales made of a model that are deducted separately in stock in hand table & not giving an uptodate figure.

IN stage two I would like to link Transit table & On order table.

At present I would like to keep it simple with just generating daily available stock that could either stock is in surplus or negative.

Thank you for your feedback

[Goons]

Hi neelsfer,

I'll try what you've suggested & see if it works.
Could you advise on how I can add up multiple sales for a model in sales table rather then doing it manually.

Thanks