In my asp.net+vb+access database web. i have a gridview with accessdatasource. i want to pull out a count function from two tables
AUTH (table) in this table TCAO is the qualification. it means this tables shows the diffrenet section who should have TCAO qualified persons
Section TCAO
Sneeker 2
Polo 1
Walls 2
Tango 2
PERS (Table)
PNO Name Section Course
k321 Sony Sneeker MBA, CDAC, BE(CS), TCAO
h123 Ashok Polo BA,LLB,BE(MECH)
y432 Baiju Walls BTECH(CS,CCNA,TCAO
u789 Pooja Tango CCNA,SQA,HITECH
k765 sonu Sneeker hitech,tata,leyland
I want to display the data in gridview as under
Section Sction.TCAO TCAO(This is the count from pers table how much persons are qualified in TCAO in that section)
Sneeker 2 1
Polo 1 0
Walls 2 1
Tango 2 0
i tried a lot with left join and all but the result was not gained
Try this query,
SELECT
au.Section, au.TCAO,
'Count_TCAO' = (SELECT COUNT(p1.Section) FROM PERS AS p1 WHERE p1.Section = au.Section
AND p1.Course LIKE '%TCAO%')
FROM AUTH AS au
INNER JOIN PERS AS pe ON au.Section = pe.Section
GROUP BY au.Section, au.TCAO
When i run the query message comes that enter parameter value
Count_TCAOTry the updated one above.
tried it
Expr1002 heading and error in all rows
Are you getting this error message in SQL server?
my database in i access
SELECT AUTH.Section,
AUTH.TCAO AS Sect_TCAO,
IIF(P.TCAO IS NULL, 0, P.TCAO) AS TCAO
FROM AUTH
LEFT JOIN (SELECT Section,
Count(*) AS TCAO
FROM PERS
WHERE Course Like '%TCAO%'
GROUP BY Section) AS P ON AUTH.Section = P.Section
Out put of this query which i got is as under
Section Sect_TCAO TCAO
in this section and Sect_TCAO is correct but The Field TCAO shows zero for all section as it should show the count of persons qualified in TCAO of that section
Baiju EP
Out put of this query which i got is as under
Section Sect_TCAO TCAO
in this section and Sect_TCAO is correct but The Field TCAO shows zero for all section as it should show the count of persons qualified in TCAO of that section
I think you're testing this in Access? In Access, the wildcard character is *, so to test this in Access, the querry must be modified to:
SELECT AUTH.Section,
AUTH.TCAO AS Sect_TCAO,
IIF(P.TCAO IS NULL, 0, P.TCAO) AS TCAO
FROM AUTH
LEFT JOIN (SELECT Section,
Count(*) AS TCAO
FROM PERS
WHERE Course Like '*TCAO*'
GROUP BY Section) AS P ON AUTH.Section = P.Section
is it possible to add two more clause in this query. itried a lot but not got through
There is a field named outdetails in the Pers Table. in which transfers are maintained
if a person is transferd to another section then the section name and date of move is entered in th outdetails field in Pers table
OutDetails like pers.section (it is OUT)
outdetails not like pers.section (it is IN) but the problem is the persons are coming IN from another section's their outdetails will be filled in their section outdetails coloumn and not in front of the section in which they are joining
Please show some example data and desired result?
Section TCAO
Sneeker 2
Polo 1
Walls 2
Tango 2
PERS (Table)
PNO Name Section Course Outdetails
k321 Sony Sneeker MBA, CDAC, BE(CS), TCAO POLO by 21 Ayg 2013
h123 Ashok Polo BA,LLB,BE(MECH)
y432 Baiju Sneeker BTECH(CS,CCNA,TCAO Tango by 23 Aug 2013
u789 Pooja Tango CCNA,SQA,HITECH
k765 sonu Sneeker hitech,tata,leyland
I want to display the data in gridview as under
Section Sction.TCAO TCAO TOUT TIN Held (TACO-TOUT+TIN)
Sneeker 2 2 2 0 0
Polo 1 0 0 1 1
Walls 2 0 0 0 0
Tango 2 0 0 1 1
The held is TCAO -TOUT(Persons moving out) + TIN (Persons comming in from other section)
i had posted it but not visible due to moderators approval is pending
SELECT Section,
Sect_TCAO,
TCAO,
TOUT,
TIN,
TCAO - TOUT + TIN AS Held
FROM (SELECT A.Section,
A.TCAO AS Sect_TCAO,
IIf(P1.TCAO Is Null,0,P1.TCAO) AS TCAO,
IIf(P2.TOUT Is Null,0,P2.TOUT) AS TOUT,
IIf(P3.TIN Is Null,0,P3.TIN) AS TIN
FROM ((AUTH AS A
LEFT JOIN (SELECT Section,
Count(*) AS TCAO
FROM PERS
WHERE Course Like '*TCAO*'
GROUP BY Section) AS P1 ON A.Section = P1.Section)
LEFT JOIN (SELECT Section,
Count(*) AS TOUT
FROM PERS
WHERE IIF(Outdetails Is Null, '', Outdetails) <> ''
GROUP BY Section) AS P2 ON A.Section = P2.Section)
LEFT JOIN (SELECT Section,
(SELECT COUNT(*)
FROM PERS
WHERE Outdetails Like '*' & AUTH.Section & '*') AS TIN
FROM AUTH) AS P3 ON A.Section = P3.Section)
沒有留言:
張貼留言