1. kolonne er varenr. 2. kolonne er dato for køb 3. kolonne er antal stk. købt 4. kolonne er en løbende sum af kolonne 3 5. kolonne er en formel der siger, hvis kolonne 4 minus kolonne 6(lager) mindre end 0 = -1 eller 1, hvis større end 0.
Jeg vil gerne have, at Access via en forespørgsel ender ud med at vise, at de 28 der ligger på lager er købt med 14 stk. den 20. juni og 14 stk. 31. maj, således jeg får en tabel med dato for køb af lagerbeholdningen med henblik på vurdering af alderen på lageret.
Men men men hvordan ?? Kan jeg eventuelt lave det hele i en forespørgsel, eller må jeg benytte hjæpeforespørgsler?
Bemærk, at tabellen i Access indeholder titusindevis af liner fordelt på 18.000 forskellige varenumre, hvor ovenstående kun indholder et varenummer.
Min SQL vedr. Forespørgsel1 af ovenstående ser således ud:
SELECT Forespørgsel2.Varenr_, Forespørgsel2.Bogføringsdato, Forespørgsel2.SumOfAntal, (SELECT SUM(SumOfAntal) FROM Forespørgsel2 AS T WHERE T.bogføringsdato >= Forespørgsel2.Bogføringsdato) AS Akk, IIf((SELECT SUM(SumOfAntal) FROM Forespørgsel2 AS T WHERE T.bogføringsdato >= Forespørgsel2.Bogføringsdato)-[lager]<0,-1,1) AS Akk1, Vare.Lager FROM Forespørgsel2 INNER JOIN Vare ON Forespørgsel2.Varenr_ = Vare.Nummer ORDER BY Forespørgsel2.Bogføringsdato DESC;
Håber virkelig I kan hjælpe, da jeg også har prøvet timevis via Excel.
Teknologi, AI og forretning er i centrum på Computerworlds Cloud og AI Festival i København d. 18. og 19. september. Se hele programmet for den store konference om strategisk brug af Cloud og AI på: www.cloud-festival.dk
The table shows all buyings divided by date so last buying was on June 20, 2007 with 14 stk. and secound last buying was on May 31, 2007 with 58 stk. so if I have 28 on stock 14 must be from June 20, 2007 and the last 14 from May 31, 2007. The princip is First In First Out so the last buying is the one in stock.
Can you explain in more detail please? First I dont see what you 28 in stock has to do with your running total (Akk).
You have 28 in stock for all dates, how can that be if ther are a number of "køb"? If you sell something your stock decreases, and if you buy then it increases.
The question for me is "How old is my stock?" In one table I have my total stock by pieces and in another table I have all entries "sold, bought, downwriting etc." So if you sum all entries for one item it would be equal to the total stock. The item GU-GG 2965/S has a stock of 28 today. The query a bow shows all buying of that item which the company have bought. In total we have bought 138 (sum of the query) It dos not matter how many we have sold because I know we have 28 in stock out of the total buy of 138 pieces. I also know that I use the First In First Out princip.
I just thought that I need a running total to help me but perhaps that is not necessery?.
I was thinking of using Akk1 in the query "Forespørgsel1" so when is <0 then return SumOfAntal but it dos not work in line two, it will only give me the first line with 14 pieces so I miss the last 14 bought in May 07 !!!!
Ok, I think I understand what your after and I dont think you can do this in a query. But I would need to play around with your queries a bit to be sure.
Is it possibe for you to send an Access table with some test data (GU-GG 2965/S for example) an dthe two queries you are using so I can reproduce the examples you show?
I'm off out for the evening but I should have a bit of time tommorrow.
I have made my Access database and in Excel made the correct filter etc. so I have a solution now.
Perhaps it could be done more easialy in Access - I do not know.
Thank you for your interest and participation.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
