artful at rogers.com
artful at rogers.com
Wed Nov 22 06:14:50 CST 2006
Right on. That's why I suggested the default GetDate(). There is a whole other subject on this, about which I have written, but I googled it and it didn't come up, so perhaps I wrote it and forgot to sell it to somebody. The gist is this: it's called PITA, which doesn't mean pain in the arse, but rather Point In Time Architecture. Without PITA, the central problem with relational databases is that they don't provide an instant "roll back to August 1" capability. With PITA, they do. It's not all that complicated, but it does require a detailed walk-through so you can understand all the implications, the most critical of which is, "Nothing is ever updated. An updated row is actually replaced, and the updated row's EndDate column is updated to reflect the datetime on which the row was "changed". Thus it becomes possible to issue a query that reflects the state of the database on August 1, 2005. Obviously this increases the size of the db significantly, but in certain environments (such as medical), this is critical -- who was JWC's physician on that date, and what tests were performed, and by which medicos, and so on. So. Today's job is to dig out that PITA article and pitch it to somebody. Arthur ----- Original Message ---- From: JWColby <jwcolby at colbyconsulting.com> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com> Sent: Wednesday, November 22, 2006 6:16:35 AM Subject: Re: [AccessD] Stored Procedure not producing results >This assumes of course that the scope is expandable so easily. (A good reason to have a "DateEntered" column in every table, which defaults to GetDate()). Amen! I do that regularly now. It juts makes managing data so much easier when you can see when it was entered. I actually use the date + time so that I can see things like how long an append query takes to run (time of last entry in the "batch minus time of first entry in the "batch"). John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com Sent: Wednesday, November 22, 2006 6:08 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Stored Procedure not producing results If the structures haven't changed, then the data is the villain. But you have a concrete clue to work from. Devise some scope that will include only the data from two months ago and verify your assertion. Then expand the scope to "two months ago plus a day" and run it again. Repeat until failure. This assumes of course that the scope is expandable so easily. (A good reason to have a "DateEntered" column in every table, which defaults to GetDate()). ----- Original Message ---- From: David Emerson <newsgrps at dalyn.co.nz> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>; Access Developers discussion and problem solving <accessd at databaseadvisors.com> Sent: Tuesday, November 21, 2006 10:53:58 PM Subject: Re: [AccessD] Stored Procedure not producing results Worse - my database from two months ago works fine, but the latest version is the one that is causing the problem. This may indicate a data problem perhaps? David -- 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