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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,544
Messages
5,832,372
Members
430,128
Latest member
ojl987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top