Query miscalculations, why?

[Guest]

So I was almost done with this entire databse, and then i discovered a major
flaw. When there are two records with the same date, it throws off the entire
query. Most of the fields, for some reason, increase exponentially. When the
records have different dates, the query looks like:

Date SumofHours
1/1 1
1/2 2

When they have the same, it looks like:

Date SumofHours
1/1 192
1/1 192

etc...i have a much larger query, but i imagine you get the point. Now, if i
change the query fields to "group by" instead of "sum" I get the real values,
but I also get exponentially more rows in the query. So even if there are
only 2 records stored in the form, the query returns something like 98 rows
of records.

Anyway, i'll paste the SQL from the query below, and any help would be much
appreciated!

SELECT [Litter Totals].Date, [Route Totaling].Type, Sum([Parking Lot
Totals].[Parking Hours Totals]) AS [SumOfParking Hours Totals], Sum([Spaces
Totals].[Parking Spaces Total]) AS [SumOfParking Spaces Total], Sum([Leaf
Hours Totals].[Leaf Hours Totals]) AS [SumOfLeaf Hours Totals], Sum([Litter
Totals].[Litter Totals]) AS [SumOfLitter Totals], Sum([Bike Totals_1].[Bike
Paths Totals]) AS [SumOfBike Paths Totals], Sum([Misc Totals].[Misc Hours
Totals]) AS [SumOfMisc Hours Totals], Sum(LeafLoads.LeafLoadTotals) AS
SumOfLeafLoadTotals, Sum([Route Totaling].Sweephours) AS SumOfSweephours,
IIf([Type]="RES",[Sweephours],0) AS [Res Totals],
IIf([Type]="BUS",[Sweephours],0) AS [Bus Totals],
IIf([Type]="IND",[Sweephours],0) AS [Ind Totals],
IIf([Type]="ART",[Sweephours],0) AS [Art Totals], [Route Totaling].Curbmiles,
IIf([Type]="BUS",[Curbmiles],0) AS [Bus Miles],
IIf([Type]="ART",[Curbmiles],0) AS [Art Miles],
IIf([Type]="RES",[Curbmiles],0) AS [Res Miles],
IIf([Type]="IND",[Curbmiles],0) AS [Ind Miles], Sum([LeaveTraining
Totals].NonProdHours) AS SumOfNonProdHours, [LeaveTraining Totals].[Sick
Totals], [LeaveTraining Totals].[Flt Totals], [LeaveTraining Totals].[Vac
Totals], [LeaveTraining Totals].[PB Totals], [LeaveTraining Totals].[Hol
Totals], [LeaveTraining Totals].[7100 Totals], [LeaveTraining Totals].[7200
Totals]
FROM [LeaveTraining Totals] INNER JOIN ([Route Totaling] RIGHT JOIN
(LeafLoads INNER JOIN ([Misc Totals] INNER JOIN ([Leaf Hours Totals] INNER
JOIN ([Bike Totals] AS [Bike Totals_1] INNER JOIN ((([Bike Totals] INNER JOIN
[Litter Totals] ON [Bike Totals].Date = [Litter Totals].Date) INNER JOIN
[Spaces Totals] ON [Litter Totals].Date = [Spaces Totals].Date) INNER JOIN
[Parking Lot Totals] ON [Spaces Totals].Date = [Parking Lot Totals].Date) ON
[Bike Totals_1].Date = [Parking Lot Totals].Date) ON [Leaf Hours Totals].Date
= [Bike Totals_1].Date) ON [Misc Totals].Date = [Leaf Hours Totals].Date) ON
LeafLoads.Date = [Misc Totals].Date) ON [Route Totaling].Date =
LeafLoads.Date) ON [LeaveTraining Totals].Date = LeafLoads.Date
GROUP BY [Litter Totals].Date, [Route Totaling].Type,
IIf([Type]="RES",[Sweephours],0), IIf([Type]="BUS",[Sweephours],0),
IIf([Type]="IND",[Sweephours],0), IIf([Type]="ART",[Sweephours],0), [Route
Totaling].Curbmiles, IIf([Type]="BUS",[Curbmiles],0),
IIf([Type]="ART",[Curbmiles],0), IIf([Type]="RES",[Curbmiles],0),
IIf([Type]="IND",[Curbmiles],0), [LeaveTraining Totals].[Sick Totals],
[LeaveTraining Totals].[Flt Totals], [LeaveTraining Totals].[Vac Totals],
[LeaveTraining Totals].[PB Totals], [LeaveTraining Totals].[Hol Totals],
[LeaveTraining Totals].[7100 Totals], [LeaveTraining Totals].[7200 Totals]
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));

 

