tricky rapport

Lav først en foresp som krydstabulering mad alle måneder som kolloneoverskrifter.

Jeg vil tro at 'hekseriet' skal ske her, der efter er det 'nemt' at lave en rapport.

hvis din db er i ver. 2000, så prøv at sende den:
nih@mail1.stofanet.dk

Niels

Først laves en UNION således at der fåes en tabel: KundeID, TerminId, Præmie:

SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[1 Termin] as termin, Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid
UNION all
SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[2 Termin], Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid
UNION all
SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[3 Termin], Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid
UNION ALL SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[4 Termin], Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid;

Derefter krydsTabulering
TRANSFORM Sum(Q_AlleTerminer_1.Terminspræmie) AS SumOfTerminspræmie
SELECT Q_AlleTerminer_1.kundeid, Kundeoplysninger.Fornavn, Kundeoplysninger.Firmanavn, Q_AlleTerminer_1.bilid
FROM (Q_AlleTerminer AS Q_AlleTerminer_1 LEFT JOIN Måned ON Q_AlleTerminer_1.termin = Måned.nr) INNER JOIN Kundeoplysninger ON Q_AlleTerminer_1.kundeid = Kundeoplysninger.kundeid
GROUP BY Q_AlleTerminer_1.kundeid, Kundeoplysninger.Fornavn, Kundeoplysninger.Firmanavn, Q_AlleTerminer_1.bilid
ORDER BY Måned.nr
PIVOT Måned.nr;

Eksembeldb sendt til dsjk

Niels

det ser meget godt ud.
Bare lige en ting.
Kan jeg få lagt terminspræmien sammen pr måned, så der i januar står 1000, februar 20000 i en række for sig selv nedefor neden?

dsjk

Ja - Det skal foregå i rapporten:

I rapportfoden skal laves tekstfelter med kontrolelementkilde:
=Sum([1])  for januar
og en mere
=Sum([2])  for februar
osv.

Niels

Har du brug for flere felter i din rapport trækker du dem bare ned i foresp gitteret 'Q_rpt' som rækkeoverskrift.

Du kan også tilføje tabellen 'biler' og trække eks. 'bilmærke' ned - slet dog nogen af alle relationerne, således der kun er relateret mellem 'biler' og 'Q_AlleTerminer'

Niels

hvordan tilføjer jeg "kundeidfod" sektionen?

jeg har jo rapporthoved, sidehoved, kundeidhoved, detaljesektion, sidefod og rapportfod

men mangler kundeidfod, og det er her jeg skal indsætte sum feltet.

dsjk

Tryk på knappen 'Sortering og gruppering' - her sætter du gruppefod til ja.

MEN så får du en sum for hver kundeid, hvis du vil have en samlet sum skal du anvende 'rapportfoden'

ok, så har jeg fået kigget lidt mere på det, og har fundet en lille fejl.
November vises ikke i rapporten, og jeg kan ikke vælge den.
Så vidt jeg kan se skyldes det at der ikke er nogen biler der har termin i denne måned, men jeg vil gerne have den på alligevel.

Hvad skal jeg rette til?

dsjk

JA - Jeg har vendt et outerJoin forkert, Desuden er der lidt jeg ikke har tænkt på - måned '0' ???? - Det må være et normaliserings problem ;o)

Q_AlleTerminer:
SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[1 Termin] as termin, Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid
WHERE  Biler.[1 Termin] between 1 and 12
UNION all
SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[2 Termin], Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid
WHERE  Biler.[2 Termin] between 1 and 12
UNION all
SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[3 Termin], Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid
WHERE  Biler.[3 Termin] between 1 and 12
UNION ALL SELECT Kundeoplysninger.kundeid, Biler.bilid, Biler.[4 Termin], Biler.Terminspræmie
FROM Kundeoplysninger INNER JOIN Biler ON Kundeoplysninger.kundeid = Biler.kundeid
WHERE  Biler.[4 Termin] between 1 and 12;

