Display "Totals row" in a form?

[Mantispony]

Hi everyone!

I'm back with another (possibly silly) question:

How do I turn the totals row into an actual record, so that I can display it in a form? It should still function as the totals row, though (so "copy/paste into an empty record" is not what I'm looking for here).

The idea is that when filtering on a salesperson in the form, we'd display all his sales per month and per year (which I've got working fine), but I've been asked if it's possible to display that person's totals as well.

I'd copy/paste the total rows in a separate table, make another form and link the 2 forms, but I was just wondering if it's possible to display the totals row directly into a form. That way I could eliminate copy/paste errors, AND it would automatically be up-to-date..

Bottom line: how (if at all possible) do I display the Totals Row as-is in a form as if it was a regular record?

EDIT: I'm not familiar with VBA, so if the solution requires VBA, please explain it like you're talking to a 3-year-old

 

[BobMcClellan]

Reporting should really be done on the front end side but..
if you wanted to create a view to be used in a form....

I use SQL for everything but in the qbe grid you could build 3 queries.

You can use a union query. Have one query that aggregates the totals, and one query that shows the detail. Then make a union query to display them both....
In SQL it would be something like......

Select *
from (

select Details.ID, Details.FirstName, Details.LastName, Details.Data
From
[YourTable]

union

Select
ID , FirstName = "Total", LastName = "Dollars", TotalData = Sum([Data])
From [YourTable]
Group by ID
)x
where ID = YourParam
order by YourSort. '-- you would add a column for sort and make totals the last row.

just an idea... food for thought...
hth,
..bob

 

[Mantispony]

Thanks bob, I'll play around with that for a while.. (it's looking a bit like spaghetti to me right now, but I'm sure I'll work it out eventually..)