Fomularer med flere tabeller

Jo det kan du godt !!!!

Det kommer an på hvordan du joiner dem.

Et eksempel er en tabel med adresser og postnr. - Hvis man joiner dem kan du kun indsætte en ny adresse hvis du indsætter postnr i tabellen adresser - Hvis du bytter om på felterne \'postnr\' kan du IKKE indsætte poster.

Prøv at kopiere din SQL - forespørgsel herind, så vi kan se hvad det handler om.

Niels

Hvorfor mener du, at man ikke kan opdatere når forespørgslen joiner 2 tabeller?
Det er muligt, at jeg helt misforstår dig - men jeg har da op til mange tabeller i forespørgsler, og det går da fint at opdatere.
En mulig årsag kan være, at relationerne \"vender forkert\" i forhold til hinanden. Jeg mener der var et tilsvarende spørgsmål for ikke så længe siden. Prøver at finde det og vender tilbage.

se her:
www.stigc.dk/db1.mdb

Er sagen ikke at du ønsker at vise at en person kan have flere forskellige kæledyr?
Hvis det er tilfældet, er det langt mere hensigtsmæssigt at lave en underformular til hovedformularen.

Dvs du har en formular med User-oplysninger.
Så har du en anden formular med Extra-oplysningerne (denne bør være fortsløbende eller dataark-format).
I User-formularen i design anbringer du et Underformular-objekt og angiver kildeobjektet til at være lig Extra-formularen.

Derved vil du kunne se at hver user kan have flere extra-ting.

Kan du følge mig?

/Thomas
www.makeiteasy.dk

stigc>First problem is that you need primary keys on each table! Next is to define relationships between the tables. I can see NO relationship betwen the two tables and neither have primary keys. There is a little symbol of a key in the menu. Select the field which is to be the primary key and then press the symbol.
A primary key is like a CPR number it makes that record UNIQUE (only one). Then you may have a foreign key in  a table also. A good example of this is a post number. This pionts to another table where the post number is the primary key.

So I think you need to sit down and define EXACTLY what you want to do. Then difine th etables needed to hold thhe information you are intreested in. For example persons, pets, etc. Once this is in place it makes putting the forms together far easier.

I design på din forespørgsel sikrer du dig bare at din record type = Dynaset (Inconsistent Updates)

Before you do anything with the forms you MUST get the desugn of your tables right! Primary keys and foreign keys.
Once you have done this make a query using the query wizard. Include BOTH tables in the query.
Then use the form wizard to create a form. If your tables are designed correctly it will make a form for your persons and it will also make a subfrom for the pets, and it will include th esub form on th emain form. That is the easiest way, but as I said YOU MUST get your tables in order first!!

Look at it another way.
Can a person have only one pet, say a dog or a cat. If that was the case then you wouldnt need a sub form. You would use a combo box for selecting the pet. If a person could have a number of pets then you have what is called a many to many relationship and you would in fact need three tables. The third (middle) table contains foreign keys to the pets table and to the persons table.

Jeg vil give Terry fuldstændigt ret, inden du begynder på forespørgsler og formularer skal du have styr på dine tabeller og relationer.
Når det er sket, vil ThomasJepsen\'s forslag være den rigtige måde at opbygge din formular på, hvis det er en mange-til-mange join du vil lave.

stigc> I have altered your database, do you have an email address i can send it to?

Hov, hov :) De tabeller og formularer lavede jeg på 2 minutter . Jeg kan godt designe \"rigtige\" tabeller. Men princippet er det samme, selvom der ikke er primærnøgle, autonummering osv.

Tabeller er faktisk kun 1 til 1, man skal ikke kunne hvae flere pets. Alligevel kan man ikke opdatere en JOIN! Har jeg ret?

Jeg VIL IKKE definere nogen relationships, da jeg ikke mener det er nødvendigt, har aldrig brugt det!

har uploadet en ny version, med primærnøgler:
www.stigc.dk/db1.mdb

I thin kyou are missing the point stigc> You need a foreign key in the user table which references th eprimary key in the pets table!

User Table:

Id (primary key)
Name (text)
PetID (foreign key)

PetID references ID in the extra table.

Extra table:
ID (primary key)
Pet (text)

Make an extra field in the user table and select lookup wizard from the type list. You will then be able to make a refrence from this field to the ID in the extra table.
Then make a query using BOTH tables you will be able to update!

But what you should do is make a form for the user table using the form wizard. It will make a form containing a combo box for choosing a pet from!

user table:

no
name


Extra Table:

no
pet



no er det unikke nummer på en bruger derfor kan du slå op i extra tabellen og finde hans Pet!

Det er da almindelig relationel datasbasestruktur!

Det er også bygget til, 1 til mange relation, men jeg har kun brug for en 1 til 1 relation.

Jeg kunen også have kaldt det userid:

user table:

userid
name


Extra Table:

userid
pet

foreign key ligger dermed i Extra tabellen!

OK make a query and join the two tables by the \"no\" field. Then right click on the line and you will see three choices. select the radio button which selects all records from the users table. If you dont do this then you will not see users who have no pets.

Then you can edit as you want (IKKE?)

To join the two fields you just drag the field from one table over to the same field on the other table.

I dont really see what your getting from this though. It isnt normalizing your database at all!
More than one person may have a pet, so it is logical that you would have a table for pets.
DOG, CAT, HORSE etc. etc.

You would have foreign key field in the users table referencing the pets table. This means yoiu only have one field containg the pet \"CAT\" an dthis is in the pets table. In your example, you can have the same word \"CAT\" in many records. This is NOT normalized and is waisting space. You could just as well have written the pet directly into the user table!

I can send you the example I have if you like!

terry>> The tables is an example.

Ok a good example would be like this:

user table:

userid
username
schoolid


school table:

schoolid
address
city

Now download this eaxmple here:

www.stigc.dk/db1.mdb 

Go into design view for the user table. In the school field choose \"lookup wizard\" from the datatype list and following the instruction.
If you now make a query using BOTH tables there will automatically be a thin line (relationship) between the two tables. Add the fields you want from both tables.
If you now open the user table to view the data you can see that the School field ha snow been changed to a combo box where you canalter the field a syou wish, so having a query to alter both tables is not necessary!

Det virker som om du vil have en formular der kan bruges til indtastning af data til begge tabeller. Det kan nemt lade sig gøre, da du har valgt nøgler der bliver autonummereret.
I dette tilfælde bør du lave en relation mellem dine to tabeller (user.schoolid=school.schoolid).
Herefter opretter du en formular vha. wizarden, hvor du tilføjer alle felter fra begge tabeller og ordner resultatet efter user-tabellen.
Når du så indtaster data kan du bladre mellem dine user\'s og enten vælge en eksisterende school eller oprette en ny.

stigc>Is it making sence yet? I can send you an example showing you how to do it!

stigc>Come on!

stigc>Have you gotthe answer you wanted now?

ja mange tak.....

selv tak :o)