Count Distinct Weeks

[RachelBedi]

I really hope I'm posting in the correct place!



Here's a run down of what I need to do; I need to find the average number of activated companies within my database in a week - therefore I need to divide the total number of activations by the total number of weeks.

What I did was created a date table with date - month - week number and year (to create the week number I simply formatted the column in my table as WW and entered the date again)

Now I have linked my date table of "week" to the "Date Modified"(date activated) date in my master table in a query so that each "Date Modified" is linked to a "Week"

I need to count the number of distinct weeks however I think my table formatting may have messed that up. Is there any way I can do this? I really hope this isn't confusing and I apologize ahead of time if it is!

Please help!

[jzwp11]

Actually you do not need your date table. You can extract the information you need using the date related functions available in Access: month(datefield), year(datefield), datepart("ww",datefield) et.

average number of activated companies within my database in a week

Now since you are just looking for the average number of activated companies per week over a period of time, I do not think that you even need the week number. I'll assume that Date Modified is the date field in your table.

First, create a query that pulls the data for the period you want

query name: qryDetailForAPeriod
SELECT tblMaster.[date modified], tblMaster.txtClient, datediff("ww",[date1],[date2]) AS WeeksInPeriod
FROM tblMaster
WHERE (((tblMaster.[date modified]) Between [date1] And [date2]));
date1 and date2 are parameters that you would supply directly when you run the query. You can also replace date1 and date2 with references to controls on a form.

Now to get your count of activated companies over the period, you will need an aggregate query based on the above

query name: qryCountOfActivationsForAPeriod
SELECT Count(qryDetailForAPeriod.[date modified]) AS [CountOfdate modified], qryDetailForAPeriod.WeeksInPeriod
FROM qryDetailForAPeriod
GROUP BY qryDetailForAPeriod.WeeksInPeriod;


Now one final query to get the average:

SELECT qryCountOfActivationsForAPeriod.[CountOfdate modified]/qryCountOfActivationsForAPeriod.WeeksInPeriod AS AvgActivations
FROM qryCountOfActivationsForAPeriod;

You would only need to run this last query to get your average. It should prompt you to enter the beginning and ending dates of the period of interest

[RachelBedi]

Thank you so much for your response - I believe this will work unfortunately it's asking me for parameter values for Master Table.txtClient (my table name is Master Table - I know it's not the normal naming that most people use but I have never taken an access class so I was unaware of the standard naming until recently!)

Should it be:
SELECT [Master Table].[date modified], [Master Table].txtClient,datediff("ww",[date1],[date2]) AS WeeksInPeriod
FROM [Master Table]
WHERE ((([Master Table].[date modified]) Between [date1] And [date2]));

or would I use an _ there somewhere for the table?

Clearly there is user error here so any help would be appreciated!

[jzwp11]

Since your table name is Master Table is must be enclosed in the square brackets just as you show. You would only include the underscore if that is exactly how you named the table.

Is the field name actually txtClient? It has to be spelled exactly as it is in the table. Do you have a caption defined in properties of the field? If so, I would remove the caption and just go with the field name.

[RachelBedi]

I got it! Now - I would like to adjust this to supply me with the daily (weekday), and monthly - I started to write it but it's coming back with some funky numbers it's giving me a 4 for every line - I entered 7/1/12 - 8/1/12 for date 1 and date 2

SELECT [Master Table].[Date Modified], DateDiff("W",[date1],[date2]) AS DaysInPeriodFROM [Master Table]
WHERE ((([Master Table].[Date Modified]) Between [date1] And [date2]) AND (([Master Table].Status)="Activated"));

I really do appreciate all the help! I'm a little over my head so any help would be GREATLY appreciated!

[RachelBedi]

P.S. I got the weekly one to work! Is there any way I can give you credit for your help jzwp11?

[jzwp11]

I'm glad you got the weekly one figured out.

Are you still after the average? In other words are you after the average daily activations for a time period? If so, then you have to create a new group of queries

query name: qryDetailForAPeriodDays
SELECT tblMaster.[date modified], tblMaster.txtClient, datediff("d",[date1],[date2]) AS DaysInPeriod
FROM tblMaster
WHERE tblMaster.[date modified] Between [date1] And [date2] AND [Master Table].Status)="Activated"



query name: qryCountOfActivationsForAPeriodDays
SELECT Count(qryDetailForAPeriodDays.[date modified]) AS [CountOfdate modified], qryDetailForAPeriodDays.DaysInPeriod
FROM qryDetailForAPeriodDays
GROUP BY qryDetailForAPeriodDays.DaysInPeriod;


SELECT qryCountOfActivationsForAPeriodDays.[CountOfdate modified]/qryCountOfActivationsForAPeriodDays.DaysInPeriod AS AvgDailyActivations
FROM qryCountOfActivationsForAPeriodDays;

For the monthly averages:

query name: qryDetailForAPeriodMonths
SELECT tblMaster.[date modified], tblMaster.txtClient, datediff("m",[date1],[date2]) AS MonthsInPeriod
FROM tblMaster
WHERE tblMaster.[date modified] Between [date1] And [date2] AND [Master Table].Status)="Activated"



query name: qryCountOfActivationsForAPeriodMonths
SELECT Count(qryDetailForAPeriodMonths.[date modified]) AS [CountOfdate modified], qryDetailForAPeriodMonths.MonthsInPeriod
FROM qryDetailForAPeriodMonths
GROUP BY qryDetailForAPeriodMonths.MonthsInPeriod;


SELECT qryCountOfActivationsForAPeriodMonths.[CountOfdate modified]/qryCountOfActivationsForAPeriodMonths.MonthsInPeri od AS AvgMonthlyActivations
FROM qryCountOfActivationsForAPeriodMonths;

Please keep in mind that if your date range spans more than one month, you will combine data (ie Jan 2012 and 2013) if you run the query from 1/1/12 to say 2/1/13.

There are ways to handle the year and month combination, but the queries would need more modifications.

Is there any way I can give you credit for your help jzwp11?

A simple thank you is fine, but you can also add to my reputation. Check out the FAQ section under Settings and Profile Features--Reputation

[jzwp11]

I did find a flaw in my logic in the queries I posted earlier. For the monthly average, if you put in a date range of 1/1/12 to 12/31/12, the datediff("m",[date1],[date2]) function returns 11. To calculate the average correctly, we need to have the value of 12. To correct this, we would just need to add 1 to the value generated by the datediff() function. Also, I did forget to remove a parenthesis in my original query (shown in blue below). Also, I was not consistent in the table name (see green below)

query name: qryDetailForAPeriodMonths
SELECT tblMaster.[date modified], tblMaster.txtClient, datediff("m",[date1],[date2]) AS MonthsInPeriod
FROM tblMaster
WHERE tblMaster.[date modified] Between [date1] And [date2] AND [Master Table].Status)="Activated"

So with the adjustments, the query should look like this:

SELECT tblMaster.[date modified], tblMaster.txtClient, 1+datediff("m",[date1],[date2]) AS MonthsInPeriod
FROM tblMaster
WHERE tblMaster.[date modified] Between [date1] And [date2] AND tblMaster.Status)="Activated"


Of course, you will have to substitute your own table and field names in the query



I am guessing that we would have to make the +1 adjustment for the query by week as well as the query by day. I'll let you verify this with your data to make sure.

[RachelBedi]

Hi again! This worked like a charm, thank you SO much! I can't even tell you how helpful you've been, my brain was melting!

Sorry I took so long to get back - it's been nuts here, I'm the only one that maintains and programs this DB!

[jzwp11]

You're welcome. I'm glad you got everything sorted out.