Homeowrk Help for Access/SQL

[jlbovo]

hi, i had a couple of problems with my homework this evening. it's some SQL/ Access. i had gone through and created my neccessary tables, and entered all the data, and i flippe dthe page and i have to do some querys. i have two tables ITEM(ItemNo, ItemName, ItemPrice, QtyOnHand) and the INVITEM(InvNo, ItemNo. Qty)

so i usually am used to the SQL at school, but i am home fro the weekend and all i have here is MS access. but i only have 3 questions left that i need help with

6- Display the INVNO and number of items ordered in each invoice.


11- display INVNO and the total price for each invoice.

13- display ItemName and ItemPrice for all items cheaper than a Nut.

please if some one could help me out jus a bit with these last couple it'd be great. but i think i have to do them with access, and i know they have a sql design. and help would b great... thanks - justin

6- Display the INVNO and number of items ordered in each invoice.

SELECT INVITEM.InvNo, Count(INVITEM.Qty) AS CountOfQty
FROM INVITEM INNER JOIN ITEM ON INVITEM.ItemNo=ITEM.ItemNo
GROUP BY INVITEM.InvNo;

....that's what i got in access, and im pretty sure that's not really what i am trying to get at. access si alot more differnt than i thought..? i gives me the wrong quanitity. like i have invoice number 1001, and i has 3 diff items on it, that total 19 pieces. but some how i only get 3. b/c i must add up each oreder not each piece. please help.

 

[jlbovo]

here

im on the last one now and i am a little stuck...
here is my code...

SELECT ITEM.ItemName, ITEM.ItemPrice
FROM INVITEM INNER JOIN ITEM ON INVITEM.ItemNo=ITEM.ItemNo
WHERE (((ITEM.ItemPrice)<5))
GROUP BY ITEM.ItemName, ITEM.ItemPrice;

it displays everything i need except for the "Nail" which is only $0.99. and ideas why it wont show up, eveything else that is under 5$ comes up in the query, but no nails ??? help me out thanks - justin

 

[Richard Schollar]

At a guess, I suspect it is because there are no nails in your INVITEM table? I say this because you have used an INNER JOIN which will only include items listed in BOTH tables in field ItemNo. You can amend the SQL hence:

SELECT ITEM.ItemName, ITEM.ItemPrice 
FROM INVITEM RIGHT JOIN ITEM ON INVITEM.ItemNo=ITEM.ItemNo 
WHERE (((ITEM.ItemPrice)<5)) 
GROUP BY ITEM.ItemName, ITEM.ItemPrice;

which should return everything in ITEM whether or not it has a match in INVITEM.

Regards

Richard

 

[jlbovo]

yes that worked perfectly that whole change inner to right. thanks. but now i think i have thsi problem. now for

display the INVNO and total price for each invoice...

and here's what i got from access myself...

select invitem.invno, sum(item.itemprice) as SumofItemPrice
From invitem inner join item on invitem.itemno = item.itemno
group by invitem.invno;

the caluclations is a little off...for example i have from the invitem table.
invno- 1005
itemno- 4
qty - 10

4 is the hammer which is 9.99 so the toal should be 9.99 * 10 right...?
how do i do that..?

 

[Richard Schollar]

I presume you would need something along the lines of:

SELECT invitem.invno, SUM(item.itemprice*invitem.qty) AS SumofItemPrice 
FROM invitem INNER JOIN item ON invitem.itemno = item.itemno 
GROUP BY invitem.invno;

Regards

Richard