Roll up values in an Access query

[Guest]

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,

 

[Jeff Boyce]

"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).

 

[Guest]

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]

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"?

 

[Guest]

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]

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?

 

[Guest]

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

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>

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]

David

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

Jeff Boyce
<Office/Access MVP>

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

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>

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

:

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>

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

:

"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>

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,