Criteria/If situation?

G

Guest

So I'm nearing completion of this extensive (well, extensive for me)
database, much thanks to all of you. I'm working on the last parts of the
report, and I've run into a problem:

I need to total the number of hours spent cleaning different types of roads
(summed and divided up by type), as well as the total miles cleaned (again,
summed and divided by type). So there is a daily reports table/form, where
sweepers enter their daily reports. There is also a related table that stores
info on the different routes (name, type, length). So I've figured out how to
get the report (which is based off a query) to sum everything up, but I can't
figure out how to get it to split off the different type of roads and sum
them seperately. Any ideas? Criteria in the query? If statements? Any help is
much appreciated. I'll paste the SQL from the master and route tables,
respectively, below:

SELECT [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], 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
FROM (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) INNER JOIN [Route
Totaling] ON LeafLoads.Date = [Route Totaling].Date
GROUP BY [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], [Route Totaling].Route
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));

SELECT master.Date, master.Sweephours, master.Route, master.Percentcomplete,
routes.Type, routes.Curbmiles, Nz([Percentcomplete])*([Curbmiles])/100 AS
[Route Totals]
FROM routes INNER JOIN master ON routes.Route = master.Route;

Thanks, any let me know if providing any additional info would help.
 
D

Duane Hookom

Do you really have all these different tables or are they separate queries?
This seems very un-normalized. I would probably combine the tables/queries
with a union query rather than INNER JOIN.

--
Duane Hookom
MS Access MVP


Evan Goldin said:
So I'm nearing completion of this extensive (well, extensive for me)
database, much thanks to all of you. I'm working on the last parts of the
report, and I've run into a problem:

I need to total the number of hours spent cleaning different types of
roads
(summed and divided up by type), as well as the total miles cleaned
(again,
summed and divided by type). So there is a daily reports table/form, where
sweepers enter their daily reports. There is also a related table that
stores
info on the different routes (name, type, length). So I've figured out how
to
get the report (which is based off a query) to sum everything up, but I
can't
figure out how to get it to split off the different type of roads and sum
them seperately. Any ideas? Criteria in the query? If statements? Any help
is
much appreciated. I'll paste the SQL from the master and route tables,
respectively, below:

SELECT [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], 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
FROM (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) INNER JOIN
[Route
Totaling] ON LeafLoads.Date = [Route Totaling].Date
GROUP BY [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], [Route Totaling].Route
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));

SELECT master.Date, master.Sweephours, master.Route,
master.Percentcomplete,
routes.Type, routes.Curbmiles, Nz([Percentcomplete])*([Curbmiles])/100 AS
[Route Totals]
FROM routes INNER JOIN master ON routes.Route = master.Route;

Thanks, any let me know if providing any additional info would help.
Click to expand...
 
G

Guest

I could do that, but doesn't the problem remain of the types still not
seperated?

Duane Hookom said:
Do you really have all these different tables or are they separate queries?
This seems very un-normalized. I would probably combine the tables/queries
with a union query rather than INNER JOIN.

--
Duane Hookom
MS Access MVP


Evan Goldin said:
So I'm nearing completion of this extensive (well, extensive for me)
database, much thanks to all of you. I'm working on the last parts of the
report, and I've run into a problem:

I need to total the number of hours spent cleaning different types of
roads
(summed and divided up by type), as well as the total miles cleaned
(again,
summed and divided by type). So there is a daily reports table/form, where
sweepers enter their daily reports. There is also a related table that
stores
info on the different routes (name, type, length). So I've figured out how
to
get the report (which is based off a query) to sum everything up, but I
can't
figure out how to get it to split off the different type of roads and sum
them seperately. Any ideas? Criteria in the query? If statements? Any help
is
much appreciated. I'll paste the SQL from the master and route tables,
respectively, below:

SELECT [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], 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
FROM (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) INNER JOIN
[Route
Totaling] ON LeafLoads.Date = [Route Totaling].Date
GROUP BY [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], [Route Totaling].Route
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));

SELECT master.Date, master.Sweephours, master.Route,
master.Percentcomplete,
routes.Type, routes.Curbmiles, Nz([Percentcomplete])*([Curbmiles])/100 AS
[Route Totals]
FROM routes INNER JOIN master ON routes.Route = master.Route;

Thanks, any let me know if providing any additional info would help.
Click to expand...
Click to expand...
 
D

Duane Hookom

Are "types of roads" the same as Route Types? If so, you should be able to
remove some fields from the query to group by Route Type and sum your other
fields.

--
Duane Hookom
MS Access MVP


Evan Goldin said:
I could do that, but doesn't the problem remain of the types still not
seperated?

Duane Hookom said:
Do you really have all these different tables or are they separate
queries?
This seems very un-normalized. I would probably combine the
tables/queries
with a union query rather than INNER JOIN.

--
Duane Hookom
MS Access MVP


Evan Goldin said:
So I'm nearing completion of this extensive (well, extensive for me)
database, much thanks to all of you. I'm working on the last parts of
the
report, and I've run into a problem:

I need to total the number of hours spent cleaning different types of
roads
(summed and divided up by type), as well as the total miles cleaned
(again,
summed and divided by type). So there is a daily reports table/form,
where
sweepers enter their daily reports. There is also a related table that
stores
info on the different routes (name, type, length). So I've figured out
how
to
get the report (which is based off a query) to sum everything up, but I
can't
figure out how to get it to split off the different type of roads and
sum
them seperately. Any ideas? Criteria in the query? If statements? Any
help
is
much appreciated. I'll paste the SQL from the master and route tables,
respectively, below:

SELECT [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], 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
FROM (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) INNER JOIN
[Route
Totaling] ON LeafLoads.Date = [Route Totaling].Date
GROUP BY [Litter Totals].Date, [Route Totaling].Sweephours, [Route
Totaling].Type, [Route Totaling].[Route Totals], [Route Totaling].Route
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));

SELECT master.Date, master.Sweephours, master.Route,
master.Percentcomplete,
routes.Type, routes.Curbmiles, Nz([Percentcomplete])*([Curbmiles])/100
AS
[Route Totals]
FROM routes INNER JOIN master ON routes.Route = master.Route;

Thanks, any let me know if providing any additional info would help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query miscalculations, why? 4
Total fields w/o the fields 1
Sum/Query problem 2
SubQuery or SubReport?? 13
Duplicates - Query Joining 1
Sum means empty query? 5
Total count for crosstab query 5
Rolling Totals for 6 month and 12 month 2

Top