Lookup field in a many to many relationship

[Kat-ness]

I have a many to many relationship between two tables in my database I'm creating.



Application
App #, pk

Contact
Contact ID, pk

Contact type
Contact Type ID, pk
App #, fk
contact Id, fk

I am using Contact Type as the junction table and also have fields in the contact type table that will be relevant to both application and contact table (thanks to suggestions from this forum. Thanks Jane.). As in...one application may have multiple contacts (that could appear on a different contact), and the Contact type for one contact may be different on one application from another for the same individual.

Firstly, have I set the relationships up correctly for this? And secondly, how do I set up my lookup fields (or should I avoid that altogether?). On my form I want to have multiple tabs and I want there to be a separate contacts tab. Since there will be duplicates I'd like to have access automatically look up the information for my contact tab from the contact table but I've heard and seen in places that lookup fields can cause problems later on.

Am I better off just adding the records one at a time, even if there are duplicates? or is there some other way to do this? Which table would I put the lookup field in in order to pull the data from the contact table to the contact tab?

[alansidman]

You don't want to have your lookups in your table. Create combo boxes in your forms

http://access.mvps.org/access/lookupfields.htm

Alan

[Kat-ness]

Thanks Alansidman.

Is it possible to set up combo boxes on my form when the table isn't using combo boxes? Can you please explain how this would work a little further or do you know of a link that will?

[orange]

Try free video tutorial here
http://www.datapigtechnologies.com/f...combobox1.html

[Kat-ness]

Thanks Orange!

[orange]

You are welcome. Glad to help.

Lot's of excellent videos at that site.