Microsoft Access: lomakkeet ja raportit - Demo 4

• Mallivideot
• Ulkoisen tietokannan linkittäminen
• Taulujen lisämääritykset
  • Banaanicurry - esimerkki reseptistä
• Lomakkeet
  • Relationships
  • Useaan tauluun kohdistuvat lomakkeet
  • Lomakkeiden muokkaaminen
  • Alilomakkeen lisääminen
• Raportit
  • Raportit usean taulun pohjalta
  • Raportin ulkoasun korjaaminen
  • Monimutkaisemmat raportit
  • Yhteenvedot raporteissa

Näissä demoissa linkitetään PostgreSQL-tietokanta Microsoft Accessiin, tehdään tauluille muutama käyttöä helpottava lisämääritys, rakennetaan lomakkeilla pieni käyttöliittymä ja tehdään pari raporttia.

• Käynnistä Microsoft Access (Start|Programs|Microsoft Access
• Luo uusi tietokanta valitsemalla Blank Database
• Anna tietokannalle nimeksi demo4.mdb ja valitse sijoituspaikaksi U:\tiedonhallinta\demo4

Ulkoisen tietokannan linkittäminen

Linkitetään Microsoft Accessiin demo 2:ssa itse luomasi reseptitietokanta. Jos et ole ehtinyt tehdä reseptitietokantaa niin voit luoda sen testidatoineen itsellesi demo 3:ssa käytetyn tietokannan luontilauseilla ODBC Query Toolin avulla samaan tapaan kuin Demo 2:ssa ja Demo 3:ssa.

• Valitse File|Get External Data|Link Tables

  

• Valitse List of type-alasvetovalikosta kohta ODBC Databases()

  

• Ota yhteys omaan tietokantaasi käyttäen demo2:ssa määrittelemääsi tietokantayhteyttä
• Valitse kaikki Reseptitietokantaan liittyvät taulut ja napauta OK

  

• Valitut taulut ilmaantuvat Accessin Tables-välilehdelle

  

Taulujen lisämääritykset

Ennen lomakkeiden luomista kannattaa Accessissa määritellä hakutaulut kaikkiin kentteihin joihin se on suinkin mahdollista. Näitä kenttiä ovat ainakin ne joista on määritelty viite-eheys.

Voit kokeilla tietojen syöttämistä esim. Ohje-tauluun tuplanapauttamalla taulun nimeä. Tietojen syöttämisessä on ongelmana oikean reseptin valitseminen koska pitäisi muistaa ulkoa jokaisen reseptin tunniste.

Määrittelemällä hakutaulu saadaan tietojen syöttäminen huomattavasti helpommaksi

• Valitse hiiren oikealla näppäimellä Ohje-taulu. Valitse valikosta Design View

  

• Access ilmoittaa, että taulu on linkitetty eikä kaikkia sen ominaisuuksia voida muokata. Valitse Yes

  

• Valitse Reseptiid-kenttä napauttamalla sen tiedot sisältävää riviä. Siirry Lookup-välilehdelle.

  

• • Valitse Display Control -alasvetovalikosta vaihtoehto Combo Box
  • Valitse Row Source Type -kohtaan vaihtoehto Table/Query
  • Valitse Row Source -kohtaan taulu josta reseptiid-kenttään kelpaavat arvot löytyvät eli resepti
  • Bound column -kohdassa kerrotaan monesko kenttä resepti-taulussa sisältää kelpuutettavan arvon. Malliratkaisussa ReseptiID-kenttä on neljäs (4). Varmista monesko kenttä on omassa tietokannassasi.
  • Column Count -kohdassa kerrotaan montako kenttää halutaan näyttää. Tähän kannattaa valita sen verran kenttiä että Bound column -kohdassa kerrottu kenttä mahtuu mukaan. Malliratkaisun mukaisessa tietokannassa valitaan neljä (4) kenttää.
  • Column Heads -kohdassa voi määrätä tarvittaessa kentille otsikot. Yleensä niitä ei tarvita joten valitaan No
  • Column Widths -kohdassa kerrotaan kuinka paljon esitystilaa varataan kullekin kentälle. Jätetään kaikki muut kentät piiloon paitsi oleellisin eli reseptin nimi. Eri kenttien pituudet erotetaan toisistaan puolipisteellä (;). Malliratkaisun mukaisessa tietokannassa kenttien pituudet olisivat 5cm;0cm;0cm;0cm
  • List Rows kertoo montako riviä vaihtoehdoista näytetään kerrallaan
  • List Width määrää alasvetovalikon leveyden
  • Limit To List määrää sallitaanko myös sellaisia arvoja joita ei listasta löydy

  

• Sulje Design-ikkuna ja anna Accessin tallentaa muutokset

  

• Kokeile nyt syöttää tietoja Ohje-tauluun. Nyt voit valita reseptin kätevästi alasvetovalikosta

  

• Määrittele tismalleen samaan tapaan hakutaulut viite-eheysmääritysten mukaisesti (kts. kuva) myös seuraaville:
  • Resepti-taulun ruokalajiid-kentästä haku Ruokalaji-taulun ruokalajiid-kenttään
  • Liittyy-taulun Resepti_Reseptiid-kentästä haku Resepti-taulun reseptiid-kenttään
  • Liittyy-taulun Aine_Aineid-kentästä haku Aine-taulun aineid-kenttään
  • Liittyy-taulun Yksikko_Lyhenne-kentästä haku Yksikko-taulun lyhenne-kenttään

  

• Syötä kaikkiin tauluihin muutaman tietueen verran testidataa niin saat varmistettua hakutaulujen toimivuuden sekä tehtyä vähän testidataa seuraavia tehtäviä varten. Jos tietokannassasi ei ole vielä tietoja niin syötä vaikkapa seuraavan reseptin tietoja sen verran että varmistut tietokantasi toimivuudesta:

  Banaanicurry - esimerkki reseptistä

  • 2 kypsää banaania
  • 1-2 rkl tulista currya
  • 1 sipuli
  • 1-3 valkosipulinkynsi
  • 2 dl ruuanvalmistusjugurttia tai 2 dl kermaa
  • 3 rkl mango- tai hedelmachutneyta
  • ripaus suolaa
  • öljyä
  1. Silppua sipuli ja murskaa valkosipulinkynsi
  2. Kuumenna pannussa oljya ja kuullota sipulisilppu
  3. Lisaa curryjauhe, valkosipuli, mangochutney ja suola
  4. Kiehuta miedolla lammolla viisi minuuttia, lisaa kerma ja kiehuta viela viisi minuuttia
  5. jos kaytat ruuanvalmistusjugurttia niin ala keitä sitä, juoksettuu helposti.
  6. Lohko banaanit viipaleiksi ja sekoita joukkoon.
  7. Tarjoaa persiljariisin kera... alkuperaisen ohjeen mukaan pitaisi syoda kylmänä mutta maistuu erinomaiselta myös lämpimänä :)

  Lisää testidataa voit kopioida mallitietokannan luomiseen tarkotetuista sql-lauseista.

Lomakkeet

Lomakkeilla voidaan luoda lähes millainen tahansa käyttöliittymä tietokannan päälle. Monimutkaisten lomakkeiden tekeminen edellyttää ohjelmointitaitoja ja tarkempaa ymmärrystä graafisten käyttöliittymien toiminnasta. Tällä kurssilla tyydytään tekemään lomakkeita joihin ei vielä vaadita ohjelmointitaitoja.

• Valitse Forms-välilehti
• Valitse Create form by using wizard

  

• Valitse Tables/Queries-alasvetovalikosta taulu, jonka päälle haluat lomakkeen muodostaa. Valitaan tässä tapauksessa ruokalaji-taulu
• Valitse Available fields-listasta kentät jotka halutaan lomakkeelle. Valitse kaikki ruokalaji-taulun kentät

  

  Valitse Next

• Valitse lomakkeen ulkoasuksi Tabular
  • Tabular-muodossa lomakkeella näkyy samaan aikaan useita tietueita
  • Columnar-muodossa lomakkeella näkyy samaan aikaan vain yksi tietue.
  • Muihin ulkoasumuotoihin voit tutustua itseksesi

  

• Valitse lomakkeen tyyliksi haluamasi vaihtoehto

  

• Anna lomakkeelle nimeksi ruokalaji

  

• Pääset kokeilemaan valmista lomaketta. Yritä syöttää jokin uusi ruokalaji ja sulje sen jälkeen lomake
• Luo samanlaiset lomakkeet myös tauluille aine, Liittyy ja yksikko

Relationships

Lomakkeella voi myös tietyin rajoituksin syöttää tietoja samanaikaisesti useampaan eri tauluun. Jos taulujen välillä on selkeä yhden suhde moneen niin useampaan tauluun pohjautuvaa lomaketta voi yrittää tehdä.

Access ei ikävä kyllä osaa etsiä taulujen välisiä viite-eheyksiä PostgreSQL-tietokannasta vaan ne täytyy kertoa Accessille erikseen.

• Valitse Tools|Relationships
• Valitse kaikki taulut ja napauta Add-painiketta

  

• Sulje Show Table-dialogi valitsemalla Close
• Vedä hiirellä relaatiokuvan mukaiset viite-eheydet taulujen välille. Muista vetää viite-eheydet oikean suuntaisesti eli viittaavasta taulusta alkaen. Access avaa jokaisen viite-eheysmäärityksen yhteydessä Edit Relationships-dialogin jossa riittää kun valitsee Create

  

  

• Sulje Relationships-ikkuna ja anna Accessin tallentaa muutokset

Useaan tauluun kohdistuvat lomakkeet

Luodaan lomake jolla syötetään reseptin tiedot ja reseptiin liittyvät ohjeet

• Valitse Create form by using wizard
• Valitse resepti-taulusta kaikki kentät. Valitse myös ohje-taulusta vaihenro- ja ohjeteksti-kentät

  

• Valitse, että lomake esitetään by resepti eli ryhmitellään reseptien mukaan. Kerrallaan lomakkeella näytetään yhden reseptin tiedot ja kaikkien siihen liittyvien ohjeiden tiedot. Valitse myös Form with subform(s). Valinta Linked forms tekisi useita eri lomakkeita jotka olisi vain yhdistetty toisiinsa

  

• Valitse lomakkeen ulkoasuksi Datasheet
• Valitse lomakkeen tyyliksi haluamasi tyyli
• Anna lomakkeelle nimeksi Resepti ja alilomakkeelle (subform) nimeksi ohje Subform

  

• Testaa valmista lomaketta. Lisää uusi resepti ja sille muutama valmistusohje.

  

Lomakkeiden muokkaaminen

Lisätään resepti-lomakkeeseen painikkeet joilla voi helposti avata muut resepti-tietokantaan liittyvät lomakkeet.

• Avaa resepti-lomake
• Valitse View|Design View

  

• Levennä lomakealuetta hiirellä niin paljon, että painikkeille tulee tilaa oikeaan laitaan.
• Lisää lomakkeelle uusi painike Command Button-painikkeella

  

• Command Button Wizard aukeaa. Valitse Form Operations Open Form

  

• Valitse lomake, joka painikkeella avataan. Valitse Aine

  

• Valitaan, että lomake näyttää kaikki tietueet Open the form and show all the records

  

• Kirjoitetaan painikkeen tekstiksi Aine

  

• Painikkeen nimeksi voi jättää saman mitä Access ehdottaa

  

• Kokeile painikkeen toimintaa vaihtamalla lomake käyttötilaan valitsemalla View|Form view
• Lisää samalla tavalla painikkeet yksikko- ja ruokalaji-lomakkeiden avaamiseksi

Alilomakkeen lisääminen

Lomakkeelle voidaan lisätä myös uusia alilomakkeita, jotka toimivat samalla tavalla kuin aiemmin velhon avulla luotu alilomake. Lisätään resepti-lomakkeelle alilomakkeena liittyy-lomake jolloin saamme helposti yhdellä ja samalla lomakkeella syötettyä kaikki reseptiin liittyvät tiedot. Ainoastaan harvemmin tarvittavat asiat kuten aineiden, yksiköiden tai ruokalajien lisäykset ja muutokset täytyy tehdä muilla lomakkeilla.

• Vaihda lomake muokkaustilaan View|Design view
• Tee lomakkeelle lisää tilaa pystysuunnassa venyttämällä lomaketta

  

• Lisää lomakkeelle alilomake Subform/Subquery-painikkeella

  

  Sijoita alilomake ohje-alilomakkeen alapuolelle
• SubForm Wizard käynnistyy. Valitse Liittyy-taulu.

  

• Valitse lomakkeita yhdistävät kentät. Form/report fields -puolelle valitse reseptiid ja Subform/report fields -puolelle valitse resepti_reseptiid eli tismalleen samat kentät jotka löytyvät viite-eheysmäärittelystäkin.

  

• Jätä alilomakkeen nimeksi Accessin ehdotus

  

• Kokeile lomakkeen toimintaa valitsemalla View|Form view. Syötä jokin uusi resepti ja sille lyhyet ohjeet ja tarvittavat valmistusaineet.

  

• Sulje lomake ja anna Accessin tallentaa muutokset

Raportit

Raporttien avulla voidaan tulostaa siistejä yhteenvetoja suoraan taulujen tai kyselyjen pohjalta.

• Siirry Reports-välilehdelle
• Valitse Create report by using wizard
• Valitse ruokalaji-taulusta kaikki kentät

  