og Q_rpt:
TRANSFORM Sum(Q_AlleTerminer_1.Terminspræmie) AS SumOfTerminspræmie
SELECT Q_AlleTerminer_1.kundeid, Kundeoplysninger.Fornavn, Kundeoplysninger.Firmanavn, Q_AlleTerminer_1.bilid, Biler.Bilmærke
FROM ((Q_AlleTerminer AS Q_AlleTerminer_1 RIGHT JOIN Måned ON Q_AlleTerminer_1.termin = Måned.nr) LEFT JOIN Kundeoplysninger ON Q_AlleTerminer_1.kundeid = Kundeoplysninger.kundeid) LEFT JOIN Biler ON Q_AlleTerminer_1.bilid = Biler.bilid
WHERE (((Kundeoplysninger.kundeid) Is Not Null))
GROUP BY Kundeoplysninger.kundeid, Q_AlleTerminer_1.kundeid, Kundeoplysninger.Fornavn, Kundeoplysninger.Firmanavn, Q_AlleTerminer_1.bilid, Biler.Bilmærke
ORDER BY Måned.nr
PIVOT Måned.nr;

Du skal bare klippe og klistre de ovenstående SQL'er ind i dine foresp.

Fik du styr på de totaler i rapporten ????

Niels

rettelse:
og Q_rpt:
TRANSFORM Sum(Q_AlleTerminer_1.Terminspræmie) AS SumOfTerminspræmie
SELECT Q_AlleTerminer_1.kundeid, Kundeoplysninger.Fornavn, Kundeoplysninger.Firmanavn, Q_AlleTerminer_1.bilid, Biler.Bilmærke
FROM ((Q_AlleTerminer AS Q_AlleTerminer_1 RIGHT JOIN Måned ON Q_AlleTerminer_1.termin = Måned.nr) LEFT JOIN Kundeoplysninger ON Q_AlleTerminer_1.kundeid = Kundeoplysninger.kundeid) LEFT JOIN Biler ON Q_AlleTerminer_1.bilid = Biler.bilid
GROUP BY Kundeoplysninger.kundeid, Q_AlleTerminer_1.kundeid, Kundeoplysninger.Fornavn, Kundeoplysninger.Firmanavn, Q_AlleTerminer_1.bilid, Biler.Bilmærke
ORDER BY Måned.nr
PIVOT Måned.nr;

ok, så virker det hvis.
Har også i mellem tiden fundet ud af at der i "de rigtige" data er biler med termin i hver måned.

Yep totalerne virker fint.

Hvad mener du med måned 0?? - der er ikke nogen måned 0.

dsjk

felterne [1 termin], [2 termin], osv i tabellen 'biler', indeholder flere steder værdien 0.
Da disse felter er relateret til 'måned', som IKKE har et recordset med værdien 0, er der lidt kludder i tabelstrukturen. Det behøves/er ikke noget problem, blot man er opmærksom på det.

Glad for det lykkes
Niels

hmmm, såvidt jeg kan se har 1-4 termin enten et tal mellem 1 og 12, eller også står der ikke noget der i. Altså ingen med 0.
Men skidt med det.

Jeg har lige et par spm. mere:
Jeg har sat sum til =sum([1]) osv. ved alle måneder, og det virker fint, men hvis der er en måned uden terminspræmier, kommer der ikke til at stå 0 i sum, den forsvinder helt. Kan jeg få den til at skrive et 0?
JEg har også lavet en total sum på denne måde:
=Sum([1])+Sum([2])+Sum([3])+Sum([4])+Sum([5])+Sum([6])+Sum([7])+Sum([8])+Sum([9])+Sum([10])+Sum([11])+Sum([12])
Kan det gøres lettere? også her går det galt hvis den er en måned uden terminspræmier, så forsvinder total summen også?

dsjk

