Populating a Table from two other tables

[terencesweeney]

Hi There

I am creating a questionaire to find out how a participant will grade particular subjects.

I have a list of 20 subjects in a table called Subjects.

I have created a form in which the participant enters their details and these are stored in a Customer table.

I then want to populate a table Customer_Subjects with fields Customer and Subject so that there is a record in the new table for the customer and every subject. ie

John Physics
John Maths
John English

How would I do this?

Also, ideally I would like to be able to do this automatically when the save record button is pressed on my original new customer form. What is the best way for this?

Many Thanks

[PhilOfWalton]

Welcome to Bytes, Terence

Your question is rather vague. You have the information before you, we can only guess what you want

See https://bytes.com/faq.php?faq=how_to...fic_guidelines

Anyway, I'm going to assume that you have done things correctly and have 2 tables

TblCustomers
    CustomerID         AutoNumber     Primary Key
    CustomerFirstName  Text
    CistomerLastName   Text
    ....
    ....

and

TblSubjects
    SubjectID   AutoNumber    Primary Key
    Subject     Text Required - Yes, Allow Zero Length - No 
                     Indexed - Yes(No Duplicates)

Then you need a new table to join the 2 tables above

TblJoinCustomerSubject
    CustomerID   Number Long Joint Primary Key
    SubjectID    Number Long Joint Primary Key
    Grade        Number
 

Set up and enforce relationships between the 3 tables.

Your main for will be for customers and you will have a continuous Subform with Combo Boxes for the various subjects

Here is a botched picture to give you the idea.

The beauty of this method is that although you normally would have the Customer as the main form, and see the subjects he studies on the subform, you can do it the other way round, and have the Subject as the main form and see which customers are studying that subject.

Phil

[terencesweeney]

Thanks Phil... appologies for the vagueness. Will try to improve that next time.

Yes I had setup the table correctly. The outcome I am trying to get is a little different from what you suggested though.

To use your example, say I have the following table
TblSubjects
SubjectID Subject
1 Maths
2 Physics
3 Biology
4 English

If then I create a new customer record in the following table
TblCustomers
CustomerID CustomerFirstName CustomerLastName
1 John Smith

How can I then autopopulate TblJoinCustomerSubject with records so that the new customer has a record for each subject (see below)?

CustomerID SubjectID
1 1
1 2
1 3
1 4


Terence

[PhilOfWalton]

The tables are fine, and the Join table will give you exactly what you have, but you also said you needed a column of Grades as well, and you may also need a column to show the Date the Grade was awarded. They also go into the Join table as mentioned in an earlier post.

In regards to the "Populate" your table automatically.
Now here are some question that need careful thought before answering.

1) Will EVERY Customer ALWAYS be involved with ALL 20 subjects?

2) What happens when you add Subject 21? (I ask this because a database should be designed to be "future proof" - e.g. additional or change of subjects.

3) If the answer to question 1 is "Yes" (and I would be a little surprised if it were), is your intention to add the 20 Subjects automatically as you enter a new Customer?

What you are asking for is comparatively simple to achieve, but you will need to use a bit of VBA code. Are you happy with this?

Phil

[terencesweeney]

Hi Phil

In regard to your questions:
1. Yes
2. If subject 21 is added, existing customers will still have 20 subjects, however future customers will have 21 subjects populated for them.
3. Yes... All subjects to be automatically populated when adding a new customer.

VBA will be fine. I will learn as I go.

Thanks.

[PhilOfWalton]

Hi Terence

OK, I suggest you have a go at creating your Customer Main form and a continuous Subject Subform. Remember, with the Subform, the RecordSource should include all the fields from your Join table + Subject from your TblSubjects. Also it needs setting to Allow Edits - No, Allow Additions - No and Allow Deletions - No.

If you would like me to look at this before dealing with the automatic population of the Join table, let me know.

Phil

[terencesweeney]

Okay. Thanks will make sure that It is set up the way you suggest. No need for me to run it by you at the moment.

Would love if you can let me know how to automatically populate using VBA though.

[PhilOfWalton]

First things First

Phil

[terencesweeney]

"First thing first"?

I appreciatte your help Phil but I am not wanting you to look at the tables and form set up. My tables and forms are different to those described here. I have used your example tables for simplicity.

If you answer the question according to the setup you described, I can then adjust it to my setup.

Will you do this or not?

[zmbd]

(...) To use your example, say I have the following table
TblSubjects
SubjectID Subject

If then I create a new customer record in the following table
TblCustomers
CustomerID CustomerFirstName CustomerLastName
1 John Smith

How can I then autopopulate TblJoinCustomerSubject with records so that the new customer has a record for each subject (see below)?

CustomerID SubjectID
1 1
(...)

Not sure where Phil is going with this at this point; however, I'm sure that he's waiting for you to let us know that your forms are properly setup.

While we're waiting, I would think that a simple update/append query should achieve this action.

Start with a simple select query - this is based on a student database I use - you should be able to get the idea.

For a single student we would start with something like"

SELECT StudentList.PK_StudentList
 , t_Assignment.PK_Assignment
FROM StudentList, t_Assignment
WHERE (((StudentList.PK_StudentList)=1));

This should give you the Cartesian product between the two tables filtered down to one student.

Make sure the query looks right (one record entry for each combination of the student and subject) then one would switch to the action query - here I'll use t_ActiveAssignments:

INSERT INTO t_ActiveAssignments 
  ( FK_StudentList, FK_Assignment )
SELECT StudentList.PK_StudentList
 , t_Assignment.PK_Assignment
FROM StudentList, t_Assignment
WHERE (((StudentList.PK_StudentList)=1));

You can use this as a stored query and have the VBA code set a member of the TempVars Object (Access) collection for the criteria:

INSERT INTO t_ActiveAssignments 
  ( FK_StudentList, FK_Assignment )
SELECT StudentList.PK_StudentList
 , t_Assignment.PK_Assignment
FROM StudentList, t_Assignment
WHERE (((StudentList.PK_StudentList)
   =TempVars![StudentID]));

This way one doesn't have to open a recordset etc...