A.D.
My understanding is that Terry wanted to find all the records where Start matched End in a specific record (or possibly all records at one time).
Results would be such that Record 2 would be returned if Record 2's Start equals Record 1's End. (ro maybe it was Rec 2's End matched Rec 1's Start, I can't remember)
As you said, it would be most helpful if Terry responded.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@...> wrote:
>
> Bill,
>
> There has been no response so far from the OP (Terry Olsen). I was just curious whether I could correctly understand his needs, and what could have been the output ideally sought by him. It would have been helpful if the OP were to provide some sample data as well as desired sample output (based upon such data) as requested in my post.
>
> Just wondering whether you might like to add anything, before this thread becomes dormant.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: A.D. Tejpal
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Saturday, August 20, 2011 14:04
> Subject: Re: [MS_AccessPros] Re: Need SQL help to find records in a table = to a column in the same table
>
>
> Bill,
>
> Apparently, for a given reference record, Terry is seeking to display that record along with all other records whose Start equals the End of reference record.
>
> Let us consider table T_A having fields ID (PK - autonumber), Start and End. If the very first record in the table is to be reckoned as reference record, sample query Q_A1 as given below, should get the desired output:
>
> Q_A1 (Sample Query)
> ===============================
> SELECT T_A.*
> FROM T_A
> WHERE (((T_A.ID)=DLookUp("ID","T_A"))) OR (((T_A.Start)=DLookUp("End","T_A")));
> ===============================
>
> However, if user choice is to be allowed for pinpointing the reference record, sample query Q_A2 as given below, could be adopted (User prompt in sample query could be replaced by form based parameters):
>
> Q_A2 (Sample Query)
> ===============================
> PARAMETERS [Enter ID Of Starting Record] Long;
> SELECT T_A.*
> FROM T_A
> WHERE (((T_A.ID)=[Enter ID Of Starting Record])) OR (((T_A.Start)=DLookUp("End","T_A","ID = " & [Enter ID Of Starting Record])));
> ===============================
>
> If the results are not in line with what is actually needed, it would be helpful if Terry could provide some sample data along with sample output based upon such data.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: Bill Mosca
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Saturday, August 20, 2011 00:47
> Subject: [MS_AccessPros] Re: Need SQL help to find records in a table = to a column in the same table
>
>
> A.D.
>
> I know you decided not to join in on this thread because Terry said Lee's solution worked, but as I suspected, it produces a cartesian set.
>
> Do you have a better solution?
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, "Terry Olsen" <terryomsn@> wrote:
> >
> > It is Cartesian, but it gives me data to work with. I'd be interested in knowing how to refine it.
> > Thanks
> > Terry
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Lee" <leevt99@> wrote:
> > >
> > > Make sure it's not cartesian like Bill said, I didn't even think to check when I ran my mini-test.
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Terry Olsen" <terryomsn@> wrote:
> > > >
> > > > Thanks, Lee. Your code worked like a charm.
> > > > Terry
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "Lee" <leevt99@> wrote:
> > > > >
> > > > > Something like
> > > > >
> > > > > SELECT a.start, b.end FROM mytable as a
> > > > > INNER JOIN mytable as b ON a.start = b.end
> > > > >
> > > > > ?
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > > > >
> > > > > > Terry
> > > > > >
> > > > > > Maybe A.D. Tejpal will spot this request. He's very good at taking something way out of the ordinary and finding a solution.
> > > > > >
> > > > > > He hails from India so the time difference is huge. It might take a day or so for him to respond.
> > > > > >
> > > > > > bill
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, "Terry Olsen" <terryomsn@> wrote:
> > > > > > >
> > > > > > > It would be any record's Start equaling Rec 1's End
> > > > > > > Thanks,
> > > > > > > Terry
> > > > > > >
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > > > > > >
> > > > > > > > Terence
> > > > > > > >
> > > > > > > > You mean Record 1's End equals Record 1's Start?
> > > > > > > > SELECT * FROM MyTable
> > > > > > > > WHERE End = Start
> > > > > > > >
> > > > > > > > Or are you talking about any record's Start equaling Rec 1's End?
> > > > > > > >
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Bill Mosca, Founder - MS_Access_Professionals
> > > > > > > > http://www.thatlldoit.com
> > > > > > > > Microsoft Office Access MVP
> > > > > > > > https://mvp.support.microsoft.com/profile/Bill.Mosca
> > > > > > > >
> > > > > > > >
> > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, "Terry Olsen" <terryomsn@> wrote:
> > > > > > > > >
> > > > > > > > > I have a table, in which I'm trying to find all related records for which one column equals a second column in the same table.
> > > > > > > > > tableA has the columns Start and End
> > > > > > > > > I'm looking for all records in tableA in which End is equal to Start.
> > > > > > > > > Thank you,
> > > > > > > > > terryomsn
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar