Problem With A Query

[q582gmzhi]

Hi,

I am trying to create a query that shows departments that have and have not reported any data.

Table 1: Departments (Contains 40 Records\Departments In Total)
Field 1 = fID
Field 2 = fName

Table 2: Reported Data (Contains 5000 Records From 40 Departments)
Field 1 = fID (Relates to Above fID)
Field 2 = DataReported

The query uses expressions for fields and then I group and sum the fields:
ie: IIf([DataReported]="A",1,0)

I can create a query that will show all departments who have reported data, this works fine.

However, I also need the same query to display the departments who have not reported any data.

Current Query Example:
A,10
B,6
E,12

What I Need To Display Example:
A,10
B,6
C,0 or Null
D,0 or Null
E,12

I have tried a number of different angles but I can't quite figure out how to do it?

Any advice is welcome.

Daz...

 

[FoFa]

Question:
You show "DateReported" as being "A".
Your example shows "A" (I assume this A is the DateReported A) has 10 (whatevers).
So where would this C and D come from if there is no "DateReported" to give you the C and D value?
Or maybe I misunderstand.

 

[q582gmzhi]

Hi,

I will see if I can explain this a little easier.

I have two tables:

Departments (List of departments)
Reported Data (Records created when a department reports info)

I can run a query on the "Reported Data" table that shows which departments reported information, because a record was created in the table "Reported Data" for the department.

However If a department did not report any information no record was created in the "Reported Data" table.

This is where I have a problem, because the query will only show which dealers reported info and not the ones who did not.

Thanks

Daz

 

[john471]

This Thread may be of interest, especially post #3.

Regards

John

 

[q582gmzhi]

John,

That works fine.

Thanks

Daz...

 

[john471]

You're welcome. Glad it helped. Thanks for replying back.

Regards

John.