How do I fill my junction tables? (1 Viewer)

daniel.winson

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
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
 

Attachments

  • msdatabase.zip
    113.4 KB · Views: 178

namliam

The Mailman - AWF VIP
Local time
Today, 16:51
Joined
Aug 11, 2003
Messages
11,696
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

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
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

The Mailman - AWF VIP
Local time
Today, 16:51
Joined
Aug 11, 2003
Messages
11,696
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

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
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.
 

Attachments

  • MS contacts database.zip
    272.4 KB · Views: 212

daniel.winson

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
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

The Mailman - AWF VIP
Local time
Today, 16:51
Joined
Aug 11, 2003
Messages
11,696
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

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
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?
 

Attachments

  • msdatabase.zip
    111.5 KB · Views: 171

namliam

The Mailman - AWF VIP
Local time
Today, 16:51
Joined
Aug 11, 2003
Messages
11,696
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

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
So is there any way, even if it includes changing my design, that I can get the entry method I am after?
 

daniel.winson

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
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
 

Attachments

  • msdatabase.zip
    135.3 KB · Views: 158

namliam

The Mailman - AWF VIP
Local time
Today, 16:51
Joined
Aug 11, 2003
Messages
11,696
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...
 

Attachments

  • msdatabase.zip
    128.7 KB · Views: 596

daniel.winson

Registered User.
Local time
Today, 15:51
Joined
May 28, 2009
Messages
18
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

Member
Local time
Today, 09:51
Joined
Jan 30, 2024
Messages
51
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

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Feb 19, 2002
Messages
42,989
I think it is this form
1707261659887.png



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.
1707261858742.png




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

Member
Local time
Today, 09:51
Joined
Jan 30, 2024
Messages
51
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



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!
 

Users who are viewing this thread

Top Bottom