Loading results from 2 recordsets into an array

[Joel Racicot]

Hi all,

I have 2 queries producing two recordsets, one listing a series of
courses available online and the other listing the courses that the
user has completed with a passing grade. I need to output the results
to an HTML table that lists the available courses and puts a check
mark beside the completed/passed courses. At this point I'm stuck,
not being that familiar with arrays.

Any ideas?

Thanks

Joel

[Ray at]

I'd imagine you could return this information all in one query if you share
your table structure and what your two current queries are.

Ray at work

"Joel Racicot" <j_racicot@hotmail.com> wrote in message
news:39f0c695.0311041259.629d0643@posting.google.c om...

> Hi all,
>
> I have 2 queries producing two recordsets, one listing a series of
> courses available online and the other listing the courses that the
> user has completed with a passing grade. I need to output the results
> to an HTML table that lists the available courses and puts a check
> mark beside the completed/passed courses. At this point I'm stuck,
> not being that familiar with arrays.
>
> Any ideas?
>
> Thanks
>
> Joel

[Aaron Bertrand - MVP]

> I have 2 queries producing two recordsets, one listing a series of

> courses available online and the other listing the courses that the
> user has completed with a passing grade. I need to output the results
> to an HTML table that lists the available courses and puts a check
> mark beside the completed/passed courses. At this point I'm stuck,
> not being that familiar with arrays.

Why do you need arrays? More details, please.

[Joel Racicot]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message news:<OVzGutxoDHA.2268@TK2MSFTNGP12.phx.gbl>...

> > I have 2 queries producing two recordsets, one listing a series of
> > courses available online and the other listing the courses that the
> > user has completed with a passing grade. I need to output the results
> > to an HTML table that lists the available courses and puts a check
> > mark beside the completed/passed courses. At this point I'm stuck,
> > not being that familiar with arrays.

>
> Why do you need arrays? More details, please.

OK, here are the details.

Table Structures:

Table: tblCourseMod
Columns
Name Type Size
ModID Long Integer 4
ModNum Long Integer 4
ModName Text 255
LessonRef Long Integer 4
LessonName Text 255
LessonPath Text 255
ModType Text 50
ModLang Text 50

Table: tblLoginInfo
Columns
Name Type Size
fldUID Long Integer 4
fldUserID Text 25
fldPassword Text 25
fldUserFirstName Text 50
fldUserLastName Text 50
fldLastLogin Date/Time 8
fldCreated Date/Time 8

Table: tblTestScore
Columns
Name Type Size
fldUID Long Integer 4
fldModID Long Integer 4
fldDateTime Date/Time 8
fldTestScore Long Integer 4
fldTestType Long Integer 4


Query to get the course list of all courses
select distinct modname, modnum from tblCourseMod where ModType =
'retail' and lessonref = 1 order by modnum

