Select og join typer

try copying your SQL into Access query builder and then by right clicking on the relationships (thin line) choose the JOIN which suits your needs. This will end up as a RIGHT or LEFT OUTER JOIN

It will be something like this

SELECT T_Stamoplysninger.*, T_Postnr.Bynavn, T_Udstillinger.*
FROM (T_Postnr RIGHT JOIN T_Stamoplysninger ON T_Postnr.PostNr = T_Stamoplysninger.PostNr) LEFT JOIN T_Udstillinger ON T_Stamoplysninger.StamID = T_Udstillinger.StamID

Du har ret det er en LEFT JOIN men det løser ikke helt mit problem.

T_Udstillinger er en sub tabel til T_Stamoplysninger så der kan godt være flere records i T_Udstillinger til samme StamID. Derfor er der nu flere records i min forespørgsel end der er i T_Stamoplysninger.

Jeg er faktisk kun interestret i de records fra T_Udstillinger hvor "Årstal" er = 2004 kan jeg forespørge så jeg kun får dem frem ?

Ps. du har svaret på mit første spørgsmål så du får point

Har lige prøvet med denne her

SELECT T_Stamoplysninger.Fornavn, T_Stamoplysninger.Efternavn, T_Stamoplysninger.Adresse1, T_Stamoplysninger.Postnr, T_Postnr.Bynavn, T_Stamoplysninger.StamID, T_Udstillinger.AnmellesesblanketNr, T_Udstillinger.Årstal
FROM (T_Postnr RIGHT JOIN T_Stamoplysninger ON T_Postnr.Postnr = T_Stamoplysninger.Postnr) LEFT JOIN T_Udstillinger ON T_Stamoplysninger.StamID = T_Udstillinger.StamID
WHERE (((T_Udstillinger.Årstal)=2003));

Men der giver kun de records som opfylder WHERE (((T_Udstillinger.Årstal)=2003)) delen og ikke alle andre records.

Hmmm

is it possible for you to send me your database so that I can see the full problem?

eksperten@NOSPAMsanthell.dk

remove NOSPAM

and thanks for the points

25/04-2005 20:29:59
why do you use WHERE T_Udstillinger.Årstal=2003 when you want those for 2004?

Normally if there is mre than one related record (T_Udstillinger) then you WILL get more than one record for each record in T_Stamoplysninger.

You will need to use

SELECT DISTINCT or maybe even SELECT DISTINCTROW but this will only solve your problem if all columns in the duplicate rows are the same. If only one column is different then they are regarded as NOT distinct and therefore you WILL get more than one record. To get around this you have to select only the columns giving you a DISTINCT record

to use DISTINCT or DISTINCTROW right click in the query window and choose either Unique Values or Unique Records in the parameters

I think you need to decide what it is you want to see in the query.

If we take "Lotte Agger" (stamID = 165) then there are 7 records in T_Udstillinger. NONE of these have a date = 2004!!!

If you only want to see those with Årstal 2004 then just add the column to the query and write 2004 in the criteria field.

But is it not possible to STILL have more than one record?

If you want to see information from the table T_Udstillinger in the query then it will NOT be possible to only see one record if the information is different.

It is possible to only see one but which one?

You can use grouping. Press the totals button on the menu. Now you will see "Group By" in the totals row. You can change "Group By" to for example "First" or "Last". Look in th elist to see what choices you have. Then you could use one of these to for example select the first Anmellesesblanketnr from T_Udstillinger.


I hope this helps.

Jo tak Terry

Det hjælper en del men det gør at jeg lige må gennemtænke hvordan jeg så kan opnå det jeg gerne vil.

Mange tak for hjælpen.

thanks
what is it you want to do?