Säg att jag vill få ett recordset från min databas med:

strSQL1 = SELECT SUM(tid) AS OdebiterbarTid FROM tabell WHERE tabell.kontoTyp=1
strSQL2 = SELECT SUM(tid) AS debiterbarTid FROM tabell WHERE tabell.kontoTyp=2

Om tabell.KontoTyp == 1 är kontot odebiterbar, 2 -> debiterbar.

Nu vill jag sammanfoga dessa två frågor i en och samma SQL-fråga, går det? Kan man köra med delfråga, eller UNION eller vad?

Detta är ett förenklat exempel, det jag försöker göra i verkligheten är lite mer komplicerad.


Jag använder Access

Såhär skulle recordsetet kunna se ut.


ID | Person | totalt debiterbarTid | totalt OdebiterbarTid
----------------------------------------------------------------
22 | bengt | 200h | 50h
72 | janne | 22h | 12h

Där de sistnämnda kolumnerna beror på vilket konto personen satt sina timmar på... Varje kontotyp bestäms av tex en siffra 1 eller 2 (eller fler).

I princip kan du väl göra en join. Något i stil med:

(SELECT tabell.id as id1, SUM(tid) AS OdebiterbarTid FROM tabell WHERE tabell.kontoTyp=1)
outer join
(SELECT tabell.id as id2, SUM(tid) AS debiterbarTid FROM tabell WHERE tabell.kontoTyp=2)
on id1 = id2

låter ju smidigt, men jag får det inte att fungera..

Här är ett litet test jag gjorde....

recordset1;
SELECT kontoTider.ID, kontoTider.tid AS debit FROM kontoTider WHERE(Cint(kontoTider.tid) >20)
ger ->
ID debit
----------
178 77
182 502
214 24
337 88
544 55
==================================================
recordset2;

SELECT kontoTider.ID, kontoTider.tid AS debit FROM kontoTider WHERE(Cint(kontoTider.tid) >10)
ger ->

ID debit
----------
175 13
178 77
182 502
214 24
215 15
307 16,1
337 88
544 55

=======================================
Recordset 1+ 2;

(SELECT kontoTider.ID AS t1, kontoTider.tid AS debit1 FROM kontoTider WHERE(Cint(kontoTider.tid) >20)) INNER JOIN (SELECT kontoTider.ID AS t2, kontoTider.tid AS odebit FROM kontoTider WHERE(Cint(kontoTider.tid) >10)) ON t1=t2

ger ->
Feltyp: Microsoft JET Database Engine (0x80004005)
Syntaxfel i unionsfråga.

Har även provat med LEFT, INNER, RIGHT och OUTER, får samma fel. varför?
Är det inte så att Access inte stöder OUTER JOIN?


_______________________________________________

En annan fråga: varför får jag fel vid detta:
'strSQL = "SELECT kontoTider.ID, kontoTider.tid as debit FROM kontoTider WHERE(Cint([debit]) >10)"

-> Feltyp: Microsoft JET Database Engine (0x80040E10)Det har inte angetts något värde för en eller flera nödvändiga parametrar.

Kan man inte använda egennämnda fält i WHERE-satsen eller MÅSTE man köra med "WHERE kontoTider.tid > X)....

Stämmer, Outer finns inte (behövs inte heller, eftersom sum() väl ger noll om det inte finns några poster?

Felet i din join är att du måste sätta WHERE-biten sist.

Är inte ett dugg insatt i Access. Någon annan får svara på sista frågan.

provade att ta bort WHERE-satsen HELT o döpte tabellerna...

(SELECT k1.ID AS id1, k1.tid AS debit FROM kontoTider k1) INNER JOIN (SELECT k2.ID AS id2, k2.tid AS odebit FROM kontoTider k2) ON id1=id2

Får samma fel, varför??? börjar bli irriterande detta:/

Det kan vara så enkelt att Access inte klarar det.

Det närmaste skulle vara:
(SELECT k1.ID AS id1, k1.tid AS debit FROM kontoTider k1) INNER JOIN (SELECT k2.ID AS id2, k2.tid AS odebit FROM kontoTider k2) ON k1.id1=k2.id2

Nästa steg jag skulle testa skulle vara:
SELEC * FROM
(SELECT k1.ID AS id1, k1.tid AS debit FROM kontoTider k1) INNER JOIN (SELECT k2.ID AS id2, k2.tid AS odebit FROM kontoTider k2) ON k1.id1=k2.id2

Som sagt jag är inte ett dugg insatt i Access. Jag vet inte ens om det är tillåtet enligt SQL-standarden, men antar det.

det verkar som att Access inte klarar av det, för inget av de du skrev fungerade... samma felmeddelande återigen...

Fick ett tips om att köra underfrågor:

SELECT i2.UserID, Sum(k2.tid) AS debSum FROM kontoInst i2 INNER JOIN (kontoTider k2 INNER JOIN Projekt p2 ON k2.ID = p2.id) ON i2.ID = k2.kontoInst_P WHERE ((p2.Typ=1) AND (i1.UserID = i2.UserID)) GROUP BY i2.UserID
ger ->

UserID debSum
42 16,2
51 16,6
58 7

där summan är antalet timmar på debiterade konton.
__________________________________________________
SELECT i1.UserID, Sum(k1.tid) AS SumTot FROM kontoInst i1 INNER JOIN (kontoTider k1 INNER JOIN Projekt p1 ON k1.ID = p1.id) ON i1.ID = k1.kontoInst_P WHERE (1=1) GROUP BY i1.UserID;

ger->

UserID SumTot
41 121
42 87,9
44 15,5
48 45
51 32,1
55 28
58 31
59 70,1
som är totala antalet timmar, dvs deb + odeb (ändrar jag WHERE(1=1) till p1.Typ=0 kan jag även lista odebiterbara Konton).

Nu vill jag alltså sammanfoga dessa två så att jag får ->

UserID SumTot debSum
41 121 16,2
42 87,9
44 15,5
48 45
51 32,1 16,2
55 28
58 31 7
59 70,1


Sättar jag ihop SQL1+SQL2 =>

SELECT i1.UserID, Sum(k1.tid) AS SumTot, EXISTS (SELECT i2.UserID,
Sum(k2.tid) AS Summa2 FROM kontoInst i2 INNER JOIN (kontoTider k2 INNER JOIN
Projekt p2 ON k2.ID = p2.id) ON i2.ID = k2.kontoInst_P WHERE ((p2.Typ=1) AND
(i1.UserID = i2.UserID)) GROUP BY i2.UserID) As debSum FROM kontoInst i1 INNER JOIN
(kontoTider k1 INNER JOIN Projekt p1 ON k1.ID = p1.id) ON i1.ID =
k1.kontoInst_P GROUP BY i1.UserID;

UserID SumTot debSum
41 121 0
42 87,9 -1
44 15,5 0
48 45 0
51 32,1 -1
55 28 0
58 31 -1
59 70,1 0

Är på rätt spår känns, det som förstör är EXISTS. OM jag inte använder det får jag :

Feltyp:
Microsoft JET Database Engine (0x80004005)
Du har skrivit en underfråga som kan returnera flera fält utan att använda det reserverade ordet EXISTS i huvudfrågans FROM-instruktion. Ändra underfrågans SELECT-uttryck så att endast ett fält efterfrågas.


Känns som att snubbla på mållinjen:( nån som orkar hjälpa mig?

Hej!

Det finns ett alternativt sätt att lösa den här typen av problem.

Jag orkade tyvärr inte läsa igenom hela tråden ordentligt så jag vet inte riktigt hur dina tabeller ser ut. Jag fattade det så att du använde Access?

Tanken är att du gör en group by på användare (var det väl?) och att du i sum-uttrycket bara summerar de rader du är intresserad för (dvs bara debiterbar tid) och i ytterligare ett sum-uttryck, som ju blir en ny kolumn, bara sumemerar övriga rader (odebiterbar tid).

Givet en tabell som heter Tabell1 med 3 kolumner personid, tid och kontotyp så ser frågan ut så här:

SELECT
tabell1.personid
,sum(IIf(tabell1.kontotyp = 1, tabell1.tid, 0)) AS Debiterbartid
,sum(IIf(tabell1.kontotyp = 2, tabell1.tid, 0)) AS Odebiterbartid
FROM tabell1
GROUP BY tabell1.personid

Man kollar helt enkelt i sum-funktinen för varje rad om man ska summera värdet av kolumnen eller värdet noll för varje rad ingående i summan beroende på kontotyp.

I Transact-SQL i t.ex SQL-Server skulle man använda CASE istället för IIf men principen är densamma.

Översätt bara uttrycket till din/dina egna tabeller så borde det funka!

/Per Johansson

aha, fan va smart... det där borde funka..
Var faktiskt inne och nosade lite på IIF, men vet inte varför jag inte spann vidadre på det spåret... tack..ska genast undersöka detta..

Problemet är ju att såna metoder inte är generellt användbara. Jag föredrar grejer rent ur SQL-standarden om det finns. (Och om inte prestandan tvingar en, vill säga).

Det är alltid bra att använda standarder. Jag gillar dock att använda metoder som fungerar på enklaste sätt utan att ställa till för mycket problem. Att använda join med sig själv i det här sammanhanget fungerar t.ex inget vidare när tabellerna börjar bli av det större slaget (miljontals rader).

En annan sak att fundera över är hur ofta man byter databasmotor i verkligheten. Vanligtvis har företaget man jobbar åt valt en linje gällande platformar, t.ex SQL Server eller Oracle. Att byta platform i en organisation är oftast mycket komplext och är inget som sker dirket ofta.

Sen är det klart att om man utvecklar ett ramverk eller en produkt som skall användas på olika platformar så är standarder att föredra.
Eftersom du inte kom med något bra förslag på hur man löser det med standar-sql så gör jag väl ett försök även med det:

select
yxa.personid
,yxa.odebiterbartid
,foo.debiterbartid
from
(select
pers.personid
,fii.odebiterbartid
from
(SELECT distinct personid
FROM Tabell1) pers
left join
(SELECT personid, sum(tid) as odebiterbartid
FROM Tabell1
where kontotyp = 2
group by personid) fii on pers.personid = fii.personid) yxa
left join
(SELECT personid, sum(tid) as debiterbartid
FROM Tabell1
where kontotyp = 1
group by personid) foo on yxa.personid = foo.personid


Jag vet att mina alias suger men jag har inte tid att städa upp. I t.ex SQL Server hade man bara gjort en full join mellan de två subselecterna som grupperar men det funkar tydligen inte i Access. Det verkar inte heller gå att göra två olika left joins mot subselecten yxa utan jag måste wrappa det i tre nivåer.

Jag har i och för sig inte hållt på med Access sen ver2.0 så det kankse finns bättre sätt att lösa joinarna på men det här fungerar iaf.

Lösningen bygger på antagandet att en person kanske bara ha tid på ett av kontona och att man vill ha med alla, även de som bara har odebiterbar tid t.ex.

Jag skulle fortfarande rekommendera min första lösning då den här som synes inte är vare sig effektiv eller lättläst.

/Per

Något sådant här borde gå bra, iallafall mot Access.

Har utgått från två tabbeller.

PERSONAL
Id
Person

TIMDEBITERING
PersonId
Kontotyp
Timmar


<code>
Sub test()

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

con.ConnectionString = "MyConnectionString"
con.Open

sql = "TRANSFORM Sum(Timdebitering.Timmar) AS SummaförTimmar " & _
"SELECT Personal.Id, Personal.Person " & _
"FROM Personal INNER JOIN Timdebitering ON Personal.Id = Timdebitering.PersonId " & _
"GROUP BY Personal.Id, Personal.Person " & _
"PIVOT IIf(Timdebitering.Kontotyp > 1,'Debiterbart','Odebiterbart')"

rs.Open sql, con

While Not (rs.BOF Or rs.EOF)
Debug.Print rs("Id"), rs("Person"), rs("Debiterbart"), rs("Odebiterbart")
rs.MoveNext
Wend

rs.Close
con.Close
End Sub
</code>

Det du behöver tänka på är att rs("Debiterbart") och rs("Odebiterbart") returnerar Null om inga timmar hittas men det kan ju ordnas t ex så här,
<code>
Debug.Print rs("Id"), rs("Person"), IIf(IsNull(rs("Debiterbart")), 0, rs("Debiterbart")), IIf(IsNull(rs("Odebiterbart")), 0, rs("Odebiterbart"))
</code>

Jag har försökt förstå din datastruktur. Men jag gick bet på det.
Har skapat en egen som jag tror påminner om din:

Table: Users
Field: UserId int PK
Field: UserName varchar(20)
...

Table: Projects
Field: ProjectId int PK
Field: ProjectName varchar(20)
...

Table: Accounts
Field: AccountId int PK
Field: AccountName varchar(20)
...

Table: Times
Field: TimeId int PK
Field: TimeUser int FK -> Users.UserId
Field: TimeProject int FK -> Projects.ProjectId
Field: TimeAccount int FK -> Accounts.AccountId
Field: TimeAmount int
...

Jag har använt följande exempeldata:

UserId UserName
----------------------------
22 "bengt"
72 "jannet"
94 "oscar"

ProjectId ProjectName
----------------------------
1 "Project 1"
2 "Project 2"
3 "Project 3"
4 "Project 4"

AccountId AccountName
----------------------------
1 Debitable
2 Undebitable

TimeId TimeUser TimeProject TimeAccount TimeAmount
--------------------------------------------------------
1 22 1 1 10
2 22 1 2 10
3 22 3 1 20
4 22 3 2 10
5 22 4 1 30
6 72 2 1 100
7 72 2 2 100
8 72 3 1 150
9 72 3 2 100
10 72 4 1 200
11 94 4 1 50

Vilket kan utläsas:

TimeId TimeUserName TimeProjectName TimeAccountName TimeAmount
--------------------------------------------------------
1 bengt Project 1 Debitable 10
2 bengt Project 1 Undebitable 10
3 bengt Project 3 Debitable 20
4 bengt Project 3 Undebitable 10
5 bengt Project 4 Debitable 30
6 jannet Project 2 Debitable 100
7 jannet Project 2 Undebitable 100
8 jannet Project 3 Debitable 150
9 jannet Project 3 Undebitable 100
10 jannet Project 4 Debitable 200
11 oscar Project 4 Debitable 50

Från denna datastruktur drar jag slutsatsen att du vill ha en fråga som påminner om den här:

SELECT Users.UserId, Users.UserName, QuerySumOfAccount1.SumOfAccount1, QuerySumOfAccount2.SumOfAccount2
FROM (Users LEFT JOIN 
    QuerySumOfAccount1 ON Users.UserId = QuerySumOfAccount1.TimeUser) LEFT JOIN 
    QuerySumOfAccount2 ON Users.UserId = QuerySumOfAccount2.TimeUser;

Där QuerySumOfAccount1 motsvarar:

SELECT Times.TimeUser, Sum(Times.TimeAmount) AS SumOfAccount1
FROM Times
WHERE Times.TimeAccount=1
GROUP BY Times.TimeUser;

Och där QuerySumOfAccount2 motsvarar:

SELECT Times.TimeUser, Sum(Times.TimeAmount) AS SumOfAccount2
FROM Times
WHERE Times.TimeAccount=2
GROUP BY Times.TimeUser;

Dessa tre frågor kan skrivas som en fråga:

SELECT Users.UserId, Users.UserName, QuerySumOfAccount1.SumOfAccount1, QuerySumOfAccount2.SumOfAccount2
FROM (Users LEFT JOIN 
    (SELECT Times.TimeUser, Sum(Times.TimeAmount) AS SumOfAccount1
        FROM Times
        WHERE Times.TimeAccount=1
        GROUP BY Times.TimeUser) QuerySumOfAccount1 ON Users.UserId = QuerySumOfAccount1.TimeUser) LEFT JOIN 
    (SELECT Times.TimeUser, Sum(Times.TimeAmount) AS SumOfAccount2
        FROM Times
        WHERE Times.TimeAccount=2
        GROUP BY Times.TimeUser) QuerySumOfAccount2 ON Users.UserId = QuerySumOfAccount2.TimeUser;

JAg hoppas detta varit till hjälp att lösa ditt problem.

Andreas, det här gör väl samma sak, i alla fall när det gäller Access.


TRANSFORM Sum(Times.TimeAmount) AS SumForTimeAmount
SELECT Users.UserId, Users.UserName
FROM Users RIGHT JOIN (Projects RIGHT JOIN (Accounts RIGHT JOIN Times ON Accounts.AccountId = Times.TimeAccount) ON Projects.ProjectId = Times.TimeProject) ON Users.UserId = Times.TimeUser
GROUP BY Users.UserId, Users.UserName
PIVOT Accounts.AccountName;

Skall det inte gå åt höger för att då få med samtliga anställda. Har organiserat om tabellerna i from satsen så det tydligare framgår från vilken och vilken ordning data hämtas från tabellna.
Med IN-satsen efter piviot-satsen kan man garanterar vilka och ordningen på kolumner.

Så den borde vara:

TRANSFORM Sum(Times.TimeAmount) AS SumForTimeAmount
SELECT Users.UserId, Users.UserName
FROM Users LEFT JOIN 
    ((Times LEFT JOIN 
    Projects ON Times.TimeProject = Projects.ProjectId) LEFT JOIN 
    Accounts ON Times.TimeAccount = Accounts.AccountId) ON Users.UserId = Times.TimeUser
GROUP BY Users.UserId, Users.UserName
PIVOT Accounts.AccountName IN ("Debitable", "Undebitable");

Om man har många fält man vill hämta från användare. KAn man dela upp frågan för att slippa gruppera:

SELECT Users.UserId, Users.UserName, Sub.*
FROM Users LEFT JOIN 
    (TRANSFORM Sum(Times.TimeAmount) 
    SELECT Times.TimeUser
    FROM (Times LEFT JOIN 
    Projects ON Times.TimeProject = Projects.ProjectId) LEFT JOIN 
    Accounts ON Times.TimeAccount = Accounts.AccountId 
    GROUP BY Times.TimeUser
    PIVOT Accounts.AccountName IN ("Debitable", "Undebitable")) Sub ON Users.UserId = Sub.TimeUser

Jag håller med att korsfrågor/transform i access är väldigt smidig. Men tillhör inte SQL standarden.

Men då det rör sig om ett fast antal kolumner/konto Debitable, Undebitable. Tycker jag en fråga som är i sql standarden bättre. Då du kan porta applikationen till andra databaser: MS SQL Server, My SQL, etc.

@Andreas

Ang "Men då det rör sig om ett fast antal kolumner/konto Debitable, Undebitable" i Hobbes ursprungliga fråga så skrev han "Om tabell.KontoTyp == 1 är kontot odebiterbar, 2 -> debiterbar" det uppfattade jag som att Konto 1 var odebiterbart och konton 2 eller större var debiterbara.

Problemet i access: Du har inte Full Outer Joins. Alltså kan du bara få med dig alla anställda eller alla konton. Men inte sammtidigt. Alltså det måste finnas data som debiterade och odepiterade för att det skall dyka upop som kolumner i en korsfråga om du inte avänder explicit deklaration genom IN.