How do I fill my junction tables?

[daniel.winson]

Hi everyone, I am a beginner Access user and have to make a 'simple' contacts database for work. Basically it is a database for looking up support organisations listed by area supported and what they support.

My table design at this stage (simplified) is:

ORGANISATION
organisationID (PK)
organisationName
organisationPhone

ORGANISATIONLOCATION
organisationLocationID (PK)
organisationID (FK)
locationID (FK)

LOCATION
locationID (PK)
locationName
country

ORGANISATIONSERVICES
organisationServicesID (PK)
organisationID (FK)
servicesID (FK)

SERVICES
servicesID (PK)
serviceName
serviceDescription


What I am trying to do is create a form for adding new organisations to the database, ideally a single page form will populate the ORGANISATION table as well as the two junction tables ORGANISATIONLOCATION & ORGANISATIONSERVICES. Each organisation will work in a range of locations and will offer a range of services, but thankfully the same services are offered accross all locations covered by any organisation.

I have looked into my options and have had a play with sub forms, continuous subforms, combo boxes and list boxes... I'd really like to have check boxes but this looks very difficult without trashing the database design and using a lot of yes/no fields which I would rather avoid.

Whatever design I end up using will need to have a single page with a simple interface to choose from a dozen locations and about 20 services.

I've attached a copy of the database along with my attempt at a form - doesn't work and layout isn't finished but it should help in understanding what I am trying to do. Any hints, suggestions or templates would be most welcome. Thanks in advance.

Dan

 

[namliam]

Each Junction table is simply a subform... this then gets auto populated by access as you are doing data entry...

its magic

Good luck!

 

[daniel.winson]

That's what I thought, but I need to add multiple records in each subform and this seems to be when I run into problems... maybe I am just doing something simple incorrectly.

 

[namliam]

You dont have any subforms containing any of your junction tables though??
Mainform > Junction form > Subform you need 3 forms stacked to have it working.

 

[daniel.winson]

Sorry, I thought I was being clever saving my database as Access 2003 format because not everyone has 2007 - but it deleted my form! I've attached a copy in 2007 format that has the form still in there. Thanks for your help.

 

[namliam]

Dont have 2007

 

[daniel.winson]

Like I said I am new to this... after a bit of playing I found that the form wasn't gone in the 2003 version, just not showing up in the navigation pane... I thought that what I had was a form 'organisations' and two sub forms 'organisationLocation Subform' and 'orgnaisationServices subform1'... have I set up these subforms correctly? (I know they don't look like subforms because I have changed them from data sheet view to single form view)... can this work for my purposes?

 

[namliam]

Yes the 3 forms are there... But you need 5, one form for each table....
Then have them "route" Mainform > Junction form > Subform
Where your junction form doesnt actually do anything and is basicaly hidden from the user.... but performs a task non the less.

 

[daniel.winson]

OK, that kind of makes sense but I don't fully understand how to implement, I've attached a very rough attempt at what I think you mean... am I on the right track? Thanks again for your assistance, it is good to know there is a solution out there and that I just have to learn how to implement it... have you got any example databases I could look at to see how it is done?

 

[namliam]

This database alos only contains these forms:
organisations
organisationLocation Subform
organisationServices subform1

While I have your attention, first this... NAMING CONVENTION... Use it!
Tables start with tbl, queries qry, frm for forms etc...
sub for subforms possibly... this will make things much more understandable in your DB (in the long run)

Now you need a main form (fmrOrganisations)
Then a junction form (junOrganisationLocation)
Then a subform (sublocation)

Your problem is not this though... Your problem is that your are not setup as you say you are.... Services is NOT a sub of OrgServices... It is what is called a lookup...

You cannot use a multi select box in cases like this...

 

[daniel.winson]

So is there any way, even if it includes changing my design, that I can get the entry method I am after?

 

[daniel.winson]

I have fixed up my naming convention, thanks for the advise.

I've attached a new attempt at 'nesting' the forms within each other, but don't understand how to set it up in a way that will allow me to add an organisation + populate the junction table with all locations/services.

Thanks for your patience.

Dan

 

[namliam]

Yes, I think so...

I normally dont do this, but its beautifull weather today and I feel good.... so...
See if you like attached...

I created 2 forms in your database
frmnamliaM
subnamliaM

Open frmnamliaM and see if that does what you are looking for...

 

[daniel.winson]

like it... I love it.. thanks heaps... I'll have a play with it and will hopefully be able to make it work. Cheers

Dan

 

[Missomissou]

My org's computer won't let me open the above attachment. Anyway someone can describe in words (or screenshots) what happened here to make @daniel.winson so happy? I'm struggling with something similar.

 

[Pat Hartman]

I think it is this form

Here's one from my attached sample of m-m forms. The sample also includes a different way of viewing the data using a list with a popup if that makes more sense. Both variations are valid and can be used from left to right or right to left depending on your perspective.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

If you can't download either of these examples at work, then you need to do them from home or get special dispensation for someone to download them at work for you so you can use them to understand how m-m relationships work.

Remember - when you download files from the internet. It is possible that you just need to put the file into a trusted folder of just trust it.

 

[Missomissou]

I think it is this form
View attachment 112441


Here's one from my attached sample of m-m forms. The sample also includes a different way of viewing the data using a list with a popup if that makes more sense. Both variations are valid and can be used from left to right or right to left depending on your perspective.
View attachment 112442

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

If you can't download either of these examples at work, then you need to do them from home or get special dispensation for someone to download them at work for you so you can use them to understand how m-m relationships work.

Remember - when you download files from the internet. It is possible that you just need to put the file into a trusted folder of just trust it.

Thank you so much! I have a very long thread on this elsewhere on the site but I think the information I need may be embedded within this thread/database, so I will take a look on my home computer later tonight (end of the workday here). Thank you again!