Join query including calculated (count) field

[RogerTE via AccessMonster.com]

I am having problems creating a joined query in Access 2003.

I have 2 tables

One of which has the format
Group, Impact, SLA Hours, Mins
e.g.
GrpA, Critical, 1, 60
GrpA, Critical, 2, 120
GrpA, Critical, 4, 240
GrpA, Severe, 2, 120
GrpA, Severe, 4, 240
GrpA, Severe, 6, 360

The second table consists of a list of incidents format:
Group, Impact, Incident, TimeTakenMins
e.g.
GrpA, Critical, Inc001, 27
GrpA, Critical, Inc002, 1348
GrpA, Critical, Inc003, 138
GrpA, Critical, Inc004, 227

Not all of the records in the first table will necessarily have matching
records in the second table.

What I want to do is return all the records from the first table, and the
count of the records from the second table where the value of TimeTakenMins
is equal or less than the corresponding Mins

e.g.
GrpA, Critical, 1, 60, 1
GrpA, Critical, 2, 120, 1
GrpA, Critical, 4, 240, 3
GrpA, Severe, 2, 120, 0 (or null)
GrpA, Severe, 4, 240, 0 (or null)
GrpA, Severe, 6, 360, 0 (or null)

Whenever I have tried using the query builder it always returns only those
records which are in both tables, even if I specify option2 in the join
dialog.

Can anyone tell me where I am going wrong?

Many thanks

Roger

 

[KC-Mass]

Change your join. double click on the join line; in the panel that appears
select allrecords from table 1
matching records from table 2

Regards

 

[RogerTE via AccessMonster.com]

Thanks, but it seems it was the fact I had a "where" condition in the query
that was causing the join to fail. I created an intermediate query using the
where clause that returned just Group, Impact, Hours and Count. I then linked
the first query to this query and it worked as expected.

I daresay there are better ways of doing it, but it worked (eventually!)

Many thanks

Roger

Change your join. double click on the join line; in the panel that appears
select allrecords from table 1
matching records from table 2

Regards

I am having problems creating a joined query in Access 2003.

[quoted text clipped - 43 lines]

Roger