SQL i Excel

Jeg kan hjælpe dig "den anden vej" at eksportere fra Access til Excel. Er du interesseret skal jeg gerne sende dig en testdb. Blot læg din e-mail.

Hej Mogens!

Det er stadig mit øredatabaseprojekt, jeg arbejder på. Jeg har i databasen udvalgt mit materiale til projektet ud fra nogle eks- og inklusionskriterier. Jeg skal nu dokumentere, hvilke patienter i databasen jeg har udeladt og hvorfor.
For hvert in- eller eksklusionskriterium vil jeg tælle antallet af patienter i databasen, som IKKE opfylder kriteriet. Noget i stil med:
Antal ialt: 1325
Antal kaffedrikkere: 125
Antal tedrikkere: 15, rest = 110
Antal coladrikkere: 16, rest = 94

(Hvis det at drikke kaffe ELLER te ELLER cola er eksklusionskriterier).
Resten på bundlinien skal så gerne have samme værdi som antallet af udvalgte patienter i mit materiale.

/Peter Jacobsen

Hov, klovnede vist lidt i den! Prøver lige igen:

Antal ialt: 1325
Antal kaffedrikkere: 125, rest = 1200
Antal tedrikkere: 15, rest = 1185
Antal coladrikkere: 16, rest = 1169

/PJ

Hej Peter

Jeg kan svagt huske noget med lidt øre-halløj, men ikke ret meget om, hvad det egentlig drejede sig om.

Men hvorfor vil du over i Excel. Hvis det er et krav, at du stå i Excel og vil importere, så må jeg bakke ud. Men taler vi om ren Access, er jeg med på klodsen.

Umiddelbart vil jeg tro, at vi kan bruge funktionen DCount, men lad mig lige arbejde lidt. Hvordan bestmmer du om de er kaffedrikkere? Er det et Ja/Nej felt?

Jeg har en testdb klar i Access. Læg din e-mail. Så sender jeg den.

Her er et eksempel på hvordan det kunne gøres. Vær opmærksom på at i Vb-editoren under Tools / References skal der være sat flueben i Microsoft ActiveX dataobject library.


Public Sub PlainTextQuery()
    Dim rsData As ADODB.Recordset
    Dim szConnect As String
    Dim szSQL As String
   
    ' Lav connect-streng.
    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\Files\Northwind.mdb;"
   
    ' lav SQL Statement.
    szSQL = "SELECT CompanyName, ContactName " & _
            "FROM Customers " & _
            "WHERE Country = 'UK' " & _
            "ORDER BY CompanyName"
   
    ' Lav Recorset object og kør queryen.
    Set rsData = New ADODB.Recordset
    rsData.Open szSQL, szConnect, adOpenForwardOnly, _
                adLockReadOnly, adCmdText

    ' vær sikker på der kommer records i den
    If Not rsData.EOF Then
        ' Smid hele recordsettet til excel i med start i celle A2
        Sheet1.Range("A2").CopyFromRecordset rsData
        ' Luk recordsettet
        rsData.Close
    Else
        MsgBox "Fejl : Ingen records fundet.", vbCritical
    End If
   
    Set rsData = Nothing
   
End Sub


I øvrigt ville det være nemmere og smartere at gemme din (SQL)forespørgsel i access og hente dine data via menuen DATA / Hent eksterne data.
En sådan forspørgsel kan opdateres vedc åbning af excel eller ved højreklik på tabellen

Tak for tippet!
Men hvor skal jeg skrive koden? Jeg forestiller mig noget med en
on-open-event, men den er ikke sådan ligetil at finde... Vil du være
rar at forklare, præcis hvad jeg skal gøre for at finde den?
Jeg skal hente 63 forskellige værdier (fra samme access-forespørgsel),
som skal fyldes i forskellige celler. Hvordan gøres det mest elegant?
Det er jo lidt klodset at skrive ovenstående 63 gange...

Hvorfor vil du skifte til Excel?

Jeg skal bruge dataene i et projekt, som skal udskrives og afleveres. Excel giver mig bedre muligheder for at redigere designet.

