Two Sums, Three Table, and joins problem

[jbarbara11]

Hi,

I am using MS Access 2003. My tables and data are set up like this:

Table_One

Record_ID(pk)|   Record_Name|   Record_Status
123abc|     Test – red|    Active
456def|    Test – blue|    Active

Table_Two

ID(pk)|   Hours|        Date|                   ID_Record
111|         1|              10/1/2010|           123abc  
222|         4|              11/15/2010|         123abc  
333|         2|              11/20/2010|         123abc
444|         7|              11/25/2010|         456def
555|         3|              11/30/2010|         456def

Table_Three

UID(pk)| Units| Date, ID_Record
aaa| 1| 10/1/2010| 123abc
bbb| 6| 11/15/2010| 123abc
ccc| 8| 11/20/2010| 123abc
ddd| 4| 11/25/2010| 456def
eee| 4| 11/30/2010| 456def

I would like a single query (although that is not required), I currently have three queries but cannot even get those to work and need some help. These are the query results I want to get:

Record_Name|    SumOfHours|    Record_Status|    SumOfUnits
Test - blue|    10|    Active| 8
Test - red|    6|    Active| 14

Here are the queries I currently have:

qry_Two
SELECT Table_One.Record_ID, Table_One.Record_Name, Sum(Table_Two.Hours) AS SumOfHours, Table_One.Record_Status
FROM Table_One LEFT JOIN Table_Two ON Table_One.Record_ID=Table_Two.ID_Record
WHERE Table_Two.Date>=#10/31/2010#
GROUP BY Table_One.Record_ID, Table_One.Record_Name, Table_One.Record_Status;

qry_Three
SELECT Table_One.Record_ID, Table_One.Record_Name, Sum(Table_Three.Units) AS SumOfUnits, Table_One.Record_Status
FROM Table_One LEFT JOIN Table_Three ON Table_One.Record_ID=Table_Three.ID_Record
WHERE Table_Three.Date>=#10/31/2010#
GROUP BY Table_One.Record_ID, Table_One.Record_Name, Table_One.Record_Status;

qry_One - This is the combined query I run that calls the two queries above.
SELECT Table_One.Record_Name, qry_Two.SumOfHours, Table_One.Record_Status, SumOfUnits
FROM (Table_One INNER JOIN qry_Two ON Table_One.Record_ID=qry_Two.ID_Record) INNER JOIN qry_Three ON Table_One.Record_ID=qry_Three.ID_Record
GROUP BY Table_One.Record_Name, qry_Two.SumOfHours, Table_One.Record_Status, SumOfUnits;

I am getting prompted for the qry_Two.ID_Record and the qry_Three.ID_Record. Then, I get results only for either the records for 123abc, or 456def if I enter one of those twice. Example: enter 123abc for prompt of qry_Two.ID_Record and again for qry_Three.ID_Record produces the following:

Record_Name| SumOfHours| Record_Status| SumOfUnits
Test - red| 6| Active| 8
Test - red| 6| Active| 14
Test - red| 10| Active| 8
Test - red| 10| Active| 14

qry_Two executed by itself produces good results:

Record_Name|    SumOfHours|    Record_Status
Test - blue|    10|    Active
Test - red|    6|    Active

and so does qry_Three:

Record_Name|    SumOfUnits|    Record_Status
Test - blue|    8|    Active
Test - red|    14|    Active

Is there a way to get the following results for all the records without being prompted as well as not have duplicate lines? I am stuck.

Record_Name|    SumOfHours|    Record_Status|    SumOfUnits
Test - blue|    10|    Active| 8
Test - red|    6|    Active| 14

[weekend00]

It seems like the dates in table 2 are unique, if not, there will be problem.

Qry_One:

SELECT qry_two.Record_Name, qry_Two.SumOfHours, qry_two.Record_Status, SumOfUnits FROM qry_Two INNER JOIN qry_Three ON Qry_two.Record_ID=qry_Three.ID_Record