Create Weekly and Monthly Flag in database/table

[Lucas_london via AccessMonster.com]

Hi

I have set up a database/tables in Access based on daily timeseries data.
However I would like to create two additional columns in the table as a basis
to pull the data on a weekly and monthly series basis.

Currently, in excel I am using the weekday and EOMONTH functions to create
the flags/indicator in my excel dataset:

(Cells in column A contains daily dates)

Weekly: =IF(WEEKDAY(A12)>WEEKDAY(A13), "WEEKLY", "")
Monthly
=IF(EOMONTH(A13,0)>EOMONTH(A12,0),"Monthly","")

Are these functions are also available in Access? Or is possible to achieve
the result in some other way?

Thanks,

Lucas

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

[Bob Quintal]

"Lucas_london via AccessMonster.com" <u35944@uwewrote in
news:7696fca841644@uwe:

Hi

I have set up a database/tables in Access based on daily
timeseries data. However I would like to create two additional
columns in the table as a basis to pull the data on a weekly
and monthly series basis.

Currently, in excel I am using the weekday and EOMONTH
functions to create the flags/indicator in my excel dataset:

(Cells in column A contains daily dates)

Weekly: =IF(WEEKDAY(A12)>WEEKDAY(A13), "WEEKLY", "")
Monthly
=IF(EOMONTH(A13,0)>EOMONTH(A12,0),"Monthly","")

Are these functions are also available in Access? Or is
possible to achieve the result in some other way?

Thanks,

Lucas

There are similar functions in Access. The IIF() function is
similar to the if IIF stands for Immediate IF, to differentiate
it from the IF in VB.

datediff(period,onedate, seconddate) compares 2 dates. Period is
a text value, eg "d" for days "m" for months, "n" for minutes,
and "yyyy" for years.

See the Visual Basic help on both functions.

However, flags like this do not belong in fields in a table. The
are computed in a query based on the table.

Not knowing how you use the flags, I can't offer specific
instructions on how to write the query you need. Please supply a
few more details.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Lucas_london via AccessMonster.com]

Hi Bob,

In terms of what I'm trying to do. The query below that brings back daily
data from the table "DATA". It is a timeseries table with daily observations,
it is seven fields, and the date field is called field2 and is of the format:
DD/MM/YYY. What I want to do is to have a query to bring back the same output
as below, but instead of daily series, I want it to return weekly data. So I
need a clause in the query that will convert the data to weekly.

So for example for the following sample of dates, I only want to bring back
the row for 29-Jun-07 and 06-Jul-07 only, which are both Fridays.

25-Jun-07
26-Jun-07
27-Jun-07
28-Jun-07
29-Jun-07
02-Jul-07
03-Jul-07
04-Jul-07
05-Jul-07
06-Jul-07

However sometimes this last observation in the week may not always fall on a
Friday, for example during Easter when Friday is a public holiday, the last
observation in the week will be Thursday. That's why I was using that
comparison function in excel so that I could create a flag to identify the
last available observation for any given week. For the majority of cases this
will fall on a Friday but not always.

Hope this makes things clearer.

Thanks,

Lucas
SELECT *
FROM Data
WHERE field1 In ('LLOY','RSA');
Bob Quintal wrote:

>Hi

[quoted text clipped - 18 lines]

>>
Lucas

There are similar functions in Access. The IIF() function is
similar to the if IIF stands for Immediate IF, to differentiate
it from the IF in VB.

datediff(period,onedate, seconddate) compares 2 dates. Period is
a text value, eg "d" for days "m" for months, "n" for minutes,
and "yyyy" for years.

See the Visual Basic help on both functions.

However, flags like this do not belong in fields in a table. The
are computed in a query based on the table.

Not knowing how you use the flags, I can't offer specific
instructions on how to write the query you need. Please supply a
few more details.

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

[Bob Quintal]

"Lucas_london via AccessMonster.com" <u35944@uwewrote in
news:76f8d0ac114cd@uwe:

Hi Bob,

In terms of what I'm trying to do. The query below that brings
back daily data from the table "DATA". It is a timeseries table
with daily observations, it is seven fields, and the date field is
called field2 and is of the format: DD/MM/YYY. What I want to do
is to have a query to bring back the same output as below, but
instead of daily series, I want it to return weekly data. So I
need a clause in the query that will convert the data to weekly.

So for example for the following sample of dates, I only want to
bring back the row for 29-Jun-07 and 06-Jul-07 only, which are
both Fridays.

