Variable Field not wanted in Access Queries

[email.bernie]

Hello everybody,

I am currently writing a cross-tab query using the following details:

Level Allocated
tbl Contracts
Group By
Row Heading

LevelID
tbl Position Details
Group By
Column Heading

ContractID
tbl Contracts
Count
Value
The table structure is as follows:

tbl Contracts tbl Positions
------------- -------------
ContractID*
PositionID -------PositionID*
Level Allocated LevelID
Other fields... Other fields...

Where * denotes Primary Key and ----- denotes a Foreign Key Link.

A position has a levelID between 1 to 8.
A position with a contract can have Level Allocated from 1 to 8, but it
is not always necessary.

<Question 1>
At the moment, the cross-tab query shows the count results with all the
positions that are not Level Allocated as well.
But I only want to view the count of positions with contracts
(ContractID) that have Level Allocated. I basically want to get rid of
the second line in the table below. Is this possible?

<Question 2>
I also want the blank count fields to show with 0. Is this possible?

Here is the results table as shown:

Level Allocated | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| | 2 | | 4 | 3 | 10| 6 | 12|
2 | 1 | | | | | | | |
4 | | 1 | 1 | | 1 | | | |
6 | | | | 2 | 4 | | | |
7 | | 2 | | | 1 | 2 | | |
8 | | 2 | | | 1 | | 2 | |
I've been working on these issues for hours on end for two days now,
maybe I'm thinking TOO hard! Any help would be appreciated. Thanks!
Regards,
Bernard.

[David Schofield]

On 12 Jul 2005 23:45:29 -0700, em**********@gm ail.com wrote:

<snip>

<Question 2>
I also want the blank count fields to show with 0. Is this possible?

Hi
Use Nz(xxx,0) rather than xxx as the field to show.
In code you can call Nz with only one argument and it defaults to 0 as
the second, but not in SQL as I found out the hard way.
David

[David Schofield]

On 13 Jul 2005 02:55:01 -0500, not@here (David Schofield) wrote:

On 12 Jul 2005 23:45:29 -0700, em**********@gm ail.com wrote:

<snip>

<Question 1>

..

Hi
Not sure I fullly understand Question 1 but I thibk you want something
like
TRANSFORM Count(nz([ContractId],0))
SELECT tblContracts.Le velallocated
FROM tblPositions INNER JOIN tblContracts ON tblPositions.Po sitionId =
tblContracts.Po sitionId
WHERE tblContracts.Le velallocated<>0
GROUP BY tblContracts.Le velallocated
PIVOT tblPositions.Le velId;

This assumed that Levelallocated is set to default to zero, otherwise
use
WHERE isNull(tblContr acts.Levelalloc ated)

David

[Rog]

Bernard:

To have zeroes instead of blanks, change ContractID to
NZ(Count([ContractID]),0) and change Count to Expression. Then, in the
criteria line of Level Allocated, type > 0.

Rog

[Rog]

(sorry if this is a double post, my first reply did not seem to go
through)

Bernard,

To have zeroes instead of blanks, change ContractID to
NZ(Count([ContractID]),0) and change Count to Expression. Then to get
rid of the blank (which is now 0), type >0 in the criteria of Level
Allocated.

Rog

[Pachydermitis]

Bernard,
I hope I understood your questions properly.
Q1 : put an 'is not null' criteria (no quotes) in the [level
allocationed] field to get rid of the blank line
Q2 : nz(count(Contra ctID),0) . . . then put 'Expression' in the total
Line
HTH
Pachydermitis

[pietlinden]

Q1
filter out the stuff you don't want with a select query, and base the
crosstab on that select query.

Q2
one way - base a query on this one and use NZ to convert the nulls to
zeroes.

[Gord]

1. Try specifying "Is Not Null" in the criteria row for the [Level
Allocated] column.

2. Instead of...

ContractID
tbl Contracts
Count
Value

....try...

Expr1: IIf(IsNull([Contracts]![ContractID]),0,1)
Sum
Value

[email.bernie]

Hi guys!
Thanks for all your help. It works wonders. I had no idea about the NZ
function.

Cheers!
Bernard