Re: ASP / Access: use of same table twice in one query

From: Ron (r_at_xs.nl)
Date: 02/10/04 

Date: Tue, 10 Feb 2004 17:39:37 +0100

Thx,
I needed the two user tables, because the data of the user and his manager
are in the same table and I need them both in one query.
I agree it is better to specify the fields I need in the select statement,
but as I needed it all...

Thanks for your fast reponse,
Kind regards,
Ron

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> schreef in bericht
news:O$E4UY%237DHA.3012@TK2MSFTNGP09.phx.gbl...
> That's right. You cannot use the table names. They are not part of the
field
> names supplied to ado. The solution is to use the good programming
practice
> of always explicitly naming the fields you want your query to return,
> avoiding the use of selstar (select *).
>
> Your query is returning too many columns. You have two user_id columns
that
> both contain the same data, etc. Why do you need to return both? It's a
> waste of processing time and network traffic. By explicitly naming the
> columns you wish to return, you avoid returning duplicate data, and you
> improve performance by not requiring ado to make an extra trip to the
> database to get the field names.
>
> Oh! I see that you answered your own question below. Yes, you need to "
...
> specify the individual fieldnames and name them in the select statement,
> i.e. Select u.user_id as userid, u2.user_id as manageruserid."
>
> HTH,
> Bob Barrows
>
> Ron wrote:
> > Hi,
> >
> > In a query I use the same table twice:
> >
> > Select u.*, ud.*, dep.*, u2.*
> <snip>
> >
> > When I try to retrieve this data, I am allowed to use fieldnames, but
> > I ca not include the tablename:
> >
> > user = objRs("User_Name") is ok
> > usermanager = objRs("U2.User_id") is not ok, I can not use u2 to
> > define the tablename.
> >
> > The data is stored in an Access database. Is this not possible in
> > Access in combination with ASP? Do I really have to specify the
> > individual fieldnames and name them in the select statement, i.e.
> > Select u.user_id as userid, u2.user_id as manageruserid?
> >
> > Thx,
> > Ron
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Relevant Pages

  • Re: Export mailing addresses to file for merging labels at end of day.
    ... > mailing address info through the day and we can print labels a the end of the ... This sounds like you might need something like an Access database where the user ... Link the mail merge to that query, ... This reply is posted in the Newsgroup; please post any follow question or reply ...
    (microsoft.public.word.mailmerge.fields)
  • Re: query missing some columns
    ... > if I run "New Crosstab" inside the Access Database? ... A query should return the correct number of columns whether it is run ... Please reply to the newsgroup. ...
    (microsoft.public.scripting.vbscript)
  • Re: Problem with Mailmerge in Word 2003
    ... Please reply to the newsgroup unless you wish to avail yourself of my ... Doug Robbins - Word MVP ... in Office 2000 pointing to a Query in Access, ... the Access Database it ask me what table I want, there is no option for a ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Easy Query Question
    ... query to contain 3 fields (ID, Name, and Species). ... When I run the query I get 402 records. ... which you can specify the type of join. ... Please reply to the newsgroup. ...
    (microsoft.public.access.queries)
  • RE: LDAP query for xerox 3545 printer/scanner blocked by SBS 2003?
    ... I am able to query the LDAP successfully from the second workstation but not ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ... Step2:Please ensure the DNS on workstation is pointed to SBS server. ...
    (microsoft.public.windows.server.sbs)
Loading