I am creating a customer db with a stock ordering capability. I wish to create an inventory that updates based on stock sold. The order details table lists the numbers of each item that is sold but I need to be able to group similar items from different orders, total the sales and then deduct the result from the on hand figure to create a new on hand. Basically a spreadsheet function. Can any one suggest how I can go about this please.
Creating an inventory
- Thread starter Rats
- Start date
shadow9449
Registered User.
- Local time
- Today, 03:38
- Joined
- Mar 5, 2004
- Messages
- 1,037
Hi, Rats.
I assume that you have assigned a unique ID (hopefully a primary key) to each item.
What you want to do is use the "GROUP BY" clause in your query (or use it as a Total in the QBE grid, if that's how you do your queries). You will group by ID, and then sum the number of units sold.
It might look like something like this:
SELECT [Sales].ID, Sum([Sales].Qty) AS UnitsSold
FROM [Sales]
GROUP BY [Sales].ID;
Does this steer you in the right direction?
SHADOW
I assume that you have assigned a unique ID (hopefully a primary key) to each item.
What you want to do is use the "GROUP BY" clause in your query (or use it as a Total in the QBE grid, if that's how you do your queries). You will group by ID, and then sum the number of units sold.
It might look like something like this:
SELECT [Sales].ID, Sum([Sales].Qty) AS UnitsSold
FROM [Sales]
GROUP BY [Sales].ID;
Does this steer you in the right direction?
SHADOW
shadow9449
Registered User.
- Local time
- Today, 03:38
- Joined
- Mar 5, 2004
- Messages
- 1,037
Rats:
Ok, but if you want to group the items together then you need to use the products key.
Think of it this way:
You don't really care if Mr. Jones bought 2 basketballs and then Mrs. Smith bought a basketball. All you need to know is that 3 basketballs were sold.
Does that help you with the concept?
SHADOW
Ok, but if you want to group the items together then you need to use the products key.
Think of it this way:
You don't really care if Mr. Jones bought 2 basketballs and then Mrs. Smith bought a basketball. All you need to know is that 3 basketballs were sold.
Does that help you with the concept?
SHADOW
shadow9449
Registered User.
- Local time
- Today, 03:38
- Joined
- Mar 5, 2004
- Messages
- 1,037
Most welcome.
SHADOW
SHADOW
Next problem encountered! I have created queries to count opening stock, stock sold, stock received. When I try to create a query that works out the On Hand amount, ie Opening stock +Stock received - stock sold, the query will only list those products that have actually had a sale. That is if I have 20 products types in stock and I sell goods from 5 of those products my on hand figure will only list the products that I have sold from. I know this is because the goods sold query only displays the list of sold products and when it is included with the query for the stock on hand etc the query will only show those products that are in all fields of the query.
Is there a way of bypassing this so that the query will show the full products list? Hope this hasn't been too confusing (frustration is frazzling the grey matter)
Is there a way of bypassing this so that the query will show the full products list? Hope this hasn't been too confusing (frustration is frazzling the grey matter)
shadow9449
Registered User.
- Local time
- Today, 03:38
- Joined
- Mar 5, 2004
- Messages
- 1,037
Hi Rats...I'm back 
Do you know what a left join is?
Instead of making a relationship where only items in both tables are listed, you can create a type of join where ALL items (received, for example) are listed, as well as items from the other table (sold, for example) when they exist. If they don't, then you get a null value which you can convert to zeros by using the NZ function.
Check out the left join and NZ function from the help.
Good luck
SHADOW
Do you know what a left join is?
Instead of making a relationship where only items in both tables are listed, you can create a type of join where ALL items (received, for example) are listed, as well as items from the other table (sold, for example) when they exist. If they don't, then you get a null value which you can convert to zeros by using the NZ function.
Check out the left join and NZ function from the help.
Good luck
SHADOW
shadow9449
Registered User.
- Local time
- Today, 03:38
- Joined
- Mar 5, 2004
- Messages
- 1,037
Beer sounds good...let me know next time you come to Canada 
SHADOW
SHADOW