• Älä lisää mitään ryhmittelyjä koska sellaisia ei järkevästi löydy pelkästä ruokalaji-taulusta.

  

• Järjestä raportti ruokalajin nimen mukaan

  

• Valitse raportin ulkoasuksi Tabular
• Valitse haluamasi tyylipohja
• Anna raportille nimeksi ruokalaji
• Esikatsele raportti
• Sulje raportti

Raportit usean taulun pohjalta

Useaan tauluun pohjautuvat raportit onnistuvat samalla tavalla kuin vastaavat lomakkeetkin.

• Siirry Reports-välilehdelle
• Valitse Create report by using wizard
• Valitse resepti-taulusta kentät: nimi, kuvaus ja henkilomaara sekä ohje-taulusta kentät: vaihenro ja ohjeteksti.
• Ryhmittele raportti reseptien mukaan

  

• Älä lisää muita ryhmittelytasoja (grouping levels
• Järjestä vaihenro-kentän mukaan
• Valitse ulkoasuksi Outline 1
• Valitse haluamasi tyyli
• Anna raportille nimeksi resepti
• Esikatsele raportti

Raportin ulkoasun korjaaminen

• Valitse View|Design View
• Nyt voit hiirellä siirtää haluamiasi kenttiä paikasta toiseen, muuttaa niiden kokoa tai jopa poistaa niitä.

  

• Levennä ohjetekstin kenttä niin leveäksi kuin mahdollista.
• Napauta hiiren oikeaa painiketta ohjeteksti-kentän päällä ja valitse Properties

  

  • Valitse Format-välilehti
  • Muuta Can Grow -kohdan arvoksi Yes, jonka jälkeen ohjeteksti-kenttä voi laajeta sen mukaan kuinka paljon tekstiä siihen on tulossa
  • Voit tutkia muitakin tarjolla olevia ominaisuuksia mutta niiden toimintaan joudut perehtymään tarkemmin Accessin avustuksen avulla
  • Sulje Properties-ikkuna
• Kokeile raportin uutta ulkoasua valitsemalla View|Print Preview

Monimutkaisemmat raportit

Accessin oman raporttivelhon kyvyt loppuvat kesken jos raporttiin yrittää liittää useita tauluja ja vielä ryhmitellä niiden tietoja. Yleensä viisainta onkin tehdä raporttia varten oma kysely, joka hoitaa tietojen yhdistämisen useammasta taulusta, ja luo raportin suoraan tämän kyselyn pohjalta.

• Luodaan ensimmäisenä kysely raportin pohjaksi. Valitse Queries-välilehti.
• Valitse Create query in Design view
• Sulje Show table -dialogi
• Valitse View|SQL View
• Nyt voit kirjoittaa tavallisen SQL-kyselyn. Kirjoita kysely, joka listaa reseptin nimen ja reseptiin liittyvien aineiden nimet, ainemäärät ja ainemäärän yksiköt reseptin nimen ja aineiden nimien mukaan järjestettynä.

  SELECT resepti.nimi AS resepti,  liittyy.maara, liittyy.yksikko_lyhenne, aine.nimi AS aine
  FROM Resepti, liittyy, aine
  WHERE Resepti.reseptiid = liittyy.resepti_reseptiid
  AND liittyy.Aine_aineid = Aine.aineid
  ORDER BY resepti.nimi, aine.nimi
  ;

• Voit kokeilla kyselyn suorittamista valitsemalla Query|Run. Takaisin SQL-tilaan pääset valitsemalla View|SQL view
• Saatuasi kyselyn kuntoon tallenna se reseptin_aineet-nimellä
• Luo uusi raportti
• Valitse raporttiin edellä tekemäsi kyselyn kaikki kentät
• Ryhmittele raportti reseptin mukaan
• Älä lisää muita ryhmittelytasoja
• Järjestä aineen mukaan
• Valitse ulkoasuksi Outline 1
• Valitse haluamasi tyyli
• Anna raportille nimeksi reseptin_aineet

Yhteenvedot raporteissa

Raporteissa voi laskea yhteenvetoja eri kenttien tiedoista.

• Kirjoita uusi kysely: Listaa paljonko kutakin ainetta tarvitaan ja missä resepteissä. Järjestä ainemäärät aakkosjärjestykseen aineen nimen, reseptin ja käytetyn yksikön mukaan.

  SELECT aine.nimi, resepti.nimi, maara, yksikko_lyhenne
  FROM Resepti, aine, liittyy
  WHERE resepti.reseptiID=liittyy.resepti_reseptiID And aine.aineID=liittyy.aine_aineID
  ORDER BY aine.nimi, resepti.nimi, yksikko_lyhenne;

  Tallenna kysely ainemäärät-nimellä. Kyselyn tuloksen pitäisi näyttää seuraavan tyyppiseltä (riippuen tietokantasi sisällöstä):

  aine.nimi	resepti.nimi	maara	yksikko_lyhenne
  Ananasmurska	Ananas Con Carne	450	g
  Banaani	Banaanicurry	2	kpl
  Cayennepippuri	Ananas Con Carne	2	tl
  Curry	Banaanicurry	2	rkl
  Digestivekeksi	Rommikakku	200	g
  Jauheliha	Ananas Con Carne	400	g
  Kahvi	Tiramisu	2	dl
  Kananmuna	Kauralastut	1	kpl
  Kananmuna	Porkkanapiirakka	4	kpl
  Kananmuna	Tiramisu	4	kpl
  Kaurahiutale	Kauralastut	2,5	dl
  Keltuainen	Rommikakku	4	kpl
  Keltuainen	Tiramisu	3	kpl
  Kuohukerma	Rommikakku	4	dl
  Kuohukerma	Tiramisu	2	dl
  Lehtiselleri	Ananas Con Carne	2	kpl
  Leivinjauhe	Kauralastut	1	tl
  Leivinjauhe	Porkkanapiirakka	2	tl
  Leivinjauhe	Tiramisu	1	tl
  Lihaliemikuutio	Ananas Con Carne	1	kpl
  Liivatelehti	Rommikakku	5	kpl
  Liivatelehti	Tiramisu	4	kpl
  Mangochutney	Banaanicurry	3	rkl
  Mantelilikööri	Tiramisu	6	rkl
  Margariini	Kauralastut	50	g
  Margariini	Rommikakku	100	g
  Maustamaton koskenlaskijajuusto	Porkkanapiirakka	200	g
  Maustamaton tuorejuusto	Tiramisu	300	g
  Perunajauho	Tiramisu	0,75	dl
  Porkkanaraaste	Porkkanapiirakka	6	dl
  Purjo	Ananas Con Carne	1	kpl
  Ranskankerma	Tiramisu	1	dl
  Rommi	Rommikakku	3	rkl
  Ruuanvalmistusjugurtti	Banaanicurry	2	dl
  Sipuli	Ananas Con Carne	4	kpl
  Sipuli	Banaanicurry	1	kpl
  Sokeri	Kauralastut	1	dl
  Sokeri	Porkkanapiirakka	3	dl
  Sokeri	Rommikakku	1,5	dl
  Sokeri	Rommikakku	1	rkl
  Sokeri	Tiramisu	1,5	dl
  Sooda	Porkkanapiirakka	3	tl
  Suola	Ananas Con Carne	1	tl
  Suola	Banaanicurry	1	tl
  Tomaattimurska	Ananas Con Carne	400	g
  Tomusokeri	Porkkanapiirakka	3	dl
  Tomusokeri	Tiramisu	2	dl
  Valkoiset pavut tomaattikastikkeessa	Ananas Con Carne	400	g
  Valkosipulinkynsi	Ananas Con Carne	3	kpl
  Valkosipulinkynsi	Banaanicurry	3	kpl
  Valkuainen	Tiramisu	3	kpl
  Vaniljasokeri	Kauralastut	1	tl
  Vaniljasokeri	Porkkanapiirakka	3	tl
  Vehnäjauho	Kauralastut	2	rkl
  Vehnäjauho	Porkkanapiirakka	4	dl
  Vehnäjauho	Tiramisu	1	dl
  Voi	Porkkanapiirakka	250	g
  Öljy	Ananas Con Carne	5	rkl
  Öljy	Banaanicurry	1	rkl

• Luo uusi raportti edellä tekemäsi kyselyn pohjalle
• Ryhmittele raportti aineen nimen mukaan
• Lisää uudeksi ryhmittelytasoksi yksikko_lyhenne

  

  yksikko_lyhenne pitää ottaa mukaan ryhmittelyyn koska saman aineen määrä voidaan esittää useassa eri yksikössä eikä niitä voi suoraan laskea yhteen.

• Järjestä reseptin nimen mukaan. Muuta nyt myös kyselyn yhteenvetoasetuksia eli summary options. Laske yhteen (sum) maara-kentän sisältö.
• Valitse haluamasi ulkoasu ja tyyli. Keksi raportille hyvä nimi
• Tarvittaessa muotoile raporttia uudelleen poistamalla turhia tekstejä.

Kokeile itse luoda jotain uusia lomakkeita tai raportteja.