SQL / VBA It's an Access question

Houndx

Okay, I'm working on an Access project at work and I need to generate a report totaling the hours for either all records or between two dates of something similar to the following:

Table1 is entries of time spent working on various projects, including if that time is related to a facility. So Table1 is

ID	DATE	HOURS	Facility
1	1/1/11	4		1
2	1/2/11	3
3	1/3/11	4		2
4	1/4/11	4		3
5	1/5/11	3
6	1/6/11	3		4

Facility looks up values on Table2 to define itself. So Table2 is

ID	Name	Category
1	Garage	A
2	Office	B
3	Market	A
4	Museum	B

So getting a total of the hours for facilities is easy

Dim intHours, intHours2 as Integer
intHours = DSum("Hours", "Table1", "Facility = 1"
intHours2 = DSum("Hours, "Table1", "Facility = 1 AND Date >= #1/1/11# AND Date <= #1/3/11#")

So intHours = 4 and intHours2 = 4, because my example table is too small, but what I'm having trouble figuring out is how to get a sum of the hours entered where the Facility contains Category A on Table2. Table1 has thousands of entries and Table2 has around 500 so I either need to figure out a query that will return the records and Dsum that or figure out how to get Dsum to do the work for me. Something like:

SELECT Hours AS intHours3 FROM Table1 WHERE Facility = (SELECT ID FROM Table2 WHERE Category = 'A');

But Access seems to enjoy letting me know that subqueries can only return one record. So, even if the query ran it would only return the first record on Table2 rather than records 1 and 3.

Preferably the VBA code would work in both Access 2007 and 2010. Support for prior versions is irrelevant.

Any help is appreciated.

edit: Oh, and I'm a Sql/VBA noob. I've earned a living programming C++ in the past so it isn't hard for me to understand these things with a half decent explanation.

admanb

Use a join?

SELECT Hours AS intHours3 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Facility = t2.ID WHERE t2.Category = 'A'

Houndx

Edited the OP to reflect that I'm a Sql dummy. I was reading about JOIN and UNION earlier today, suspecting that is where I'd find my answer but all the pages I found on the subject were clear as mud. I'll continue reading so I can try and understand what's actually happening there.

Thanks for the answer Barring additional requests, that's one of the last things keeping my project from being completed.

Seguer

SELECT SUM(t1.HOURS) AS TotalHours FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Facility = t2.ID WHERE t2.Category = 'A'

SELECT SUM(t1.HOURS) AS TotalHours FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Facility = t2.ID WHERE t2.Category = 'A' AND t1.DATE >= #1/1/11# AND t1.Date <= #1/3/11#

A join is definitely the way to go here. For total hours you'll want to SUM() the hours column, this should only return you with one record (a total of the hours) due to the way the select/where conditions are setup.

Double check that Access uses SUM(), otherwise use its equivalent :P