UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Not Updatable, Join Issues, Access 2016    
 
   
arpirnat
post Dec 29 2017, 02:14 PM
Post#1



Posts: 83
Joined: 25-June 16



So I have a query that uses 2 tables.
One table has only 2 fields. Service Name (the PK) and Clinic.
The Clinic field on my tblScheduler_Sheet table matches the data in the Clinic field on the tblConsultAlias table. On the form I have this query linked to I want it to display the Service Name value that is linked to the 'Clinic' on the tblScheduler_Sheet.

If I use the Join in the query between the Clinic and Clinic values it displays the information just fine but the query becomes non-updatable. If I link the Clinic to Service Name fields it is updatable but the information does not show.
Do I HAVE to have it linking to the PK for this to remain updatable or am I missing something else here?

Ideally yes this information would have simply been incorporated on the one table but this need for an 'alias' did not come up until after the fact. It may be less work to simply add this to the main sheet and have it automatically update the information.
Go to the top of the page
 
MadPiet
post Dec 29 2017, 02:17 PM
Post#2



Posts: 2,313
Joined: 27-February 09



Could you post the table definitions? Usually you have to do the join in the WHERE clause in update queries.

UPDATE MyTable
SET MyTable.Field2 = OtherTable1.Field1
WHERE MyTable.PK = OtherTable.FK;
Go to the top of the page
 
projecttoday
post Dec 29 2017, 02:19 PM
Post#3


UtterAccess VIP
Posts: 8,809
Joined: 10-February 04
From: South Charleston, WV


To make the query update-able remove the join. You can put a combo box on the form to display the alias.

--------------------
Robert Crouser

Go to the top of the page
 
arpirnat
post Dec 29 2017, 02:26 PM
Post#4



Posts: 83
Joined: 25-June 16



How would you code the combo box to pull that info? I did try but either I am having a brain fart kind of day or it just wouldn't work smile.gif
Go to the top of the page
 
projecttoday
post Dec 29 2017, 02:31 PM
Post#5


UtterAccess VIP
Posts: 8,809
Joined: 10-February 04
From: South Charleston, WV


Just drop a combo box on the form (in design view) and a wizard will come up. Follow the prompts.

--------------------
Robert Crouser

Go to the top of the page
 
arpirnat
post Dec 29 2017, 03:00 PM
Post#6



Posts: 83
Joined: 25-June 16



I can get it to allow a selection so I can PICK the Service Name, but I just wanted it to automatically display the one linked to what was already populated in the 'Clinic' field of the form.
Go to the top of the page
 
projecttoday
post Dec 29 2017, 03:06 PM
Post#7


UtterAccess VIP
Posts: 8,809
Joined: 10-February 04
From: South Charleston, WV


A combo box will display as well as input.

--------------------
Robert Crouser

Go to the top of the page
 
mike60smart
post Dec 29 2017, 03:20 PM
Post#8


UtterAccess VIP
Posts: 12,430
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Can you upload a zipped copy of the Db?

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
arpirnat
post Dec 29 2017, 03:56 PM
Post#9



Posts: 83
Joined: 25-June 16



I cannot due to network security.

I was able to get projecttoday's idea to work though smile.gif
Go to the top of the page
 
mike60smart
post Dec 29 2017, 03:57 PM
Post#10


UtterAccess VIP
Posts: 12,430
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Great, Luck with your project

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th January 2018 - 02:45 AM