Roll up values in an Access query

I receive daily spreadsheet submittals with cost information. I enter the
information into an Access table. Sometimes I don't receive a submittal. I
want to use a query to produce a complete report. The query should use the
current value if available. If the current value isn't available then the
query should use the most recent information available. How do I roll up
values in a query?

Thanks,

"most recent" implies that you have date/time information.

If your Access table has an amount field and a date/time field, it seems
likely you could use a Totals query (see Access HELP) to get the most recent
date/time. NOTE: do NOT use the First or Last aggregator, as these are
internal to how Access stores data in tables. Instead, you want to use the
MAXIMUM (of your date/time field).

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
I receive daily spreadsheet submittals with cost information. I enter the
information into an Access table. Sometimes I don't receive a submittal.
I
want to use a query to produce a complete report. The query should use
the
current value if available. If the current value isn't available then the
query should use the most recent information available. How do I roll up
values in a query?

Thanks,

Thanks Jeff,

I think you got me onto the right track. The Max function does find the
most recent date. Unfortunately, my task is more complicated than my
question. My report is not limited to a single day; it displays several. So
now I am trying to to devise a query that produces the max value that is less
than or equal to all the reporting dates. Right now I have queries feeding
queries with some promise but I haven't quite reached the Promised Land.
Please let me know if you have an elegant solution..

Thanks again,

David

"Jeff Boyce" wrote:

"most recent" implies that you have date/time information.

If your Access table has an amount field and a date/time field, it seems
likely you could use a Totals query (see Access HELP) to get the most recent
date/time. NOTE: do NOT use the First or Last aggregator, as these are
internal to how Access stores data in tables. Instead, you want to use the
MAXIMUM (of your date/time field).

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
I receive daily spreadsheet submittals with cost information. I enter the
information into an Access table. Sometimes I don't receive a submittal.
I
want to use a query to produce a complete report. The query should use
the
current value if available. If the current value isn't available then the
query should use the most recent information available. How do I roll up
values in a query?

Thanks,

We're not there, we can't see your data (structure), we don't have examples
to infer from.

I don't understand "the max value that is less than or equal to all the
reporting dates."

Are you saying you want a single value, or one from each reporting date?
How do you determining "reporting dates"?

--
More info, please ...

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
Thanks Jeff,

I think you got me onto the right track. The Max function does find the
most recent date. Unfortunately, my task is more complicated than my
question. My report is not limited to a single day; it displays several.
So
now I am trying to to devise a query that produces the max value that is
less
than or equal to all the reporting dates. Right now I have queries
feeding
queries with some promise but I haven't quite reached the Promised Land.
Please let me know if you have an elegant solution..

Thanks again,

David

"Jeff Boyce" wrote:

"most recent" implies that you have date/time information.

If your Access table has an amount field and a date/time field, it seems
likely you could use a Totals query (see Access HELP) to get the most
recent
date/time. NOTE: do NOT use the First or Last aggregator, as these are
internal to how Access stores data in tables. Instead, you want to use
the
MAXIMUM (of your date/time field).

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
I receive daily spreadsheet submittals with cost information. I enter
the
information into an Access table. Sometimes I don't receive a
submittal.
I
want to use a query to produce a complete report. The query should
use
the
current value if available. If the current value isn't available then
the
query should use the most recent information available. How do I roll
up
values in a query?

Thanks,

HI Jeff,

There are about 20 reporting entities. Each entity submits a spreadsheet
with several columns and rows. The rows represent about 80 cost categories;
for example personnel or transportation. The columns also represent
different classifications; for example different Fiscal Years. We also
capture the reporting date.

The entities are asked to submit a report daily. Sometimes they do and
sometimes they don't. So we end up with a table of data with gaps in the
reporting dates. The summary report includes a time series with the current
date and a few dates before now. In the query, the value for today would be
today's submittal value if there was one. If an entity didn't submit today
then the query should look backward to the most recent submittal for that
entity. Your suggestion to use the Max Date finds that value.

The complication I alluded to in my last note was finding data for the prior
dates that are included in the report. Max date works fine for today's data.
But for yesterday's data it would be the max date that is less than or equal
to yesterday.

I hope that helps.

David

"Jeff Boyce" wrote:

We're not there, we can't see your data (structure), we don't have examples
to infer from.

I don't understand "the max value that is less than or equal to all the
reporting dates."

Are you saying you want a single value, or one from each reporting date?
How do you determining "reporting dates"?

--
More info, please ...

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
Thanks Jeff,

I think you got me onto the right track. The Max function does find the
most recent date. Unfortunately, my task is more complicated than my
question. My report is not limited to a single day; it displays several.
So
now I am trying to to devise a query that produces the max value that is
less
than or equal to all the reporting dates. Right now I have queries
feeding
queries with some promise but I haven't quite reached the Promised Land.
Please let me know if you have an elegant solution..

Thanks again,

David

"Jeff Boyce" wrote:

"most recent" implies that you have date/time information.

