Query Returns Too Many Records

[funderburgh]

I have created a simple query to calculate averages from data in the records of one table. I have no selection criteria in this query, I want to calculate averages for each record and use the resulting recordset as the basis for a report.

In my table there are 811 records, but the query returns 1271 records into the recordset.

The query is extremely straight forward, it simply consists of the fields I want to average and the calculated fields.

If someone can suggest why I am not getting exactly the number of records in the table that I am querying, I would be grateful for the assistance.

 

[jwlankford]

Could you post an example of the SQL statement? I suspect it has to do with not having a where clause or group by in the SQL.

 

[funderburgh]

Here is an ammendment to my question. In the query builder I had a related database, with data I intended to add on the report. At the time of the question posed above, it was not part of the query, just in the query builder. When I remove it, the query works as I expect, it returns 811 records. Why would the presence of the related table change the query results?

 

[funderburgh]

Here is the SQL:

SELECT Classes.[Class Name]
FROM (Students INNER JOIN Classes ON Students.StudentID = Classes.ID) INNER JOIN [Grade Level] ON Students.StudentID = [Grade Level].ID;

It is the presence of the Grade Level table (147 records) that results in the return of 1271 results. The Student table has 88 records.

Obviously I am not well versed in SQL, and I don't get it.

 

[pbaldy]

it was not part of the query, just in the query builder

If you'd looked at the SQL view of the query, it would have said:

FROM Table1, Table2

so the other table was part of the query. Without a join between the 2 tables, you would get unexpected results (Cartesian product).

 

[funderburgh]

Right, I get it now - the builder is generating the SQL not simply the fields I select. I am getting a Cartesian product, a concept new to me today. I tried monkeying with the joins in the SQL and wound up with over 10 million results.

Thanks for the direction, I now know what to go learn about.