A few ?simple? questions

[d0h]

Hello everyone,

I'm currently learning the basics of Access, and I have a project which requires me to set up a database for a financial institution which deals with unit trusts and customers.

Each customer has a certain risk tolerance, and each unit trust has a risk rating, both on the scale of 1-10. I believe this is a many-to-many relationship

I've made junction table (called Recommendations) linking these 2 tables, and i've made a query to list out all the relevant entries where a unit trust is linked to a customer with the correct risk tolerance level.

here's my first question. how do I transfer the data from the query to the Recommendations table? I've made 2 queries, to Delete the entire table, and another to Append all the query results into the table, but i believe this method is inefficient especially for huge companies with many clients and many unit trusts. Is there some other way to populate the junction table?

Next question.. in the event that a new client is obtained, or a bank releases a new unit trust, how do i update the junction table? i.e. If the customer has a risk tolerance of 5, and there are 20 unit trusts with risk rating 5 and below, i'll need 20 new entries into the junction table.. this cant be done manually right?!?
Are macros needed to link forms to tables? i've been trying to find info about this all day and i'm still stuck

any help would be GREATLY appreciated!
thanks in advance

 

[DCrake]

Is this a real life exercise or a home work project of school/college/Uni?

 

[d0h]

it's a college homework project

 

[Niroth]

I would think it's better for you to use a query to draw out that data whenever needed, instead of creating another table. But I don't fully understand your problem, why you wanted to create another table and how you want to display the data. BTW, using queries would resolve your second problem.

If you insist on adding data to a new table, you can call up an append query whenever a customer is added, and filtering for that new client's name (I used to do this and it sucked BTW). As for the new trust, you can create a new query that shows only the new unit trust, create a relationship with the customer table similar to what you used to append your table, so you now have all the customers who has the same risk tolerance as the unit trust rating, and append that to the table. I can't emphasis enough how bad this method is, it created a lot of problem later on when I tried to reconcile all my various data.

Here's an example of query by form in case you don't know already:
http://support.microsoft.com/kb/304428

 

[d0h]

ahh i think i've been blindly following my teacher's instructions :s

from my lab notes, i understand that many-to-many relationships are joined by a junction table..which was why i included it in the first place.. perhaps i ought to just delete that table

anyway, the point of this "recommendations" table is such that a financial advisor can refer to it and recommend unit trusts that fit his risk appetite.. but it seems that a query will serve the same purpose..

i'll ask my teacher again this thursday.. thanks for your help