Trouble combining 3 different SQL tables items and getting the sum

Question

I am having trouble with a system that my dad uses for reporting at his company. I didn't build it but I am trying my best to wrap my head around it. It's ASP and I am not so well versed in it (or writing sql for that matter).

Basically I have three tables I am working with, Item, ItemHistory, and Devices. In those tables what I am trying to do is

• Get the items a user has worked on via the "serialnumber" column from the "Item History" table
• Compare that serial number to the "serialnumber" column in the "Item" table to get the device type (from the device column)
• Use the device type to grab a predetermined number value from "points" column in the "Devices" table
• Store the value and add it to any other values that were pulled back by the query then display the total.

*See Update*

As you can see, it would have been much easier if the Device Type was included in the ItemHistory table, but it wasn't. I've tried JOINing the tables but keep getting errors (which is from my lack of knowledge im fairly certain). The query is by date range and user (from the ItemHistory table), but I think I've got that figured out. I just can't get it to reference all the tables and add the "points" together.

Any help is greatly appreciated! If you need any more info let me know. Thank you in advance.

---

Update

So basically what I am having problems with now is that the query keeps coming back with "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record." which I think is being caused because it is just adding the numbers in the points column.

I think the problem with my last question is that I made it seem like there were "Points" for every device, but really there are a set number of devices (lets say five) and there could be multiple serial numbers all pointing at one device. I think this is where the query is running into trouble (but I could be wrong). Also, my sql statement looks pretty much exactly like the one littlebobbytables posted below, except for my own variables added in.

Here's what the database looks like.

ItemHistory Table

Date      | SerialNumber |  DeviceType | User
----------|--------------|-------------|-------
11/1/12   | 123-456-789  |     NULL    | Bill
11/1/12   | 456-123-987  |     NULL    | Bill
11/1/12   | 987-654-321  |     NULL    | Bill
11/1/12   | 216-897-631  |     NULL    | Bill
11/1/12   | 874-547-277  |     NULL    | Bill

Item Table

  SerialNumber |  DeviceType
  -------------|-------------
  123-456-789  |  Device1
  456-123-987  |  Device2
  987-654-321  |  Device3
  216-897-631  |  Device1
  874-547-277  |  Device2

Devices Table

  Device   |  Points
 ----------|----------
  Device1  |    20
  Device2  |    25
  Device3  |    40

And what I am hoping the output will be is

  User   |  Points
 --------|----------
  Bill   |    130

Answer 1

You can sum the points together, and group by the User ID:

SELECT [User], SUM(Points) AS Points
FROM ItemHistory IH
INNER JOIN Item I ON IH.SerialNumber = I.SerialNumber
INNER JOIN Devices D ON I.DeviceType = D.Device
WHERE IH.[Date] = @YourDateVariable
GROUP BY [User]