25. marts 2003 - 17:05Der er
25 kommentarer og 1 løsning
3 spg: Relativ linktable DB-path/Foranstillet nuller /Lookup....
Det her i virkeligheden 3 spørgsmål, der er 30 points for hvert :)
Jeg er igang med et projekt, en journalhåndterings system i Access.
1: Hvordan henter jeg data fra en anden tabel via et relationship? Jeg har 3 tabeller som er relavante i denne sammenhæng: [Sager], [Abonnenter] og [Institutioner]. Sammenhængen er at hver abonnent har en eller flere institutioner unders sig. På hver institution er angivet om regning for netop denne institution, skal sendes til abonnenten eller institutionen. På hver record i [Sager] er der angivet en Institution, og udfra dette relationship skal jeg på min Sags-form (eller en rapport, er vidst ligemeget?) hente adressen på enten Institutionen eller Abonennten som regningsadresse. Regningsadresse skal ikke registreres udover på abonnent/institution men være et lookup som foretages når formen/rapporten åbnes.
2: Systemet er delt op i 2 Access databaser, en ren frontend og en ren data - dette for at jeg kan opdatere GUI op sende ud til dem, uden at de skal til at eksportere nuværende data og importere dem i deres nye system. Mit problem er at Access link til data-databasen er en absolut sti istedet for en relativ! :( Jeg har IKKE systemet liggende på C-drevet på den maskine jeg udvikler på, hvilket betyder at når jeg kopierer den til deres maskine, så skal jeg genoprette tablelinks, meget ufedt! Er det ikke muligt at angive en relativ sti, eller på anden måde sørge for at dette ikke sker? Jeg havde forestillet at hvis man linker til en DB i samme mappe, at Access så selv finder ud af det, hvis begge filer flyttes til en anden mappe?!
3: Sagsnummer er bygget op af <år>.<fortløbende nummer> - fx. 03.0123. Dette repræsentere sag nummer 123 i år 2003. Hvordan sørger jeg for at dette fungere? Jeg har en knap til at oprette en ny sag, hvor der i forvejen bliver kørt noget kode. Hvordan sørger jeg smartest for at "sagsnummer" automatisk genereres ud for de kriterier jeg har nævnt? Måske noget med at have en "system-tabel" i ACcess, hvor jeg registrere hvilket sagsnummer basen er nået til eller? Og i givet fald, hvordan konverterer jeg et nummer fx. fra en tabel over system-variable til text og "sætter nuller foran"?
Med version 7 af TeamShare tager Lector næste skridt og bygger en platform for AI-agenter, der i højere grad kan følge medarbejderen gennem hele arbejdsprocessen.
1: Hvordan henter jeg data fra en anden tabel via et relationship? You make a query adding the necessary tables. Then IF youhave designed your database correctly you will see thin lines JOINing the tables. So it should be just a case of selecting the fields you want to see. If there are NO thin lines JOINing the tables then drag and drop the primary key field on to the foreign key field in the related table. You can then right click the thin line to modify the JOIN type from an INNER to an OUTER (left or right).
Well the easiest solution is to move the backend to (for example) your C: drive so that the path will be the same for all users. OR do this just before you send the dB out. What I dont understand is IF the backend is on a server and the same backend is used for ALL users (also you when developing) then there shoul dbe no need to re-link.
That was an asnwer to 2: This is an answer to 3: The only way you can dio this IS via code (IF you must have this format for Sagsnummer ) You have a system table which starts at 1 and you read this to get the next number. In the form where you need to create "Sagsnummer" you place this in the forms BEFORE UPDATE event
If me.isnewrecord Then me.Sagsnummer = Format(Year(date()), "00") & Format(Dlookup("NextSaysNummer", "YourSysTable"), "0000")
Docmd.runsql "UPDATE YourSysTable SET NextSaysNummer = NextSaysNummer +1"
end if
NOTE: THis is NOT tested and you need to alter the table/field names accordingly
Each 'Sag' has a billing address, which is determined by the 'Institution' selected
(A). Each 'Institution' has two billing address options (B), either the
'Institution' itself or the 'Abonnent' tied to the 'Institution'. Depending on the
value of (B), the address information from either the 'Institution' (C) og the
'Abonnent' (D) should be used on the 'Sager' form's textfields with the billing
address (E).
Is this possible? - The solution must also be compatible with reports, so that it
can be used to print out a billing invoice.
Hope you understand what I'm trying to formulate!? :)
2: "Well the easiest solution is to move the backend to (for example) your C: drive so
that the path will be the same for all users. OR do this just before you send the
dB out. What I dont understand is IF the backend is on a server and the same backend is
used for ALL users (also you when developing) then there shoul dbe no need to
re-link."
- That's kind of what I'm doing now, but in my dev. environment, the DB resides on my server. But at my customer, the DB resides on one local machine. I would just like to avoid relinking DB when switchin from dev. to production environment, is it really not possible to link DB's using relative link?
1:Yes its possible :o) You make a query and add the fields you want other than those to the billing address. Now you have to make one calculated field for each field in the billing address. You do this by entering "something" like this in an empty field in the query
Explanation: N will be the name of the column (field) in the query, I think you can use Navn here without any problem. iif() is a function like IF. IF [RegningTil]=1 then I use [Institutioner]![Navn] otherwise I use [Abonnenter]![Navn]
You need to do this for all fields in the address.
2: When you say relative sti I take it you mean ./SomedB.mdb or something like that. No as far as I know you cant. There are a number of ways around this problem but WILL require a bit of programming.
1: Looking at this now, just to get things straight: ControlSource equals "iif([Sager]![RegningTil]='institution';[Institutioner]![Navn];[Abonnenter]![Navn])" - should be ok?
2: Yes something like that, or in this case just "data.mdb" :) - When you say 'a bit of programming', are we then talking OS-size code, ora few lines? :)
3: Nothing happens, this is on my form, BeforeUpdate:
If Me.NewRecord Then Me.TextSagsnr = Format(Year(Date), "00") & Format(DLookup("NextSagsnr", "System"), "0000") DoCmd.RunSQL "UPDATE System SET NextSagsnr = NextSagsnr +1" End If
But, if I create a button and assign the same piece of code it works fine! But, I'm not sure it's such a big problem, beacause I can just assign the code to the button I use for creating a new 'Sag' - I'll just try this :)
1: With that line in ControlSource, i get "#Name?" - shouldn't I somehow specify from which record of Institution or Abonnent i should use Navn (or any other address information)?
1: This works excellent!! - Thanx Terry, gonna start worship you soon! ;) 2: Think i found it, sound a bit like http://support.microsoft.com/default.aspx?scid=kb;en-us;210062 - but I'll stay completely out of it, haven't got a clue what they talk about, so this issue is also closed now! :) 3: Looking further into this now :)
1: Hmm, geez a little thing more :) - One of the fields needed in the mailmerge, using the query is selected from another table. Problem is that the value stored in my DB is the ID in the other table, not the description field. Which means, that instead of getting a string i get the ID value from the other table. How do i get the corresponding text to that ID?
3: It seem to work just fine, but there are a couple of things.
First of all, to comply with their current datastructure, the format must be YY.0000 - but i get YYYY.0000, kind'a weird!? :)
Second, what is the smartest way to handle a reset of the "nextSagsNr" when the year is changed? I was toying in my head with something like "current year" in my systable, and when i click new record, it checks if current year is different than actual year, and if so: reset the counter and set "actualyear" to the computers actual year - how does that sound? :)
Hope you can keep up with your patience, I'm kind of a newbee to this.
Lastly, how come you always write in english, it seems that you understand danish just fine - are you from US/UK/?
3: Apart from the last issues, can I avoid the "You are about to update 1 row(s)" security warning, else i have to find another solution to increment the counter
1:In the query you add the other table. There may already be a thin line between the ID in both tables if not drag and drop the ID in one table onto the ID in the other table. You have now made an INNER JOIN. Now drag the description field into th equery :o)
3: Yes I have tried that Format(Year(Date), "00") Here is another idea Right(Year(date), 2)
3a: resetting nextSagsNr. You can have in your sys table the year which you are working with (2003). Then when you start the application or at some other time, you read the value and if it is differnt from Year(Date) you reset the nextSagsNr and dont forget to reset the year too :o)
3b: Docmd.setwarnings False then make the updates then docmd.setwarnings True
4: This must be worth extra points. I'm from the UK, only been in DK for 20 years so I'm not so good at writing danish yet :o)
1: It works for one of the fields! :) - Though, I have two fields which lookup values in the same table, Receiver (of the case) and Assignee (of the case), when i add the field [Ansatte]![Navn], it fills in the last value i used, which would be Assignee. - This is the only thing missing now, my patient friend! :) :)
2: Not exactly less scary! ;) - I'll just relink the table on my C-drive before sending out the DB. Tnx anyway! :)
3: Right(... works like a champ!! :) a: Works quite allright now! :) - One thing, how do i check if the sagsnr i just generated already exists? Naturally, sagsnr is indexed and cannot contain duplicates. If the computerclock screws up, or something else, and the same year is used for the second time, the counter would be reset, and i will generate a sagsnr that already exist. But instead of the odd error message, i'd like to be able to run some code (eg. simply displaying an alertbox). How to check if this sagsnr already exist? b: Also perfect!!
1:I'm not quite sure I understand what you mean here! 2: :o) 3: a: You can do this in a number of ways. IF your "sagsnr" is a unique key then IF it already exists and you will get an error. OR you can use If Dcount("*", "NameOfTable", "sagsnr = '" & TheNumberYouWantToCreate & "'") > 0 Then number already exists. b: :o)
1: I have one table containing employees, i lookup values in this table from to different fields in Sager. One is for the receiver of the call, and one is the person the Sag is assigned to. So, if i just add "Navn" from "Ansatte" to my query, I only get the one whom the sag is assigned to. While Exp.dk was down, I found out how to solve this! :) - I just add the table again (sort of a copy!?) to the query, and making the innerjoins between Ansatte to Receiver, and between Ansatte to AssignedTo :)
3A: Aaaah, great! :)
4: No prob
Thanx a lot terry, you deserved every little point! - Gonna replace my Vishnu with you! :D
If you're listening, have a look at http://www.eksperten.dk/spm/389768 - you helped me a great deal last time, and I think this is no prob. for a pro like you! :)
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.
