Solved Total of totals in a query

[Garren.Shannon]

Hey all,

Another beginner question. I have a simple query that pulls trips by bus by day and totals the trips for all buses by the type of ridership... i.e. basic, sped, etc.

How can I make the table show totals at the bottom of each column so we know what the totals are for the year?

The SQL for this query is as follows.

SELECT Trip.[T-Date], Sum(Trip.[Basic-Count]) AS [SumOfBasic-Count], Sum(Trip.[Sped-Count]) AS [SumOfSped-Count], Sum(Trip.[HS-Count]) AS [SumOfHS-Count], Sum(Trip.[Walk-Count]) AS [SumOfWalk-Count], Sum(Trip.MaxCount) AS SumOfMaxCount
FROM SchoolYrDates, Vehicles INNER JOIN ([Trans-Type] INNER JOIN Trip ON [Trans-Type].CodeID = Trip.CodeID) ON Vehicles.VehicleID = Trip.VehicleID
WHERE (((SchoolYrDates.CurrentYear)=True))
GROUP BY Trip.[T-Date], SchoolYrDates.SchoolYRStart, SchoolYrDates.SchoolYrEnd
HAVING (((Trip.[T-Date]) Between [SchoolYrDates]![SchoolYRStart] And [SchoolYrDates]![SchoolYrEnd]))
ORDER BY Trip.[T-Date];

 

[June7]

Click the Sigma (Totals) button on the Home tab Records section.

Or build a report and use its Sorting & Grouping with aggregate calcs. This will allow display of raw data records as well as summary calcs.

 

[Garren.Shannon]

I am already summing on that report. It is totaling all the trips per day. I need it to sum up all the data by column now. Sounds like I need to delve into reports next.

 

[arnelgp]

not in your Query design.
create a Datasheet form for your query and display it.
click the Totals (sigma) on the ribbon while you are viewing the datasheet form (ribbon->home->records->Totals)

 

[Garren.Shannon]

Got it... THAT was simple. So much to learn still but that really helped. Thank you

I owe you a libation of your choice now...

 

[June7]

The Sigma works on tables, queries, and datasheet form. But your users should not work directly with tables and queries.

 

[Garren.Shannon]

Thanks June7... I am working to keep the users from direct access of the data. Locking this down will be a future question.