Queries

  • Thread starter Thread starter Loi
  • Start date Start date
L

Loi

Hi,
I have two tables which has one to many relationship.

The table A(primary table- one) has field named
HOSPITALNAME.

At second table B(Many), the HOSPITALNAME becomes foreign
field.

For example,

Table A
HOSPITALNAME: Carle, Clinic and Chirstie.

Table B
HOSPITALNAME VISITED DATE
Carle 06/12/04
Carle 06/13/04
Chirstie 06/13/05
Chirstie 06/12/04

I like to create a querie which shows:

TOTAL: Carle = 2
Chirstie = 2
and Clinic =0 ( because clinic is not in the table B)
I used Crosstab query. It shows that carle=2, Chirstie=2,
but it not include Clinic=0.

I have tried many diffirent ways, but it didn't work ( It
not shows Clinic=0)

Please help me.
Loi
 
Loi -

It sounds to me like your relationship needs a left outer join to include all the records in table A: Here's the sql I dummied up in to replicate your situation.

SELECT a.HospitalName, Count(b.visited_date) AS CountOfdate
FROM a LEFT JOIN b ON a.HospitalName = b.HospitalName
GROUP BY a.HospitalName;

Hope this helps
 
Back
Top