Report shows 16 records when only supposed to show 1

[halt4814]

Hi all,

I have a report that when displayed should show information from various forms. On my report design I have 3 subforms, which the report is pulling data from.

The strange part is that each record displayed in the report shows the same information over and over 16 total times. I was thinking it was some kind of grouping error, but I do not know how to go about fixing this issue. Any ideas?

[Dal Jeanis]

Usually, this will be the result of a query that is improperly joined, something called a cross-join.

Suppose you have two tables: T1 has 12 records in it and T2 has 16 records in it.

If you have a query that looks like this:

SELECT T1.field1, T2.Field2
FROM T1, T2
WHERE T1.field1 = "george";

Then you're going to get 16 records that all say "george" - 1 for each record in T2.

The proper query will look more like this

SELECT T1.field1, T2.Field2
FROM T1, T2
WHERE T1.field1 = "george"
AND T1.Matchfield = T2.MatchField;

Or the same query also might look like this

SELECT T1.field1, T2.Field2
FROM T1 
        INNER JOIN T2
        ON T1.Matchfield = T2.MatchField
WHERE T1.field1 = "george";

[halt4814]

This is my query that the report is based off...

SELECT PLANT_AREAS.PA_NAME, PLANT_AREAS.ASSOC_ROOM_LIST, PLANT_AREAS.PA_DESCR, FIXED.FC_HEATCONTENT, TRANSIENT.TC_HEATCONTENT, CABLE_TRAYS.CT_HC, PLANT_AREAS.FLOOR_AREA, PLANT_AREAS.PA_COMMENT
FROM ((PLANT_AREAS INNER JOIN FIXED ON PLANT_AREAS.[PA_NAME] = FIXED.[PA_NAME]) INNER JOIN TRANSIENT ON PLANT_AREAS.[PA_NAME] = TRANSIENT.[PA_NAME]) INNER JOIN CABLE_TRAYS ON PLANT_AREAS.[PA_NAME] = CABLE_TRAYS.[PA_NAME]
WHERE (((PLANT_AREAS.PA_NAME) Like [Forms]![Combustible Summary Report Parameters]![Combo45]));


So, I have some INNER JOIN's in there, but it still displays 16 records when I only want one....

[Dal Jeanis]

Okay, so (1) check each table and make sure that [PA_NAME] is a unique key, duplicates not allowed.

(2) something looks squirrelly about the join, given the names.

I'm assuming that PA_NAME is the name of a area in the plant, and that Fixed is a one-to-many list of (Items of Some Sort) that are in fixed areas of the plant, while TRANSIENT is a one-to-many list of (Items of Some Sort) that moves from place to place in the plant as needed. If that's true, then it's nonsensical to ever join FIXED to TRANSIENT. I'd expect a similar design error with CABLE_TRAYS.

THE only way your join makes sense is if there is only ever one Fixed, one Transient, and one CableTray for each area of the plant. Is that the actual business situation?

[halt4814]

Ok so I took out the INNER JOINs, and it worked. Only one record is displayed, however now the table fields that I had in the header section are blank. Nothing is populating in those fields now.

[Dal Jeanis]

If you explain what each of those tables actually mean, and which of them can have duplicates, I can tell you what I'd do to display them. I think I've worked it out, but you can tell me if I'm wrong.

It's likely, given the field names, that you are trying to display the total heat load. That's the "SUM" in each section below. That calculation will give you the sum of all the heat content records of each type, if any records of that heat category type exist for that Plant Area, or return zero if none exist.

You could also switch each SUM to MAX (or MIN, or AVG) in the code below will give you the single record of each type with the largest (or smallest, or average) heat content, if any records of that heat category type exist for that Plant Area, or return zero if none exist.

SELECT 
   tPA.PA_NAME, 
   tPA.ASSOC_ROOM_LIST, 
   tPA.PA_DESCR, 
   NZ((SELECT SUM(tF.FC_HEATCONTENT) FROM FIXED as tF 
        WHERE (tPA.[PA_NAME] = tF.[PA_NAME]))) AS FC_HEATCONTENT,
   NZ((SELECT SUM(tT.TC_HEATCONTENT) FROM TRANSIENT as tT
       WHERE (tPA.[PA_NAME] = tT.[PA_NAME]))) AS TC_HEATCONTENT,
   NZ((SELECT SUM(tCT.CT_HC) FROM CABLE_TRAYS AS tCT
       WHERE (tPA.[PA_NAME] = tT.[PA_NAME]))) AS CT_HC,
   tPA.FLOOR_AREA, 
   tPA.PA_COMMENT
FROM  PLANT_AREAS as tPA 
WHERE (tPA.[PA_NAME]) Like [Forms]![Combustible Summary Report Parameters]![Combo45]));

[orange]

Do you have a data model (tables and relationships diagram)?
Perhaps you could post a copy as a jpg.