SQLkyselyitä tehtäessä tulee toisinaan vastaan tarve hakea tietoja kahdesta eri tietokannasta. Esimerkiksi katelaskelmia tehtäessä tarvitaan tuotteiden myyntitiedot haetaan laskutustietokannan (.wls) tuoterivit -taulusta ja tuotteiden ostohinnat haetaan tuotekortiston (.wtk) tuotteet-taulusta.
Tietojen hakemiseen kahdesta eri taululusta käytetään JOIN-komentoa. JOIN-komento ei kuitenkaan toimi kahden tietokannan välillä, vaan vaatii että taulut ovat samassa tietokannassa. Taulut saadaan samaan tietokantaan linkittämällä yhden tietokannan taulu toiseen tietokantaan Microsoft Access 2000 -tietokantaohjelman avulla.
Vaihe 1:Linkitä tuotekortisto laskutustietokantaan
Linkitä tuotekortisto laskutustietokantaan
Vaihe 2: Rakenna katelaskelmia
Rakenna katelaskelma valitsemalla laskunsyöttötilassa Tilasto | Kyselytilastot (SQL) | Luo kyselyitä
Valitse vasemman yläkulman pudotuslistalta määrittämätön kysely
Kopioi alla olevissa esimerkeissä kysely sille varattuun tilaan ja suorita kysely napsauttamalla [Suorita] painiketta
Jos kysely ilmoittaa ”Virhe kyselyssä:Microsoft Jet -tietokantamoduuli ei löydä syötetaulukkoa tai -kyselyä ”TUOTTEET”. Varmista, että se on luotu ja että kirjoitit sen nimen oikein” on todennäköinen syy siinä, että et ole tehnyt vaiheen 1 mukaista tuotekortiston linkittämistä laskutustietokantaan. Jos et saa linkitystä tehtyä (esim. käytössäsi ei ole Access 2000 tietokantaohjelmaa) ei tämän sivun ohjeitakaan voi hyödyntää.
Katelaskelma tuotteittain
Rakennetaan kysely paloittain. Tehdään ensin kysely tuoterivit kantaan, jotta nähdään, minkä nimisiä kenttiä siellä on:
SELECT * FROM TUOTERIVIT
Seuraavaksi rajataan tuoterivejä päivämäärän
mukaan. Kirjoita kysymys 1 kohtaan alkupäivä ja kysymys 2 kohtaan
loppupäivä ja kirjoita kysymysten vastauksiksi jotkut sopivat
päivät. Kopioi seuraavat rivit kyselyksi ja suorita kysely:
SELECT * FROM TUOTERIVIT
WHERE CDATE(PäIVäMääRä) BETWEEN DATEVALUE(’%1’) AND DATEVALUE(’%2’)
Tuoterivejä ei kuitenkaan käytetä sellaisenaan, vaan lasketaan tuotenumeroittain tuotteiden myyty kappalemäärä ja myyntihinnat.
SELECT TUOTENUMERO,
SUM(MÄÄRÄ) AS [MYYTY KPL],
SUM([NETTO MK]) AS [MYYTY VEROTON],
SUM([BRUTTO MK]) AS [MYYTY SIS ALV]
FROM TUOTERIVIT
WHERE CDATE(PäIVäMääRä) BETWEEN DATEVALUE(’%1’) AND DATEVALUE(’%2’)
GROUP BY TUOTENUMERO
Otetaanpa tähän väliin esimerkki tietojen hakemisesta kahdesta taulusta JOIN-komennon avulla:
SELECTTUOTERIVIT.TUOTENUMERO, TUOTTEET.NIMIKE
FROMTUOTERIVIT
INNERJOINTUOTTEET
ONTUOTERIVIT.TUOTENUMERO=TUOTTEET.TUOTENUMERO
Ylläolevassa kyselyssä haetaan tuotenumero tuoterivit taulusta ja nimike tuotteet taulusta. kahden taulun tietueet yhdistetään alimman rivin ohjeen ONTUOTERIVIT.TUOTENUMERO=TUOTTEET.TUOTENUMERO mukaan. Kenttien nimen alussa käytetylle taulun nimelle (TUOTERIVIT. tai TUOTTEET.)voidaan antaa lyhyt alias, jolloin kyselyistä tulee lyhyempiä. Tässä esimerkissä TUOTERIVITsaa aliaksen A ja TUOTTEETaliaksen B:
SELECTA.TUOTENUMERO, B.NIMIKE
FROMTUOTERIVIT A
INNERJOINTUOTTEET B
ONA.TUOTENUMERO=B.TUOTENUMERO
Jatketaan katelaskelman tekemistä. Aiempana tehtyä tuotteiden myyntimäärät laskevaa kyselyä käytetään seuraavassa alikyselynä ja sen saa aliaksen A, jolloin kyselyn palauttamiin kenttiin viitataan A.kentännimi. Tuotekortisto saa puolestaan aliaksen B, jolloin tuotekortiston kenttiin viitataan B.kentännimi.
SELECT A.TUOTENUMERO, B.NIMIKE , B.OSTOHINTA, A.[MYYTYKPL], A.[MYYTYVEROTON], A.[MYYTY SISALV]
FROM (
SELECT TUOTENUMERO,
SUM(MääRä) AS [MYYTY KPL],
SUM([NETTO MK]) AS [MYYTY VEROTON],
SUM([BRUTTO MK]) AS [MYYTY SIS ALV]
FROM TUOTERIVIT
WHERE CDATE(PäIVäMääRä) BETWEEN DATEVALUE(’%1’) AND DATEVALUE(’%2’)
GROUP BY TUOTENUMERO
) A
INNERJOIN TUOTTEETB ONA.TUOTENUMERO = B.TUOTENUMERO
Ylläolevassa kysely palauttaa tarvittavat tiedot tuotteiden katteen laskemiseksi. Seuraavaksi jääkin vain tehtäväksi katteiden laskeminen, joista seuraavassa muutamia esimerkkejä.
Verollinen summa saadaan muutettua verottomaksi kertomalla summa 100/124:lla. Esimerkiksi verollinen ostohinta saadaan muutetuksi verottomaksi B.OSTOHINTA/(100/124)
B.OSTOHINTA*(100/124) AS [veroton ostohinta]
Näissä esimerkeissä ostohinta sisältää 24% veroa, joka vähennetään kertomalla ostohinta 100/124:lla. Jos tuotekortistossasi ostohinnat ovat verottomat, jätä pois näistä kaavoista alv:n vähentävä laskutoimitus, jolloin ostohinta B.OSTOHINTA*(100/124) muuttuu muotoon B.OSTOHINTA, ts ota pois merkit *(100/124).
Verottomat kate-eurot saadaan laskettua laskemalla ensin, mikä oli myyntierän hankintahinta (myyty määrä * ostohinta) ja vähentämällä näin saatu summa myyntihinnasta
A.[MYYTYKPL] * (B.OSTOHINTA*(100/124)) AS [veroton hankintahinta]
A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124))) AS [kate-eurot yhteensä]
Kate-eurot saadaan siistittyä FORMATfunktion avulla
FORMAT(A.[MYYTY VEROTON] – (A.[MYYTY KPL] * (B.OSTOHINTA*(100/124))) ,’### ### ##0.00′) AS [kate-eurot yht]
Kate-eurot per myyty tuote saadaan jakamalla ylläoleva luku myytyjen tuotteiden kappalemäärällä
(A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124))))/A.[MYYTYKPL] AS [kate-eurot per myyty tuote]
Kate-eurot saadaan siistittyä 2 desimaalin tarkkuuteen FORMAT funktion
avulla:
FORMAT((A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124))))/A.[MYYTYKPL],’### ##0.00′) AS [kate per tuote]
Kateprosentti saadaan jakamalla kate-eurot tuote-erän myyntihinnalla
(A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124)))) / A.[MYYTYVEROTON] * 100 AS[Kate%]
Kateprosentin desimaaleja saadaan siistittyä 2 desimaalin tarkkuudella
FORMAT-funktion avulla:
FORMAT((A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124)))) / A.[MYYTYVEROTON] ,’0.00%’) AS[KateP%]
Kysely täydentyy katteita laskevilla riveillä:
SELECT A.TUOTENUMERO, B.NIMIKE , B.OSTOHINTA, A.[MYYTYKPL], A.[MYYTYVEROTON], A.[MYYTY SISALV],
B.OSTOHINTA*(100/124) AS [veroton ostohinta],
A.[MYYTYKPL] * (B.OSTOHINTA*(100/124)) AS [veroton hankintahinta],
A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124))) AS [kate-eurot yhteensä],
FORMAT(A.[MYYTY VEROTON] – (A.[MYYTY KPL] * (B.OSTOHINTA*(100/124))) ,’### ### ##0.00′) AS [kate-eurot yht],
(A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124))))/A.[MYYTYKPL] AS [kate-eurot per myyty tuote],
FORMAT((A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124))))/A.[MYYTYKPL],’### ##0.00′) AS [kate per tuote],
(A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124)))) / A.[MYYTYVEROTON] * 100 AS[Kate%],
FORMAT((A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124)))) / A.[MYYTYVEROTON] ,’0.00%’) AS[KateP%]
FROM (
SELECT TUOTENUMERO,
SUM(MääRä) AS [MYYTY KPL],
SUM([NETTO MK]) AS [MYYTY VEROTON],
SUM([BRUTTO MK]) AS [MYYTY SIS ALV]
FROM TUOTERIVIT
WHERE CDATE(PÄIVÄMÄÄRÄ) BETWEEN DATEVALUE(’%1’) AND DATEVALUE(’%2’)
GROUP BY TUOTENUMERO
) A
INNERJOIN TUOTTEETB ONA.TUOTENUMERO = B.TUOTENUMERO
Katetta laskettaessa ylläolevista kentistä voi jättää
varsinaiseen kyselyyn vain tarvittavat, esimerkiksi:
SELECT A.TUOTENUMERO, B.NIMIKE , A.[MYYTYKPL], A.[MYYTYVEROTON],
B.OSTOHINTA*(100/124) AS [veroton ostohinta],
FORMAT(A.[MYYTY VEROTON] – (A.[MYYTY KPL] * (B.OSTOHINTA*(100/124))) ,’### ### ##0.00′) AS [kate-eurot yht],
FORMAT((A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124))))/A.[MYYTYKPL],’### ##0.00′) AS [kate per tuote],
FORMAT((A.[MYYTYVEROTON] – (A.[MYYTYKPL] * (B.OSTOHINTA*(100/124)))) / A.[MYYTYVEROTON] ,’0.00%’) AS[KateP%]
FROM (
SELECT TUOTENUMERO,
SUM(MääRä) AS [MYYTY KPL],
SUM([NETTO MK]) AS [MYYTY VEROTON],
SUM([BRUTTO MK]) AS [MYYTY SIS ALV]
FROM TUOTERIVIT
WHERE CDATE(PÄIVÄMÄÄRÄ) BETWEEN DATEVALUE(’%1’) AND DATEVALUE(’%2’)
GROUP BY TUOTENUMERO
) A
INNERJOIN TUOTTEETB ONA.TUOTENUMERO = B.TUOTENUMERO
