M
mscertified
How do I do a DATEDIFF calculation but ignoring Saturdays and Sundays?
D
Douglas J. Steele
Take a look at the Date/Time section of The Access Web.
http://www.mvps.org/access/
There are a couple of different solutions there.
http://www.mvps.org/access/
There are a couple of different solutions there.
J
Jamie Collins
Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
"A calendar table can make it much easier to develop solutions around
any business model which involves dates. Last I checked, this
encompasses pretty much any business model you can think of, to some
degree. Constant problems that end up requiring verbose, complicated
and inefficient methods include the following questions: How many
business days between x and y?..."
Jamie.
--
http://www.aspfaq.com/show.asp?id=2519
"A calendar table can make it much easier to develop solutions around
any business model which involves dates. Last I checked, this
encompasses pretty much any business model you can think of, to some
degree. Constant problems that end up requiring verbose, complicated
and inefficient methods include the following questions: How many
business days between x and y?..."
Jamie.
--
K
Klatuu
The only thing a business calander needs is one table with Holiday dates in
it and switch to tell if it is a working day or not. I added this switch
because I did some work for a utility company where some Saturdays are work
days and some or not. I set the switch to True if it is a working day and
False if it is not:
tblCalendar
CAL_DATE - Date/Time - PK
DATE_DESCR - Text
WORK_DAY - Boolean
I read the info at the link you posted. I was not terribly impressed. I
have enough date handling functions to calculate anything I need using
nothing more that the above table.
Have a wonderful day, Jamie (My favorite Anti-Access person)
it and switch to tell if it is a working day or not. I added this switch
because I did some work for a utility company where some Saturdays are work
days and some or not. I set the switch to True if it is a working day and
False if it is not:
tblCalendar
CAL_DATE - Date/Time - PK
DATE_DESCR - Text
WORK_DAY - Boolean
I read the info at the link you posted. I was not terribly impressed. I
have enough date handling functions to calculate anything I need using
nothing more that the above table.
Have a wonderful day, Jamie (My favorite Anti-Access person)
J
Jamie Collins
I prefer one with all dates with a column to indicate columns for
holidays. A calendar table has many uses e.g. remember the example I
posted where a calendar table is used to efficiently identify
overlapping periods? You couldn't do that with a table of Holiday
dates
I would agree it's not the definitive article on the subject, notably
because it uses SQL Server dialect SQL, but it's the best I know of as
an introduction to the data-driven approach of a calendar table as an
alternative to the 'calculation engine' approaches Douglas J. Steele
linked to.
Jamie.
--
K
Klatuu
I don't see that the overlapping dates example uses the calendar table, so
that is a non issue.
In a true Client Server environment, your approach may have some value;
however, as always, you forget this is an Access newsgroup (or you are in
denial), so what works well for SQL Server applications may not always be the
best solution for Access which is a File Server application. I doubt the
performance in Access using the data driven module would be as good as the
code driven model I prefer.
The only thing I found that I don't already have canned code for it the
Third Friday question. Well, I guess I know what I have to do this afternoon.
that is a non issue.
In a true Client Server environment, your approach may have some value;
however, as always, you forget this is an Access newsgroup (or you are in
denial), so what works well for SQL Server applications may not always be the
best solution for Access which is a File Server application. I doubt the
performance in Access using the data driven module would be as good as the
code driven model I prefer.
The only thing I found that I don't already have canned code for it the
Third Friday question. Well, I guess I know what I have to do this afternoon.
D
Douglas J. Steele
Take a look in my June, 2005 "Access Answers" column for a generic function.
http://www.accessmvp.com/DJSteele/SmartAccess.html
http://www.accessmvp.com/DJSteele/SmartAccess.html
K
Klatuu
Well, I need to improve my routine if I want to be able to calculate
Victouria Day
<just trying to improve my spelling so I wont mess up on Labour again <g>>
Victouria Day
<just trying to improve my spelling so I wont mess up on Labour again <g>>
J
Jamie Collins
See this:
CHECK (NOT EXISTS (
SELECT T1.key_col, C1.CalDate
FROM Calendar AS C1
INNER JOIN MyHistoryTable AS T1
ON C1.CalDate BETWEEN T1.start_date AND T1.end_date
GROUP BY T1.key_col, C1.CalDate
HAVING COUNT(*) > 1));
How gracious of you
Agreed. But the SQL (the language) is supported in Jet (which I what I
assume you mean when you say 'Access' in context). If you it won't
work for Jet, the onus is on you to demonstrate it, rather than simply
posting disparaging remarks.
Care to put you doubts to the test and post your finding here?
Jamie.
--
K
Klatuu
CHECK (NOT EXISTS (
I missed that. In this case, finding overlaps and gaps, the SQL approach
would be easier to code.
The majority of your posts are based on SQL server. Rather than me
forwarding them, read them.
Not fully. No stored procs in Jet, for example. In context, yes, I mean
Jet. I don't mean that it will not function, I mean that it may not be a
good choice for a Jet implementation. I see it as a performance issue.
I missed that. In this case, finding overlaps and gaps, the SQL approach
would be easier to code.
The majority of your posts are based on SQL server. Rather than me
forwarding them, read them.
Not fully. No stored procs in Jet, for example. In context, yes, I mean
Jet. I don't mean that it will not function, I mean that it may not be a
good choice for a Jet implementation. I see it as a performance issue.
Okay, but it will take a little time to set something up.
J
Jamie Collins
I would like to be sensitive here because I'm conscious that English
might not be your first language. However, I must point out that
"majority" means "better than 50%". On this basis, I emphatically
reject your claim. I sometimes compare Jet to SQL Server as an aside
to add (IMO) interest; if you are not interested, it's your right to
ignore my asides.
You want me to do the work? Why am I not surprised <g>? OK then,
here's a review of my last ten posts:
1) SCALING OF DECIMAL VALUE RESULTED IN DATA TRUNCATION
http://groups.google.co.uk/group/microsoft.public.access/msg/edeb6a7de1caa190
JamieC on Jet: "If this were Jet... when trying to force a value with
excess
scale into a Jet column of type DECIMAL... For example, Jet DDL... Jet
does error when the integer portion won't fit..."
JamieC on SQL Server: [None].
--
2) Database Design and Relationships
http://groups.google.co.uk/group/microsoft.public.access.tablesdbdesign/msg/5e2cd1d962ff58f5
JamieC on Jet: [None].
JamieC on SQL Server: [None].
[Note: I described a design principle here].
--
3) table with no primary key
http://groups.google.co.uk/group/microsoft.public.access.tablesdbdesign/msg/56e7f741298d4b52
JamieC on Jet: "...because Jet is a SQL engine... For Jet, PRIMARY KEY
designation determines physical ordering on disk... bold text in the
Relationships window in the Access
user interface..."
JamieC on SQL Server: [a link to a SQL Server MVP's blog but no
explicit mention].
--
4) Parameter query in which you can search for any word
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/13a896422923f642
JamieC on Jet: "Better to be ANSI mode neutral: ALike '%' & [Your
Parameter] & '%'
[note: ANSI Query Modes and the ALike operator are exclusive to Jet].
JamieC on SQL Server: [None].
--
5) Creating a complicated query
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/2e8e11f9b706aede
JamieC on Jet: "Here's a simple example using northwind to divide
employees into three
'classes' using the MOD operator" [Note SQL Server has no MOD
operator].
JamieC on SQL Server: [None].
--
JamieC on Jet: "replace 'Date()' with 'Now()'..."
[Note SQL Server uses getdate() and CURRENT_TIMESTAMP];
".. DATEADD('M', DATEDIFF('M',..."
[Note the quotes around 'M' would cause an error on SQL Server].
"...#1990-05-01 00:00:00#..."
[Note the # delimiters would cause an error in SQL Server; the
equivalent 'safe' SQL Server format would be '1990-05-01T00:00:00.000'
which would in turn error on Jet].
JamieC on SQL Server: [None].
--
6) If... Else Statement in SQL
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/bed588027357663a
JamieC on Jet:
"SELECT ID, Product, Trading_ID, SWITCH (..."
[Note SQL Server has no SWITCH() expression].
JamieC on SQL Server: [None].
--
7) have query based from a left join, now need to exclude records....
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/7467c6f4594b39c5
JamieC on Jet: [None].
JamieC on SQL Server: [None].
[Note: I posted a 'vanilla' WHERE clause here].
--
8) how would I write this criteria for a query?
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/ecc8a59d7e0b338c
JamieC on Jet: "...use IsNull([Date Approve])..."
[Note SQL Server has no IsNull() expression].
JamieC on SQL Server: [None].
--
9) Sort Last 4 Numbers of SSN
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/5b8a57a19dc7108d
JamieC on Jet:
"...use a stored proc (Access Query object with a PARAMETERS
clause)..." [Note SQL Server has no PARAMETERS clause]
"...Example using ANSI-92 Query Mode syntax SQL DDL..."
[Note ANSI Query Modes are exclusive to Jet].
"...CREATE PROCEDURE GetMember (arg_EIN CHAR(8) = NULL,..."
[Note SQL Server parameter names must be prefixed with the commercial
at ('@') character].
JamieC on SQL Server: [None].
--
10) What function will return the most recent entry date for a record?
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/c41c0cc07416d7c6
JamieC on Jet: ...SELECT ID, Date1 AS result1, IIF(Date2 > result1...
[Note SQL Server has no IIF() expression].
JamieC on SQL Server: [None].
--
Get with the 1990's ROFL! See:
Microsoft Office Access 2003 Help
CREATE PROCEDURE Statement
http://office.microsoft.com/en-gb/access/HP010322191033.aspx
"Creates a stored procedure ."
Seriously, take my advice: read the following two articles:
Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
Advanced Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx
Having read them, you may be better placed to spot Jet SQL syntax in
the 'groups.
Please also see:
Microsoft Communities Home
Rules of Conduct
http://www.microsoft.com/communities/conduct/default.mspx
"Please avoid personal attacks [and] slurs"
Note I'm not the only regular in the 'groups who has recommended using
a Calendar table:
Allen Browne Access MVP e.g.
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/a126329ced95ef8
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/a96e9cc47c54fbb7
Ken Sheridan e.g.
http://groups.google.co.uk/group/microsoft.public.access.gettingstarted/msg/7d2c5e8827905e0a
Michael Gramelspacher e.g.
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/ac8f99fc86e7c26d
Gary Walter e.g.
http://groups.google.co.uk/group/microsoft.public.access.queries/msg/e8512b21da59dd71
Bob Quintal e.g.
http://groups.google.co.uk/group/microsoft.public.access/msg/c413dc7013a44b40
Just to be clear, I am NOT urging you to give these people a hard
time; I'm asking you to cease posting your spurious "client server"
"SQL Server" responses to me simply because a calendar table is not
your style.
Dave, you're an MVP now. You are expected to set a good example rather
than lower the tone.
Jamie.
--
K
Klatuu
I have reviewed the articles you posted and there is something there to
learn; however, I do take a couple of exceptions. You and Microsoft are
overly optimistic about ansi 92 compliance. For example, try to create a
temporary table using DDL with Jet. It doesn't work. Or try SELECT %
FROM... OOOPs! Syntax error. There are other issues I have run accross, but
can't remember them at the moment.
As to stored procedures, It may look like a stored procedure, but as Access
is a file server application and all processing is done on th workstation, I
don't believe it qualifies as a true Stored Procedure.
English is my only language and I am pretty good at it. I see you examples,
but as I have previously stated, you knowledge of SQL is exceptioinal;
howver, the OP can be confused by generic answers.
I consider you knowledgable and intelligent. We do have differences in our
approach. I have never said you are wrong about anything. I consider our
discussions more a philosophical exchange.
Try not to get your feathers up and have a wonderful day.
--
Dave Hargis, Microsoft Access MVP
learn; however, I do take a couple of exceptions. You and Microsoft are
overly optimistic about ansi 92 compliance. For example, try to create a
temporary table using DDL with Jet. It doesn't work. Or try SELECT %
FROM... OOOPs! Syntax error. There are other issues I have run accross, but
can't remember them at the moment.
As to stored procedures, It may look like a stored procedure, but as Access
is a file server application and all processing is done on th workstation, I
don't believe it qualifies as a true Stored Procedure.
English is my only language and I am pretty good at it. I see you examples,
but as I have previously stated, you knowledge of SQL is exceptioinal;
howver, the OP can be confused by generic answers.
I consider you knowledgable and intelligent. We do have differences in our
approach. I have never said you are wrong about anything. I consider our
discussions more a philosophical exchange.
Try not to get your feathers up and have a wonderful day.
--
Dave Hargis, Microsoft Access MVP
Jamie Collins said:
J
Jamie Collins
I'll take your good advice
Have a good one yourself...Access 2007 Help: SQL reference
CREATE TABLE Statement
http://office.microsoft.com/en-gb/access/HA012314411033.aspx
Syntax
CREATE [TEMPORARY] TABLE table (...
[Unquote]
Yes that's SQL-92 syntax but Jet doesn't support it! AFAIK this is
merely poor editorship. And it's not the only 'fabricated' feature on
this page e.g.
"You can use NOT NULL ... within a named CONSTRAINT clause that
applies ... to a multiple-field named CONSTRAINT." Erm, no you can't!
Also, why is the DEFAULT clause not mentioned here?!
And it's not the only page with such errors e.g.
CONSTRAINT Clause
http://office.microsoft.com/en-gb/access/HA012314371033.aspx
" NOT NULL (notnull1[, notnull2 [, ...]])" -- no (again!)
"[ON UPDATE ... SET NULL]" -- no, that's not supported.
Also, where are CHECK constraints?!
OK, then, so there's some shockingly bad technical authorship
throughout the Jet SQL Help, especially considering it's been like
this for two releases now (I've given feedback to Microsoft, I've told
Albert and Larry, what else can a mere mortal do <g>?)
Dunno what you mean by SELECT %. If you mean the MOD operator, it was
omitted from SQL-92 by design (probably because there were too many
and varied implementations already in existence). If you mean the
wildcard, I've recently been advised that using ALIKE with the '%' and
'_' wildcards in Jet yields the same behaviour in both Query Modes so
I hope everyone would be happy with this.
Bloopers aside, what's the issue with ANSI SQL? Microsoft do NOT claim
entry level SQL-92 for Jet; I refute their claim that it is 'near'
entry level because there are some serious omissions. Most notable,
IMO, is the UPDATE syntax which should be:
UPDATE table
SET column1 = (<scalar subquery>)
For example, this works fine:
UPDATE Employees
SET has_sales = 1
WHERE EXISTS
(
SELECT *
FROM Orders AS O1
WHERE Employees.EmployeeID = O1.EmployeeID
);
....but this does not:
UPDATE Employees
SET has_sales = IIF(EXISTS
(
SELECT *
FROM Orders AS O1
WHERE Employees.EmployeeID = O1.EmployeeID
), 1, 0)
The failure message is the oddly applied, "Operation must use an
updateable query." Mind you, they use the same message to mean, "We
intentionally crippled updating Excel using Jet SQL in Access" ;-)
We're supposed to use a JOIN syntax which gives unpredictable results
but that's another story...
Yes, I would like entry level SQL-92 compliance for Jet. Yes, I would
like full SQL-92 compliance for Jet. Yes, I would like a truly
relational (e.g. D compliant) language for Jet. And yes, none of these
wishes will ever come to fruition: Jet is currently in maintenance
mode and anyhow is too badly flawed... erm, I mean, has a distinctive
design
For me, the bottom line is that ANSI-92 Query Mode is richer than
ANSI-89 ('traditional') Query Mode, making it more suited to newsgroup
posts e.g. easier say CREATE VIEW than to say, "In the Access user
interface, create a Query object whose SQL View comprises a non-
parameterized SELECT query that returns a resultset..." etc. You would
try to deny its existence at your peril; rather, you should IMO code
for it even if you don't personally use it e.g. avoid ANSI-89
('traditional') Query Mode-only wildcard characters otherwise
*someone* could use ANSI-92 Query Mode to insert those characters as
literals.
And for the very same reasons some people say that Jet SQL may look
like the SQL language but do not believe it qualifies as a true SQL
implementation (and in turn some do not believe SQL DBMS qualifies as
relational technology); the weapons of choice here are the DISTINCTROW
qualifier and the FIRST and LAST set <sniggers> functions. If Jet
isn't a SQL DBMS then can the last person please turn off the
lights...
I understand where you are coming from in that the 'stored procedure'
moniker is a little controversial (I usually avoid it in favour of SQL
PROCEDURE) but there is no denying it, it is what it is: a persisted
SQL object (better IMO for ten applications to call a shared proc than
to have ten local implementations of the same logic using ad hoc SQL)
whose resultset (if it returns one) cannot be used as a table (i.e.
cannot be JOINed to another table, cannot be operated on using INSERT/
UPDATE/DELETE), giving us a distinction between a proc and a VIEW
(viewed table) with increased ability to manage permissions: removing
update privileges from base tables, hiding and deriving columns in
VIEWS, managing CRUD operations via SQL procs. Better practice which
leads naturally to easier maintenance, IMO.
Jamie.
--