Query to find number on stock

[ClowneUser]

Hi,

I have a small database that i use to 'try' to keep track of all the toners/carts we purchase in our company, i have a table to record all the purchases and a seperate table to record all the itams that have been handed out to people, i think i should have used a transaction table, but i couldn't get that working....

What i need is to create someway of knowing how many of each toner/cart i have in stock, any ideas?
below are the two tables for the order/issue there is also a table with the cart descriptions in as well...

tbl_cart_order

Order_No
Order_date
Item
qty

tbl_cart_issue

Issue_No
Issue_date
Item
qty


Thanks in advance
Mick.

 

[Golom]

In the ordering scenario there's usually a table of received goods in addition to the table for orders because in most environments you don't receive it the same day you ordered it nor do you necessarily receive the quantity that you ordered.

I infer that the "item" field is a generic descriptor for the type of item rather than a unique identifier for the item being ordered or issued.

I'm not sure what the business meaning of "Issued" is. If you "Issue" a cart and/or toner do you still have it in the company?

Assuming that "Orders" represents received goods and "Issues" represents goods leaving inventory, then

Select R.Item, (SUM(R.Qty) - SUM(I.Qty)) As [In Stock]

From Ordered R LEFT JOIN Issued I
     ON R.Item = I.Item

Group By R.Item

Gives you the current In-Stock levels for each item.

 

[ClowneUser]

Hi Golom,

Thanks for your reply,

Issued is when the toner has been installed in a printer or given to someone to install in the printer.

I ran the query and it prompts me for input and ideas why this is happening?

Thanks in advance

Mick.

 

[Golom]

You didn't say what it is prompting for but the usual reason is that one (or more) of the fields mentioned in the SQL is not a valid field name in the table so it assumes that it must be a user-supplied parameter. Check your tables and ensure that the referenced fields are in fact valid fields in your tables.

 

[ClowneUser]

Hi,
You were correct, incorrect referenced table..... I changed it and now i get all toners, but in the stock field i get values ranging from -5 and going up in multiples of 5?? so I'm puzzled, below is the code i'm using, Can you see anything wrong with what I've done?

SELECT cart_orders.ItemTagNbr, (Sum([cart_orders].[ItemTagNbr])-Sum([cart_issues].[ItemTagNbr])) AS [In Stock]
FROM cart_orders, cart_issues
GROUP BY cart_orders.ItemTagNbr;

Thanks in advance.

Mick.

 

[ClowneUser]

Just reread the code, realised I'd put item where qty should have been!!! but still i don't get the correct numbers. I've only got entries in the issued table for 3 toners, but all are showing, i thought only the ones that had entries in both tables would show?

 

[Golom]

This

FROM cart_orders, cart_issues

is called a cross-join or cartesian product. It combines every record in "cart_orders" with every record in "cart_issues" and you will get n * m records in the result where "n" is the number of records in "cart_orders" and "m" is the number of records in "cart_issues".

What I gave you was

From Ordered R LEFT JOIN Issued I
     ON R.Item = I.Item

A LEFT JOIN will give you all the records in "cart_Orders" and matching records (on the item field) from "Cart_Issues" OR NULLS if there is no matching record in "Cart_Issues". That would happen if an item had been ordered but never issued.

 

[lespaul]

read the SQL Join document linked below for more information JOINS and how they work.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ClowneUser]

Sorry golom,
I tried to write it myself instead of copy and pasting your code, i totally missed the left join.. I put it in but it brought me back all of the items in the orders table but only the numbers for the entries in the issued table, so i added cart_issues.qty > 0 but its bringing me back the wrong amounts... below is the sql code from the query

SELECT cart_orders.ItemTagNbr, Sum([cart_orders].[quantity]) - Sum([cart_issues].[quantity]) AS [In Stock]
FROM cart_orders LEFT JOIN cart_issues ON cart_orders.ItemTagNbr = cart_issues.ItemTagNbr
GROUP BY cart_orders.ItemTagNbr, cart_issues.Quantity
HAVING (((cart_issues.Quantity)>0));


what i have in the orders table for one item is:

Order_Date = 31/07/06
ItemTagNbr = C9700
Quantity = 3

Order_Date = 02/08/06
ItemTagNbr = C9700
Quantity = 4

In the issues table i have:

Issue_Date = 01/08/06
ItemTagNbr = C9700
Quantity = 2

Issue_Date = 01/08/06
ItemTagNbr = C9700
Quantity = 1

which should give a total of 4 but the query gives 2 entries 1 of 5 and 1 of 3?

Have you any idea why this is?

 

[Golom]

Doing this

HAVING (((cart_issues.Quantity)>0));

effectively changes a LEFT JOIN into an INNER JOIN ... that is the retrieved records exclude those where cart_issues.Quantity IS NULL which is what they will be if there is no matching record for a particular value of cart_orders.ItemTagNbr. Perhaps you need

SELECT cart_orders.ItemTagNbr, Sum([cart_orders].[quantity]) - Sum([cart_issues].[quantity]) AS [In Stock]
FROM cart_orders LEFT JOIN cart_issues ON cart_orders.ItemTagNbr = cart_issues.ItemTagNbr
GROUP BY cart_orders.ItemTagNbr

grouping by cart_orders.Quantity just creates a separate record for each different quantity.

 

[ClowneUser]

Hi Golom,

Thanks for your response, i tried the code you gave, this still gives me blank entries from the orders table, also the totals are still wrong, a little different from befor now it gives me for C9700 1 entry of 8 where it should be 4, I'm not sure where it's getting this from, i have only put 4 or 5 entries in the database ( to test ) and all these entries have the incorrect amount, ie.
C9701 should give 2 but im getting 4
C9703 should give 1 but its giving -1

I'm really not sure how it's calculating this.

Any ideas?

Thanks in advance
Mick.