Noget ligende igen...

Så har du vel fået det!

Det virker underligt at henvise til et spørgsmål hvor svaret ikke fremgår.

Ja, men jeg kan ikke finde ud af det....

Ergo har jeg brug for hjælp igen...

Zack>I think it would be best to close the question and then make a new one giving an explanation as to what you want. That way everyone gets a chance to answer.

Tjaa.. det har du måske ret i... jeg prøver at beskrive det...

Jeg har en tabel, hvor et dessin har eksempel vis flere farver. For hver af disse farver, er der eksempel vis 9 størrelser. Det jeg ønsker, er at der skal laves en tabel, på grundlag af den originale tabel. Den nye tabel, kan også være en forespørgsel, skal have en post for hver størrelse... altså hvis der er et dessin med to farver, med hver 9 størrelser.. skal det blive til 1 x 2 x 9 = 18 poster i den nye tabel/forespørgsel... hvor det i den originale tabel er to poster... en for hver farve..

Hvordan fremgår størrelserne?

hmm... som hvert sit felt.. horisontalt.... de skal i stedet være vertikalt....

Kolonner kan flyttes rundt ved hjælp af union.

SELECT farve, size1 as SIZE, qnt1 as QUANTITY from yourtable
WHERE qnr1<>0
UNION ALL
SELECT farve, size2 as SIZE, qnt2 as QUANTITY from yourtable
WHERE qnr2<>0
UNION ALL
SELECT farve, size3 as SIZE, qnt3 as QUANTITY from yourtable
WHERE qnr3<>0

Dette kan ikke gøres automatisk, man bliver nødt til at selekte på hver size. Det er på den anden side ligegyldigt hvormange unions den enkelte query har indbygget (der er ikke nogen logisk overgrænse).

Når denne query er gemt i access kan den anvendes i en ny query til at danne nye queries (dvs anvendes som om den var en tabel).

Ja, det er noget i den stil.... men jeg må indrømme at det ligger over min forstand...kan jeg evt. sende DB'en...?

Jeg har ikke tid til at kigge på det før senere idag, så send den til terry, han har den måske allerede.

Nej, jeg vil lige give ham chancen, for selv at spørge efter den.. det virker så dumt, at sende den uopfordret.

Thats right zack :o) I am also VERY busy so I dont have the time at the moment either, but if your prepared to wait then you can send it to me. But then hansk should maybe get the first shot as he has some ideas I can see!

Jeg sender den til jeg begge to nu...

Jeg mangler bare nogle mailadresser...

dktebrsa@europe.lego.com

Hej Zack,

Har du fået løst dit problem. Hvis ikke skal jeg gerne kigge på det.
Du kan evt. sende til dko9517@vip.cybercity.dk

Ja, nu har jeg jo været så smart, ikke at sende det hjem... altså kan jeg ikke sende den før i morgen formiddag, medmindre jeg kan få Terry til at sende en kopi tilbage... sorry

OK first solution:
SELECT dessin, farve, "0" as Size, [0] as Qty FROM [Tex_ProStatus1] WHERE [0] > 0
UNION ALL
SELECT dessin, farve, "1" as Size, [1] as Qty FROM [Tex_ProStatus1]  WHERE [1] > 0
UNION ALL
SELECT dessin, farve, "2" as Size, [2] as Qty FROM [Tex_ProStatus1]  WHERE [2] > 0
UNION ALL
SELECT dessin, farve, "3" as Size, [3] as Qty FROM [Tex_ProStatus1]  WHERE [3] > 0
UNION ALL
SELECT dessin, farve, "4" as Size, [4] as Qty FROM [Tex_ProStatus1]  WHERE [4] > 0
UNION ALL
SELECT dessin, farve, "5" as Size, [5] as Qty FROM [Tex_ProStatus1]  WHERE [5] > 0
UNION ALL
SELECT dessin, farve, "6" as Size, [6] as Qty FROM [Tex_ProStatus1]  WHERE [6] > 0
UNION ALL
SELECT dessin, farve, "7" as Size, [7] as Qty FROM [Tex_ProStatus1]  WHERE [7] > 0
UNION ALL
SELECT dessin, farve, "8" as Size, [8] as Qty FROM [Tex_ProStatus1]  WHERE [8] > 0
UNION ALL
SELECT dessin, farve, "9" as Size, [9] as Qty FROM [Tex_ProStatus1]  WHERE [9] > 0
UNION ALL
SELECT dessin, farve, "10" as Size, [10] as Qty FROM [Tex_ProStatus1]  WHERE [10] > 0
UNION ALL
SELECT dessin, farve, "11" as Size, [11] as Qty FROM [Tex_ProStatus1]  WHERE [11] > 0
UNION ALL SELECT dessin, farve, "12" as Size, [12] as Qty FROM [Tex_ProStatus1]  WHERE [12] > 0
ORDER BY dessin, farve;

