Homeowrk Help for Access/SQL

jlbovo

New Member
Joined
Oct 8, 2004
Messages
13
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
Code:
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

Code:
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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jlbovo

New Member
Joined
Oct 8, 2004
Messages
13
here

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

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

New Member
Joined
Oct 8, 2004
Messages
13
yes that worked perfectly that whole change inner to right. thanks. but now i think i have thsi problem. now for
Code:
display the INVNO and total price for each invoice...
and here's what i got from access myself...
Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I presume you would need something along the lines of:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,268
Messages
5,577,102
Members
412,768
Latest member
klig
Top