Prev: Need to migrate phone no into the next record if the family
Next: Average interpurchase times
From: ghetto_banjo on 14 Apr 2010 09:28
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.
From: SamMexico via AccessMonster.com on 14 Apr 2010 10:11
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

--
Message posted via http://www.accessmonster.com

From: ghetto_banjo on 14 Apr 2010 10:15
for the criteria under the date, you can say:

Is Not Null



Then it will exclude individuals who do not have a date.
From: SamMexico via AccessMonster.com on 14 Apr 2010 10:26
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 ;)

--
Message posted via http://www.accessmonster.com

From: ghetto_banjo on 14 Apr 2010 10:36
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]);
 |  Next  |  Last
Pages: 1 2 3
Prev: Need to migrate phone no into the next record if the family
Next: Average interpurchase times