Matching Dates

Ok guys fairly complicated one here...
I have a single table with 20,000 transactions within it. Each transaction 
has a date attached to it.
I have another table which contains 3 fields, a StartDate, a FinishDate and 
a WeekNumber. i.e.:

startdate             finishdate           weeknumber
01/01/2007         08/01/2007                 1

What I need to be able to do is take each transactions from the transactions 
table and link it to this calendar table to get the weeknumber of when each 
transaction occured.
How on earth can I produce this?

Many thanks.
Ash.
0
Utf
8/21/2007 8:58:03 AM
access.queries 6343 articles. 1 followers. Follow

18 Replies
1100 Views

Similar Articles

[PageSpeed] 26

hi,

ashg657 wrote:
> Ok guys fairly complicated one here...
Not really:)

> I have a single table with 20,000 transactions within it. Each transaction 
> has a date attached to it.
> I have another table which contains 3 fields, a StartDate, a FinishDate and 
> a WeekNumber. i.e.:
> startdate             finishdate           weeknumber
> 01/01/2007         08/01/2007                 1
You need a Join:

SELECT t.*, w.WeekNumber
FROM Transactions t
INNER JOIN Weeks w
ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate

http://office.microsoft.com/en-us/access/HP010984841033.aspx
http://www.mvps.org/access/datetime/date0012.htm

mfG
--> stefan <--

0
Stefan
8/21/2007 9:30:30 AM
On 21 Aug, 10:30, Stefan Hoffmann <stefan.hoffm...@explido.de> wrote:
> > I have a single table with 20,000 transactions within it. Each transaction
> > has adateattached to it.
> > I have another table which contains 3 fields, a StartDate, a FinishDate and
> > a WeekNumber. i.e.:
> > startdate             finishdate           weeknumber
> > 01/01/2007         08/01/2007                 1
>
> You need a Join:
>
> SELECT t.*, w.WeekNumber
> FROM Transactions t
> INNER JOIN Weeks w
> ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate

If OP is to do that -- and the suggestion is a good one -- then they
should make the data in their calendar table more like

startdate=#2007-01-01 00:00:00#
finishdate=#2007-01-08 23:59:59#
weeknumber=1

With their existing data (i.e. one day granularity), the value
#2007-01-08 12:00:00# might return weeknumber=2 or, worse, no
weeknumber at all!

Alternatively, they could ensure that every DATETIME value encountered
is of one day graularity (e.g. only use SQL stored procs with strongly
type DATETIME parameters always 'rounded') but it's must easier to
accept the fact that all DATETIME values are of one second graularity
and code accordingly.

Jamie.

--


0
Jamie
8/21/2007 1:53:46 PM
All Date values in Access are not granular to 1 second.  It is up to the 
developer to determine and control whether a time value is included.  That is 
why there are three different functions.
Date() returns only the date portion.
Now() returns date and time.
Time returns only the time.

What is not clear here is which date to use for the week.  There are two 
dates, but the OP did not say which should be used to return the week.  In 
the example, the start date is week 1, but the  end date is certainly not.

Additionally, is there any reason the DatePart function can't be used to 
determine the week rather than having to maintain a table?  It would be 
faster.

=DatePart("ww",SomeDate)
-- 
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

> On 21 Aug, 10:30, Stefan Hoffmann <stefan.hoffm...@explido.de> wrote:
> > > I have a single table with 20,000 transactions within it. Each transaction
> > > has adateattached to it.
> > > I have another table which contains 3 fields, a StartDate, a FinishDate and
> > > a WeekNumber. i.e.:
> > > startdate             finishdate           weeknumber
> > > 01/01/2007         08/01/2007                 1
> >
> > You need a Join:
> >
> > SELECT t.*, w.WeekNumber
> > FROM Transactions t
> > INNER JOIN Weeks w
> > ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate
> 
> If OP is to do that -- and the suggestion is a good one -- then they
> should make the data in their calendar table more like
> 
> startdate=#2007-01-01 00:00:00#
> finishdate=#2007-01-08 23:59:59#
> weeknumber=1
> 
> With their existing data (i.e. one day granularity), the value
> #2007-01-08 12:00:00# might return weeknumber=2 or, worse, no
> weeknumber at all!
> 
> Alternatively, they could ensure that every DATETIME value encountered
> is of one day graularity (e.g. only use SQL stored procs with strongly
> type DATETIME parameters always 'rounded') but it's must easier to
> accept the fact that all DATETIME values are of one second graularity
> and code accordingly.
> 
> Jamie.
> 
> --
> 
> 
> 
0
Utf
8/21/2007 5:02:07 PM
Thanks for the input.
DatePart could not be used as our company calendar runs from October - 
September, i.e. 01/10/2007 - 07/10/2007 is Week1 etc etc. Thats why I have 
decided that this seperate table is necessary - correct me if I am wrong.
The only other suggestion I have had is to create a table with every single 
date of the year in it and assign week numbers to each indivudally, possible 
a long winded way of doing things but never the less I could see it working.
Many thanks again.

"Klatuu" wrote:

> All Date values in Access are not granular to 1 second.  It is up to the 
> developer to determine and control whether a time value is included.  That is 
> why there are three different functions.
> Date() returns only the date portion.
> Now() returns date and time.
> Time returns only the time.
> 
> What is not clear here is which date to use for the week.  There are two 
> dates, but the OP did not say which should be used to return the week.  In 
> the example, the start date is week 1, but the  end date is certainly not.
> 
> Additionally, is there any reason the DatePart function can't be used to 
> determine the week rather than having to maintain a table?  It would be 
> faster.
> 
> =DatePart("ww",SomeDate)
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Jamie Collins" wrote:
> 
> > On 21 Aug, 10:30, Stefan Hoffmann <stefan.hoffm...@explido.de> wrote:
> > > > I have a single table with 20,000 transactions within it. Each transaction
> > > > has adateattached to it.
> > > > I have another table which contains 3 fields, a StartDate, a FinishDate and
> > > > a WeekNumber. i.e.:
> > > > startdate             finishdate           weeknumber
> > > > 01/01/2007         08/01/2007                 1
> > >
> > > You need a Join:
> > >
> > > SELECT t.*, w.WeekNumber
> > > FROM Transactions t
> > > INNER JOIN Weeks w
> > > ON t.TransactionDate BETWEEN w.StartDate AND w.FinishDate
> > 
> > If OP is to do that -- and the suggestion is a good one -- then they
> > should make the data in their calendar table more like
> > 
> > startdate=#2007-01-01 00:00:00#
> > finishdate=#2007-01-08 23:59:59#
> > weeknumber=1
> > 
> > With their existing data (i.e. one day granularity), the value
> > #2007-01-08 12:00:00# might return weeknumber=2 or, worse, no
> > weeknumber at all!
> > 
> > Alternatively, they could ensure that every DATETIME value encountered
> > is of one day graularity (e.g. only use SQL stored procs with strongly
> > type DATETIME parameters always 'rounded') but it's must easier to
> > accept the fact that all DATETIME values are of one second graularity
> > and code accordingly.
> > 
> > Jamie.
> > 
> > --
> > 
> > 
> > 
0
Utf
8/22/2007 7:34:02 AM
On 21 Aug, 18:02, Klatuu <Kla...@discussions.microsoft.com> wrote:
> It is up to the
> developer to determine and control whether a time value is included.  That is
> why there are three different functions.

> Date() returns only the date portion.
> Now() returns date and time.
> Time returns only the time.

No!

Standard SQL has DATE and TIME date types, plus some others, but
Access/Jet has but one temporal data type, named DATETIME (close to
Standard SQL's TIMESTAMP data type but nothing like SQL Server's). The
clue's in the name...

In Access/Jet, Date() always returns a time portion, being midnight;
Time() always returns a date portion, being of 30th of December
eighteen hundred and ninety-nine. You can hide certain date/time
elements using formatting but that doesn't make the underlying values
disappear. This is newbie stuff and you know it :)

> All Date values in Access are not granular to 1 second.

I guess I was summarizing my conclusions based on experience rather
than stating a solid fact; sorry for not being clear. Time is in
continuum, periods can be infinitely divided and in this regard the
floating point nature of Access/Jet's DATETIME functionality is a good
fit; pity it's not practical. Unless prevented, someone can put sub-
second values in a DATETIME column but to operate on it would have to
roll their own temporal functionality (non-trivial to say the least)
because the smallest useable time granule in Access/Jet's own is one
second. If you take the aforementioned prevention approach it gets to
be a pain too, not so bad for table columns where engine-level
validation rules can be used but really onerous for input parameter
values (I shudder to contemplate the consequence if you're a 'dynamic
SQL' person).

Similarly, someone could roll their own fixed point temporal data
type; this is not so fanciful: we see many people here trying to use
text data types to achieve the same and the result usually a horrible
mess.

 Of course, most designers use DATETIME, take no action at all but
assume all dates will be of one day granularity, and we see many of
them in the groups wondering out loud why their rows disappear when
JOINed on DATETIME columns and the answer is they allowed time
elements other than midnight and consequently got them.

My conclusions are to use DATETIME for instants, a pair of DATETIMEs
for periods,use engine-level validation rules to ensure DATETIME
columns' values are of known granularity no smaller than one second
(but often larger granules) and either 'round' parameter values to the
same granularity of the column they are being compared while allowing
sub-second values to be implicitly rounded (fine by me: it's not my
fault that DATETIME is not based on fixed point so I shouldn't be
expected to fix it!) I can then used closed-closed representation for
periods e.g. period for the current month would be represented as

[#2007-08-01 00:00:00#, #2007-08-31 23:59:59#]

The OP is also using closed-closed but would represent the same period
as

[#2007-08-01 00:00:00#, #2007-08-31 00:00:00#]

which begs the question, where does the value #2007-08-31 00:00:00#
fall? No where probably.

Now you may ask where does the value 39325.999994213 fall in my
version and I tell you that 39325.999994213 is not a valid DATETIME
value (!!) and if the front end designer or user allowed it to be used
as if it were on then that's their problem (of course I could ensure
the values were always rounded but it would result in a complex and
less flexible system -- and I repeat I shouldn't be expected to fix
the DATETIME data type's floating point 'problem'). I don't think the
OP could use the same defence because valid DATETIME values other than
midnight are far more commonly encountered and there no real excuse
for not anticipating them e.g. as a I suggested they could make their
end DATETIMEs have time element one second before midnight OR use
closed-open representation e.g.

[#2007-08-01 00:00:00#, #2007-09-01 00:00:00#)

and how hard is that <g>?!

> What is not clear here is which date to use for the week.  There are two
> dates, but the OP did not say which should be used to return the week.  In
> the example, the start date is week 1, but the  end date is certainly not.
>
> Additionally, is there any reason the DatePart function can't be used to
> determine the week rather than having to maintain a table?  It would be
> faster.
>
> =DatePart("ww",SomeDate)

Is there any reason the DatePart function can't be used? You seem to
have answered your own question: using DatePart is fair enough when
your definition of week start date and week end date happens to
coincide with Microsoft's; if not, you've got to roll your own.

SQL (the language) was designed for data storage and data retrieval
and not surprisingly it excels in this area. It was not designed as a
calculation engine, hence doesn't do so well in this area. It stands
to reason to prefer a data-driven solution, using data stored in
permanent tables joining to other tables etc, over a calculation.
Putting the logic into a UDF, for example, obscures the logic of the
application and can only run inside the Access user interface, whereas
a calendar table style solution is clear and available to all. It also
has a many uses e.g. finding the number of enterprise days between two
dates; identifying overlapping periods (JOIN to the calendar table and
GROUP BY calendar day), etc.

Maintenance isn't usually a problem because the calendar tends to be
quite stable <g>! Also consider it's easier to change data in a table
than it is to make a code change to a UDF. Further, the table-driven
approach ports well to other SQL DBMSs.

Jamie.

--


0
Jamie
8/22/2007 8:13:09 AM
hi,

ashg657 wrote:
> DatePart could not be used as our company calendar runs from October - 
> September, i.e. 01/10/2007 - 07/10/2007 is Week1 etc etc. Thats why I have 
> decided that this seperate table is necessary - correct me if I am wrong.
I would encapsulate this in a function. Cause it is easier to maintain. 
Just take a look at the common week number algorithms and use it for 
your definition of a year.



mfG
--> stefan <--
0
Stefan
8/22/2007 8:34:05 AM
On 22 Aug, 09:34, Stefan Hoffmann <stefan.hoffm...@explido.de> wrote:
> > DatePart could not be used as our company calendar runs from October -
> > September, i.e. 01/10/2007 - 07/10/2007 is Week1 etc etc. Thats why I have
> > decided that this seperate table is necessary - correct me if I am wrong.
>
> I would encapsulate this in a function. Cause it is easier to maintain.
> Just take a look at the common week number algorithms and use it for
> your definition of a year.

Funny, I find it easier to change data in a table (or even alter a
database object) in the database than to roll out a code change
(certainly costs us less money!) Consider a FE/BE split: is it easier
to change data in the BE or change *all* the FEs?

I use the Calendar table approach and it works well.

Do you think it could be the case that you find it easier to write
procedural code than to devise a set-based, table driven approach? :)

Jamie.

--


0
Jamie
8/22/2007 9:18:25 AM
hi Jamie,

Jamie Collins wrote:
>> I would encapsulate this in a function. Cause it is easier to maintain.
>> Just take a look at the common week number algorithms and use it for
>> your definition of a year.
> Funny, I find it easier to change data in a table (or even alter a
> database object) in the database than to roll out a code change
> (certainly costs us less money!) Consider a FE/BE split: is it easier
> to change data in the BE or change *all* the FEs?
The distribution is not a real argument, as we are in the phase of 
creating (designing and implementing) an application.

> I use the Calendar table approach and it works well.
When this kind of week number calculation is needed only once, you may 
be right.

But i suppose this is not true. Just consider report printing with week 
numbers. This would mean you have to use a bunch of these joins just for 
a simple week number in the page footer/header. Okay, you may use a 
DLookup() replacement, but I think this quite equivalent to a join.

> Do you think it could be the case that you find it easier to write
> procedural code than to devise a set-based, table driven approach? :)
This kind of functionality needs a larger degree of abstraction. This is 
  covered by my functional approach.

Just my 2 cents.


mfG
--> stefan <--
0
Stefan
8/22/2007 9:56:14 AM
On 22 Aug, 10:56, Stefan Hoffmann <stefan.hoffm...@explido.de> wrote:
> > > I would encapsulate this in a function. Cause it is easier to maintain.
> >
> > Funny, I find it easier to change data in a table (or even alter a
> > database object) in the database than to roll out a code change
> > (certainly costs us less money!) Consider a FE/BE split: is it easier
> > to change data in the BE or change *all* the FEs?
>
> The distribution is not a real argument, as we are in the phase of
> creating (designing and implementing) an application.

I was responding to your point about ease of maintenance.

> > I use the Calendar table approach and it works well.
> >
> When this kind of week number calculation is needed only once, you may
> be right.
>
> But i suppose this is not true. Just consider report printing with week
> numbers. This would mean you have to use a bunch of these joins just for
> a simple week number in the page footer/header.

My way involves retieving data, not a calculation. If I want to use a
retirieved value multiple times I will cache it (e.g. disconnection
recordset) rather than make multiple database round trips.

I use a proferssional 'industrial strength' report writer myself and
I'm pretty sure it uses a locally cached resultset and hits the
database just the once.

Despite the non-applicable example you picked, I really don't see the
problem. If a table JOIN was an issue then we'd all have very
denormalized databases <g>.  And if looking up a value in a table was
an issue we'd all be stuffed <vbg>!

Jamie.

--


0
Jamie
8/22/2007 11:13:37 AM
Jaime,

I wonder why you bother to use Access at all.  You seem not to like it very 
much. You use an "industrial strength" report writer and you try to impose 
SQL Server techniques on Jet and give newbies answers based on Non Access 
products.  I think this only leads to confusion.

As to whether to use code or data retrieval... In 30 years of multiple 
languages and database engines, I have yet to find one where data retrieval 
is faster than a code calculation.  As to  your deployment issue, if you use 
an auto FE updater, it is absolutely no problem.  It would seem to me to be 
more problematic to have to take the database down for a schema change and 
then be sure the table is correct and up to date.

As a humorous aside, I saw this happen in 1982.  The  bank I was working at 
had a calendar table on the DEC PDP 1170's we used for ATM and manned teller 
processing.  At 3:00 PM on December 31 when cutover occurred, the table had 
no 1983 data.  Before we got the system down, it captured 450 ATM cards 
including the president of the bank.

IMHO if it can be calculated, it should be.
-- 
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

> On 21 Aug, 18:02, Klatuu <Kla...@discussions.microsoft.com> wrote:
> > It is up to the
> > developer to determine and control whether a time value is included.  That is
> > why there are three different functions.
> 
> > Date() returns only the date portion.
> > Now() returns date and time.
> > Time returns only the time.
> 
> No!
> 
> Standard SQL has DATE and TIME date types, plus some others, but
> Access/Jet has but one temporal data type, named DATETIME (close to
> Standard SQL's TIMESTAMP data type but nothing like SQL Server's). The
> clue's in the name...
> 
> In Access/Jet, Date() always returns a time portion, being midnight;
> Time() always returns a date portion, being of 30th of December
> eighteen hundred and ninety-nine. You can hide certain date/time
> elements using formatting but that doesn't make the underlying values
> disappear. This is newbie stuff and you know it :)
> 
> > All Date values in Access are not granular to 1 second.
> 
> I guess I was summarizing my conclusions based on experience rather
> than stating a solid fact; sorry for not being clear. Time is in
> continuum, periods can be infinitely divided and in this regard the
> floating point nature of Access/Jet's DATETIME functionality is a good
> fit; pity it's not practical. Unless prevented, someone can put sub-
> second values in a DATETIME column but to operate on it would have to
> roll their own temporal functionality (non-trivial to say the least)
> because the smallest useable time granule in Access/Jet's own is one
> second. If you take the aforementioned prevention approach it gets to
> be a pain too, not so bad for table columns where engine-level
> validation rules can be used but really onerous for input parameter
> values (I shudder to contemplate the consequence if you're a 'dynamic
> SQL' person).
> 
> Similarly, someone could roll their own fixed point temporal data
> type; this is not so fanciful: we see many people here trying to use
> text data types to achieve the same and the result usually a horrible
> mess.
> 
>  Of course, most designers use DATETIME, take no action at all but
> assume all dates will be of one day granularity, and we see many of
> them in the groups wondering out loud why their rows disappear when
> JOINed on DATETIME columns and the answer is they allowed time
> elements other than midnight and consequently got them.
> 
> My conclusions are to use DATETIME for instants, a pair of DATETIMEs
> for periods,use engine-level validation rules to ensure DATETIME
> columns' values are of known granularity no smaller than one second
> (but often larger granules) and either 'round' parameter values to the
> same granularity of the column they are being compared while allowing
> sub-second values to be implicitly rounded (fine by me: it's not my
> fault that DATETIME is not based on fixed point so I shouldn't be
> expected to fix it!) I can then used closed-closed representation for
> periods e.g. period for the current month would be represented as
> 
> [#2007-08-01 00:00:00#, #2007-08-31 23:59:59#]
> 
> The OP is also using closed-closed but would represent the same period
> as
> 
> [#2007-08-01 00:00:00#, #2007-08-31 00:00:00#]
> 
> which begs the question, where does the value #2007-08-31 00:00:00#
> fall? No where probably.
> 
> Now you may ask where does the value 39325.999994213 fall in my
> version and I tell you that 39325.999994213 is not a valid DATETIME
> value (!!) and if the front end designer or user allowed it to be used
> as if it were on then that's their problem (of course I could ensure
> the values were always rounded but it would result in a complex and
> less flexible system -- and I repeat I shouldn't be expected to fix
> the DATETIME data type's floating point 'problem'). I don't think the
> OP could use the same defence because valid DATETIME values other than
> midnight are far more commonly encountered and there no real excuse
> for not anticipating them e.g. as a I suggested they could make their
> end DATETIMEs have time element one second before midnight OR use
> closed-open representation e.g.
> 
> [#2007-08-01 00:00:00#, #2007-09-01 00:00:00#)
> 
> and how hard is that <g>?!
> 
> > What is not clear here is which date to use for the week.  There are two
> > dates, but the OP did not say which should be used to return the week.  In
> > the example, the start date is week 1, but the  end date is certainly not.
> >
> > Additionally, is there any reason the DatePart function can't be used to
> > determine the week rather than having to maintain a table?  It would be
> > faster.
> >
> > =DatePart("ww",SomeDate)
> 
> Is there any reason the DatePart function can't be used? You seem to
> have answered your own question: using DatePart is fair enough when
> your definition of week start date and week end date happens to
> coincide with Microsoft's; if not, you've got to roll your own.
> 
> SQL (the language) was designed for data storage and data retrieval
> and not surprisingly it excels in this area. It was not designed as a
> calculation engine, hence doesn't do so well in this area. It stands
> to reason to prefer a data-driven solution, using data stored in
> permanent tables joining to other tables etc, over a calculation.
> Putting the logic into a UDF, for example, obscures the logic of the
> application and can only run inside the Access user interface, whereas
> a calendar table style solution is clear and available to all. It also
> has a many uses e.g. finding the number of enterprise days between two
> dates; identifying overlapping periods (JOIN to the calendar table and
> GROUP BY calendar day), etc.
> 
> Maintenance isn't usually a problem because the calendar tends to be
> quite stable <g>! Also consider it's easier to change data in a table
> than it is to make a code change to a UDF. Further, the table-driven
> approach ports well to other SQL DBMSs.
> 
> Jamie.
> 
> --
> 
> 
> 
0
Utf
8/22/2007 2:04:05 PM
On 22 Aug, 15:04, Klatuu <Kla...@discussions.microsoft.com> wrote:
> In 30 years of multiple
> languages and database engines, I have yet to find one where data retrieval
> is faster than a code calculation.

I'm specifically referring to calculations performed in the SQL (the
classic straw man example being amortization calculations).
Regardless, a calculation doesn't have to bee too involved before it's
cost exceeds that of a read; I've seen plenty of examples! I doubt
that in the OP's case either makes too much of a difference and is
down to personal preference: SQL code or VBA code (and there are
wizards to write both).

> you try to impose
> SQL Server techniques on Jet and give newbies answers based on Non Access
> products.  I think this only leads to confusion.

I don't come here to tell people to use SQL Server (or any other SQL
DBMS) by rote; you may be thinking of a certain KAaron empf. SQL
Server is based on SQL (the language), Jet is based on SQL (the
language), and in these groups I post SQL which is as close to the
SQL-92 standard as Jet allows. Therefore, it is of no coincidence that
many approaches which work well in SQL Server will also work well in
Jet but that doesn't mean I'm imposing 'SQL Server techniques'
inappropriately.

I've no intention of accusing you of accusing me of something <g> but
I would really appreciate it if you could post some specific examples
of where you think I've caused confusion by posting something 'non
Access' because I'm a little baffled by your comments which I know you
make in good faith. TIA.

> I wonder why you bother to use Access at all.  You seem not to like it very
> much.

How could anyone feel anything but unconditional love for Access? Its
forms and controls look really state of the art, even on Vista. The
quality of the average Access application is so high that I often
forget I'm not looking at a website! Its reports are so pretty and end
users really appreciate the interactive output, able to dynamically
alter the layout and so on. As a designer I really value the fact that
Jet is the most state of the art relational database around and
compliant with full ANSI standard SQL-92. It's just great that bugs in
the engine are fixed quickly and released as service packs on a near
weekly basis. And it's fortunately that I'm so in love with Access
because there are simply no alternative software development tools in
the marketplace with data aware controls, data binding, visual
designers for database objects, etc. Excuse me, I need to find some
water because my pants are on fire.

PS great story about the bank :)

Jamie.

--


0
Jamie
8/23/2007 12:02:52 PM
Was I halucinating or did I just read a post where you were praising Access?

No, Jamie I don't have any specific examples of confusion.  It is just a 
general tone.  When you give ANSI  answers, a lot of the posters here don't 
really understand the difference and may try to apply a solution that Jet 
can't handle.  Most have little or no experience true database design or 
software development.  Too many are admin types who happen to be a bit more 
savy than their coworkers and so were assigned duties beyond their experience 
by managers who don't know any different.  It happened even in my own family. 
 When Dbase III was the hot desktop database application, my wife was sent to 
a beginner class.  Upon her return, her boss said, "okay, now you know DBase, 
write us a library management system to keep track of all our documentation." 
 She thought she could until she tried to do it.  Well, to shorten the story, 
I wrote it for her.

So, that is why I think it is important to assume the poster has little or 
no experience unless the content of the post leads me to believe otherwise.  
Therefore, I scope my answers accordingly.

Access does get beat up a lot and has little respect in the "real" 
application world.  To some degree, that is justifiable.  It was never 
intended to by C++, Oracle, Crystal Reports, or Dot Net.  It is to allow 
small to medium scale application development at an affordable cost and be 
managed by knowledable users.

Part of its design goal, however, is overly optimistic.  Users who have no 
experience with software development start of lost.  But, Access didn't 
invent this misconception of user capability.  COBOL was written so users 
could create their own programs without having to rely on programmers.

But, for what it does, it does well.  A seasoned professional can do some 
pretty amazing things with it.  I work for a software development and 
services company.  The products, services, and web sites we provide are all 
SQL Server and dot net. (Pure MS)  But, the company' internal operational 
business (an 800 Million dollar company) is all  in Access except for 
accounting.

One comment really puzzles me.  I haven't seen a situation where a disc seek 
is faster than a calculation.  It would have to be a really complex 
calculation for that to happen.  Not saying it can't, but it would IMHO be 
unlikely.
-- 
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

> On 22 Aug, 15:04, Klatuu <Kla...@discussions.microsoft.com> wrote:
> > In 30 years of multiple
> > languages and database engines, I have yet to find one where data retrieval
> > is faster than a code calculation.
> 
> I'm specifically referring to calculations performed in the SQL (the
> classic straw man example being amortization calculations).
> Regardless, a calculation doesn't have to bee too involved before it's
> cost exceeds that of a read; I've seen plenty of examples! I doubt
> that in the OP's case either makes too much of a difference and is
> down to personal preference: SQL code or VBA code (and there are
> wizards to write both).
> 
> > you try to impose
> > SQL Server techniques on Jet and give newbies answers based on Non Access
> > products.  I think this only leads to confusion.
> 
> I don't come here to tell people to use SQL Server (or any other SQL
> DBMS) by rote; you may be thinking of a certain KAaron empf. SQL
> Server is based on SQL (the language), Jet is based on SQL (the
> language), and in these groups I post SQL which is as close to the
> SQL-92 standard as Jet allows. Therefore, it is of no coincidence that
> many approaches which work well in SQL Server will also work well in
> Jet but that doesn't mean I'm imposing 'SQL Server techniques'
> inappropriately.
> 
> I've no intention of accusing you of accusing me of something <g> but
> I would really appreciate it if you could post some specific examples
> of where you think I've caused confusion by posting something 'non
> Access' because I'm a little baffled by your comments which I know you
> make in good faith. TIA.
> 
> > I wonder why you bother to use Access at all.  You seem not to like it very
> > much.
> 
> How could anyone feel anything but unconditional love for Access? Its
> forms and controls look really state of the art, even on Vista. The
> quality of the average Access application is so high that I often
> forget I'm not looking at a website! Its reports are so pretty and end
> users really appreciate the interactive output, able to dynamically
> alter the layout and so on. As a designer I really value the fact that
> Jet is the most state of the art relational database around and
> compliant with full ANSI standard SQL-92. It's just great that bugs in
> the engine are fixed quickly and released as service packs on a near
> weekly basis. And it's fortunately that I'm so in love with Access
> because there are simply no alternative software development tools in
> the marketplace with data aware controls, data binding, visual
> designers for database objects, etc. Excuse me, I need to find some
> water because my pants are on fire.
> 
> PS great story about the bank :)
> 
> Jamie.
> 
> --
> 
> 
> 
0
Utf
8/23/2007 2:36:01 PM
On 23 Aug, 15:36, Klatuu <Kla...@discussions.microsoft.com> wrote:
> One comment really puzzles me.  I haven't seen a situation where a disc seek
> is faster than a calculation.  It would have to be a really complex
> calculation for that to happen.  Not saying it can't, but it would IMHO be
> unlikely.

I suspect you may be thinking of a specific definition of calculation.
I can fetch 100K rows from an mdb into a recordset in a fraction of a
second but to loop through from BOF to EOF to sum the values in a
field would take several minutes. I have an Excel spreadsheet here
that fetches a few hundred rows from a SQL database in no time at all
but its thousands of cell formulas take several seconds to
recalculate; it's not the most efficient calculation method but it
does show all its workings out at every intermediate stage which is
what the client wants.

In the case of the OP, I don't think there would be any noticeable
difference in performance between DATEPART, a similar UDF and a
fetching some rows from a table via a join.

> Was I halucinating or did I just read a post where you were praising Access?
>
> No, Jamie I don't have any specific examples of confusion.  It is just a
> general tone.  When you give ANSI  answers, a lot of the posters here don't
> really understand the difference and may try to apply a solution that Jet
> can't handle.  Most have little or no experience true database design or
> software development.

I'm here for generally the same reasons as everyone else: take on a
challenge, get some practice in, altruistic warm fuzzy feelings
inside, perhaps glean something new along the way. I also want to give
the sort of advice I wish I'd got when I first started using Access
e.g. awareness that Jet has rich SQL DDL syntax via its ANSI-92 Query
Mode. I'm slightly bothered by seeing the same old answers
regurgitated on a seemingly daily basis, especially when the language
used is of the style, "You have to use DAO to do this." But not as
bothered as I am by seeing the same flawed answers e.g. a validation
rules that pay no regard to alternative mode wildcard characters. But
what can one do? I plug away, posting original answers in Jet dialect
SQL (not ANSI) and refuting apparent fallacies with examples and links
to articles. I've seen some of the approaches I've introduced being
adopted, even by some regulars, but I don't seek to take credit and I
can't take a compliment (fortunately I don't get many <g>).

