98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Access
Access
Resolved · Medium Priority · Version 2016
Mon 10 Feb 2020, 14:03Delegate 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:26Trainer 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:10Delegate 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:18Trainer 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:05Delegate 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:38Trainer 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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Creating Parameter wildcard queriesTo creat a parameter query that also uses a wildcard, in the query design type in like []+*. |
