Sum/Query problem

[Guest]

So here's the situation. I have a data entry form used to store data
(obviously). I then have 5-6 queries that are used to sum up the different
categories of the form horizontally. Ex:<BR>

Date Street 1 Street 2 Street Total<BR>
1/5 4 2 6<BR>

So the last column of each Query is a horizontal sum of the other queries
using a nz() expression with all the other column names. Then, I have a
master query that I would like to use to sum all the categories together,
with the date they are tied to. However, when I use the master query, the
values are way too high. It seems that for every value of 1 that is entered
in the original form (or table) it multiplies that by 4096. The master query
consists of the date, and the Summation columns from the category queries
(which are all set to sum, so that each date only generates one entry. Also,
there is criteria in the date column of the master query so that I set the
date parameters for the query through a form. Here is the SQL of the master
query:<BR>

SELECT [Litter Totals].Date, Sum([Spaces Totals].[Parking Spaces Total]) AS
[SumOfParking Spaces Total], Sum([Litter Totals].[Litter Totals]) AS
[SumOfLitter Totals], Sum([Parking Lot Totals].[Parking Hours Totals]) AS
[SumOfParking Hours Totals]
FROM [Bike Totals], [Litter Totals], [Spaces Totals], [Parking Lot Totals]
GROUP BY [Litter Totals].Date
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));
<BR>


Thanks! I've been struggling with this for a while and any help is much
appreciated.
- Evan

 

[Guest]

There are no joins between the tables, so what happen is, each tables records
multiply by the other table.
You have to join the tables.

 

[Guest]

That's it! Sorry I had related tables I didn't realize I had to join. Feel
like such an amateur...

There are no joins between the tables, so what happen is, each tables records
multiply by the other table.
You have to join the tables.

So here's the situation. I have a data entry form used to store data
(obviously). I then have 5-6 queries that are used to sum up the different
categories of the form horizontally. Ex:<BR>

Date Street 1 Street 2 Street Total<BR>
1/5 4 2 6<BR>

So the last column of each Query is a horizontal sum of the other queries
using a nz() expression with all the other column names. Then, I have a
master query that I would like to use to sum all the categories together,
with the date they are tied to. However, when I use the master query, the
values are way too high. It seems that for every value of 1 that is entered
in the original form (or table) it multiplies that by 4096. The master query
consists of the date, and the Summation columns from the category queries
(which are all set to sum, so that each date only generates one entry. Also,
there is criteria in the date column of the master query so that I set the
date parameters for the query through a form. Here is the SQL of the master
query:<BR>

SELECT [Litter Totals].Date, Sum([Spaces Totals].[Parking Spaces Total]) AS
[SumOfParking Spaces Total], Sum([Litter Totals].[Litter Totals]) AS
[SumOfLitter Totals], Sum([Parking Lot Totals].[Parking Hours Totals]) AS
[SumOfParking Hours Totals]
FROM [Bike Totals], [Litter Totals], [Spaces Totals], [Parking Lot Totals]
GROUP BY [Litter Totals].Date
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));
<BR>


Thanks! I've been struggling with this for a while and any help is much
appreciated.
- Evan