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
> Join Query With Pass Through, Access 2016    
 
   
arpirnat
post Feb 13 2018, 01:20 PM
Post#1



Posts: 97
Joined: 25-June 16



I have a query that links data on a table to a pass-through query. In short what this does is look for a unique number on the query and matches it to the same unique number on the table and shows me the ones where they match.
The query is an ODBC table and I have no control over it. There is no primary key and this query is not editable which I understand. Is there a way to make the form that displays this data editable for the data on the local table only?
Go to the top of the page
 
John Vinson
post Feb 13 2018, 01:32 PM
Post#2


UtterAccess VIP
Posts: 4,179
Joined: 6-January 07
From: Parma, Idaho, US


Without seeing the Query or the design of the tables it's hard to say, but you may be able to use an In() clause to select the ID's from the ODBC table rather than a JOIN:

SQL
SELECT this, that, theother
FROM localtable WHERE localtable.ID IN (SELECT IDfield FROM remotetable WHERE <criteria>);


If it's necessary to use a Passthrough query then you will probably need to create and save a passthrough query to return the needed IDs and use that stored query as the Subquery.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
Doug Steele
post Feb 13 2018, 01:33 PM
Post#3


UtterAccess VIP
Posts: 21,605
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Are you currently trying to join to Access table to the pass-through query, like

CODE
SELECT AT.Field1, AT.Field2
FROM AccessTable AS AT
INNER JOIN PassthroughQuery AS PT
ON AT.Field1 = PT.Field1


You could try

CODE
SELECT AT.Field1, AT.Field2
FROM AccessTable AS AT
WHERE AT.Field1 IN (SELECT PT.Field1 FROM PassthroughQuery AS PT)

If that doesn't work, you could always import the data from your pass-through query into a temporary Access table.


--------------------
Go to the top of the page
 
arpirnat
post Feb 13 2018, 01:40 PM
Post#4



Posts: 97
Joined: 25-June 16



Sorry should have included it:
CODE
SELECT Scheduler_Sheet.FirstName, Scheduler_Sheet.LastName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.LastAction, Scheduler_Sheet.ConsultNumber, qClosedConsultPull.ConsultIen, qClosedConsultPull.Status
FROM Scheduler_Sheet INNER JOIN qClosedConsultPull ON Scheduler_Sheet.ConsultNumber = qClosedConsultPull.ConsultIen
WHERE (((Scheduler_Sheet.LastAction)="Records Received" Or (Scheduler_Sheet.LastAction)="Records Requested") AND ((qClosedConsultPull.Status)="Complete" Or (qClosedConsultPull.Status)="Cancelled" Or (qClosedConsultPull.Status)="Discontinued"));


I do not need any fields from the qClosedConsultPull in the Select, more so had them there to check the data smile.gif
Go to the top of the page
 
Doug Steele
post Feb 13 2018, 02:07 PM
Post#5


UtterAccess VIP
Posts: 21,605
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Try

CODE
SELECT Scheduler_Sheet.FirstName, Scheduler_Sheet.LastName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.LastAction, Scheduler_Sheet.ConsultNumber, qClosedConsultPull.ConsultIen, qClosedConsultPull.Status
FROM Scheduler_Sheet
WHERE Scheduler_Sheet.ConsultNumber IN (SELECT qClosedConsultPull.ConsultIen FROM qClosedConsultPull WHERE qClosedConsultPull.Status IN ("Complete", "Cancelled", "Discontinued"))
AND Scheduler_Sheet.LastAction IN ("Records Received", "Records Requested");


--------------------
Go to the top of the page
 
arpirnat
post Feb 13 2018, 02:14 PM
Post#6



Posts: 97
Joined: 25-June 16



This did work to pull the same data but unfortunately still non-editable frown.gif
Unfortunately the only thing I wish to edit on this is the LastAction field.
Go to the top of the page
 
Doug Steele
post Feb 13 2018, 02:26 PM
Post#7


UtterAccess VIP
Posts: 21,605
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Out of curiosity, does it work if you don't have the WHERE clause on the subquery based on the pass-through query?

CODE
SELECT Scheduler_Sheet.FirstName, Scheduler_Sheet.LastName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.LastAction, Scheduler_Sheet.ConsultNumber, qClosedConsultPull.ConsultIen, qClosedConsultPull.Status
FROM Scheduler_Sheet
WHERE Scheduler_Sheet.ConsultNumber IN (SELECT qClosedConsultPull.ConsultIen FROM qClosedConsultPull)
AND Scheduler_Sheet.LastAction IN ("Records Received", "Records Requested");

If so, try adding the WHERE clause to the pass-through query.

If that still doesn't work, you may have no choice but to create a temporary table in Access based on the results of the pass-through query.


--------------------
Go to the top of the page
 