25-Jun-07
26-Jun-07
27-Jun-07
28-Jun-07
29-Jun-07
02-Jul-07
03-Jul-07
04-Jul-07
05-Jul-07
06-Jul-07

However sometimes this last observation in the week may not always
fall on a Friday, for example during Easter when Friday is a
public holiday, the last observation in the week will be Thursday.
That's why I was using that comparison function in excel so that I
could create a flag to identify the last available observation for
any given week. For the majority of cases this will fall on a
Friday but not always.

Hope this makes things clearer.

Thanks,

Lucas
SELECT *
FROM Data
WHERE field1 In ('LLOY','RSA');

the weekday(datefield [,optional first day of week)) returns a
number which represents the day of the week, Without the optional
number, fridays are 6.

In the query designer if you put your field2, then surround it wth
the weekday function, you can set the 6 in the criteria row.

Access will add a label, in the form of expr1:, you can change that
to any name you want for the column (within reason)

Now the issue with the missing fridays, one approach is to make a
small table with the holidays that fall on a friday. and join that
table on the dates, changing the join to show all records from your
main table ano the ones from the holidays table where the dates are
equal.

now you can add one to the weekday if the holiday table date is not
null.

so your query becomes something close to

SELECT *
From Data
LEFT Join Holidays
ON data.field2 = Holidays.holiday
WHERE field1 In ('LLOY','RSA');
AND weekday(field2)
+ iif( isnull(Holidays.holiday),0,1)
= 6;
Q

Bob Quintal wrote:

>>Hi

[quoted text clipped - 18 lines]

>>>
Lucas

There are similar functions in Access. The IIF() function is
similar to the if IIF stands for Immediate IF, to differentiate
it from the IF in VB.

datediff(period,onedate, seconddate) compares 2 dates. Period is
a text value, eg "d" for days "m" for months, "n" for minutes,
and "yyyy" for years.

See the Visual Basic help on both functions.

However, flags like this do not belong in fields in a table. The
are computed in a query based on the table.

Not knowing how you use the flags, I can't offer specific
instructions on how to write the query you need. Please supply a
few more details.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Lucas_london via AccessMonster.com]

Bob Quintal wrote:

>Hi Bob,

[quoted text clipped - 38 lines]

>FROM Data
WHERE field1 In ('LLOY','RSA');

the weekday(datefield [,optional first day of week)) returns a
number which represents the day of the week, Without the optional
number, fridays are 6.

In the query designer if you put your field2, then surround it wth
the weekday function, you can set the 6 in the criteria row.

Access will add a label, in the form of expr1:, you can change that
to any name you want for the column (within reason)

Now the issue with the missing fridays, one approach is to make a
small table with the holidays that fall on a friday. and join that
table on the dates, changing the join to show all records from your
main table ano the ones from the holidays table where the dates are
equal.

now you can add one to the weekday if the holiday table date is not
null.

so your query becomes something close to

SELECT *
From Data
LEFT Join Holidays
ON data.field2 = Holidays.holiday
WHERE field1 In ('LLOY','RSA');
AND weekday(field2)
+ iif( isnull(Holidays.holiday),0,1)
= 6;

Q

>>>Hi

[quoted text clipped - 18 lines]

>>>instructions on how to write the query you need. Please supply a
few more details.

------------------------------------------------------------------------------
------
Hi Bob,

Thanks very much for the code and explaining how it works. As per my orginal
posting, I also need to devise a similar query to bring back monthly data -
is there a similar function I could use for this? I've checked the functions
available in Access but could not see one. Again, just likely the weekly
query, for each month I want to bring back the row for the last available
date in that month. I suspect I will need to use a combination of functions
to get the result I need but maybe I'm over complicating things. Any ideas on
the best approach?

Many Thanks,

Lucas

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

[Bob Quintal]

"Lucas_london via AccessMonster.com" <u35944@uwewrote in
news:7779dded54462@uwe:

Bob Quintal wrote:

>>Hi Bob,

[quoted text clipped - 38 lines]

>>FROM Data
WHERE field1 In ('LLOY','RSA');

the weekday(datefield [,optional first day of week)) returns a
number which represents the day of the week, Without the optional
number, fridays are 6.

In the query designer if you put your field2, then surround it wth
the weekday function, you can set the 6 in the criteria row.

Access will add a label, in the form of expr1:, you can change
that to any name you want for the column (within reason)

Now the issue with the missing fridays, one approach is to make a
small table with the holidays that fall on a friday. and join that
table on the dates, changing the join to show all records from
your main table ano the ones from the holidays table where the
dates are equal.

