Access

Resolved · Medium Priority · Version 2016

Mon 10 Feb 2020, 14:03 Delegate Gordon said...

Gordon has attended:
Access Advanced course

Access

I created parallel tables but cannot now make one piece of information from 'Table 1' update in the forms, reports etc associated with Table 2. How can I rectify this?

Mon 10 Feb 2020, 14:26 Edited on Mon 10 Feb 2020, 14:26 Trainer Jens said...

RE: Access

Hi Gordon,

Thank you for the forum question.

When you say you have created parallel tables, do you mean that they are related by a relationship?

What do you mean by update? The information will not be displayed in the form or when you enter the information in the form, it doesn't get into to table 1?

Do you have the primary key in table 1 or table 2?

Sorry I just ask you a lot of questions but I will need to know more about your data model and how you have created the form and report. If it is not working both in your report and form, a good guess is that you have a problem with relationships.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 10 Feb 2020, 15:10 Delegate Gordon said...

RE: Access

Hi Jens,

Yes, I tried to edit the relationship but despite a line appearing between the two equivalent fields on the relationships screen, it doesn't help.

Mon 10 Feb 2020, 15:18 Trainer Jens said...

RE: Access

Hi Gordon,

I still need you to clarify more to be able to help.

Do you enforce referential integrity in the relationship? This will test the relationship.

Which kind of relationship do you have:

One to Many

or

One to One?

What do you mean by update? The information will not be displayed in the form or when you enter the information in the form, it doesn't get into to table 1?

Do you have the primary key in table 1 or table 2?

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 10 Feb 2020, 16:05 Delegate Gordon said...

RE: Access

Relationships are 1:1

The primary key is in Table 1

I want a 'status' field to update across the two tables.

The 'status' is updating all related forms/reports associated with Table 2 but not 'crossing over' to Table 1

Mon 10 Feb 2020, 16:38 Trainer Jens said...

RE: Access

Hi Gordon,

A One to One relationship. So all the data are a fact of the same key.

Do you have the same fields in both tables? Then you should make a union query to get it all in one table.

If it is different fields you will need to use a update query, which will update status in table 1 when you change it in table 2.

If you have a look at the pasted link below (scroll down to the update query)

https://support.office.com/en-gb/article/create-and-run-an-update-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514 #__toc336583663

I hope I understand you right. If I do this is the only solutions I can come up with now.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector