Millard, Paul --- Sr. Developer Analyst ---WGO
Paul.Millard at freight.fedex.com
Fri Jun 4 14:10:53 CDT 2004
Folks, I need help in calculating working days shown in table below. The tricky thing about this is I need to calculate the number of days between departments, particularly the 'Pricing' department. In table below, there are a total of 7 days using DATEDIFF. I need to report number of days within Pricing, which is two days in the example table shown below. i.e. Pricing on 2004-05-01 to 2004-05-2 equals 1 day and Pricing again on 2004-05-07 to 2004-05-08. Thanks in Advance! Paul Millard San Jose, CA table1 StatusDate Department 2004-05-01 Pricing (1 day) 2004-05-02 Costing (5 day) 2004-05-07 Pricing (1 day) 2004-05-08 Closed -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Joseph O'Connell Sent: Friday, June 04, 2004 10:36 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] RE: Getting Started Floating point numbers are not the best choice for storing monetary values. Define the fields as Currency and you will have precise dollars and cents. Joe O'Connell -----Original Message----- From: Mackin, Christopher <CMackin at quiznos.com> To: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com> Date: Friday, June 04, 2004 12:14 PM Subject: RE: [AccessD] RE: Getting Started |One note on the inconsistency of the Round function in Access. Aside from |the floating point data storage issues, Access utilizes what I've been told |is "Banker's Rounding" which will always round to the nearest even number. | |So in the immediate window, here are the results: |? Round(1.55,1) | 1.6 |? Round(1.65,1) | 1.6 | |Both go to 1.6 because it's the nearest even last decimal place to round to. | |-Chris Mackin | |-----Original Message----- |From: Brett Barabash [mailto:BBarabash at tappeconstruction.com] |Sent: Friday, June 04, 2004 10:42 AM |To: 'Access Developers discussion and problem solving' |Subject: RE: [AccessD] RE: Getting Started | | |>The lady I am working for is quite upset about what we've termed "The |Rounding Issue" of Access. | |If you google for info on floating point data storage, you will learn that |this is definitely not an Access-specific issue. For example, we have an |accounting system on a SQL Server 2000 platform that has the same quirks |(funny, I don't recall receiving an invoice for 11.7687381!). It is an |inherent difficulty of converting numerical data to/from the binary format |in which it is stored. | |Now, OTOH, Access has a lousy (inconsistent) Round function built into it. |There are several alternatives to that. Gustav wrote a pretty good one, for |example. | | |-----Original Message----- |From: David Lind [mailto:David_Lind at acordia.com] |Sent: Friday, June 04, 2004 11:27 AM |To: Robert L. Stewart |Cc: accessd at databaseadvisors.com |Subject: [AccessD] RE: Getting Started | | |Robert, | |Thanks for the suggestions. | |Right now my being self-taught is the biggest problem. Because I am |self-taught, I've managed to not teach myself good programming basics. (I am |looking into legal action against myself right now!) The biggest problem I |have is non-normalized tables. The tech came down and spent 3 days here and |one of the biggest issues was the normalization of the tables. (I have one |table with 80+ fields including the ANPK. Didn't realize it was quite that |bloated!) There are other issues such as I have a financial table currently |setup that handles both debits and credits. However multiple debits can |refer to a single credit and sometimes multiple credits can refer to a |single debit. The lady I am working for is quite upset about what we've |termed "The Rounding Issue" of Access. I never realized that Access |automatically figures everything out to the 11th decimal point on |calculations. That creates a real problem when trying to get exact finances |and a clear statement for an insurance bil! l! Those are just the big |problems, and I haven't even begun to scratch the surface. | |I would love to get as much input as possible to get things fixed (in fact I |wouldn't mind 2nd or 3rd opinions on what the tech we had said), however, we |have strict security issues and I think the tech that helped had to have |blood drawn, produce three forms of ID, prove that the last 4 generations of |his and his wife's families were not in any way linked to any possible |criminal activity and promise the life of his firstborn child (if he ever |has one) just to be allowed to look at our data. The database I'm working on |is for the Medical Malpractice division of an insurance company that was |recently bought by a bank, so we have all kinds of security and privacy |issues involved. | |David | | |--------------------------------------------------------------------------- - |---------------------------------------- |The information in this email may contain confidential information that |is legally privileged. The information is only for the use of the intended |recipient(s) named above. If you are not the intended recipient(s), you |are hereby notified that any disclosure, copying, distribution, or the |taking |of any action in regard to the content of this email is strictly prohibited. |If |transmission is incorrect, unclear, or incomplete, please notify the sender |immediately. The authorized recipient(s) of this information is/are |prohibited |from disclosing this information to any other party and is/are required to |destroy the information after its stated need has been fulfilled. | |Any views expressed in this message are those of the individual |sender, except where the sender specifies and with authority, |states them to be the views of Tappe Construction Co. | |This footer also confirms that this email message has been scanned |for the presence of computer viruses.Scanning of this message and |addition of this footer is performed by SurfControl E-mail Filter software |in conjunction with virus detection software. | |-- |_______________________________________________ |AccessD mailing list |AccessD at databaseadvisors.com |http://databaseadvisors.com/mailman/listinfo/accessd |Website: http://www.databaseadvisors.com |-- |_______________________________________________ |AccessD mailing list |AccessD at databaseadvisors.com |http://databaseadvisors.com/mailman/listinfo/accessd |Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com ******************************************************* This message conatains information that is confidential and proprietary to FedEx Freight or its affiliates. It is intended only for the recipient named and for the express purpose(s) described therein. Any other use is prohibited. *******************************************************