summing records in sub form

[Mark Baker]

I have a sub form that can possibly contain many records each with fields
including 'year' and 'CAPEX value'. Problem:
There will be multiple records in the subform for each year and I only want
to sum the latest entry against EACH year?

I have been able to do this using DSum and having a check box against each
record that I want to use, but I would prefer it to automatically using the
latest entry for each year if possible. Need to erradicate potential for
human error as much as possible...

TIA
Mark

 

[Allen Browne]

If you want a total for each year, then there will be multiple totals for
the current record.

That would imply that you need a related subform (or at least a list box) to
show the totals. If you existing subform is in Continuous view, you may be
able to put a list box in its Form Footer section, and show the results
there. The Row Source of the list box would be a Totals query that gives the
total per year, with critieria that limit it to the record in the subform,
e.g.:
[Forms].[Form1].[Sub1].[Form].[CAPEX]

You may run into grief using a field named Year. That's a reserved word in
JET, as well as a function in VBA. For a list of the names to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

 

[Mark Baker]

Sorry prehaps I'm a little confusing today. I actually need 1 total, and
what i want summed in that total is the latest value for each year. Other
other data against the years is considered historical, but is required for
other purposes.

Thanks for list of names to avoid, very useful - although my field isn't
actually 'year'

cheers

If you want a total for each year, then there will be multiple totals for
the current record.

That would imply that you need a related subform (or at least a list box) to
show the totals. If you existing subform is in Continuous view, you may be
able to put a list box in its Form Footer section, and show the results
there. The Row Source of the list box would be a Totals query that gives the
total per year, with critieria that limit it to the record in the subform,
e.g.:
[Forms].[Form1].[Sub1].[Form].[CAPEX]

You may run into grief using a field named Year. That's a reserved word in
JET, as well as a function in VBA. For a list of the names to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a sub form that can possibly contain many records each with fields
including 'year' and 'CAPEX value'. Problem:
There will be multiple records in the subform for each year and I only
want
to sum the latest entry against EACH year?

I have been able to do this using DSum and having a check box against each
record that I want to use, but I would prefer it to automatically using
the
latest entry for each year if possible. Need to erradicate potential for
human error as much as possible...

TIA
Mark

 

[Allen Browne]

Okay, Mark, so you just want to show just the latest value for the matching
Capex + Year?

A text box bound to a DLookup() expression should do that, but DLookup() may
not be powerful enough to choose the latest match. (It just chooses the
first match.) Here's a replacement:
http://allenbrowne.com/ser-42.html
ELookup() has an extra argument indicating a sort order. By sorting
Descending on whatever field defines "latest", you can display the value
from the field you want.

Post back if that's still not what you want.

If this subform just displays answers (i.e. you don't need to be able to
edit data), a subquery would be more efficient:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry prehaps I'm a little confusing today. I actually need 1 total, and
what i want summed in that total is the latest value for each year. Other
other data against the years is considered historical, but is required for
other purposes.

Thanks for list of names to avoid, very useful - although my field isn't
actually 'year'

cheers

If you want a total for each year, then there will be multiple totals for
the current record.

That would imply that you need a related subform (or at least a list box)
to
show the totals. If you existing subform is in Continuous view, you may
be
able to put a list box in its Form Footer section, and show the results
there. The Row Source of the list box would be a Totals query that gives
the
total per year, with critieria that limit it to the record in the
subform,
e.g.:
[Forms].[Form1].[Sub1].[Form].[CAPEX]

You may run into grief using a field named Year. That's a reserved word
in
JET, as well as a function in VBA. For a list of the names to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

I have a sub form that can possibly contain many records each with
fields
including 'year' and 'CAPEX value'. Problem:
There will be multiple records in the subform for each year and I only
want
to sum the latest entry against EACH year?

I have been able to do this using DSum and having a check box against
each
record that I want to use, but I would prefer it to automatically using
the
latest entry for each year if possible. Need to erradicate potential
for
human error as much as possible...

 

[Mark Baker]

thanks for your patience, but I do need a sum function. I'm starting to
think I might not be able to do this... Anyway, if you can imagine the
following scenario:

Fields: Record Updated AP Year CAPEX
1 3/5/06 2007 3,000
2 1/3/07 2007 3,500
3 1/12/06 2008 4,000
4 1/10/06 2009 2,000

What i would need it to do in this case is sum the latest values from each
year. i.e. 3500+4000+2000. As in record 1 is superceded by record 2 as it
is the same year but an updated estimate of cost.


This have been previously achieved this by using a check box in each record
so the user selects the records that are to be summed, but as this will be
used by local council the potential for human error is pretty big - whoever
inputs the data is likely to forget to uncheck the previous 'latest' value.

Thanks again.

Okay, Mark, so you just want to show just the latest value for the matching
Capex + Year?