Det er jeg ikke enig i. Men du bestemmer. Skal jeg sende min testdb?

Peter > Jeg har tilføjet en funktion, der eksporterer dine data til Excel.

propelhat->jeg forstår ikke rigtigt din kommentar.
hvis du kører min kode henter den alle data i forespørgslen i et hug og placerer dem med start i A2.
Prøv at uddybe det lidt.
Til dit OnOpen spm.

I modulet ThisWorkbook kan du indsætte denne makro

Private Sub Workbook_Open()
Call Plaintextquery
End Sub

Bak -> Jeg skal tælle et antal poster i DB, hvis sum skal placeres i en bestemt celle.
Jeg skal dokumentere, hvilke poster jeg har udeladt under udvælgelsen af mit materiale til et projekt. Derfor skal jeg angive, hvor mange der er udeladt pga rift i trommehinden, hvor mange der er udeladt pga rift i trommehinden OG defekte øreknogler, hvor mange der er udeladt pga rift i trommehinden OG defekte øreknogler OG tinnitus, hvor mange der er udeladt osv osv osv. Der er ialt 63 eksklusionskriterier, hvis samlede antal jeg skal tælle, efterhånden som jeg når ned i rækken af kriterier.
Var det svar nok?

/Peter Jacobsen

Bak -> Jeg tror, det der undrer dig er mit problem med de 63 værdier. Hvis man skal hente 63 værdier fra samme access-forespørgsel, har jeg problemer med at skrive koden. Jeg er tilbøjelig til at skrive dit ovenstående eksempel 63 gange, men da dataene stammer fra samme recordset, siger min fornuft mig, at der må være en smukkere løsning...

/Peter Jacobsen

propelhat (og Bak) >  I må undskylde, at jeg bliver ved mit mit Access. Hvis det drejer sig om at tælle poster, kan Access sagtens klare den opgave. Hvis poster skal over i Excel, kan Access også gøre dette. Jeg vil stadig gerne sende min db (og propelhast er velkommen til at afvise mit svar).

VBA-koden til at tælle alle poster i Access:
a = DCount("*", "Tabel1")

Koden der tæller f.eks. kaffedrikkere:
b = DCount("*", "Tabel1", "[kaffe] = True")

Koden der eksporterer til Excel:

Dim xls As New Excel.Application
Dim a As String
a = "Q1"
DoCmd.TransferSpreadsheet acExport, 8, "Q1", "C:\Mappe1.xls.", True, ""
xls.Visible = True
xls.Workbooks.Open Filename:="C:\Mappe1.xls"
xls.Sheets(a).Select
xls.Worksheets("Forespørgsel21").UsedRange.Columns.AutoFit
DoCmd.Quit

Sidste linie lukker Access.

"... iøvrigt mener jeg, at du skal bruge pivot-tabeller!"
Jeg tror ikke du behøver at gå i gang med den store programmering.
Såvidt jeg kan se, så skal du udarbejde statistik på et univers af testpersoner eller patienter. Fra xcel, kan der oprettes en kæde til en Access-tabel, som automatisk opdateres når data ændres i Access. Kæden oprettes i forbindelse med oprettelsen af Pivot-tabellen, og du vil have meget nemt ved at oprette grafer og procentberegninger.
Giv lige pivot-tabellen en chance, selvom du synes at 63 parametre er mange. Du ønsker jo kun at tælle én af dem.

