Summing Nulls Problem

[StarScream]

Hypothetical:

You are having an international party. In MS Access you have a table with a list of each person, the country their from, and their jobtitle (Ambassador, President, Minister, Driver, etc).

If you query a list of countries who are bringing their Ambassador by setting ="Ambassador" in the criteria section then your query returns only those countries where that is true.

I want to return all countries, list all people and flag who is the Ambassador and who is the President (for example) in a report based upon that query. (Something where you need to highlight 2 or more people from a table of people based upon a specific job title.) I'm not talking about listing the [Job Title] field in the report. I want to say in the report:

Belgium
The President is .....
The Ambassador is .....
Then list all people coming from Belgium
(and do that for each country)

MY PROBLEM IS:
As soon as I add the column ="President" then I get no records since no one person (record) has a duty title of both Ambassador and President. I can't use an OR statement since I need to know each individual's name. If I have separate queries (one that just identifies each country's Amb, one for Pres), than link them to a master query, that only returns those countries where an Amb and a Pres are identified. Maybe some countries aren't sending their President (he hates your country). Since I'm using text, I'm dealing with nulls. I can't convert any nulls to some other text (using IIF IS NULL or NZ) since the nulls are not in an actual table field.

I am currently using something similar to the separate queries which means my report only shows that countries with both a Pres and an Ambassador.

Am I missing the obvious? Any suggestions? Thanks.

 

[KenReay]

Hi

Are you saying that different people have different job titles, but that in each case there is (or maybe) a job title which is equivalent to the title Ambassador, and a job title which is equivalement to the title President?

If yes, eitehr you need to put column(s) in the table to hold such indication, or (betetr in my view) you need a table which holds a list of valid job title, that table should include a column which indicates that that job title is equivalent to President etc, then you can via SQL pick out the people you require and in the report you can put the required wording

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[StarScream]

The SQL part is the part I am missing.

TABLE
Canada John President
Canada Steve Driver
Canada Pete Ambassador
Belgium Dana President
Belgium Paula Vice President
Germany Hans Secretary of State

If I set up the following query:

QUERY
Country Name JobTitle JobTitle
="President" ="Ambassador"

I want all 3 countries to come back and show a blank for those countries where a Pres or Amb haven't been identified.

RESULT
COL1 COL2 COL3 COL4
Belgium Dana Dana (blank)
Belgium Paula Dana (blank)
Canada John John Pete
Canada Steve John Pete
Canada Pete John Pete
Germany...etc...

In this example, the first column lists all countries, second column lists all names, 3rd column list that country's president, 4th column lists that country's ambassador. The actual query only returns Canada's info since that is the only country that identified a President and an Ambassador.

I just need to get an idea on where to look to make that happen. I tried an:

IIF(ISNULL([JobTitle]="President&quot, "None", [JobTitle])

and tried the NZ method too, and no luck.

Thoughts?

 

[BobJacksonNCI]

Recommend you post the actual SQL here as many sharp people visit.

I suspect null is the problem. You might check thread702-534470 for a discussion on it.

Think of null not as blank or empty, but "I don't know".
In a query, if a null is encountered in a field being used for selection criteria, that row will not be returned.

You may have to/want to restructure your database.
Using the country (preferably an ID field) as the primary key, you could have a separate table for Presidents, Ambassadors, etc.


HTH,
Bob
Thread181-473997 provides information regarding this site.

 

[BobJacksonNCI]

Alternatively, without changing your structure you could retrieve the data at print time by using DLOOKUP.

It DOES run SLOWLY, however.

Bob

 

[cmmrfrds]

This is untested but 3 of the columns could be correlated subqueries I believe. General idea below.

select A.Country,
(select Names = Name + ', ' From Yourtable where
Country = A.Country) as NameList,
(select JobTitle From Yourtable where
Country = A.Country and JobTitle="President&quot as President,
(select JobTitle From Yourtable where
Country = A.Country and JobTitle="Ambassador&quot as Ambassador
From yourtable as A
Group by Country