Query to get the completed/passed courses for the current user
SELECT Count(*) AS [count], tblTestScore.fldModID,
tblCourseMod.ModName
FROM tblLoginInfo INNER JOIN (tblCourseMod INNER JOIN tblTestScore ON
tblCourseMod.ModID = tblTestScore.fldModID) ON tblLoginInfo.fldUID =
tblTestScore.fldUID
WHERE (((tblLoginInfo.fldUserID)='" & Request.Cookie("login")("userid"
&"') AND ((tblTestScore.fldTestType)=2))
GROUP BY tblTestScore.fldModID, tblCourseMod.ModName;

Once I've got the results of these queries, I need to match up the
records from the second query to those from the first query in order
to output the course list along with a checkmark or other marker
indicating that the course has been completed.

Does this make any sense? Am I just making this too complicated for
nothing? Is there an easier way to do this?

Thanks

Joel

[Mark Schupp]

You should be able to get what you want by combining the 2 queries and doing
a left outer join between the course and score table. What DBMS are you
using?

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Joel Racicot" <j_racicot@hotmail.com> wrote in message
news:39f0c695.0311060811.1e8ba31@posting.google.co m...

> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message

news:<OVzGutxoDHA.2268@TK2MSFTNGP12.phx.gbl>...

> > > I have 2 queries producing two recordsets, one listing a series of
> > > courses available online and the other listing the courses that the
> > > user has completed with a passing grade. I need to output the results
> > > to an HTML table that lists the available courses and puts a check
> > > mark beside the completed/passed courses. At this point I'm stuck,
> > > not being that familiar with arrays.

> >
> > Why do you need arrays? More details, please.

>
> OK, here are the details.
>
> Table Structures:
>
> Table: tblCourseMod
> Columns
> Name Type Size
> ModID Long Integer 4
> ModNum Long Integer 4
> ModName Text 255
> LessonRef Long Integer 4
> LessonName Text 255
> LessonPath Text 255
> ModType Text 50
> ModLang Text 50
>
> Table: tblLoginInfo
> Columns
> Name Type Size
> fldUID Long Integer 4
> fldUserID Text 25
> fldPassword Text 25
> fldUserFirstName Text 50
> fldUserLastName Text 50
> fldLastLogin Date/Time 8
> fldCreated Date/Time 8
>
> Table: tblTestScore
> Columns
> Name Type Size
> fldUID Long Integer 4
> fldModID Long Integer 4
> fldDateTime Date/Time 8
> fldTestScore Long Integer 4
> fldTestType Long Integer 4
>
>
> Query to get the course list of all courses
> select distinct modname, modnum from tblCourseMod where ModType =
> 'retail' and lessonref = 1 order by modnum
>
> Query to get the completed/passed courses for the current user
> SELECT Count(*) AS [count], tblTestScore.fldModID,
> tblCourseMod.ModName
> FROM tblLoginInfo INNER JOIN (tblCourseMod INNER JOIN tblTestScore ON
> tblCourseMod.ModID = tblTestScore.fldModID) ON tblLoginInfo.fldUID =
> tblTestScore.fldUID
> WHERE (((tblLoginInfo.fldUserID)='" & Request.Cookie("login")("userid"
> &"') AND ((tblTestScore.fldTestType)=2))
> GROUP BY tblTestScore.fldModID, tblCourseMod.ModName;
>
> Once I've got the results of these queries, I need to match up the
> records from the second query to those from the first query in order
> to output the course list along with a checkmark or other marker
> indicating that the course has been completed.
>
> Does this make any sense? Am I just making this too complicated for
> nothing? Is there an easier way to do this?
>
> Thanks
>
> Joel

Using Access 2000. I thought of using a union query. Just
not sure that it wouldn't be easier to load the results of
the scores query into an array and compare them to the
results of the courses query when I write out the course
list. Still not sure if I'm making any sense, though.

Thanks for the help. Any ideas appreciated as usual.

Joel

>-----Original Message-----
>You should be able to get what you want by combining the 2

queries and doing

>a left outer join between the course and score table. What

DBMS are you

>using?
>
>--
>Mark Schupp
>Head of Development
>Integrity eLearning
>[url]www.ielearning.com[/url]
>
>
>"Joel Racicot" <j_racicot@hotmail.com> wrote in message
>news:39f0c695.0311060811.1e8ba31@posting.google.c om...

>> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in

message

>news:<OVzGutxoDHA.2268@TK2MSFTNGP12.phx.gbl>...

>> > > I have 2 queries producing two recordsets, one

listing a series of

>> > > courses available online and the other listing the

courses that the

>> > > user has completed with a passing grade. I need to

output the results

>> > > to an HTML table that lists the available courses

and puts a check

>> > > mark beside the completed/passed courses. At this

point I'm stuck,

>> > > not being that familiar with arrays.
>> >
>> > Why do you need arrays? More details, please.

>>
>> OK, here are the details.
>>
>> Table Structures:
>>
>> Table: tblCourseMod
>> Columns
>> Name Type Size
>> ModID Long Integer 4
>> ModNum Long Integer 4
>> ModName Text 255
>> LessonRef Long Integer 4
>> LessonName Text 255
>> LessonPath Text 255
>> ModType Text 50
>> ModLang Text 50
>>
>> Table: tblLoginInfo
>> Columns
>> Name Type Size
>> fldUID Long Integer 4
>> fldUserID Text 25
>> fldPassword Text 25
>> fldUserFirstName Text 50
>> fldUserLastName Text 50
>> fldLastLogin Date/Time 8
>> fldCreated Date/Time 8
>>
>> Table: tblTestScore
>> Columns
>> Name Type Size
>> fldUID Long Integer 4
>> fldModID Long Integer 4
>> fldDateTime Date/Time 8
>> fldTestScore Long Integer 4
>> fldTestType Long Integer 4
>>
>>
>> Query to get the course list of all courses
>> select distinct modname, modnum from tblCourseMod where

ModType =

>> 'retail' and lessonref = 1 order by modnum
>>
>> Query to get the completed/passed courses for the

current user

>> SELECT Count(*) AS [count], tblTestScore.fldModID,
>> tblCourseMod.ModName
>> FROM tblLoginInfo INNER JOIN (tblCourseMod INNER JOIN

tblTestScore ON

>> tblCourseMod.ModID = tblTestScore.fldModID) ON

tblLoginInfo.fldUID =

>> tblTestScore.fldUID
>> WHERE (((tblLoginInfo.fldUserID)='" &

Request.Cookie("login")("userid"

>> &"') AND ((tblTestScore.fldTestType)=2))
>> GROUP BY tblTestScore.fldModID, tblCourseMod.ModName;
>>
>> Once I've got the results of these queries, I need to

match up the

>> records from the second query to those from the first

query in order

>> to output the course list along with a checkmark or

other marker

>> indicating that the course has been completed.
>>
>> Does this make any sense? Am I just making this too

complicated for

>> nothing? Is there an easier way to do this?
>>
>> Thanks
>>
>> Joel

>
>
>.
>