A text box bound to a DLookup() expression should do that, but DLookup() may
not be powerful enough to choose the latest match. (It just chooses the
first match.) Here's a replacement:
http://allenbrowne.com/ser-42.html
ELookup() has an extra argument indicating a sort order. By sorting
Descending on whatever field defines "latest", you can display the value
from the field you want.

Post back if that's still not what you want.

If this subform just displays answers (i.e. you don't need to be able to
edit data), a subquery would be more efficient:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry prehaps I'm a little confusing today. I actually need 1 total, and
what i want summed in that total is the latest value for each year. Other
other data against the years is considered historical, but is required for
other purposes.

Thanks for list of names to avoid, very useful - although my field isn't
actually 'year'

cheers

If you want a total for each year, then there will be multiple totals for
the current record.

That would imply that you need a related subform (or at least a list box)
to
show the totals. If you existing subform is in Continuous view, you may
be
able to put a list box in its Form Footer section, and show the results
there. The Row Source of the list box would be a Totals query that gives
the
total per year, with critieria that limit it to the record in the
subform,
e.g.:
[Forms].[Form1].[Sub1].[Form].[CAPEX]

You may run into grief using a field named Year. That's a reserved word
in
JET, as well as a function in VBA. For a list of the names to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

I have a sub form that can possibly contain many records each with
fields
including 'year' and 'CAPEX value'. Problem:
There will be multiple records in the subform for each year and I only
want
to sum the latest entry against EACH year?

I have been able to do this using DSum and having a check box against
each
record that I want to use, but I would prefer it to automatically using
the
latest entry for each year if possible. Need to erradicate potential
for
human error as much as possible...

 

[Allen Browne]

This kind of query will give you tot latest value for each year:

SELECT DISTINCT [AP Year],
(SELECT TOP 1 Capex FROM Table1 AS Dupe
WHERE Dupe.[AP Year] = Table1.[AP Year]
ORDER BY Dupe.Updated DESC, Dupe.[Record])
AS LatestYearValue
FROM Table1;

The text box on your form would then use:
=DSum("LatestYearValue", "Query1")

If there is actually another foreign key field involved here, you may need
to write a function in VBA that accepts that key value, adds it to the
subquery's WHERE clause, and returns the summed values.

The subquery link given previously explains the 2nd SELECT (inside the
brackets.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for your patience, but I do need a sum function. I'm starting to
think I might not be able to do this... Anyway, if you can imagine the
following scenario:

Fields: Record Updated AP Year CAPEX
1 3/5/06 2007 3,000
2 1/3/07 2007 3,500
3 1/12/06 2008 4,000
4 1/10/06 2009 2,000

What i would need it to do in this case is sum the latest values from each
year. i.e. 3500+4000+2000. As in record 1 is superceded by record 2 as
it
is the same year but an updated estimate of cost.


This have been previously achieved this by using a check box in each
record
so the user selects the records that are to be summed, but as this will be
used by local council the potential for human error is pretty big -
whoever
inputs the data is likely to forget to uncheck the previous 'latest'
value.

Thanks again.

Okay, Mark, so you just want to show just the latest value for the
matching
Capex + Year?

A text box bound to a DLookup() expression should do that, but DLookup()
may
not be powerful enough to choose the latest match. (It just chooses the
first match.) Here's a replacement:
http://allenbrowne.com/ser-42.html
ELookup() has an extra argument indicating a sort order. By sorting
Descending on whatever field defines "latest", you can display the value
from the field you want.

Post back if that's still not what you want.

If this subform just displays answers (i.e. you don't need to be able to
edit data), a subquery would be more efficient:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry prehaps I'm a little confusing today. I actually need 1 total,
and
what i want summed in that total is the latest value for each year.
Other
other data against the years is considered historical, but is required
for
other purposes.

Thanks for list of names to avoid, very useful - although my field
isn't
actually 'year'

cheers



:

If you want a total for each year, then there will be multiple totals
for
the current record.

That would imply that you need a related subform (or at least a list
box)
to
show the totals. If you existing subform is in Continuous view, you
may
be
able to put a list box in its Form Footer section, and show the
results
there. The Row Source of the list box would be a Totals query that
gives
the
total per year, with critieria that limit it to the record in the
subform,
e.g.:
[Forms].[Form1].[Sub1].[Form].[CAPEX]

You may run into grief using a field named Year. That's a reserved
word
in
JET, as well as a function in VBA. For a list of the names to avoid,
see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

I have a sub form that can possibly contain many records each with
fields
including 'year' and 'CAPEX value'. Problem:
There will be multiple records in the subform for each year and I
only
want
to sum the latest entry against EACH year?

I have been able to do this using DSum and having a check box
against
each
record that I want to use, but I would prefer it to automatically
using
the
latest entry for each year if possible. Need to erradicate
potential
for
human error as much as possible...