November 1, 2004 at 11:51 pm
i have 2 tables
1 is candidateregistration and the 2nd is skillset
there schema is
for [candidateregistration]
candid
candiname
dateofbirth
for [skillset]
skillsetid
candid
programminglangid
yearsofexp
now in this 2 tables candid is used as the matching key
now a single candidate can have multiple skill sets.
like he can know VB ,ASP,SQL, then 3 entries will be done for a single candidate in a skillsettable
now when i want to display the records in an asp page
i want them to display like this
name age skillsets
amin 27 VB,ASP,SQL
you got my point i want to convert some rows into column values and display
them..
how can i do that i tried various options
and 1 thing iam using ms-access2002
thanks in advance
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
November 2, 2004 at 10:33 am
pls reply to my question,
nobody knows ans to my such a simple question ??.
anybody home
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
November 2, 2004 at 11:32 am
If you were using SQL Server, I would suggest getting a count from you Skillset table and either ALTER TABLE (if you are putting these into a #temptable for reporting) or simply appending them to you return string if you are doing a single entry.
Not sure if Access can do that...
I wasn't born stupid - I had to study.
November 2, 2004 at 8:53 pm
If Access 2002 supports user defined functions (maybe you can use VB to do this) you could create a function that expects a candidate and then loops through all the skill sets and adds each one to a returned string.
I think the VB Modules would allow this.
Hope this helps.
If the phone doesn't ring...It's me.
November 2, 2004 at 10:15 pm
u mean i need to write a function in msaccess.?
iam using asp, but doing that will slow down the process, i want to do it in a elite way.
its a question of cross tab query, but its a dynamic cross tab query, so its a bit complicated.
looping isn't the very best way to do.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
November 3, 2004 at 1:38 am
Hi,
You could create a function within SQL and just use that in your select statement fro your asp page.
The function could be something like
CREATE FUNCTION dbo.funcProgramming(@CandidID int)
RETURNS nvarchar(500)
AS
BEGIN
declare @s-2 nvarchar(500), @C nvarchar(50)
declare c cursor
for SELECT programminglangid
FROM SkillSet
WHERE CandidID = @CandidID
open c
fetch next from c into @C
while @@fetch_status = 0
begin
if @s-2 is null
set @s-2= ''
fetch next from c into @C
end
close c
deallocate c
RETURN @s-2
END
So your final select will be a straightforward
select a,b,c,dbo.funcProgramming(t2.CandidID)
FROM tb1 t1,tb2 t2 ....
WHERE Criteria
Hope this helps
Graeme
November 3, 2004 at 4:22 am
i think u gave example for sql server,
but iam using ms-access2002
thanks anyway
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
November 3, 2004 at 8:07 am
I've been coding in asp with msaccess for quite a while and there's just no way of doing this task in SQL only with access. You really need the power of T-SQL to do this. You'll have to do something like this :
Dim Candid
Dim Done
MyRs.Open "Select candid, CandiName, Age, Programminglangid from candidateregistration inner join skillset on candidateregistration.candid = skillset.candid order by candid, programminglangid"
while not myrs.eof
response.write [name, age]
Done = false
Candid = myrs.fields("Candid")
while not done 'write the languages as long as the candid doesn't change
response.write Programminglangid
myrs.movenext
if myrs.eof then
done=true
elseif Candid myrs.fields("Candid")
done=true
endif
wend
November 5, 2004 at 6:50 am
its possible using dynamic cross tab query, its possible
when i do it i will paste the code here
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
November 5, 2004 at 12:07 pm
Sukhoi,
Please DON'T post the code here. This is a SQL Server forum not an Access forum.
However, if you really must post it....this site does have an Access forum, so post it there.
Please remember, this site is for SQL Server not Access, Oracle, Sybase, DB2 or anything else. While there MIGHT be someone on this site with experience in one of those, you really are better off finding an appropriate tech site for what you want. You'll get quicker and better answers for non-SQL Server questions.
-SQLBill
November 5, 2004 at 12:33 pm
Yes, your best bet is to post it to newsgroups like
microsoft.public.access.* or
comp.databases.ms-access
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply