If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dcount
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. -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
dcount
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. "bmalak via AccessMonster.com" u44362@uwe wrote in message news:a41336adae253@uwe... 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. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
dcount
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; -- Daryl S "bmalak via AccessMonster.com" wrote: 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. -- Message posted via http://www.accessmonster.com . |
#4
|
|||
|
|||
dcount
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. Jeff Boyce wrote: 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. -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
dcount
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... Daryl S wrote: 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. -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
dcount
It works...typo in field name.
Thanks for the help, Daryl!!! Daryl S wrote: 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. -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
dcount
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. "bmalak via AccessMonster.com" u44362@uwe wrote in message news:a413df86a6347@uwe... 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. Jeff Boyce wrote: 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. -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
dcount
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. Jeff Boyce wrote: 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] Thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#9
|
|||
|
|||
dcount
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 wrote: Thanks for the response, Jeff. Another method would be great, except manual input, of course. Do you have an alternative method in mind? |
#10
|
|||
|
|||
dcount
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]; -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|