Complex Form Question - 2 Tables, Different Fields

[nichojo]

I need to create a report that appends 2 fields in Table 2 based on information that is contained in Table 1. In practice,



Table 1 contains the fields: ID, Course, Name.
Table 2 contains the fields: ID, Course, Completed and Date.

The problem lies in the fact that while there can be a relationship made between the two tables, the ID and Course will not be populated until completed is flagged and a date is populated in the second table.

The end game form should have every row listed from Table 1 AND include a check box and date field that, when checked and dated, will append this field to Table 2.

Can this be done semi-efficiently?

[NTC]

when one makes the query that is to be the record source for the report; and you make that relationship line between the 2 tables; it is going to default with a regular relationship line. This means it requires records in both tables. (the resulting record set when you run the table only has records for those records where there is data in both tables)

put the tip of your arrow cursor on that line and click so it thickens , right click and select join properties....and then select an option that will result in a line that has an arrow on it.

this will make all the records of the core table always show - - and only those records of the second table for which there is data. It sounds like this is what you want.

hope this helps.

[nichojo]

Thanks, I think that is exactly what I need; or at least a great foundation.

By the way, I caught the word Cahaba in your signature and couldn't help but to check out your web site to see if it was what I was thinking of. I grew up in Hoover and went to Auburn. Small world.

[nichojo]

So this takes care of part one of the question - listing all values in the table.

Part 2 is still open. I need to append based on if the check box is ticked. I fear this is slightly more complicated than Part 1 and hope this is somewhat easily achieved.

[NTC]

Greetings from Birmingham and the deep (hot & humid) south. Where are you now?

I will launch into db theory 101 to tell you that one doesn't move data from 1 table to another. This probably isn't the answer you want however. If your joined tables, in the query, result in the 'view' or record set that is accurate - - then that always exists, and there is no reason to move data from one table to another. Simply use this query any time you want to see it this way.

On the otherhand; to move data into records that already exist, then what you want is an UpdateQuery. An AppendQuery is for the creation of new records into a table. Both topics are covered in any Access textbook. You can make a new Append or Update query, sourced on this first query. However I don't fully understand what your goal is - and so this advice is generic.

[nichojo]

Thanks for the quick response. I currently live in Atlanta (couldn't get out of the south) but am doing the consulting thing and working in Ohio.

To clarify the goal- Table A contains a person's personal details while Table B contains their class enrollment history (IF THEY HAVE ONE). That's the catch. All persons will now be listed in the form because they exist in Table A, but unfortunately since we are starting from scratch there is no data in Table B. So, in theory and referencing my first post, when I check completed in the form, it should append the ID, Course, Completed and Date information into Table B.

Hopefully that made sense. I have a software engineering background but unfortunately, I'm pretty novice when it comes to DB's.

[NTC]

My advice is this: Table A is defacto the parent table. A person exists only 1 time.

Table B is a child table - because 1 person can enroll in multiple classes. It is a classic 1-to-many concept.

That is what it is.

Therefore make a form sourced on the parent table. Then insert a subform which is sourced on the child table. The wizard will assist in setting up the cross referencing field between them.