dcount

[bmalak via AccessMonster.com]

Having trouble getting dcount to work...

I have a table "providers" with the fields:
provider
[month begin date] (as date)
volume

In a query, want to see the
provider
month
MonthNumber (a sequential count of the months in sequential order)

I've tried but having #Error issues...I've concatenated [provider]&[month
begin date] as these are the "unique" value combinations to initiate a count.
Provider and Month begin date are unique values per row.

select
[provider]
,[month begin date]
,DCount("[provider]&[month begin date]","[providers]","[month begin date]
<=#" & [month begin date] & "#") as MonthNumber
from providers

So, if my data set were
prov1, 1/1/09, 321
prov1, 2/1/09, 456
prov2, 5/1/09, 789

The results would be...

prov1, 1/1/09, 1
prov1, 2/1/09, 2
prov2, 5/1/09, 1

Thanks in advance.

 

[Jeff Boyce]

Why are you using DCount()? Have you looked into using the Totals queries?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Daryl S]

Bmalak -

Try this - you just want to count the months, but restrict the records to
those with the same provider and month:

select
[provider]
,[month begin date]
,DCount("[month begin date]","[providers]","[provider] = '" & [Provider] &
"' AND [month begin date]
<=#" & [month begin date] & "#") as MonthNumber
from providers;

 

[bmalak via AccessMonster.com]

Why use "DCount?" I need the sequential order of the month based on a
"relative" measure for comparitive purposes.

Ultimately, what I'm going to be doing is then using this sequential order,
first, second, third, etc. in a cross-tab query to evaluate the volumes based
on the "relative" position of the month. In essence, regardless if the
provider's first month is 1/1/09 or 7/1/09, I can compare each provider's
first month volumes, their third months, or whatever.

Why are you using DCount()? Have you looked into using the Totals queries?

Regards

Jeff Boyce
Microsoft Access MVP

Having trouble getting dcount to work...

[quoted text clipped - 32 lines]

Thanks in advance.

 

[bmalak via AccessMonster.com]

Thanks, Daryl.

I'm still getting the #Error.

I've looked carefully at the double and single quote locations. It runs, so
it's not a syntax error.

It shouldn't have anything to do with Access 2003 as the app and using the
Access 2000 mdb format, I don't think.

Baffled...

Bmalak -

Try this - you just want to count the months, but restrict the records to
those with the same provider and month:

select
[provider]
,[month begin date]
,DCount("[month begin date]","[providers]","[provider] = '" & [Provider] &
"' AND [month begin date]
<=#" & [month begin date] & "#") as MonthNumber
from providers;

Having trouble getting dcount to work...

[quoted text clipped - 31 lines]

Thanks in advance.

 

[bmalak via AccessMonster.com]

It works...typo in field name.

Thanks for the help, Daryl!!!

Bmalak -

Try this - you just want to count the months, but restrict the records to
those with the same provider and month:

select
[provider]
,[month begin date]
,DCount("[month begin date]","[providers]","[provider] = '" & [Provider] &
"' AND [month begin date]
<=#" & [month begin date] & "#") as MonthNumber
from providers;

Having trouble getting dcount to work...

[quoted text clipped - 31 lines]

Thanks in advance.

 

[Jeff Boyce]

Thanks for the clarification...

So if you had another way to get the months in sequential order, that would
solve your issue?

Or if you had a way to identify the "third month after the current month"
(or "5th month before last month"), or other variations, you'd have a
solution?

I asked because I wasn't clear on what problem you were trying to use
DCount() to solve. Perhaps there is/was another tool...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Why use "DCount?" I need the sequential order of the month based on a
"relative" measure for comparitive purposes.

Ultimately, what I'm going to be doing is then using this sequential
order,
first, second, third, etc. in a cross-tab query to evaluate the volumes
based
on the "relative" position of the month. In essence, regardless if the
provider's first month is 1/1/09 or 7/1/09, I can compare each provider's
first month volumes, their third months, or whatever.

Why are you using DCount()? Have you looked into using the Totals
queries?

Regards

Jeff Boyce
Microsoft Access MVP

Having trouble getting dcount to work...

[quoted text clipped - 32 lines]

Thanks in advance.

 

[bmalak via AccessMonster.com]

Thanks for the response, Jeff.

Another method would be great, except manual input, of course. Do you have
an alternative method in mind?

I've gotten the DCount to work, but I can watch the screen refresh slowly as
it calcs. Building a cross-tab off this is abysmally slow...so much, in fact,
it's not usable. My real data set is 90k rows with about 9k unique providers
and it took one hour to run the cross-tab off of this DCount query as a
source, only to generate an error for some missing data in one of my rows.

The other caveat is that I need to count the rows in sequential order, within
the provider group, but to maintain a consistent order of 1, 2, 3, 4, etc.,
even if a month of volume does not exist for a provider.

So, in my example previously, if a month is skipped and I have...

So, if my data set were
prov1, 1/1/09, 321
prov1, 8/1/09, 456 <-- note the many months of gap from the prior record
prov2, 5/1/09, 789
...

The results still would be...

prov1, 1/1/09, 1
prov1, 8/1/09, 2
prov2, 5/1/09, 1
...

I wanted to avoid using a loop in VBA as I'm not so proficient...I'm trying
to go back to the data source and use the enterprise reporting system to
generate a number using a RunningCount() function...but it's an old version
of Business Objects and lacks some of the more advanced grouping and break
functionality associated with aggregates...arrrgghhh.

Thanks for the clarification...

So if you had another way to get the months in sequential order, that would
solve your issue?

Or if you had a way to identify the "third month after the current month"
(or "5th month before last month"), or other variations, you'd have a
solution?

I asked because I wasn't clear on what problem you were trying to use
DCount() to solve. Perhaps there is/was another tool...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Why use "DCount?" I need the sequential order of the month based on a
"relative" measure for comparitive purposes.

[quoted text clipped - 20 lines]

 

[John Spencer]

Do you have at most one record per month per provider? If so, you could use a
query similar to the one below. If you search for Ranking query, you should
find other examples in these newsgroups.

SELECT A.Provider
, A.[Month Begin Date]
, A.Volume
, 1 + Count(B.[Month Begin Date]) as MonthNumber
FROM [Providers] as A LEFT JOIN [Providers] As B
ON A.Provider = B.Provider
AND A.[Month Begin Date] > B.[Month Begin Date]
GROUP BY A.Provider
, A.[Month Begin Date]
, A.Volume

Obviously you don't need to include Month Begin Date in your query if you
don't need it. This should be much faster then using DCount.

IF you have more than one record per provider per month, you can build a query
to consolidate the data and then use that query as the source for the ranking
query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[bmalak via AccessMonster.com]

Ah, very clever. Thank you, John!!!

I did a little tweaking, based on your suggestion, to get exactly what I
wanted.
-took out the +1 in the count() expression
-moved the join for the date to the where clause
-changed the date "where" clause equality to ">="

PERFECT! Gracias!

SELECT
A.[provider]
, A.[Month Begin Date]
, Count(B.[Month Begin Date]) AS MonthNumber, A.[volume]

FROM
[providers] AS A LEFT JOIN [providers] AS B ON A.[provider]=B.[provider]

WHERE
A.[Month Begin Date]>=B.[Month Begin Date]

GROUP BY
A.[provider]
, A.[Month Begin Date]
, A.[volume]

ORDER BY
A.[provider]
, A.[Month Begin Date];