I think the most exciting (if that's the right word) thing about the
Jet engine is that, because there have been so few details published
about it, we must *discover* how things work (and keep on
rediscovering what others have forgotten) without knowing much about
how they are supposed to work.

Take CHECK constraints: they were announced in the Jet 4.0 release,
another brief article was published with a (flawed) usage example, and
that was about it. They have never AFAIK been mentioned in the Help
and we have no detail on how they should behave. Should they be
checked on a row-by-row basis, even though this makes no sense for
table level constraints (note the aforementioned examples provided by
MSFT are *multi* table level)? If the ANSI-92 standard specifies they
should be checked at the SQL statement level, and MSFT tells us that
Jet is nearly-but-not-quite entry level SQL-92 (table level CHECK
constraints -- those that may contain subqueries -- are a feature of
*full* SQL-92) and I can show that they are tested by Jet on a row-by-
row, table-by-table basis, how can I determine whether this is a
feature or a bug? So why so I feel like I'm the Lone Ranger here about
an important feature in a popular SQL DBMS. A very interesting
situation.

Jamie.

--


0
Jamie
8/24/2007 8:48:21 AM
In article <1187945301.685492.15830@q5g2000prf.googlegroups.com>, 
jamiecollins@xsmail.com says...
>  So why so I feel like I'm the Lone Ranger here about
> an important feature in a popular SQL DBMS. A very interesting
> situation.
> 
> 
I think the problem is that the Access query interface does not support many of 
the things you talk about.  If the interface supported them, then I think we 
would see the MPVs using them and championing them. Users here imitate what 
they see the MPVs using. The MPVs here have a huge reputation.  If a person 
asks about table constraints and an MPV says to forget that and use form event 
code, then that is the definitive word on the subject.  That is what you are up 
against.
0
Michael
8/24/2007 12:36:50 PM
I think there is a small misunderstanding.  When I say a calculation is 
faster than a disck fetch, I mean exactly that.  If you can calculate a value 
in memory it is much faster than a disc fetch to retrieve the value from a 
table.  As to your statment regarding a recordset as opposed to looping 
through from BOF to EOF, I am assuming you mean using ans SQL query as 
opposed to recordset processing.  I would agree with that.

As to the differences, I did some testing with this scenerio.  In an 
application I inherited, there was a table that defined the beginning and 
ending of each accounting period.  In a case where the accounting periods are 
not aligned with the calendar, this may be necessary.  I had one of those 
once, too.  But, this for this app each record had 3 fields - the period 
(200708), the first day of the month as a date, and the last day of the month 
as a date.  Each time a form opened, it hit the database to determine the 
beginning and ending period dates.  Determining the first and last days of a 
month are so simple to calculate, one wonders why the need for the table at 
all, other than not knowing how to use date handling functions.  The 
comparision was the the calculations were 80% faster than the fetch.
-- 
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

> On 23 Aug, 15:36, Klatuu <Kla...@discussions.microsoft.com> wrote:
> > One comment really puzzles me.  I haven't seen a situation where a disc seek
> > is faster than a calculation.  It would have to be a really complex
> > calculation for that to happen.  Not saying it can't, but it would IMHO be
> > unlikely.
> 
> I suspect you may be thinking of a specific definition of calculation.
> I can fetch 100K rows from an mdb into a recordset in a fraction of a
> second but to loop through from BOF to EOF to sum the values in a
> field would take several minutes. I have an Excel spreadsheet here
> that fetches a few hundred rows from a SQL database in no time at all
> but its thousands of cell formulas take several seconds to
> recalculate; it's not the most efficient calculation method but it
> does show all its workings out at every intermediate stage which is
> what the client wants.
> 
> In the case of the OP, I don't think there would be any noticeable
> difference in performance between DATEPART, a similar UDF and a
> fetching some rows from a table via a join.
> 
> > Was I halucinating or did I just read a post where you were praising Access?
> >
> > No, Jamie I don't have any specific examples of confusion.  It is just a
> > general tone.  When you give ANSI  answers, a lot of the posters here don't
> > really understand the difference and may try to apply a solution that Jet
> > can't handle.  Most have little or no experience true database design or
> > software development.
> 
> I'm here for generally the same reasons as everyone else: take on a
> challenge, get some practice in, altruistic warm fuzzy feelings
> inside, perhaps glean something new along the way. I also want to give
> the sort of advice I wish I'd got when I first started using Access
> e.g. awareness that Jet has rich SQL DDL syntax via its ANSI-92 Query
> Mode. I'm slightly bothered by seeing the same old answers
> regurgitated on a seemingly daily basis, especially when the language
> used is of the style, "You have to use DAO to do this." But not as
> bothered as I am by seeing the same flawed answers e.g. a validation
> rules that pay no regard to alternative mode wildcard characters. But
> what can one do? I plug away, posting original answers in Jet dialect
> SQL (not ANSI) and refuting apparent fallacies with examples and links
> to articles. I've seen some of the approaches I've introduced being
> adopted, even by some regulars, but I don't seek to take credit and I
> can't take a compliment (fortunately I don't get many <g>).
> 
> I think the most exciting (if that's the right word) thing about the
> Jet engine is that, because there have been so few details published
> about it, we must *discover* how things work (and keep on
> rediscovering what others have forgotten) without knowing much about
> how they are supposed to work.
> 
> Take CHECK constraints: they were announced in the Jet 4.0 release,
> another brief article was published with a (flawed) usage example, and
> that was about it. They have never AFAIK been mentioned in the Help
> and we have no detail on how they should behave. Should they be
> checked on a row-by-row basis, even though this makes no sense for
> table level constraints (note the aforementioned examples provided by
> MSFT are *multi* table level)? If the ANSI-92 standard specifies they
> should be checked at the SQL statement level, and MSFT tells us that
> Jet is nearly-but-not-quite entry level SQL-92 (table level CHECK
> constraints -- those that may contain subqueries -- are a feature of
> *full* SQL-92) and I can show that they are tested by Jet on a row-by-
> row, table-by-table basis, how can I determine whether this is a
> feature or a bug? So why so I feel like I'm the Lone Ranger here about
> an important feature in a popular SQL DBMS. A very interesting
> situation.
> 
> Jamie.
> 
> --
> 
> 
> 
0
Utf
8/24/2007 1:28:03 PM
Michael, I believe most MVPs are as willing to listen as they are to preach.  
I have learned more in these groups than I have taught.
Most of the MVPs here have many years of experience in Access and most have 
a general background in IT using other platforms, languages, and database 
engines.
No one technique is perfect in all circumstances.  You have to use your 
experience, knowledge, and judgement to make a decision on how to do 
something.

I think if you read enough posts, you will find that not all MVPs agree on 
every point.  I have seen and been involved in some heated arguments on 
certain points.  Sometimes, I have been convinced my approach was, after all, 
not the best. We all come from different backgrounds and have our own 
prejudice.

The validation level you bring up is one of those.  Some will argue it 
should always be done at the database engine level and others prefer the form 
level.  There are valid argument for both.  If Jet were a Client Server 
databae engine, then database level would be preferred.  But, it is not 
Client Server, it is File Server.  Therefore, there is no performance 
advantage to database validation.  All the processing happens at the client.  
But, if you plan to upsize.... Okay, good point, but how many times does that 
actually happen.  The performance advantage using Jet is with Form or Control 
level validation.  That is because the error is caught before the call to Jet.

But, other MVPs and others as well, would argue the point with me.  Okay,,, 
they can do it however they want and I am happy to hear their arguments.  I 
may be convinced to switch.  But, until one of them is signing my paycheck or 
has convinced me to switch., I will do it my way.

So, regardless of what anyone MVP or not says in these groups, make your own 
judgements, but be willing to listen.
-- 
Dave Hargis, Microsoft Access MVP


"Michael Gramelspacher" wrote:

> In article <1187945301.685492.15830@q5g2000prf.googlegroups.com>, 
> jamiecollins@xsmail.com says...
> >  So why so I feel like I'm the Lone Ranger here about
> > an important feature in a popular SQL DBMS. A very interesting
> > situation.
> > 
> > 
> I think the problem is that the Access query interface does not support many of 
> the things you talk about.  If the interface supported them, then I think we 
> would see the MPVs using them and championing them. Users here imitate what 
> they see the MPVs using. The MPVs here have a huge reputation.  If a person 
> asks about table constraints and an MPV says to forget that and use form event 
> code, then that is the definitive word on the subject.  That is what you are up 
> against.
> 
0
Utf
8/24/2007 1:42:18 PM
On 24 Aug, 13:36, Michael Gramelspacher <grame...@psci.net> wrote:
> I think the problem is that the Access query interface does not support many of
> the things you talk about.  If the interface supported them, then I think we
> would see the MPVs using them and championing them.

I've seen you've written a few table-level CHECK constraints yourself
and I assume you are familiar with the Access query builder tool thing
(consider what you need to do to get the designer to use a subquery)
with its expression builder tool thing: can you imagine how complex a
table-level CHECK constraint builder tool thing would be <g>?! I
really don't think such a beast would practical, therefore I think
writing SQL will always be a prerequisite to be able to write a table-
level CHECK constraint.

Don't forget about the DAO problem, with its hard-coded assumption
that all constraints are implemented using an index -- and you know
how the Access MVPs love their DAO <g>.

> Users here imitate what
> they see the MPVs using. The MPVs here have a huge reputation.  If a person
> asks about table constraints and an MPV says to forget that and use form event
> code, then that is the definitive word on the subject.  That is what you are up
> against.

I can't decide whether you are urging me not bother or proposing me
for MVP <vbg>.

Jamie.

--


0
Jamie
8/24/2007 2:19:40 PM
In article <1187965180.791604.128410@x40g2000prg.googlegroups.com>, 
jamiecollins@xsmail.com says...
> I can't decide whether you are urging me not bother or proposing me
> for MVP <vbg>.
> 
Microsoft has their criteria, but I have my own, and some people who are not 
MPVs meet my criteria. Not that it actually matters.
0
Michael
8/24/2007 4:23:37 PM
Reply:

Similar Artilces:

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

Updating a DATE, only if the page has been modified.
I have a spreadsheet with a cell containing: ="Print Date: "&Text(TODAY(),"mmm-dd-yyyy") I want to add another cell that reflects the date of the last time the sheet was modified/changed. So the logic would be somthing like: IF (Sheet-Changed, Current-Date, Previous-Date) Can any one point me in the right direction? Thank you That requires custom VBA code. If you want help with that just let us know. -- HTH... Jim Thomlinson "Michael" wrote: > I have a spreadsheet with a cell containing: > ="Print Date: "&...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

IIF date statement
Please help. I am trying to turn all dates equaling 1/1/2000 into N/A’s in another column if the date is not 1/1/2000 I need to pull that date pulled over. There can also be blanks in the date 1 column these can come back as blanks. Statement I'm using Test2: IIf([master]![HOC]="1/1/2000","N/A",[master]![HOC]) This comes back with blanks where they should be blanks but errors in any cell having any dates Date 1 Test2 (I need) 12/1/2005 12/1/05 1/1/2000 N/A 1/5/2006 1/5/2006 Thank you in advance. Delimite the date...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Forecasting By Date
I track inspections that are required to be done to aircraft. diffferen inspections have to be done at certain dates. I track these on spreadsheet. I have a column of dates about a mile long. Im trying t get excel to show me only the inspections that are coming up in th next 3 months(90 days) and filter out the rest. I am trying to do thi so I can record a macro to do this task at a click of a button. I woul greatly appreciate any help... -- dzeitle ----------------------------------------------------------------------- dzeitler's Profile: http://www.excelforum.com/member.php?action=get...

input date
hi all, is this possible: i will type 011005 and then excel will automatically format it as 01/10/05 and will be treated as date? thanks. Rufino Only with VBA code or a function in another cell, there is no way with the user interface of doing this automatically -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "rufino palacol jr" <rufinojr54@gawab.com> wrote in message news:%23Ve5wjg%23EHA.3376@TK2MSFTNGP12.phx.gbl... > hi all, > > is this possible: i will type 011005 and then excel will automatically > fo...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

How does MATCH handle a Non-Match?
I would like a steer on how the MATCH Worksheetfunction behaves if it doesn't find an exact match of values in the array it is searching. I am looking through a series of Dates and instead of picking the nearest one - which I need and seems most obvious - it seems to pick the previous one. I.E. - Searching for 14 Oct 2005 In a list which contains (among others) 15 Sep 2005 16 Oct 2005 And it picks the 15 Sep row! PS. Cell values are actually 14/10/2005 but formatted as above. In Excel's Help for the MATCH function, it describes how the values are returned. Chris wrote: > I w...

My inbox isn't up to date
Hi there, The last email I received in my inbox is from Dec 15, 2009. How do I get it current? I have initiate the send/receive command several times and restarted outlook, but it doesn't update my recent emails. Thanks... And is there any err msg in this unknown version of outlook? "Sassa Oz" <Sassa Oz@discussions.microsoft.com> wrote in message news:D7B044C7-C915-4CE7-8A92-EF09EBE540E0@microsoft.com... > Hi there, > The last email I received in my inbox is from Dec 15, 2009. How do I get > it > current? I have initiate the send/receive co...

Index, Match, Min and Max question
I'm trying to retrieve a date (in column A) that corresponds with a Min and Max amount (in columns B:D). Basically I need to know when my amounts hit their Highs and Lows. Can someone help me with this please? Thanks, Maria Dates in column A; values in B Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0)) Returns the date corresponding the max value in B Is this what was needed? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email <mgriffiths@klmtel.net> wrote in message news:1194464476.732636.234500@50g2000hsm.googlegroups.com... >...

Recording the Date and Time When a Record is Modified in Access
Hello, I used the 'BeforeUpdate' procedure for a form to record the date and time a record has been modified and the date that appears on the form and on the table is not right. For example I editied a record and expected the date to be 10/18/2007, but instead the form is showing 12/30/1899. The table has also been updated with this date. The time is correct but the date is wrong. Could someone please tell me how to correct this? Thanks, Tina I. Smith, MBA Manulife Financial Financial Analyst II US Support Operations What's the code you're using in the BeforeUpdate even...

version matching
Do both people have to have the same version of Outlook running in order for Tasks and Appointments to sync up properly when emailed between clients? I'm seeing some irregularities between someone running Outlook in Office 2000 and mine which is part of Office 2003. Appointments with reminders sent from Office 2002 or later will show up as text in Outlook 2000 unless a hotfix is applied to the Outlook 2000 installation. Besides this issue, I am not aware of any others except for ..pst file type incompatibilities, easily solved on the 2003 side. -- Milly Staples [MVP - Outlook] ...

More date issues
Hi, I need to pass on a month as field to a report which uses stored procedure to process data. Right now I use date_start and date_end as a range parameter but because the report is based on a full month, I prefer select the month (and the year). So can it be done? I'm thinking of a combobox which lists the months (and year?). TIA Mike On Wed, 6 Feb 2008 21:25:46 +0100, "Mike" <mikenospam@yahoo.es> wrote: >Hi, > >I need to pass on a month as field to a report which uses stored procedure >to process data. Right now I use date_start and date_end as a r...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

read Date notation
Can anybody help me with a ps script. i have the beginning of the script : $a = Get-Content c:\datdef.txt | Select-String "CurDefs" output is this now : CurDefs=20100620.006 i want to do this : 20-06-2010 006 thanks Mike So, you want $a to have the value '20-06-2010 006' ? - Larry On 6/21/2010 1:32 PM, mike wrote: > Can anybody help me with a ps script. > i have the beginning of the script : > $a = Get-Content c:\datdef.txt | Select-String "CurDefs" > output is this now : > CurDefs...

fuzzy logic matching in Excel
Is there such a thing as fuzzy logic matching in excel? For example, if I have Name Addr1 Addr2 Addr3 Davie Kings Road London England and Davie Kings Road - London This should match as a potential pair. How can I do this in Excel? Any ideas? Nothing built in. Davie wrote: > > Is there such a thing as fuzzy logic matching in excel? > > For example, if I have > Name Addr1 Addr2 Addr3 > Davie Kings Road London Eng...

how to create conditional format linked to date
I wish to create a conditional format linked to date, for example if info. in cell is less than 2 months old, the cell is green, if info. is more than 2 months old the cell turns red. one way: Format the cell to green. The Format>Conditional Formating>change cell is to formula is =if(today()-60=a1,true,false) and set the format to red HTH "nicko68" wrote: > I wish to create a conditional format linked to date, for example if info. in > cell is less than 2 months old, the cell is green, if info. is more than 2 > months old the cell turns red. I don't beli...

Getting up to date with my retirement fund
A few years ago I tried to setup my retirement account (which is in a 403(b)) in MS Money. Well, things have changed, I've added new funds and removed ones that were performing badly, but I never entered those changes in MS Money. I also have it setup so that when I get paid Money tracks my gross pay, and distributes the funds into categories like federal income tax, state income tax, my 403(b) plan, etc. So, Money has been sitting there accumulating inaccurate data for my 403(b). I would still like to track my retirement in Money, but now it is hopelessly out of date. How should I be...

Offset match formula
Hi, I have the following offset match formula that I use to correctly pull data into a summary sheet by date and metric name. Every day I update my week and it pulls in the new data corrrectly. Is it possible to add to this formula so that I can sum range of dates in my source sheet? For example, I have a YTD total that I would like to be able to use this formula for. But in order to do that I need to adjust it so that it will sum up all of the rows between two dates. Is this possible? What would the formula look like? Do I need to use a different formula? =OFFSET(Source!$F$118:$ES$201...

using MATCH for multiple matches
Greetings Is it possible to use the MATCH function to list all matches in a column rather than just the first one, or is there another way of doing this? Many thanks. Mat, Not sure what you want to extract, but the easiest way is to autofilter your list: Data | Filter | Autofilter.... HTH, Bernie MS Excel MVP "Mat" <buffaloesinc@DELETE2REPLYhotmail.com> wrote in message news:c7diur$ksu$1@news5.svr.pol.co.uk... > Greetings > > Is it possible to use the MATCH function to list all matches in a column > rather than just the first one, or is there another way o...