I noticed that there were duplicate records (dessin farve) so another solution.
This first query just GROUPS by dessin farve and sums Antal for size.

SELECT Tex_ProStatus1.Dessin, Tex_ProStatus1.Farve, Sum(Tex_ProStatus1.[0]) AS 0, Sum(Tex_ProStatus1.[1]) AS 1, Sum(Tex_ProStatus1.[2]) AS 2, Sum(Tex_ProStatus1.[3]) AS 3, Sum(Tex_ProStatus1.[4]) AS 4, Sum(Tex_ProStatus1.[5]) AS 5, Sum(Tex_ProStatus1.[6]) AS 6, Sum(Tex_ProStatus1.[7]) AS 7, Sum(Tex_ProStatus1.[8]) AS 8, Sum(Tex_ProStatus1.[9]) AS 9, Sum(Tex_ProStatus1.[10]) AS 10, Sum(Tex_ProStatus1.[11]) AS 11, Sum(Tex_ProStatus1.[12]) AS 12
FROM Tex_ProStatus1
GROUP BY Tex_ProStatus1.Dessin, Tex_ProStatus1.Farve;

then the UNION
SELECT dessin, farve, "0" as Size, [0] as Qty FROM [qrySumQty] WHERE [0] > 0
UNION ALL
SELECT dessin, farve, "1" as Size, [1] as Qty FROM [qrySumQty]  WHERE [1] > 0
UNION ALL
SELECT dessin, farve, "2" as Size, [2] as Qty FROM [qrySumQty]  WHERE [2] > 0
UNION ALL
SELECT dessin, farve, "3" as Size, [3] as Qty FROM [qrySumQty]  WHERE [3] > 0
UNION ALL
SELECT dessin, farve, "4" as Size, [4] as Qty FROM [qrySumQty]  WHERE [4] > 0
UNION ALL
SELECT dessin, farve, "5" as Size, [5] as Qty FROM [qrySumQty]  WHERE [5] > 0
UNION ALL
SELECT dessin, farve, "6" as Size, [6] as Qty FROM [qrySumQty]  WHERE [6] > 0
UNION ALL
SELECT dessin, farve, "7" as Size, [7] as Qty FROM [qrySumQty]  WHERE [7] > 0
UNION ALL
SELECT dessin, farve, "8" as Size, [8] as Qty FROM [qrySumQty]  WHERE [8] > 0
UNION ALL
SELECT dessin, farve, "9" as Size, [9] as Qty FROM [qrySumQty]  WHERE [9] > 0
UNION ALL
SELECT dessin, farve, "10" as Size, [10] as Qty FROM [qrySumQty]  WHERE [10] > 0
UNION ALL
SELECT dessin, farve, "11" as Size, [11] as Qty FROM [qrySumQty]  WHERE [11] > 0
UNION ALL SELECT dessin, farve, "12" as Size, [12] as Qty FROM [qrySumQty]  WHERE [12] > 0;

hansk>I had noticed you had not received a mail from zack. Dont worry you will get some points :o)

(once its working)

Ja, det finder vi ud af....

zack have you received my mail?

Øhh.. ikke andet en den i eftermiddag...

Zip den, da mit mail system ikke er vild med *.mdb filer...

nu har du sendt den til den rigtige adresse... jesperknudsen@inet.uni2.dk

I have sent it zipped

Det var da mærkeligt... der kommer ingenting... brug evt. denne adresse: jhk@vangard.dk

I have sent it BOTH places

Just to make sure once agian!

qryShowemall, er det jeg skal bruge. Den vil jeg godt have rettet en ting. Den skal prompte på produktion, og ikke dessin og farve. Produktions nummeret er unikt, og er derfor bedre at bruge.

zack I will look at it first thing tomorrow!
Only "produktion" and NOT dessin OR farve?

Ja, der er kun et produktions nummer, som har et dessin, men den kan godt have flere farver...

Altså den skal prompte for et produktionsnummer, hvorefter den skal vise det dessin der hører dertil. Der kan godt være flere farver der til.

I am not worried about the points.
I just believe that this place is a place of learning and updating skills, not only for the people who submit questions, but also for the people who is taking the time to work out solutions.
In short, if a solution to a problem cannot be described on these pages, then it is not a solution and nobody is going to benefit from it.
I expect that most people will agree in that, but it is so hard to live by a set of principles that we all think that others should live by (not excluding myself).
Hope this is not too philisophical on such a grey and glum day.

Det kan du have ret i hansk, men det kan dog være svært, nogle gange at forstå svarene. Især hvis man ikke lige har styr på alt inden for Access.... det er jo også grunden til, at man spørger om hjælp.... når løsningen er lavet, sidder jeg altid og kigger på det der er lavet, og derved lærer jeg meget...