Missing records in a query.

[Christopherusly]

I have a query based on two tables.

Table one gives a list of staff time transactions i.e. hours spent on a particular job. Table two gives a list of the staff rates, so enable project costs to be calcuated - hence there is a relationship between the Staff Name fields of both tables.

IF i do not have rate for a member of staff in table 2 that appears in table 1, rather than display the record with a 0 value against it, it does not display the record at all.

Q: is there anyway to get round this ... ? without having to check the table 1 each time for matching it against table 2.

thanks guys.

 

[vbaInet]

If you want to include ALL records from table 1 you need to:

1. Go into the Relationships, right-click the line that joins the two tables and click Edit Relationships
2. Change the option to include ALL records from table 1
3. Then you would need to convert the Nulls for that field to zero using the Nz() function

 

[Christopherusly]

ahhhhhhhhhh, you are a STAR vbaINET thank you. one further question, how would i use NZ() ?

Cheers

Christophe

 

[vbaInet]

Lets call the field [Staff_Rate], in the query in design view it will be displayed as [Staff_Rate], change it to:

AliasRate: Nz([Staff_Rate], 0)

You can call AliasRate anything you want but it cannot be the same name as Staff_Rate

 

[Christopherusly]

AHHHHHHH thank you for your help, appreciated as always.

 

[vbaInet]

You're welcome Christophe.