showing all related records in form ?

yeah … first i celebrate entry 1200 :slight_smile: second my problem: i’ve some tables (surprise!) an i want to build a form in which you can search in the whole entityset of all tables cause they are related with n:n relations … my form should show every record with every possible relation to this record so for a 1 : 5 relation between two tables it should show me 5 records … in every record is shown the same information of the 1-table and another information out of the 5-table so i see every possible relation … how can i build such a formula and how to access on it in the best way (speed e.g.) ??? thx *** quack ***

I’m not sure I understand your problem: 1) n:n relations simply do not exist in forms as you only have ONE record on the form at any given time (even if it’s a table form). 2) You always can add a subform to your mainform and linke the sub-table via the primary key or foreign key of the main table. Examples: Link via primary key: Customer and customer ledger-entries Link via foreign key: Adresses and ZIP-codes 3) One fundamental rule of software engineering states that you must never have an n:n relationship between two tables!!! In case such a relationship occurs (like between customers and items) you always must have a table in between which provides a 1:n relationship to each of the related tables. Example: You might have a customer-item-price table which provides a 1:n relationship to customer and a 1:n relationship to items where customerNo. and ItemNo. are the primary key of this table. It might be helpful if you describe your problem more precisely as I assume that there might be some inconsistencies in your table definitions. With best regards from Switzerland Marcus Fabian

ok … i didn’t use the relation definition in the right way … waht i wanted to say is : … better an example :slight_smile: … my maintable has something about 2000 records/articles … an article can be produced by more than one producer so in another table are records which contain the nr of the article and an id of one producer … in the same way i have two other tables related which contains a main article and extras for this article … so it looks so (simply) extras<- n:1-> basearticle<-n:1->article from others<-1:n->producer you see their are 1:n and not n:n … sorry … i wrote it cause for example one producer can also make more than one article … no i want to build a search-from which shows me every possible combination of this … how to build it ? using temporary tables ? … *** quack ***