-> fardal
Jeg har brugt hele dagen i går på at slås med pivottabeller, og har følgende problemer (og undskyld jeg bliver fagteknisk i det nedenstående. Det gør det blot nemmere for mig selv at forstå din hjælp...)
1) Jeg har en række inklusionskriterier: ("Perforation, kvadrant 1-4"  ELLER "Central perforation" ELLER "Perifer perforation") OG ("Myringoplastik onlay" ELLER "Myringoplastik underlay" ELLER "Myringoplastik kombination"). Parenteserne skal opfattes som matematiske parenteser. Det er altså nødvendigt, at patienterne har een eller anden form for perforation OG har fået foretaget en eller anden form for myringoplastik. Variablerne ovenfor er alle "booleanske". (Hvad hedder det egentlig på dansk?)
2) Derudover har jeg en masse (57!) eksklusionskriterier, eksempelvis "Malleus defekt".
3) Jeg har for længst fundet ud af at udvælge mit materiale ud fra en forespørgsel i Access, men jeg skal i mit projekt også dokumentere, hvilke patienter jeg har udeladt og hvorfor. Det er her, jeg har brug for hjælp, for jeg har store problemer med dels at tælle de patienter, jeg har udeladt, og dels opstille det på en måde, så jeg selv og andre dødelige kan overskue det... Jeg skal altså tælle de patienter, som opfylder de kriterier, som er "reciprokke" ift ovenstående kriterier.
4) Vil du give et forslag til, præcist hvilke felter jeg placerer i pivotabellens rækkesektion, kolonnesektion, sidesektion og datasektion?

/Peter Jacobsen

-> mugs. Tak for dit kodeeksempel. Se mit svar til fardal.
Kan Dcount bruges til at tælle poster ud fra flere både-og/enten-eller kriterier?

Jeg har oprettet en lidt naiv syntaks for mine forespørgsler. Jeg har overskuelighedenss skyld navngivet de enkelte kolonner i mit regneark. Navnene fremgår af nedenstående.

TÆL HVIS
(((Perforation=0 AND Centralperforation=0 AND Periferperforation=0)
OR
(Myringoplastikonlay=0 AND Myringoplastikunderlay=0 AND Myringoplastikkombination=0))
OR
Tympanoplastik=1
OR
Mastoidektomi=1
OR
Radikalkavitet=1
osv
osv)

Det er altså sådan, det skal se ud. Problemet er bare at få Excel til at fatte, hvad det er, jeg vil! ;-)

Det er ofte nemmere at vurdere løsninger, når man sidder med datamaterialet. Men hovedproblemstillingen er vist "Tælle antal rækker og tælle antal registreringer". Pivot-tabeller er oplagt til dette, samtidig med at du får en masse ekstra faciliteter i form af statistik og kan fordele antallet på forskellige grupper. Der er alt for mange fejlmuligheder, hvis du skal kode dig udenom undtagelserne. Hvis du ønsker mere input er du velkommen til at sende tabellen til mig.

Peter > Jeg har ikke særlig forstand på Piovot-tabeller i Excel, og kan derfor ikke afgøre hvad der er bedst at anvende. Men primvipelt skal man kun skifte program,hvis det er nødvendigt.
DCount kan tælle med betingelser. Det gør jeg f.eks ved b =, hvor den tæller antal poster i Tabel1, hvor feltet [kaffe] = True.

Når du har så mange kriterier, vil jeg struktuere dem i Access:
Dim a As Integer,b,c,d
a = DCount("*", "Tabel1")
b = DCount("*", "Tabel1", "[kaffe] = True")
c = DCount("*", "Tabel1", "[te] = True")
d = DCount("*", "Tabel1", "[cola] = True")

Så har du oprettet variabler. Derefter kan du foretage en udregning således:

MsgBox a - b

som giver dig tallet af poster, der ikke drikker kaffe. Husk at du også kan lave grafer i Access.

Hvis du vil benytte Excel, skal du tænke på, at det er muligt at eksportere data fra Access til Excel. Så når du har opdateret Access kan du eksportere data til Excel som derved også bliver opdateret.

Jeg kan sende dig min testdb i eftermiddag. Mangler blot din e-mail.

Til Mugs og alle andre for den sags skyld. Det er ikke nødvendigt at overføre data fra Access til Excel.
Fra Excel oprettes kæde til data i Access, og data vises direkte som en tabel og/eller graf. Når data ændres i Access, så ændres tabellen og grafen i Excel, når den opdateres.

-> fardal
Jeg mangler din mailadresse. Vil du sende den til propelhat@hotmail.com?
-> mugs
Du må gerne sende din db til mig på ovenstående mailadresse. Jeg hælder dog stadig mest til en illustration med pivottabeller, hvis det kan gøres overskueligt.

/Peter Jacobsen

Sendt.