sql query help! COUNT function

[desi90]

Hi,
I have a following report created from a table

REGION Issue count
South C1 3
South C2 1
West C1 4
North C1 2
North C2 5
East C1 1
East C2 1

Notice that for region west, there is no entry from issue 'c2'. how can
I make sure that report produce a row for every issue and region.
so in this case the report should produce one more row with the
followin entry
West C2 0.

I am creating this report from table which has the following structude

City Region Issue Encounterd?
City1 South C1 Yes
City2 South C2 Yes
City3 West C1 Yes
City4 West C2 No

any help is appreciated

[desi90]

can anyone help?

[Trevor Best]

desi90 wrote:

Hi,
I have a following report created from a table

REGION Issue count
South C1 3
South C2 1
West C1 4
North C1 2
North C2 5
East C1 1
East C2 1

Notice that for region west, there is no entry from issue 'c2'. how can
I make sure that report produce a row for every issue and region.
so in this case the report should produce one more row with the
followin entry
West C2 0.

I am creating this report from table which has the following structude

City Region Issue Encounterd?
City1 South C1 Yes
City2 South C2 Yes
City3 West C1 Yes
City4 West C2 No

any help is appreciated

You need a table of Issues and use a left join to the regions.

--
This sig left intentionally blank

[Tiga]

I think what you are after can be achieved with an iif statement.
Something like =Iif([FieldName] is null,0,[Fieldname]) This will
ensure a zero and hence a row will be returned.

[pietlinden]

Yes, someone can help. But you gotta be patient sometimes. I mean,
this thing doesn't get updated instantly... and folks answer when they
have time. Keep yer britches on!

your biggest problem is that if your data tables are not structured
correctly, you CANNOT get data that doesn't exist. You need to design
properly for that. If you had a table of Cities, a table of regions,
then a table of Issues and a table of Encountered Issues, you'd be
fine. You could inner join where you wanted to knock out no matches,
then outer join where you wanted to keep the nulls and you'd be home
free.

Simple answer... join your existing table to a table of ALL the regions
and issues

SELECT Regions.RegionN ame, Issues.IssueNam e
FROM Regions, Issues
ORDER BY Regions.RegionN ame, Issues.IssueNam e)

yes, note the LACK of any join between the tables. It will create a
cartesian product (all possible combinations of RegionName and
IssueName). And then you can LEFT JOIN to that to get the missing
parts. (Use the Find Unmatched Query Wizard...)

If my answer confuses you, try looking up my post from like 6 years ago
that Keri Hardwick answered... maybe that'll help.

[desi90]

please help

[desi90]

please help

[ja]

On 22 Mar 2005 07:36:38 -0800, pi********@hotm ail.com wrote:

SELECT Regions.RegionN ame, Issues.IssueNam e
FROM Regions, Issues
ORDER BY Regions.RegionN ame, Issues.IssueNam e)

Try adding this to the same query
SELECT Regions.RegionN ame, Issues.IssueNam e
FROM Regions, Issues
GROUP BY Regions.RegionN ame
ORDER BY Regions.RegionN ame, Issues.IssueNam e