Is there something wrong with this?

  • Thread starter Thread starter Kath
  • Start date Start date
K

Kath

I use this in my query, and produce a report for the list
of schools I don't yet have catalogs. Somehow the report
comes out wrong with multiple lines of the same school
wiht the same copy year. (see example below) the SQL code.
SELECT DISTINCTROW [TBL_Catalog Status].[Institution
Name], [TBL_Catalog Status].[Copy Year], [TBL_Catalog
Status].Received, [TBL_Catalog Status].Evaluated,
TBL_Schools.Type, TBL_Schools.[School Code], [TBL_CC
Address].FNAME, [TBL_CC Address].LNAME, [TBL_CC
Address].Title, [TBL_CC Address].CONTACTOFFICE, [TBL_CC
Address].STR1, [TBL_CC Address].STR2, [TBL_CC
Address].CITY, [TBL_CC Address].STATE, [TBL_CC
Address].ZIPC
FROM (TBL_Schools INNER JOIN [TBL_Catalog Status] ON
TBL_Schools.[Institution Name] = [TBL_Catalog Status].
[Institution Name]) INNER JOIN [TBL_CC Address] ON
[TBL_Catalog Status].[Institution Name] = [TBL_CC
Address].SCHOOLNAME
ORDER BY [TBL_Catalog Status].Received;


This report looks totally wrong with
BCC 2003 - 2004
BCC 2003 - 2004
CCC 2001 - 2003
CCC 2001 - 2003
As they exist only once in the table. what is done wrong
in my code for the consequent report to become like such?

Any help, suggestions are appreciated!
Kath
 
Kath said:
I use this in my query, and produce a report for the list
of schools I don't yet have catalogs. Somehow the report
comes out wrong with multiple lines of the same school
wiht the same copy year. (see example below) the SQL code.
SELECT DISTINCTROW [TBL_Catalog Status].[Institution
Name], [TBL_Catalog Status].[Copy Year], [TBL_Catalog
Status].Received, [TBL_Catalog Status].Evaluated,
TBL_Schools.Type, TBL_Schools.[School Code], [TBL_CC
Address].FNAME, [TBL_CC Address].LNAME, [TBL_CC
Address].Title, [TBL_CC Address].CONTACTOFFICE, [TBL_CC
Address].STR1, [TBL_CC Address].STR2, [TBL_CC
Address].CITY, [TBL_CC Address].STATE, [TBL_CC
Address].ZIPC
FROM (TBL_Schools INNER JOIN [TBL_Catalog Status] ON
TBL_Schools.[Institution Name] = [TBL_Catalog Status].
[Institution Name]) INNER JOIN [TBL_CC Address] ON
[TBL_Catalog Status].[Institution Name] = [TBL_CC
Address].SCHOOLNAME
ORDER BY [TBL_Catalog Status].Received;


This report looks totally wrong with
BCC 2003 - 2004
BCC 2003 - 2004
CCC 2001 - 2003
CCC 2001 - 2003
As they exist only once in the table. what is done wrong
in my code for the consequent report to become like such?

Any help, suggestions are appreciated!
Kath
Click to expand...

It looks like each school record has more than one Catalog Status record, or
more than one Address record.

The quick fix is to use SELECT DISTINCT instead of SELECT DISTINCTROW, but I
confess that I don't understand your database design.
 
Hi Kath,

Some of those joins look a little odd given the names of the fields. What
is it you are trying to show? Schools that have catalogs, schools without?

You could use a LEFT join of tblSchools and tblCatalogStatus on SchoolID, to
get schools with and without catalog data. You would need to have a unique
ID (primary key) for each school in the table tblSchools and a unique ID for
each catalog record in table tblCatalogStatus. Table tblCatalogStatus would
need to have the key from tblSchools included in it as the foreign key.

You can then use something like the following to get all schools and any
catalog data:

SELECT tblSchools.SchoolName AS [Shool Name], nz([CopyYear],"No Catalogs")
AS [Copy Year]
FROM tblSchools LEFT JOIN tblCatalogStatus ON tblCatalogStatus.SchoolID =
tblSchools.SchoolID
ORDER BY 2 DESC;

The output from the query is shown below. Note the nz() function allows you
to place any text you like in the place of a null value. In the sample,
School Two had no catalog data.
Shool Name Copy Year
School Two No Catalogs
School Five 2004
School Four 2004
School Four 2003
School Five 2003
School Four 2002
School One 2002
School Three 2002
School Three 2001
School One 2001
School One 2000
School Three 2000
School Three 1999
School Three 1998
School Three 1997
School Three 1996
School Three 1995



Jamie
 
Back
Top