Re: Retriving data from DB

From: Neven Klofutar (neven.klofutar*r.e..mo.v.e*_at_vip.hr)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 15:47:42 +0200

One more question on this subject ...

If I use:

rsRecordset("Name"), which name would I get from the recordset, the first
one or the second, or random or ?

Thanx, Neven

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u0q5%23HPvEHA.2804@TK2MSFTNGP14.phx.gbl...
> Ugh! Not only bad design, but he used reserved keywords for his column
> names! My condolences.
>
> You can use a union query to normalize this design. What database are you
> using? In Access, you would create a saved query (call it qSchedule) with
> this sql:
>
> Select [Date],[Day],1 As ClassRoom, 1 As Period,
> [Course 11] As Course, [Time 11] As [Time],
> [Instructor 11] As Instructor FROM tblSchedule
> UNION ALL
> Select [Date],[Day],1 As ClassRoom, 2 As Period,
> [Course 12] As Course, [Time 12] As [Time],
> [Instructor 12] As Instructor FROM tblSchedule
> UNION ALL
> Select [Date],[Day],1 As ClassRoom, 3 As Period,
> [Course 13] As Course, [Time 13] As [Time],
> [Instructor 13] As Instructor FROM tblSchedule
> etc.
>
> In SQL Server, you could use virtually the same sql statement to create a
> View.
>
> Now, finding a specific course will simply be:
>
> SELECT [Date],[Day],Period,Course,[Time],Instructor
> FROM qSchedule
> WHERE Course = 'Some Course'
>
> You could probably create a subroutine in an Access module to loop through
> the fields in your table and generate this sql statement for you (and even
> automate creating the saved query), if you know enough Access VBA. If not,
> post to an Access newsgroup for help.
>
> HTH,
> Bob Barrows
>
>
> Neven Klofutar wrote:
> > Well,
> >
> > I'm working on an upgrade of web aplication my precesor created some
> > 2-3 years ago ...
> > It's an aplication created for educational company to keep records of
> > seminars and atendants.
> >
> > The main problem is that is was probably first application my
> > precesor ever created, so it has some huge problem in DB design.
> >
> > There are 2 tables that represent schedule for all seminars throught
> > the year ...
> > tblSchedule and tlbScheduleAdd
> >
> > tblSchedule
> > *********
> > Date (datetime)
> > Day (nvarchar)
> > (now comes the fun part :))
> > Course11
> > Time11
> > Instructor11
> > Course12
> > Time12
> > Instructor12
> > ...
> > this continus throught the whoe page until Course105
> > let me explain this code:
> > Course11 - it's a field containing course name for the classroom
> > 1 in the 1st time period
> > Course21 - it's a field containing course name for the classroom
> > 2 in the 1st time period
> > Course35 - it's a field containing course name for the classroom
> > 3 in the 5st time period
> >
> > table tblScheduleAdd is almost the same
> > ******************************
> > Date (datetime)
> > Day (nvarchar)
> > Color11
> > Reservations11
> > Time11
> > Color12
> > Reservations12
> > Time12
> > ...
> >
> > Now, when I try to find a specific Course I have to loop throught
> > some wierd recordset connection this 2 tables, and as you can see
> > both tables have some fields named the same ...
> > You probly think I'm insane, that this is a joke, but this is my
> > reality for quite some time, and my boss won't let me create a new
> > application. (explanation: It would take loooooong time to create
> > something new, and it's better to keep patching this one :((()
> >
> > thanx for listening, Neven
> >
> >
> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:uADKnmOvEHA.2624@TK2MSFTNGP11.phx.gbl...
> >> Maybe not, but it's the only solution, outside of returning two
> >> recordsets. Perhaps if you could explain why it's not easy in your
> >> case, we could suggest some workarounds.
> >>
> >> I'm making the assumption that the two columns with the same name do
> >> not contain the same data. Otherwise I would be criticizing you for
> >> returning the same data twice. :-) This is always due to the
> >> inappropriate use of selstar (select *).
> >>
> >> Bob Barrows
> >>
> >> Neven Klofutar wrote:
> >>> Thanx, but it's not so easy in my case ...
> >>>
> >>> Neven
> >>>
> >>>
> >>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> >>> news:uaFTD1NvEHA.1520@TK2MSFTNGP11.phx.gbl...
> >>>> Neven Klofutar wrote:
> >>>>> Hi,
> >>>>>
> >>>>> Is it possible to retrive data from Recordset using this syntax
> >>>>>
> >>>>> rsRecordset("t_Person.s_Name")
> >>>>>
> >>>>> insted of
> >>>>>
> >>>>> rsRecordset("s_Name")
> >>>>>
> >>>>>
> >>>>> thanx, Neven
> >>>>
> >>>> No. The Field object's Name property contains only the unqualified
> >>>> column name, or the alias assigned to the column in your sql
> >>>> statement. If you have two columns with the same name, you need to
> >>>> use the AS keyword to assign an alias to one of them.
> >>>>
> >>>> Bob Barrows
> >>>>
> >>>> --
> >>>> Microsoft MVP - ASP/ASP.NET
> >>>> Please reply to the newsgroup. This email account is my spam trap
> >>>> so I don't check it very often. If you must reply off-line, then
> >>>> remove the "NO SPAM"
> >>
> >> --
> >> 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.
>
> --
> 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: Retriving data from DB
    ... Since you need to create the saved union query regardless, ... > make the refresh of the table part of the update of tblSchedule. ... you could use virtually the same sql statement to ... post to an Access newsgroup for help. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Using For...Each loops when referencing reports.
    ... SELECT tblTx.*, tblTestTx.* FROM tblTx INNER JOIN tblTestTx ON ... 199 is just the ID in your last SQL statement. ... This will now write the exact SQL statement used to open the recordset to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using For...Each loops when referencing reports.
    ... Looking at the SQL statement, I think you're trying to return the records ... from tblTx and tblTestTx where is equal in both tables. ... This will now write the exact SQL statement used to open the recordset to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using For...Each loops when referencing reports.
    ... SELECT tblTx.*, tblTestTx.* FROM tblTx INNER JOIN tblTestTx ON ... '[Print the SQL statement to the immediate window] ... recordset to the immediate window. ... something to do with how I'm using the recordset. ...
    (microsoft.public.access.modulesdaovba)
  • Re: XML Persistence scenario
    ... The main reasons for my request, ... This is why i'm wondering if an XML persisted recordset could be a starting ... >> My goal is to not use SQL statement, just put filters properties and use ...
    (microsoft.public.data.ado)