If your Access table has an amount field and a date/time field, it seems
likely you could use a Totals query (see Access HELP) to get the most
recent
date/time. NOTE: do NOT use the First or Last aggregator, as these are
internal to how Access stores data in tables. Instead, you want to use
the
MAXIMUM (of your date/time field).

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
I receive daily spreadsheet submittals with cost information. I enter
the
information into an Access table. Sometimes I don't receive a
submittal.
I
want to use a query to produce a complete report. The query should
use
the
current value if available. If the current value isn't available then
the
query should use the most recent information available. How do I roll
up
values in a query?

Thanks,

The Max([YourDateField]) when [YourDateField] is less than today's date
sounds like a selection criterion added to the query.

Or are you saying you want each/every date's number to be the most recent
date's value prior to THAT date's date?

Won't your time series analysis be skewed if you are using made-up values
(most-recent-previous)?

Could you create a query that shows the actual values/dates (including the
'holes'), then use a bit of code to step through each row, copying the
last-most-recent value into the next open hole?

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
HI Jeff,

There are about 20 reporting entities. Each entity submits a spreadsheet
with several columns and rows. The rows represent about 80 cost
categories;
for example personnel or transportation. The columns also represent
different classifications; for example different Fiscal Years. We also
capture the reporting date.

The entities are asked to submit a report daily. Sometimes they do and
sometimes they don't. So we end up with a table of data with gaps in the
reporting dates. The summary report includes a time series with the
current
date and a few dates before now. In the query, the value for today would
be
today's submittal value if there was one. If an entity didn't submit
today
then the query should look backward to the most recent submittal for that
entity. Your suggestion to use the Max Date finds that value.

The complication I alluded to in my last note was finding data for the
prior
dates that are included in the report. Max date works fine for today's
data.
But for yesterday's data it would be the max date that is less than or
equal
to yesterday.

I hope that helps.

David

"Jeff Boyce" wrote:

We're not there, we can't see your data (structure), we don't have
examples
to infer from.

I don't understand "the max value that is less than or equal to all the
reporting dates."

Are you saying you want a single value, or one from each reporting date?
How do you determining "reporting dates"?

--
More info, please ...

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
Thanks Jeff,

I think you got me onto the right track. The Max function does find
the
most recent date. Unfortunately, my task is more complicated than my
question. My report is not limited to a single day; it displays
several.
So
now I am trying to to devise a query that produces the max value that
is
less
than or equal to all the reporting dates. Right now I have queries
feeding
queries with some promise but I haven't quite reached the Promised
Land.
Please let me know if you have an elegant solution..

Thanks again,

David

"Jeff Boyce" wrote:

"most recent" implies that you have date/time information.

If your Access table has an amount field and a date/time field, it
seems
likely you could use a Totals query (see Access HELP) to get the
most
recent
date/time. NOTE: do NOT use the First or Last aggregator, as these
are
internal to how Access stores data in tables. Instead, you want to
use
the
MAXIMUM (of your date/time field).

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
I receive daily spreadsheet submittals with cost information. I
enter
the
information into an Access table. Sometimes I don't receive a
submittal.
I
want to use a query to produce a complete report. The query
should
use
the
current value if available. If the current value isn't available
then
the
query should use the most recent information available. How do I
roll
up
values in a query?

Thanks,

Hi Jeff,

First an admission. I came up with a solution; I’m just not proud of it.
The group we report to specified a reporting format in Excel. I had expected
to do collection, storage and processing in Access then export to Excel. The
solution (for now) was to export a raw table to Excel. Then use Excel pivot
tables to organize the data. The pivot tables have the date holes we talked
about so there is a second set of Excel tables that use the pivot-table value
if it isn’t zero and the value from the previous date if the pivot-table
value is zero. That provides the roll-up.

So the Access solution is academic. However I think your second paragraph
accurately describes the situation:

“Or are you saying you want each/every date's number to be the most recent
date's value prior to THAT date's date?�

I’ll work on it as time allows…

Thanks again,

David


"Jeff Boyce" wrote:

The Max([YourDateField]) when [YourDateField] is less than today's date
sounds like a selection criterion added to the query.

Or are you saying you want each/every date's number to be the most recent
date's value prior to THAT date's date?

Won't your time series analysis be skewed if you are using made-up values
(most-recent-previous)?

Could you create a query that shows the actual values/dates (including the
'holes'), then use a bit of code to step through each row, copying the
last-most-recent value into the next open hole?

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
HI Jeff,

There are about 20 reporting entities. Each entity submits a spreadsheet
with several columns and rows. The rows represent about 80 cost
categories;
for example personnel or transportation. The columns also represent
different classifications; for example different Fiscal Years. We also
capture the reporting date.

The entities are asked to submit a report daily. Sometimes they do and
sometimes they don't. So we end up with a table of data with gaps in the
reporting dates. The summary report includes a time series with the
current
date and a few dates before now. In the query, the value for today would
be
today's submittal value if there was one. If an entity didn't submit
today
then the query should look backward to the most recent submittal for that
entity. Your suggestion to use the Max Date finds that value.

The complication I alluded to in my last note was finding data for the
prior
dates that are included in the report. Max date works fine for today's
data.
But for yesterday's data it would be the max date that is less than or
equal
to yesterday.

I hope that helps.

David

"Jeff Boyce" wrote:

We're not there, we can't see your data (structure), we don't have
examples
to infer from.

I don't understand "the max value that is less than or equal to all the
reporting dates."

Are you saying you want a single value, or one from each reporting date?
How do you determining "reporting dates"?

--
More info, please ...

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
Thanks Jeff,

I think you got me onto the right track. The Max function does find
the
most recent date. Unfortunately, my task is more complicated than my
question. My report is not limited to a single day; it displays
several.
So
now I am trying to to devise a query that produces the max value that
is
less
than or equal to all the reporting dates. Right now I have queries
feeding
queries with some promise but I haven't quite reached the Promised
Land.
Please let me know if you have an elegant solution..

Thanks again,

David

"Jeff Boyce" wrote:

"most recent" implies that you have date/time information.

If your Access table has an amount field and a date/time field, it
seems
likely you could use a Totals query (see Access HELP) to get the
most
recent
date/time. NOTE: do NOT use the First or Last aggregator, as these
are
internal to how Access stores data in tables. Instead, you want to
use
the
MAXIMUM (of your date/time field).

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
I receive daily spreadsheet submittals with cost information. I
enter
the
information into an Access table. Sometimes I don't receive a
submittal.
I
want to use a query to produce a complete report. The query
should
use
the
current value if available. If the current value isn't available
then
the
query should use the most recent information available. How do I
roll
up
values in a query?

Thanks,

David

I tend to go for a solution first, elegant as time allows. Congratulations
on finding a solution!

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
Hi Jeff,

First an admission. I came up with a solution; I’m just not proud of it.
The group we report to specified a reporting format in Excel. I had
expected
to do collection, storage and processing in Access then export to Excel.
The
solution (for now) was to export a raw table to Excel. Then use Excel
pivot
tables to organize the data. The pivot tables have the date holes we
talked
about so there is a second set of Excel tables that use the pivot-table
value
if it isn’t zero and the value from the previous date if the pivot-table
value is zero. That provides the roll-up.

So the Access solution is academic. However I think your second paragraph
accurately describes the situation:

“Or are you saying you want each/every date's number to be the most recent
date's value prior to THAT date's date?�

I’ll work on it as time allows…

Thanks again,

David


"Jeff Boyce" wrote:

The Max([YourDateField]) when [YourDateField] is less than today's date
sounds like a selection criterion added to the query.

Or are you saying you want each/every date's number to be the most
recent
date's value prior to THAT date's date?

Won't your time series analysis be skewed if you are using made-up
values
(most-recent-previous)?

Could you create a query that shows the actual values/dates (including
the
'holes'), then use a bit of code to step through each row, copying the
last-most-recent value into the next open hole?

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
HI Jeff,

There are about 20 reporting entities. Each entity submits a
spreadsheet
with several columns and rows. The rows represent about 80 cost
categories;
for example personnel or transportation. The columns also represent
different classifications; for example different Fiscal Years. We
also
capture the reporting date.

The entities are asked to submit a report daily. Sometimes they do
and
sometimes they don't. So we end up with a table of data with gaps in
the
reporting dates. The summary report includes a time series with the
current
date and a few dates before now. In the query, the value for today
would
be
today's submittal value if there was one. If an entity didn't submit
today
then the query should look backward to the most recent submittal for
that
entity. Your suggestion to use the Max Date finds that value.

The complication I alluded to in my last note was finding data for the
prior
dates that are included in the report. Max date works fine for
today's
data.
But for yesterday's data it would be the max date that is less than
or
equal
to yesterday.

I hope that helps.

David

"Jeff Boyce" wrote:

We're not there, we can't see your data (structure), we don't have
examples
to infer from.

I don't understand "the max value that is less than or equal to all
the
reporting dates."

Are you saying you want a single value, or one from each reporting
date?
How do you determining "reporting dates"?

--
More info, please ...

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
Thanks Jeff,

I think you got me onto the right track. The Max function does
find
the
most recent date. Unfortunately, my task is more complicated than
my
question. My report is not limited to a single day; it displays
several.
So
now I am trying to to devise a query that produces the max value
that
is
less
than or equal to all the reporting dates. Right now I have
queries
feeding
queries with some promise but I haven't quite reached the Promised
Land.
Please let me know if you have an elegant solution..

Thanks again,

David

"Jeff Boyce" wrote:

"most recent" implies that you have date/time information.

If your Access table has an amount field and a date/time field,
it
seems
likely you could use a Totals query (see Access HELP) to get the
most
recent
date/time. NOTE: do NOT use the First or Last aggregator, as
these
are
internal to how Access stores data in tables. Instead, you want
to
use
the
MAXIMUM (of your date/time field).

--
Regards

Jeff Boyce
Office/Access MVP

"4110" wrote in message
...
I receive daily spreadsheet submittals with cost information.
I
enter
the
information into an Access table. Sometimes I don't receive a
submittal.
I
want to use a query to produce a complete report. The query
should
use
the
current value if available. If the current value isn't
available
then
the
query should use the most recent information available. How
do I
roll
up
values in a query?

Thanks,