Display Null Values - Please Help

[dennis.joseph]

Hello Everyone,
I have scoured through a million messages (ok...thats a bit
exaggerated) but I just cannot figure out how to display null values in
a query. I have tried Nz, IIF, Count(*) but it just does not work. I
hope someone can help me with this. Here are the details:

Table: Group
Fields: Group Name, Group ID

Table: Region
Fields Region Name, Region ID

Table: CIM Customer Table
Fields: Group Name, Region Name, Sales Presentation, Status

Objective: Get a count for each Group (3 groups in total) and each
Region (3 Regions each for each group) where Status <> 'Deployed' and
Sales Presentation = 'Complete'

I get the count, but it does not include those regions/groups where the
field is null. I like it to display a zero for nulls. Here is the SQL
that I am currently using:

SELECT [cim customer table].Group, [cim customer table].Region,
Count(*) AS SALES
FROM [cim customer table]
WHERE ((([cim customer table].[Sales Presentation])='Completed') AND
(([cim customer table].Status)<>'Deployed'))
GROUP BY [cim customer table].Group, [cim customer table].Region;

I tried using the group and region tables as against the group and
region fields in the customer table but it wouldn't work.

Pleae Help

Thanks

[Susan K]

Dennis,

First, Access will always display Null fields in a query if they are
there. However, if what you are after is to display records from a
table that is linked to a second table but has records that do not
match, that is an outer join. To create an outer join in Access, you
click on the line in the query window and right-click the line to get
to the link properties and select that you would want to see all the
records from the cim customer table and any records from the region or
group table that match.

Going back to your example, you have several issues going on. Based on
the way you set up the tables, I would think you were planning on using
the Region and Group tables as lookups. But in the CIM Customer table
you are using the Region Name and Group Name fields instead of the
Group ID and Region ID fields. You would usually have the id fields in
the parent table, link them to the lookup tables, and then use the
outer joins in the query to allow you to show the name if it is there
and a blank or null if not.

Susan Kennedy

[Susan K]

Dennis,

First, Access will always display Null fields in a query if they are
there. However, if what you are after is to display records from a
table that is linked to a second table but has records that do not
match, that is an outer join. To create an outer join in Access, you
click on the line in the query window and right-click the line to get
to the link properties and select that you would want to see all the
records from the cim customer table and any records from the region or
group table that match.

Going back to your example, you have several issues going on. Based on
the way you set up the tables, I would think you were planning on using
the Region and Group tables as lookups. But in the CIM Customer table
you are using the Region Name and Group Name fields instead of the
Group ID and Region ID fields. You would usually have the id fields in
the parent table, link them to the lookup tables, and then use the
outer joins in the query to allow you to show the name if it is there
and a blank or null if not.

Susan Kennedy

[dennis.joseph]

Susan,

Thanks for your quick response. I can fix the table such that it is
setup in the following fashion:

Table: Group
Fields: Group Name, Group ID

Table: Region
Fields Region Name, Region ID

Table: CIM Customer Table
Fields: Group ID, Region ID, Sales Presentation, Status

I tried the outer join but it gave me an error. Any ideas on what the
SQL will be for that. I am new to Access and so I'm sorry if my
questions are basic.

Thanks

Dennis

[Susan K]

SELECT tlkpGroup.GroupName, tlkpRegion.RegionName,
Count(tblCIMCustomer.CIMID) AS Sales
FROM (tblCIMCustomer LEFT JOIN tlkpGroup ON tblCIMCustomer.GroupID =
tlkpGroup.GroupID) LEFT JOIN tlkpRegion ON tblCIMCustomer.RegionID =
tlkpRegion.RegionID
WHERE (((tblCIMCustomer.SalesPresentation)='Completed') AND
((tblCIMCustomer.Status)<>'Deployed'))
GROUP BY tlkpGroup.GroupName, tlkpRegion.RegionName;

Notice that I would not name a table Group or Region. I believe
strongly in naming conventions, but even if you don't use anything,
Group is a reserved word and will cause you problems later.

Susan