now you can add one to the weekday if the holiday table date is
not null.

so your query becomes something close to

SELECT *
From Data
LEFT Join Holidays
ON data.field2 = Holidays.holiday
WHERE field1 In ('LLOY','RSA');
AND weekday(field2)
+ iif( isnull(Holidays.holiday),0,1)
= 6;

Q

>>>>Hi
>

[quoted text clipped - 18 lines]

>>>>instructions on how to write the query you need. Please supply a
few more details.

------------------------------------------------------------------

-

----------- ------
Hi Bob,

Thanks very much for the code and explaining how it works. As per
my orginal posting, I also need to devise a similar query to bring
back monthly data - is there a similar function I could use for
this? I've checked the functions available in Access but could not
see one. Again, just likely the weekly query, for each month I
want to bring back the row for the last available date in that
month. I suspect I will need to use a combination of functions to
get the result I need but maybe I'm over complicating things. Any
ideas on the best approach?

Many Thanks,

Lucas

the dateadd() function will point you in the right direction
if you subtract 1 from the first of any month, you get the last day
of the previous month. The dateserial builds a date from numbers.
The trick is to get the month numbers, from somewhere, perhaps a
small table with the first of each month, or the numbers 1to 12

LastOf Month = dateAdd("d",-1,dateserial(year(now(),month(now(),1)
will return the last day of july since we are in August.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Lucas_london via AccessMonster.com]

Thanks for the suggestion Bob. Not sure if I completely understand it at this
stage.

I am to use this to bring back data in a query as opposed to create a new
feild in the data table right? So in the simplest form I would use it in my
query as something like this:

SELECT *
From Data
WHERE field1 In ('LLOY','RSA');
AND field2 = dateAdd("d",-1,dateserial(year(now(),month(now(),1)

Also, for this to work, do I need to have daily observations for last
calender date in the month? For example for the month of January the last
observation available in my table might be for the 29th of Jan, as opposed to
31st of Jan. Will it still work in this instance, i.e bring back the row for
the 29th of Jan?

Thanks,

Lucas
Bob Quintal wrote:

>>>Hi Bob,

[quoted text clipped - 57 lines]

>>
Lucas

the dateadd() function will point you in the right direction
if you subtract 1 from the first of any month, you get the last day
of the previous month. The dateserial builds a date from numbers.
The trick is to get the month numbers, from somewhere, perhaps a
small table with the first of each month, or the numbers 1to 12

LastOf Month = dateAdd("d",-1,dateserial(year(now(),month(now(),1)
will return the last day of july since we are in August.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200709/1

[Bob Quintal]

"Lucas_london via AccessMonster.com" <u35944@uwewrote in
news:77b92aca6a02b@uwe:

Thanks for the suggestion Bob. Not sure if I completely understand
it at this stage.

I am to use this to bring back data in a query as opposed to
create a new feild in the data table right? So in the simplest
form I would use it in my query as something like this:

SELECT *
From Data
WHERE field1 In ('LLOY','RSA');
AND field2 = dateAdd("d",-1,dateserial(year(now(),month(now(),1)

yes, that would bring back the data for the lst day in hte month.

>
Also, for this to work, do I need to have daily observations for
last calender date in the month? For example for the month of
January the last observation available in my table might be for
the 29th of Jan, as opposed to 31st of Jan. Will it still work in
this instance, i.e bring back the row for the 29th of Jan?

This is a different scenario, and actually a little less
complicated.

You can get the highest date in a month using the year() and month()
functions with

Select max(field2) as hidate from mytable group by year(field2),
month(field2);

then join that query on your table on field2 which will return only
the records for the maximum date for that month.

You can then change the join to a subquery:

here's a good tutorial.
http://www.sqlteam.com/article/using...select-records
as is this
http://as400bks.rochester.ibm.com/is...924/index.htm?
info/sqlp/rbafymst141.htm

Thanks,

Lucas
Bob Quintal wrote:

>>>>Hi Bob,
>

[quoted text clipped - 57 lines]

>>>
Lucas

the dateadd() function will point you in the right direction
if you subtract 1 from the first of any month, you get the last
day of the previous month. The dateserial builds a date from
numbers. The trick is to get the month numbers, from somewhere,
perhaps a small table with the first of each month, or the numbers
1to 12

LastOf Month = dateAdd("d",-1,dateserial(year(now(),month(now(),1)
will return the last day of july since we are in August.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com