Date Query

[SamMexico via AccessMonster.com]

Hi all, I was wondering if anyone could please help me? I have a database
table that has a column allocated to the date a person consented to have
their details added to the database. What I need is to create a query that
displays any date entries and the number of times that particular date has
been entered (as many people would give consent on the same date).

I then need to add another column that calculates what week that date fell
into starting from Week 1 (21st December 2009).

The main reason I need this query is to build a chart that displays the
amount of consenting people (specified by date) on the y axis and the week
number across the x axis.

I have a replica in Excel but a duplicate graph in Access would be ideal, I
am only just getting to grips with Access (using 2003) so any help would be
really helpful,

Thanks,

Sam

 

[ghetto_banjo]

for your first question:

create a query and enable the "Totals" option (you can right click in
the query and enable this). Choose your date field, and set the
Total function to "Group By". Choose your unique field for the people
(employeeID or whatever) and set that to "Count". That will give you
a count of how many people entered each date.


To get the week number of a date, you can use the Date Part formula.

=DatePart("ww", yourDateField)


So just add a field your table, and then run an update query to add
all of the week numbers to that new field.

 

[SamMexico via AccessMonster.com]

Thanks for your help! I am slightly stuck on the first part of creating the
query - it all works well but I get a line that counts all the individual ID
without a date too. Do I add an expression instead of the count facility to
get rid of the non dates?

Thanks,

Sam

 

[ghetto_banjo]

for the criteria under the date, you can say:

Is Not Null



Then it will exclude individuals who do not have a date.

 

[SamMexico via AccessMonster.com]

You sir are a genius!

I have now got a query that shows the number of people who consented on a
particular date. I have added a Week Number column to the table and one to
the query but I don't understand what you mean by:

'To get the week number of a date, you can use the Date Part formula.

=DatePart("ww", yourDateField)

So just add a field your table, and then run an update query to add
all of the week numbers to that new field.'

Sorry for being a dullard

 

[ghetto_banjo]

i am definitely not a genius and you are definitely not a dullard!

So first create a new query, and change the type to an Update Query.

Choose your newly created WeekNumber field.

Set the "Update To:" part to:

DatePart("ww", [yourDateField])


replace "yourDateField" with whatever your date field is called.


So the SQL for that query will end up looking like:

UPDATE yourTable SET weekNumber = DatePart("ww", [yourDateField]);

 

[SamMexico via AccessMonster.com]

Excellent - thanks very much!

The last thing is to get the week numbers to show themselves in relation to
the 21/12/2009 as being the start of week 1...

Is this possible?

 

[SamMexico via AccessMonster.com]

Excellent - thanks very much!

The last thing is to get the week numbers to show themselves in relation to
the 21/12/2009 as being the start of week 1...

Is this possible?

The extra problem being that there are dates before this that should be
counted a Week -1 etc...

 

[ghetto_banjo]

Hmm you should be able to do that. But instead of the DatePart
function, you will need to use the DateDiff function to calculate the
number of weeks, whether it is positive or negative. I am using
American Date formats, MM/DD/YYYY.

DateDiff("ww", #12/21/2009#, [yourDateField], 2)

That ,2 at the end just specifies we are using a Monday as the first
day of a week.

Now the problem is that there is no week 0, right? You want Dec 21 to
be the start of week 1, meaning Dec 20 would be the day of week -1
correct? I am assuming this going forward. So you need an iif
statement to figure out if you need to add 1 or not.

IIf([yourDateField] < #12/21/2009#, DateDiff("ww", #12/21/2009#,
[yourDateField], 2), DateDiff("ww", #12/21/2009#, [yourDateField], 2)
+ 1)


Looks complicated, but all it is saying is if the Date is before Dec
21, take the true DateDiff value, otherwise, take the DateDiff value
and add 1. You can use this formula in your Update query.

 

[SamMexico via AccessMonster.com]

I will try that tomorrow, your help has been invaluable - thank you ever so
much!

Sam

 

[SamMexico via AccessMonster.com]

Hi again...

I did exactly what you advised and it has gone really well using:

UPDATE Data SET Data.[Week Number] = DateDiff("ww",#12/21/2009#,[Date of
Consent],2)
WHERE (((Data.[Week Number])=IIf([Date of Consent]<#12/21/2009#,DateDiff("ww",
#12/21/2009#,[Date of Consent],2),DateDiff("ww",#12/21/2009#,[Date of Consent]
,2)+1)));

The slight problem is that the query that has the week number in (after
update) has the 12th and 13th of this month recorded in Week 16 whereas I
make it Week 17. I tried to change the date a week back from the 12/21/2009
to the 12/14/2009 but this tweak doesn't seem to work. I know it's a small
thing...

Any ideas?

 

[ghetto_banjo]

That iif statement should be in the SET clause, not the WHERE
clause.

try this:

UPDATE Data SET Data.[Week Number] = IIf([Date of
Consent]<#12/21/2009#,DateDiff("ww",#12/21/2009#,[Date of Consent],
2),DateDiff("ww",#12/21/2009#,[Date of Consent],2)+1) WHERE data.[Date
of Consent] Is Not Null;

 

[SamMexico via AccessMonster.com]

That worked brilliantly - that is my reporting problem solved! Thank you
again for all your help and patience!

 

[ghetto_banjo]

you're welcome. glad to help.