Nu er jeg ikke ekspert i raporter :(

Du kan lave en foresp mere med udgangspunkt i Q_Rpt, hvor du trækker alle felter ned i foresp.giteret. Felterne 1 - 12 skrives sådan her:
    jan: nz([1];0)
    feb: nz([2];0)
    osv.
Så vil de få værdien 0 istedet for null, og dine beregninger i rapporten vil være OK. Dog skal dine udtryk i rapporten modificeres:
=Sum([jan])+Sum([feb])+ .....

Du kan også vælge at bruge nz() direkte i rapporten: =nz(Sum([1]);0)+nz(Sum([2]);0)+ .....

Jeg kender desværre ikke nogen 'nem' måde, måske kunne du oprette et nyt spm. for at få andre 'eksperter' på banen

Niels

endnu engang tak, det virker.

Jeg vender tilbage med point, når det er testet i access 97.

dsjk

hvis jeg nu runder op til 200, kan du så hjælpe mig engang til?

Jeg har lavet en tilsvarende rapport til at vise ea (erhvervsansvar) præmie betalinger, som virker fint. Men jeg har et lille problem.
Der er nogle eapræmie som som træder i kraft om et par år, jeg vil derfor ikke have dem med i rapporten. Jeg har derfor tilføjet en WHERE (Kundeoplysninger.eaikraft < now) linie i forespørglsen, så den ser således ud:

TRANSFORM Sum(Kundeoplysninger.eapræmie) AS SumOfTerminspræmie
SELECT Kundeoplysninger.kundeid
FROM Kundeoplysninger RIGHT JOIN Måned ON Kundeoplysninger.eahoved = Måned.nr
WHERE (Kundeoplysninger.eaikraft < now)
GROUP BY Kundeoplysninger.kundeid
ORDER BY Måned.nr
PIVOT Måned.nr;

men så får jeg (igen) ikke alle måneder med, jeg får kun de måneder der er betalinger i. Hvis ikke WHERE linien er med, vises alle måneder. Hvad skal laves om?

Og lige en xtra ting: eaikraft skrives som dd-mm-åååå (21-07-2002), og jeg vil gerne have at rapporten/forespørgslen vises dem der træder i kraft i hele 2002 og ikke bare now. Hvis du forstår?

dsjk

udskift di WHERE med denne:

WHERE (((Year([eaikraft]))<=Year(Now()) Or (Year([eaikraft])) Is Null))

Niels

det nærmer sig.

Du får jeg de måneder der er trådt/træder i kraft i 2002 hvilket er juni, juli, september og november) og de mdr. som der ikke er nogle betalinger i (heller ikke i dem som først træder i kraft senere), hvilke er marts, maj august, oktober og december.

Men jeg mangler Januar, februar og april, som, har jeg fundet ud af, er de måneder som eksistere i de kunder som ikke tages med da de først træder i kraft senere end 2002.

dsjk

TRANSFORM Sum(IIf(Year(Now())>=Year([eaikraft]),[eaikraft],Null)) AS SumOfTerminspræmie
SELECT Kundeoplysninger.kundeid
FROM Kundeoplysninger RIGHT JOIN Måned ON Kundeoplysninger.eahoved = Måned.nr
GROUP BY Kundeoplysninger.kundeid
ORDER BY Måned.nr
PIVOT Måned.nr;

uden en Where, men så kommer alle kunder med.

Niels

Du kan også lave din krydstabulering helt uden kriterier.
og derefter lave en ny foresp hvor du udvælger dem du vil ha' med

SELECT Forespørgsel1.*
FROM Forespørgsel1 INNER JOIN Kundeoplysninger ON Forespørgsel1.kundeid = Kundeoplysninger.kundeid
WHERE (((Year([eaikraft]))<=Year(Now())));

hvor Forespørgsel1 er krydstabulering UDEN kriterier.

Niels

Kunne ikke få den øverste til at virke.

Men den sidste løsning virke fint.

Tak for hjælpen.

dsjk

alt ser ud til at virke i access 97.

Så her får du pointene.

dsjk