arpirnat
post Feb 13 2018, 02:52 PM
Post#8



Posts: 97
Joined: 25-June 16



Seems as though putting this anywhere locks it up. Are you suggesting I try to match the consultnumber with the consultien on the pass through query?
My code for this is (I just added the status part):
CODE
select sta3n, Company, Site, cast(ConsultIen as Int) as ConsultIen, Status, Timeframe, ToRequestServicename, Requestdatetime, ConsultType, Updatedate
from D03_V23Analysts.[V23].[Consult_JP_NonVAOpenConsults]
WHERE Status IN ('Complete', 'Cancelled', 'Discontinued') AND Company like '636A8'


When I try to add the bit to match the consult numbers it is erroring on me.
Go to the top of the page
 
Doug Steele
post Feb 13 2018, 03:34 PM
Post#9


UtterAccess VIP
Posts: 21,605
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Yes, that the change I was suggesting trying in qClosedConsultPull.

Not sure what you mean by "When I try to add the bit to match the consult numbers it is erroring on me."




--------------------
Go to the top of the page
 
arpirnat
post Feb 13 2018, 03:52 PM
Post#10



Posts: 97
Joined: 25-June 16



Just trying to figure out the string to put in. Tried reversing the one from before:
CODE
select distinct sta3n, Company, Site, cast(ConsultIen as Int) as ConsultIen, Status, Timeframe, ToRequestServicename, Requestdatetime, ConsultType, Updatedate
from D03_V23Analysts.[V23].[Consult_JP_NonVAOpenConsults]
WHERE D03_V23Analysts.[V23].[Consult_JP_NonVAOpenConsults].ConsultIen IN (SELECT Scheduler_Sheet.ConsultNumber FROM Scheduler_Sheet)


I am getting an ODBC-call failed
Invalid object name 'Scheduler_Sheet'
Go to the top of the page
 
Doug Steele
post Feb 13 2018, 04:07 PM
Post#11


UtterAccess VIP
Posts: 21,605
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Pass-through queries cannot query Access tables. (The inverse is not true: Access queries can query both Access tables and pass-through queries)

I asked whether not including the WHERE clause in the subquery made a difference:

CODE
SELECT Scheduler_Sheet.FirstName, Scheduler_Sheet.LastName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.LastAction, Scheduler_Sheet.ConsultNumber, qClosedConsultPull.ConsultIen, qClosedConsultPull.Status
FROM Scheduler_Sheet
WHERE Scheduler_Sheet.ConsultNumber IN (SELECT qClosedConsultPull.ConsultIen FROM qClosedConsultPull)
AND Scheduler_Sheet.LastAction IN ("Records Received", "Records Requested");

Is that query updatable (even though it returns more data than desired)?

If it is, change qClosedConsultPull so that it only returns the rows with the desired statuses, and try the exact query shown above again.

--------------------
Go to the top of the page
 
arpirnat
post Feb 13 2018, 04:46 PM
Post#12



Posts: 97
Joined: 25-June 16



Unfortunately the data still seems unupdatable.
Go to the top of the page
 
projecttoday
post Feb 13 2018, 05:18 PM
Post#13


UtterAccess VIP
Posts: 9,017
Joined: 10-February 04
From: South Charleston, WV


What's the LastAction field? The date?

Why do you want a form instead of an update procedure?

You could dump the contents of the pass-through into a temporary table on the Access side and use that in your query. (But would the join render it un-updateable still?)

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

Go to the top of the page
 
Doug Steele
post Feb 13 2018, 05:27 PM
Post#14


UtterAccess VIP
Posts: 21,605
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If the modified queries aren't updatable, it would appear that your only option is to use the pass-through query as the basis for an INSERT INTO query and populate a temporary table.

Good luck! Let us know if you need any more help getting it working.

--------------------
Go to the top of the page
 
arpirnat
post Feb 14 2018, 11:01 AM
Post#15



Posts: 97
Joined: 25-June 16



For this instance an update query could work as well, but for others in the works it would not be as simple. Admittedly first time with an update query.

Created this query and I put in the ConsultNumber and LastAction from the Scheduler_Sheet table, as well as the ConsultIen and Status from the qCloserConsultPull pass-through query. The data pulls up fine as a select query but when I try to run it as an update query and put anything in it tells me 'Must us an updateable query'. Not sure if I should be referencing the data without actually having the query IN the update query or if I Am missing something else smile.gif
Go to the top of the page
 
projecttoday
post Feb 14 2018, 11:17 AM
Post#16


UtterAccess VIP
Posts: 9,017
Joined: 10-February 04
From: South Charleston, WV


I think if you're updating an Access table a pass-through should work for the source but I never actually did that and I don't have a system to test it. Can you post the code?

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

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th February 2018 - 03:49 AM