Populating a junction table - Mmm...

[Renoir]

I have tried the Google thing and searched this site but can’t get this in my head. I want to create a junction table and I don’t think I have a problem with this. However, I can’t seem to understand how to get it populated.
If I have a table called “kids” and a table called “sweets” and I want to know which kids consume what sweets then this is an M to M relationship – right?
So I create a junction table called “consumption”. I create the relationship and then what? How can I populate this thing so I get some information out of it?
I’m getting pissed now!
Cheers.
Renoir

 

[KenHigg]

The junction table would have a kid field and a sweet field. Populate the kid field with data from the kids table and the sweet field from the sweets table.

 

[georgedwilkinson]

If you use a form, put kids (or sweets) in the main form and the junction table in the sub-form. Use sweets (or kids) as the lookup for your field in the sub-form, which represents the junction table.

 

[KenHigg]

Look at this:

 

[Renoir]

Thanks

However, I had some problems relating this to my situation, which is this...
I have a table (TA) and a table (Clients) and all I want to know is how many Clients each TA has and to gain some other intel.
Perhaps it is easier if I attached my poor excuse for a database as explanatory evidence of my incompetence.
I am also struggling with displaying fields in my forms which are coming from a table that have a lookup happening.
Any comments will be appreciated (don’t be too rude).
Cheers,
Renoir

 

[KenHigg]

Sorry, I don't have 2007.

 

[georgedwilkinson]

It looks like many of your relationships are 1:1. This could cause you some problems.

Table Tax Agent Clients
Client Number
Client Name
Agent Number
Tax Agent Business Name

Each of these fields is a 1:1 with the FK's source table. They should be "many" on the Tax Agent Clients side.

After you've resolved that, go back to the example Ken provided earlier. Figure out how you want to manage your data. I typically create a Master Form for each "way" I want to manage the data (in your case by Client or Tax Agent). Then I create a subform based on the junction table (in your case, Tax Agent Clients). If this is not clear, let me know and I'll modify your database with the changes I'm proposing.

 

[Renoir]

Greetings again,
I have implemented your suggestion and rationalised the relationships.
I have also changed my priorities in perhaps learning how to craw before walking. Within this scope I have decided that at this stage in my development and understanding of Access, I will only allow 1 Tax Agent per Client. This is true in all but exceptional circumstances in any case.
My main tables are Tax Agents, Clients, Staff and Cases and I cannot see a many-to-many relationship between them.
In addition to being able to obtain some quantitative information from the 4 main tables, I basically would like to be able to know (in a nutshell):
• which clients are with what Tax Agent
• results of audit activities that I will be able to trace to clients, Tax Agents and Staff

I have 1 specific question.
My main tables get some of their information from looking data up in smaller tables. Ie, the Staff table has a field called “Staff Access Level”. In turn, this field is populated by the table “Staff Access”.“Staff Access Level”. However, in the form "Staff" the text box shows “Access ID” rather than the actual category such as “Administrator”
Is this fixable?
Thanks in advance.

 

[georgedwilkinson]

Yes. Use a combo-box. In the "Row Source" property, use a query with both fields (the ID and the Text). Make sure "Bound Column" is 1. Set "Column Count" to 2 and "Column Widths" to 0";1" (experiment with this value once you have it functional).