17. marts 2004 - 19:02Der er
18 kommentarer og 1 løsning
Insert max+1 (med et twist)
Jeg har en tabel ved navn Kunde: ID InstitutionID Aar Reference
Jeg vil så lave et query som kan oprette en ny Kunde hvor Institution og Aar er bestemt af mig via en form og da ID styres af autummerering skal Reference bestemmes ud fra Max(Reference) - men hvor det kun er max af de rækker med samme InstitutionID og Aar. Altså vil hver Institution for hvert Aar have ca. 100 Referencenumre - således at at der vil være mange med Reference nr. 1, men kun én per Insitution pr. år.
Ej ok, kort fortalt her er hvad jeg vil bede Access om, som et eksempel: Find det højeste Reference nr. hvor Aar = 2004 og Institution = 1. Dette ville jeg jo fint kunne klare alene, men når det er et insert som skal være max+1 hvordan skriver jeg så det hele i ét query - hvordan hiver jeg evt. resultatet frem fra et tidligere query hvis jeg valgte at dele det op i to queries: 1) select max som beskrevet ovf. 2) Insert max + 1.
Denne side indeholder artikler med forskellige perspektiver på Identity & Access Management i private og offentlige organisationer. Artiklerne behandler aktuelle IAM-emner og leveres af producenter, rådgivere og implementeringspartnere.
because I can end up using ages trying to explain, and I have no idea as to your previous knowledge of Access. But here goes!
You need to make an APPEND (INSERT) query. Start by making a new query. When you are asked to choose a table press cancel. Now in the menu choose Query+Append Query. Now choose the table yo want to append to. Now in the "field" field for the frist column right click and choose build. Now you need to use th eexpression builder to find the form/field where you are entering Aar and Institution. Choose the field you want to take the value from. Now in the "Append To" field of the first column choose the field in the table you are appending to. Repeat this for the other field.
Now the last field (Reference) You need to make a sub select to select this value and again this needs to reference the fields on your form. This is only an example as I dont have your data.
In the "field" field of the last colum.
Ref:(SELECT Max(reference) FROM Kunde WHERE Aar = [Forms!NameOfForm].[NameOfField] AND Institution = [Forms!NameOfForm].[NameOfField] )+1
And finally you need to set the "Append To" field!
OK,thanks for the answer, but I'm afraid it's reporting the same error my original code with a subselect did: "Reserved error (-3025); there is no message for this error." Great error message, eh? Do you have any idea what this means. I know it's not a data type problem. This whole query works just fine if I manually write the max+1 into the query. So all I was thinking was that sub selects aren't supported like that, but that can't be it either, it seems :(
My real name is Lennart, so for some reason some ppl just call me Lenton and I kinda like it ;)
Ah! Just thinking about it, Access is VERY BAD at sub selects in UPDATE and INSERT queries! Try changing your sub select to a DMax() function. You can use the expression builder to help you with that.
Jeg tror/ved du vil få problemer med autonummereringsfunktionen, så jeg ville nok arbejde på en ekstra tabel til styring af denne individuelle nummerering pr. Institution, hvorfra du så henter seneste værdi, tillægger 1 og gemmer denne i begge tabeller ... bare et forslag ... ;)
jensen363>why do you think that? Obviously if we are in a busy multiuser environment then this could give problems, but then using an extra table would not help as far as I can see!
Hah, I replied in the wrong thread, well I'm back: Ok, how exactly is the syntax for DMax? This is what I tried: DMax("Reference";"Kunde";"Institution=1")
I haven't got a clue about the "Criteria" syntax. -- Btw I don't see any problems concerning autonumbering
Yes it did! It now works when there is at least one "Kunde" with the selected "Institution" and "Aar". So Creating the first "Kunde" in a new year for an "Institution" will not work with this query - how would I go about this? Obviously it's because DMax is reporting NULL instead of 0...
I know :) Well, latest news is that Nz(dmax(), 0) is not working, it's reporting a syntax error. When I replace the comma with a semicolon it does not complain, but it still doesn't work. Same behaviour as before.
Make a SELECT using the same dmax and see what it is returning!
Well I'm off to relax a bit before going to my bed. If you still cant get it working then I think there is only one solution :o) but I will first have time when I get home form work tomorrow.
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.
