Table Links

I have a table A that has a one to many relationship with Table B. Most of the time there is no data in B.  If I create an update query linking A with B  with say 100 records  all of the check boxes are greyed out and are not updateable because there is no corresponding data in table B to link the two tables.

What I am trying to do is update all 100 records in the update query. So how do you force the linking field to always have the link field in table B when no data has been entered in it.

Basically I want the link field always created in table B when a record is created in table A.

I access Table B via a pop up form. In this form are check boxes that automatically add data such as today's date.
I do input data into table A via a form and I could force the pop up form for B to be visible/invisible and then just set the link field from the link field in form A. It just seems a crude way to do it.
Derek BrownMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what you need is a Form / Sub Form set up

Create a Form based on TableA ( can be a single form ) "FormA"
Then create a Form based on TableB( datasheet or continuous form) "FormB"
Saved both form and close.

in the design view of FormA, select FormB and drag and drop it on the detail section of FormA
then in the property sheet of FormB set

Link Master Fields ID field name in TableA
Link Child Fields     ID field name in tableB
Creating dummy records in table A isn't the answer.

In the query that joins the two tables, omit the FK from tblB.

Select tblA.PK, tblA.fld1, tblB.fld2
From tblA Left Join tblB ON tblA.PK = tblB.FK;

In this query, if you type something in tblB.fld2, Access will AUTOMATICALLY populate tblB.FK with the foreign key and insert a row in tblB.  This of course, assumes that you have actually defined a relationship using the relationship window that tells Access how tblA and tblB are related.  Access will also allow you to Add a row to tblA and to tblB in the same query.
1-Many relationship
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hamed NasrRetired IT ProfessionalCommented:
Upload a sample database with the 2 tables. Include 5 test records.

Explain what result is required.

Form/Subform is a natural way to input data in related tables.

The left join allows showing all records of A .
Yes, a form/subform is the natural way to do this interactively.  I believe the OP is looking for a query that will work.
In my first post I omitted the FKToTable1 field but that actually prevents you from adding to both tables in the same query.  If you include it, you can add new records to both tables in the same query.
Hamed NasrRetired IT ProfessionalCommented:
Assume tables:
a (aid, ....)
b (aid, bid, ....) - bid default value 0

UPDATE a LEFT JOIN b ON a.aID = b.aID SET b b.aID = [a].[aid];

This adds new records to table b and populates the linked field.
Derek BrownMDAuthor Commented:
Hi Ray

That would work if I am adding data to the subform and the point here is that I am not. So the subform does not create data in the linked field without data in table B.

I am trying to add the FK to table B not A without entering data in B. So is this correct?:

Select tblA.PK, tblA.fld1, tblB.fld2
From tblA Left Join tblB ON tblA.PK = tblB.FK;

Can't get that to work.

Thanks Derek
Hamed NasrRetired IT ProfessionalCommented:

UPDATE tblA LEFT JOIN tblB ON tblA.PK= tblB.PK SET tblB.PK =  tblA .PK;

You need to set a default value for tblB.FK in table tblB, say 0, and that will be inserted for each created record with tblB.PK.
Derek BrownMDAuthor Commented:
That looks really neat hnasr!

When do I run this query or can it be used with a select statement that provides the data for the form I am using to update the joined table query data?

Would it create an updateable record set?

I am trying it but not sure where it should go
Hamed NasrRetired IT ProfessionalCommented:
Create a new query. Cancel table selection. Go to SQL view.
Paste the update query. Select Update type.
Use proper names of tables and fields.
Press the run icon.
Derek BrownMDAuthor Commented:
That's great and works perfect. Unfortunately it does not answer any of my questions. Basically all we have done is create an update query. So again I can only assume that this query will not have any effect on table B when I add a new record to Table A. Which is what I am trying to do.

I can call that query every time I open my global change form. It just seems an unnatural way to do it.
Forcing dummy records to be created in tableB when you add a row to tableA is wrong.  I don't know how else to say it.  I showed you how to create an updateable query (which is not the same as an update query which is what hnasr posted) which allows you to add rows to tableA AND tableB at the same time.  You simply need to fill out data in columns for tableA AND tableB.  If you don't know the data that belongs in tableB, DO NOT create dummy records.  Wait until you have the data you need.

Typically when working with a 1-many relationship which may be sparse (no rows in the many-side table) you would use a left join - that is what the query I posted is.
Hamed NasrRetired IT ProfessionalCommented:
"That's great and works perfect. Unfortunately it does not answer any of my questions."

That's fine and indicates a misunderstanding of the issue.

If non of the other experts work for you, then try to upload a sample database with the 2 tables. Include 5 test records, and explain what result is required.
Derek BrownMDAuthor Commented:
Here it is.

Open Form "Main" as you move through the main form records with the record counter notice that although the subform shows ID number in each record there are no actual records in the subform.

Open the "Update form" and see that again there are no records in the part of the records that come from the Order table. Now click the button "Update Date in all records and change update tick box" and see that nothing happens. The query2 will show records going from query design view and choosing the show data icon, but it will not update the fields because there are no records in the Order table..

So all I need is some way to create a record in the "Order" table every time that the main form moves to a new record.

Hope that helps
Rey Obrero (Capricorn1)Commented:
test if this is what you want

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Derek BrownMDAuthor Commented:
Hi Ray

For some reason I cannot open or copy that file. Don't we normally just double click the file name?

Rey Obrero (Capricorn1)Commented:
you just have to click on the file
Rey Obrero (Capricorn1)Commented:
you just have to click on the file , and save it
Derek BrownMDAuthor Commented:
Hi Rey

Sorry about this. I do not get an option to save, it will only save link or Open. What is actually happening is that when I open the file it saves it automatically without notification or confirmation and then says I cannot open this file.  I have it saved 11 times. Crazy.

So I got it to open. If you create a new record in customer form you get error. I fixed the error but then when you add a record it does not update the child table until you move to another record. This may seem petty but if a user is entering a new customer and moves immediately to the update form the record cannot be updated. So I put the code in the form's after update procedure:

Private Sub Form_AfterUpdate()
    If IsNull(ID) = False Then
        If IsNull([Order].Form![OrderID]) = True Then
            CurrentDb.Execute "insert into [Order] (CustID) values(" & Me.ID & ")"
        End If
    End If
End Sub
Derek BrownMDAuthor Commented:
Thanks to you all

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.