[John Vinson]

So I was almost done with this entire databse, and then i discovered a major
flaw. When there are two records with the same date, it throws off the entire
query. Most of the fields, for some reason, increase exponentially. When the
records have different dates, the query looks like:

You're joining ten different tables by the (badly misnamed, as it's a
reserved word) field Date. It sounds like one or more of these tables
contain multiple records for each date; when you Sum (or group by)
these records, you'll get as many copies of each "one" side table
record as there are matching records in the other table.

That is, if you have tableMain joined to TableA and TableB; and there
are ten records matching the tableMain ID in TableA, and five records
matching in TableB, you'll get all fifty possible combinations, and
you'll see the fields in tableMain repeated fifty times; and a Sum of
a field in tableMain will return 50 times the (single) value in the
table.


John W. Vinson[MVP]

 

[Guest]

I follow you. I know the database could be better organized, but this my
first Access database, so I tried to organize it in the best way I knew how.
Anyway, I think I know what you are saying. I have a number of queries that
sum up different sections of my form/table, and those queries feed into a
master (QryDataParameter), which is used in my reports. So my question is,
how do I go about fixing this problem? I realize there are matching records
because the date fields in all the queries are linked, and there about 12
queries. Thanks much for you help John!
- Evan

 

[John Vinson]

I follow you. I know the database could be better organized, but this my
first Access database, so I tried to organize it in the best way I knew how.
Anyway, I think I know what you are saying. I have a number of queries that
sum up different sections of my form/table, and those queries feed into a
master (QryDataParameter), which is used in my reports. So my question is,
how do I go about fixing this problem? I realize there are matching records
because the date fields in all the queries are linked, and there about 12
queries. Thanks much for you help John!
- Evan

Without knowing more about the structure of the data, it's hard to
say; but here's a couple of avenues to explore:

- Create 12 Totals queries, each on a single table, summing the
records appropriately. Rather than joining the *tables* to the main
table, join the queries, with their sums already done.

- Use a Report with 12 Subreports.

John W. Vinson[MVP]

 

[Guest]

John,
After experimenting with my report (which I had believed was now working)
I've discovered some odd problems. When I either change the detail section to
be invisible, or the records have DIFFERENT dates (ironically, it used to be
that the old report didn't work when there were two records that had the SAME
date, but I believe that's an unrelated problem.) the report sums cease to be
a summation of all records, but only a summation of the data of a single
record in the stack.

As for the report structure: I took your advice and created five subreports,
which are each based on a query (each query is based on a section of my
form/table). Each subreport has a "Day" field, followed by the peices of info
I need in my report. I selected the "Running Sum - Over All" option, for each
of these subreport fields besides "day."

Then, in the main report footer (not the individual subreport footers), I
have field that are supposed to sum all those fields, with the data source
being something like: "=[Bike Litter Totals subreport].Report!Bike" for
example.

So, as an example of what is going wrong:
When the report is working (such as when the only records it is searching
through have the same date) the Bike Totals field is 62.5; however, when I
set the detail section to be invisible, that number becomes 30.5. And when I
change the date so that the records have different date, this happens in some
fields but not others (the Bike field, for example, stays correct.). Any
ideas? I know this is a tough problem to figure out without seeing it
firsthand, but I'm having trouble fixing it, and I can see firsthand! So any
ideas would be great, thanks so much John, and let me know if you need more
info.

- Evan