Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: I can't make this query
Thread ID: 288235 Message ID: 289305 # Views: 46 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Thursday, December 30, 2010 1:59:32 PM         
   


> >
> >
> > Hi Anders
> > Sorry for the late reply...
> > I corrected fieldname-typos in your query as follow
> >
SELECT T1.*, T2.trans_date ;
> > 	FROM Lib_trans T1 ;
> > 		JOIN Lib_trans T2 ;
> > 		ON T1.book_id=T2.book_id AND T1.stud_id=T2.stud_id ;
> > 		;
> >  	WHERE ;
> >  		T1.comingback = .F.  ;
> >  		AND T2.trans_date = (SELECT MIN(trans_date) ;
> >  		FROM Lib_trans T3 WHERE  T3.trans_date>=T1.trans_date AND T3.comingback=.T.)
> > 
but it shows wrong results.
> > Which actually doesn't matter, I'm more interested in the suggestion you made.
> > I have thought about doing as you said before starting this thread, but a certain (may be wrong) opinion about transaction tables prevented me from doing it: I think/thought that we should not go back and modify old records in transaction tables. We should always add new records and store actual information in these new records.
> > What do you think? Is such a opinion completely out-of-fashion (expired :-U) ? In POS I do it that way: no record can be edited, only adding records is allowed...
> >
> >
> > Regards
> > Samir
>
>
>
> I'd suggest three datetime columns: out, due, returned, in the bookloan transaction table. The default Due data should probably best be calculated from the Books table where it's stored as the number of days you're allowed to keep a loan of that particular book. It can change at any time if for instance a book becomes required reading in a course.
> Returned books will then be an update event in the loans trans table.
> -Anders

Thank you for reply, Anders. Yes, I have done so meanwhile, and it works fine. Report queries are much easier now...

Regards
Samir

ENTIRE THREAD

I can't make this query Posted by Samir H. @ 12/18/2010 4:28:32 PM
RE: I can't make this query Posted by tushar @ 12/18/2010 5:03:30 PM
RE: I can't make this query Posted by Samir H. @ 12/18/2010 5:29:22 PM
RE: I can't make this query Posted by Borislav Borissov @ 12/18/2010 5:04:44 PM
RE: I can't make this query Posted by tushar @ 12/18/2010 5:21:26 PM
RE: I can't make this query Posted by Samir H. @ 12/18/2010 5:42:15 PM
RE: I can't make this query Posted by Anders Altberg @ 12/18/2010 7:24:13 PM
RE: I can't make this query Posted by Samir H. @ 12/18/2010 5:25:24 PM
RE: I can't make this query Posted by Anders Altberg @ 12/18/2010 7:43:53 PM
RE: I can't make this query Posted by Samir H. @ 12/28/2010 3:32:52 AM
RE: I can't make this query Posted by Anders Altberg @ 12/30/2010 1:48:06 PM
RE: I can't make this query Posted by Samir H. @ 12/30/2010 1:59:32 PM
RE: I can't make this query Posted by Jun Tangunan @ 12/31/2010 2:52:21 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 3:26:06 AM
RE: I can't make this query Posted by Ony Too @ 12/31/2010 4:57:14 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 5:11:53 AM
RE: I can't make this query Posted by Ony Too @ 12/31/2010 11:26:02 AM
RE: I can't make this query Posted by Jun Tangunan @ 1/3/2011 4:56:55 AM
RE: I can't make this query Posted by Samir H. @ 1/3/2011 5:11:03 AM
RE: I can't make this query Posted by Muhammad Majid Ali Attari @ 12/31/2010 6:24:50 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 6:54:07 AM
RE: I can't make this query Posted by Muhammad Majid Ali Attari @ 1/1/2011 5:45:42 AM
RE: I can't make this query Posted by Mustapha Bihmouten @ 12/31/2010 11:06:04 AM
RE: I can't make this query Posted by Samir H. @ 12/31/2010 4:12:24 PM