SQL Sum function

  • Thread starter Thread starter Laura
  • Start date Start date
L

Laura

Hi,
I have been working on this for a while and I can get it to work. I
have two tables

Table 1: Partnum
Partnum Cost
15 13696 0.06
02 14379 2.02
02 14387 1.73
05 07475 1.73
05 07478 0.17

Table 2: Boards
BOARD# PARTnum PLACED PARTS DROPPEDPARTS
00277000 15 13696 25 0
00277000 15 13696 50 0
00275906 02 14148 424 166
00276118 02 14170 50 0
00209700 02 14379 90 0
00209701 02 14387 76 0
00276120 05 07475 26 3


I want to make a Query that will total how many parts have been
dropped for each part number. I also want it to tell me how much it
has cost so far(multiply the total dropped parts by cost of the
part). Thank you so much for any help you can give me.

Thank you
Laura Budzinski
 
Laura,

Paste the following in the SQL view of a new query:

SELECT Boards.PARTnum, Boards.DROPPEDPARTS, [DROPPEDPARTS]*[Cost] AS
DroppedPartsCost
FROM Boards INNER JOIN Partnum ON Boards.PARTnum = Partnum.Partnum

Now change to design view and see how it's done. Make sure to make any
corrections required in the table and field names!

HTH,
Nikos
 
fairly simple with the query designer

select table Boards
select table parts
link them on Partnum

add
partnum
dropped
[dropped]*[costs]

select the greek-sum icon on your taskbar

partnum: group by
dropped: sum
[dropped]*[costs]: sum



resulting sql:
*note names may differ

SELECT Parts.partnum, Sum(Board.dropped) AS SomVandropped,
Sum([dropped]*[costs]) AS Expr1
FROM Parts INNER JOIN Board ON Parts.partnum =
Board.partnum
GROUP BY Parts.partnum;
 
Back
Top