Outer Join Query Question 1

[dapotter]

I have two tables with matching primary keys. The second table is simply a place to collect notes on the records in the first table. Not all records from the first table will have notes, so the second table will likely have fewer records than the first table. I have a query that combines these tables using an outer join so that all the records from the first table show up even if there isn't a corresponding match in the second table. The query used to allow me to type a note into a field from the second table even if the second table didn't have a record matching the current row in the first table. It seemed that a record in the second table was automatically created to accept the note.

I have made a lot of changes to my database and I recently noticed that when I try do this now, I am prevented from entering anything (into the second tables fields) when the second table doesn't have a record defined yet. I'm kind of a novice at databases and setting up relationships. I don't remember doing anything special to cause the new record to be added to the second table in the first place and I can't figure out how to make that happen again.

Does anyone know what I'm missing?

Thanks.

 

[Terpsfan]

I think a better way of approaching this is to create the Notes table with it's own primary key (autonumber field) and a foreign key of the first or master table. That way you can easily create a master/detail form with the form wizard. You will have a main form/subform in which you can enter all your data. The subform will have your note data.

All you need to do is take off the primary key of the notes table and insert an autonumber field, make it the primary key and then go to the relationships diagram and specify a relationship between the tables.

Then create your form with the form wizard, first including all the fields from the main table and then from the notes table. The wizard should then create the master/detail form for you.

 

[dapotter]

Omega36,

I'm sure your right about the approach you outlined being better. My time is limited, but I may look into adopting it. I'm just frustrated that my approach used to work for me and now it doesn't.

 

[dapotter]

Omega36,

I already had a form using data from my query. So instead of creating a new master/detail form, I cut the records from my form that mapped to the second table and pasted them into a new form. I then used that new form as a subform on the original form. Now I am able to add notes for records in table one even when a corresponding record in table two doesn't exist. I didn't even have to change the primary key of my second table.

Thanks for heading me in the right direction.