Null Expression

[Steve]

I'm an access novice, but I've created a query using two tables, "Location"
and "Products Shipped". The query works fine but not all of the locations
ship all of the products. I want the "Location" table to populate with the
field name when I run the query. I've tried several IIF(IsNull) expressions
but I can't get it to work.

 

[Jeff Boyce]

We aren't there. We can't see your data. We don't know your subject area.

Please post the SQL statement your query uses...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve]

Excuse me, I thought that was enough information. Here is the expression
that I've been using but it doesn't work. Expr1: IIf(IsNull([Location
Codes]![Location Name], " ", [Location Codes]![Location Name]). The data
field that is blank is in another table but I want the location name to
always populate. I don't even know if I am placing this expression in the
correct place. Should it be in the field or criteria row.

 

[Jeff Boyce]

Steve

You provided the expression.

I still don't have any idea what your query looks like. Please post the SQL
statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Excuse me, I thought that was enough information. Here is the expression
that I've been using but it doesn't work. Expr1: IIf(IsNull([Location
Codes]![Location Name], " ", [Location Codes]![Location Name]). The data
field that is blank is in another table but I want the location name to
always populate. I don't even know if I am placing this expression in the
correct place. Should it be in the field or criteria row.

We aren't there. We can't see your data. We don't know your subject
area.

Please post the SQL statement your query uses...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Barrows [MVP]]

In order to post the SQL statement, you need to switch the query to SQL
View, via the View menu, or the toolbar button, or the right-click context
menu.

Excuse me, I thought that was enough information. Here is the
expression that I've been using but it doesn't work. Expr1:
IIf(IsNull([Location Codes]![Location Name], " ", [Location
Codes]![Location Name]). The data field that is blank is in another
table but I want the location name to always populate. I don't even
know if I am placing this expression in the correct place. Should it
be in the field or criteria row.

We aren't there. We can't see your data. We don't know your
subject area.

Please post the SQL statement your query uses...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve]

Thanks Bob. Here is the SQL statement on a different query with the same
problem.

SELECT [Location Codes].DIVISION, [Location Codes].[Location Name], [Current
Losses].[LOSS LINE], [Current Losses].[FISCAL YEAR], [Current Losses].[TOTAL
INCURRED], [Current Losses].[CLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses].[LOCATION CODE]

In order to post the SQL statement, you need to switch the query to SQL
View, via the View menu, or the toolbar button, or the right-click context
menu.

Excuse me, I thought that was enough information. Here is the
expression that I've been using but it doesn't work. Expr1:
IIf(IsNull([Location Codes]![Location Name], " ", [Location
Codes]![Location Name]). The data field that is blank is in another
table but I want the location name to always populate. I don't even
know if I am placing this expression in the correct place. Should it
be in the field or criteria row.

We aren't there. We can't see your data. We don't know your
subject area.

Please post the SQL statement your query uses...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm an access novice, but I've created a query using two tables,
"Location"
and "Products Shipped". The query works fine but not all of the
locations ship all of the products. I want the "Location" table to
populate with the
field name when I run the query. I've tried several IIF(IsNull)
expressions
but I can't get it to work.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

Thanks Bob. Here is the SQL statement on a different query with the
same problem.

?
I don't understand why you posted a different query. It's just going to
confuse things.

SELECT [Location Codes].DIVISION, [Location Codes].[Location Name],
[Current Losses].[LOSS LINE], [Current Losses].[FISCAL YEAR],
[Current Losses].[TOTAL INCURRED], [Current Losses].[CLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses].[LOCATION CODE]

In order to post the SQL statement, you need to switch the query to
SQL View, via the View menu, or the toolbar button, or the
right-click context menu.

Excuse me, I thought that was enough information. Here is the
expression that I've been using but it doesn't work. Expr1:
IIf(IsNull([Location Codes]![Location Name], " ", [Location
Codes]![Location Name]).

This statement looks all right. What does "doesn't work" mean? Please
describe your symptoms without using non-descriptive terms like "doesn't
work".

OK, I guess I still don't understand. Judging by the posted sql, this query
(products shipped) is likely to be using an inner join as well, correct?
That means you will only get records where the Location codes match between
the two tables. Are you saying that records exist in Location Codes with
Nulls in the Location Name field? Why would that be?

Or are you saying that you want to get all the locations, even if there was
nothing shipped for a particular location? If so, why would you test the
[Location Codes].[Location Name] field for null?

It should definitely be in the field row of a new column.
and "Products Shipped". The query works fine but not all of the locations
ship all of the products. I want the "Location" table to populate with the
field name when I run the query.

You want to populate what with the field name?

I think you should post a few rows of sample data from each table (just the
relevant fields to illustrate the problem - invent some data). Follow that
by the results you want your query to return from the sample data.

 

[Steve]

I still have the same problem. How do I create an expression so that all of
the locations are indicated even if their data is null?

Thanks Bob. Here is the SQL statement on a different query with the
same problem.

?
I don't understand why you posted a different query. It's just going to
confuse things.

SELECT [Location Codes].DIVISION, [Location Codes].[Location Name],
[Current Losses].[LOSS LINE], [Current Losses].[FISCAL YEAR],
[Current Losses].[TOTAL INCURRED], [Current Losses].[CLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses].[LOCATION CODE]

In order to post the SQL statement, you need to switch the query to
SQL View, via the View menu, or the toolbar button, or the
right-click context menu.

Steve wrote:
Excuse me, I thought that was enough information. Here is the
expression that I've been using but it doesn't work. Expr1:
IIf(IsNull([Location Codes]![Location Name], " ", [Location
Codes]![Location Name]).

This statement looks all right. What does "doesn't work" mean? Please
describe your symptoms without using non-descriptive terms like "doesn't
work".

OK, I guess I still don't understand. Judging by the posted sql, this query
(products shipped) is likely to be using an inner join as well, correct?
That means you will only get records where the Location codes match between
the two tables. Are you saying that records exist in Location Codes with
Nulls in the Location Name field? Why would that be?

Or are you saying that you want to get all the locations, even if there was
nothing shipped for a particular location? If so, why would you test the
[Location Codes].[Location Name] field for null?

It should definitely be in the field row of a new column.
and "Products Shipped". The query works fine but not all of the locations
ship all of the products. I want the "Location" table to populate with the
field name when I run the query.

You want to populate what with the field name?

I think you should post a few rows of sample data from each table (just the
relevant fields to illustrate the problem - invent some data). Follow that
by the results you want your query to return from the sample data.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

I still have the same problem. How do I create an expression so that
all of the locations are indicated even if their data is null?

I think you should post a few rows of sample data from each table
(just the relevant fields to illustrate the problem - invent some
data). Follow that by the results you want your query to return from
the sample data.

 

[Steve]

Here is the entire SQL:

SELECT [Location Codes].DIVISION, [Location Codes].[Location Name], [Current
Losses].[LOSS LINE], [Current Losses].[FISCAL YEAR], Sum([Current
Losses].[TOTAL INCURRED]) AS [SumOfTOTAL INCURRED], Count([Current
Losses].[CLAIM NUMBER]) AS [CountOfCLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses].[LOCATION CODE]
GROUP BY [Location Codes].DIVISION, [Location Codes].[Location Name],
[Current Losses].[LOSS LINE], [Current Losses].[FISCAL YEAR]
HAVING ((([Current Losses].[LOSS LINE])="WC") AND (([Current Losses].[FISCAL
YEAR])=2007));

Here is how the data is displayed with the exception being that the purple
region would not be displayed because their sum and count = 0. How do I get
the Division and the location of the purple region to be displayed and their
sum and count to be 0?

DIVISION Location Line FY SUM COUNT
ABC Blue Region WC 2007 1500 5
ABC Green Region WC 2007 1700 2
ABC Purple Region WC 2007 0 0
XYZ Pink Region WC 2007 2000 15

 

[Bob Barrows [MVP]]

Here is the entire SQL:

SELECT [Location Codes].DIVISION, [Location Codes]. [Location Name],
[Current Losses].[LOSS LINE], [Current Losses]. [FISCAL YEAR],
Sum([Current Losses].[TOTAL INCURRED]) AS [SumOfTOTAL INCURRED],
Count([Current Losses].[CLAIM NUMBER]) AS [CountOfCLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses]. [LOCATION CODE]
GROUP BY [Location Codes].DIVISION, [Location Codes].[Location Name],
[Current Losses]. [LOSS LINE], [Current Losses].[FISCAL YEAR]
HAVING ((([Current Losses].[LOSS LINE])="WC") AND (([Current
Losses].[FISCAL YEAR])=2007));

Here is how the data is displayed with the exception being that the
purple region would not be displayed because their sum and count = 0.
How do I get the Division and the location of the purple region to be
displayed and their sum and count to be 0?

DIVISION Location Line FY SUM COUNT
ABC Blue Region WC 2007 1500 5
ABC Green Region WC 2007 1700 2
ABC Purple Region WC 2007 0 0
XYZ Pink Region WC 2007 2000 15

Why are you making me guess?

I guess, extrapolating from what you described, that the Location Codes data
would look like this:
Location DIVISION Location
Code Name
1 ABC Blue Region
2 ABC Green Region
3 ABC Purple Region
4 XYZ Pink Region

Correct?
And the Current Losses data would look like this:

Location LOSS Fiscal TOTAL CLAIM
Code LINE Year INCURRED NUMBER
1 WC 2007 300 1
1 WC 2007 300 2
1 WC 2007 300 3
1 WC 2007 300 4
1 WC 2007 300 5
2 WC 2007 800 6
2 WC 2007 900 7
4 WC 2007 200 8
14 more records for code 4 totaling 2000 for total incurred

Do I have this correct? if so, you will need to use an outer join to a
subquery. Actually, a join to a saved query sounds like it might be a better
idea since it will make it easier to read and maintain the final result and
you may need to re-use that data aggregation. Start by creating a new query
with this sql (paste it into SQL View):
SELECT [LOCATION CODE], [LOSS LINE] As Line,
[FISCAL YEAR] As FiscYr,
Sum([TOTAL INCURRED]) AS [SumINCURRED],
Count([CLAIM NUMBER]) AS [CountCLAIMs]
FROM [Current Losses]
WHERE [LOSS LINE]="WC" And [FISCAL YEAR] = 2007
GROUP BY [LOCATION CODE], [LOSS LINE], [FISCAL YEAR]

Save the query as LossTotalsByLossLineAndFiscYr (or whatever you want to
call it).

Then, create a new query with this sql:
SELECT l.DIVISION, l.[Location Name],
Nz(c.[Line], "WC") As Loss Line,
Nz(c.[FiscYr], 2007) As Fiscal Year,
Nz(c.SumINCURRED,0) As Sum Of Total Incurred,
Nz(c.CountCLAIMs,0) As Count of Claims
FROM [Location Codes] As l LEFT OUTER JOIN
LossTotalsByLossLineAndFiscYr As c ON
l.[LOCATION CODE] = c.[LOCATION CODE]

Does that work?

 

[Steve]

Thanks that helps but I think I am in way over my head. I thought I could
create a simple expression using the IIf(IsNull) or by following some other
simple steps in the expression builder.

When I pasted the results, it looked fine. Sorry for making you guess

Thanks again for your help.

Here is the entire SQL:

SELECT [Location Codes].DIVISION, [Location Codes]. [Location Name],
[Current Losses].[LOSS LINE], [Current Losses]. [FISCAL YEAR],
Sum([Current Losses].[TOTAL INCURRED]) AS [SumOfTOTAL INCURRED],
Count([Current Losses].[CLAIM NUMBER]) AS [CountOfCLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses]. [LOCATION CODE]
GROUP BY [Location Codes].DIVISION, [Location Codes].[Location Name],
[Current Losses]. [LOSS LINE], [Current Losses].[FISCAL YEAR]
HAVING ((([Current Losses].[LOSS LINE])="WC") AND (([Current
Losses].[FISCAL YEAR])=2007));

Here is how the data is displayed with the exception being that the
purple region would not be displayed because their sum and count = 0.
How do I get the Division and the location of the purple region to be
displayed and their sum and count to be 0?

DIVISION Location Line FY SUM COUNT
ABC Blue Region WC 2007 1500 5
ABC Green Region WC 2007 1700 2
ABC Purple Region WC 2007 0 0
XYZ Pink Region WC 2007 2000 15

Why are you making me guess?

I guess, extrapolating from what you described, that the Location Codes data
would look like this:
Location DIVISION Location
Code Name
1 ABC Blue Region
2 ABC Green Region
3 ABC Purple Region
4 XYZ Pink Region

Correct?
And the Current Losses data would look like this:

Location LOSS Fiscal TOTAL CLAIM
Code LINE Year INCURRED NUMBER
1 WC 2007 300 1
1 WC 2007 300 2
1 WC 2007 300 3
1 WC 2007 300 4
1 WC 2007 300 5
2 WC 2007 800 6
2 WC 2007 900 7
4 WC 2007 200 8
14 more records for code 4 totaling 2000 for total incurred

Do I have this correct? if so, you will need to use an outer join to a
subquery. Actually, a join to a saved query sounds like it might be a better
idea since it will make it easier to read and maintain the final result and
you may need to re-use that data aggregation. Start by creating a new query
with this sql (paste it into SQL View):
SELECT [LOCATION CODE], [LOSS LINE] As Line,
[FISCAL YEAR] As FiscYr,
Sum([TOTAL INCURRED]) AS [SumINCURRED],
Count([CLAIM NUMBER]) AS [CountCLAIMs]
FROM [Current Losses]
WHERE [LOSS LINE]="WC" And [FISCAL YEAR] = 2007
GROUP BY [LOCATION CODE], [LOSS LINE], [FISCAL YEAR]

Save the query as LossTotalsByLossLineAndFiscYr (or whatever you want to
call it).

Then, create a new query with this sql:
SELECT l.DIVISION, l.[Location Name],
Nz(c.[Line], "WC") As Loss Line,
Nz(c.[FiscYr], 2007) As Fiscal Year,
Nz(c.SumINCURRED,0) As Sum Of Total Incurred,
Nz(c.CountCLAIMs,0) As Count of Claims
FROM [Location Codes] As l LEFT OUTER JOIN
LossTotalsByLossLineAndFiscYr As c ON
l.[LOCATION CODE] = c.[LOCATION CODE]

Does that work?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

The Nz function is shorthand for IIF(IsNull .... (look it up in online
help), so I did use it. What you did not realize was that you had to create
an outer join to get what you wanted.

Thanks that helps but I think I am in way over my head. I thought I
could create a simple expression using the IIf(IsNull) or by
following some other simple steps in the expression builder.

When I pasted the results, it looked fine. Sorry for making you
guess

Thanks again for your help.

Here is the entire SQL:

SELECT [Location Codes].DIVISION, [Location Codes]. [Location Name],
[Current Losses].[LOSS LINE], [Current Losses]. [FISCAL YEAR],
Sum([Current Losses].[TOTAL INCURRED]) AS [SumOfTOTAL INCURRED],
Count([Current Losses].[CLAIM NUMBER]) AS [CountOfCLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses]. [LOCATION CODE]
GROUP BY [Location Codes].DIVISION, [Location Codes].[Location
Name], [Current Losses]. [LOSS LINE], [Current Losses].[FISCAL YEAR]
HAVING ((([Current Losses].[LOSS LINE])="WC") AND (([Current
Losses].[FISCAL YEAR])=2007));

Here is how the data is displayed with the exception being that the
purple region would not be displayed because their sum and count =
0.
How do I get the Division and the location of the purple region to
be displayed and their sum and count to be 0?

DIVISION Location Line FY SUM COUNT
ABC Blue Region WC 2007 1500 5
ABC Green Region WC 2007 1700 2
ABC Purple Region WC 2007 0 0
XYZ Pink Region WC 2007 2000 15

Why are you making me guess?

I guess, extrapolating from what you described, that the Location
Codes data would look like this:
Location DIVISION Location
Code Name
1 ABC Blue Region
2 ABC Green Region
3 ABC Purple Region
4 XYZ Pink Region

Correct?
And the Current Losses data would look like this:

Location LOSS Fiscal TOTAL CLAIM
Code LINE Year INCURRED NUMBER
1 WC 2007 300 1
1 WC 2007 300 2
1 WC 2007 300 3
1 WC 2007 300 4
1 WC 2007 300 5
2 WC 2007 800 6
2 WC 2007 900 7
4 WC 2007 200 8
14 more records for code 4 totaling 2000 for total incurred

Do I have this correct? if so, you will need to use an outer join to
a subquery. Actually, a join to a saved query sounds like it might
be a better idea since it will make it easier to read and maintain
the final result and you may need to re-use that data aggregation.
Start by creating a new query with this sql (paste it into SQL View):
SELECT [LOCATION CODE], [LOSS LINE] As Line,
[FISCAL YEAR] As FiscYr,
Sum([TOTAL INCURRED]) AS [SumINCURRED],
Count([CLAIM NUMBER]) AS [CountCLAIMs]
FROM [Current Losses]
WHERE [LOSS LINE]="WC" And [FISCAL YEAR] = 2007
GROUP BY [LOCATION CODE], [LOSS LINE], [FISCAL YEAR]

Save the query as LossTotalsByLossLineAndFiscYr (or whatever you
want to call it).

Then, create a new query with this sql:
SELECT l.DIVISION, l.[Location Name],
Nz(c.[Line], "WC") As Loss Line,
Nz(c.[FiscYr], 2007) As Fiscal Year,
Nz(c.SumINCURRED,0) As Sum Of Total Incurred,
Nz(c.CountCLAIMs,0) As Count of Claims
FROM [Location Codes] As l LEFT OUTER JOIN
LossTotalsByLossLineAndFiscYr As c ON
l.[LOCATION CODE] = c.[LOCATION CODE]

Does that work?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"