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
> Problem With Join, Access 2016    
 
   
azizrasul
post Oct 17 2019, 08:17 AM
Post#1



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


I am trying to recreate a SQL server query in a adp MS Access file into a new MS Access 2016 database. The SQL code is:-

CODE
SELECT     TOP 100 PERCENT tblLeaveApplicationRequests.LeaveApplicationId, tblLeaveApplicationRequests.ConsultantId, tblPeople.Surname,
                      tblPeople.UserReason, tblPeople.AdminGroup, tblLeaveApplicationRequestsSupplemental.AuthPositionId, tblLeaveApplicationRequests.StartShift,
                      tblLeaveApplicationRequests.StartDate, tblLeaveApplicationRequests.EndDate
FROM         tblLeaveApplicationRequests LEFT OUTER JOIN
                      tblLeaveApplicationRequestsSupplemental ON
                      tblLeaveApplicationRequests.LeaveApplicationId = tblLeaveApplicationRequestsSupplemental.LeaveApplicationId LEFT OUTER JOIN
                      tblPeople ON tblLeaveApplicationRequests.ConsultantId = tblPeople.PeopleId
WHERE     (tblPeople.UserReason = 5) AND (tblPeople.AdminGroup = 2) AND (tblLeaveApplicationRequestsSupplemental.AuthPositionId = 5) AND
                      (tblLeaveApplicationRequests.StartShift = 1)
ORDER BY tblLeaveApplicationRequests.StartDate


I get an error where essentially tblLeaveApplicationRequests.ConsultantId is text while tblPeople.PeopleId is numerical, when joining. In the original adp file the query works OK. The SQL server tables arfe now linked tables in the MS Access 2016 file and I caan't change the design of these tables. How do I resolve this?

Also I have noticed that in table "tblLeaveApplicationRequests" in the adp file, there is data but in the same linked table in the accdb file, all the cells show #Deleted?

--------------------
Aziz
Go to the top of the page
 
Jeff B.
post Oct 17 2019, 08:21 AM
Post#2


UtterAccess VIP
Posts: 10,323
Joined: 30-April 10
From: Pacific NorthWet


One approach might be to 'chain' together queries. If you first create a query in which you convert one of those two into the other's format (e.g., CStr() converts to a text/string value), you could then use that query in a subsequent query that joins on that (converted) value.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
cheekybuddha
post Oct 17 2019, 08:23 AM
Post#3


UtterAccess VIP
Posts: 11,668
Joined: 6-December 03
From: Telegraph Hill


If they are now linked tables then you can try:
CODE
-- ...
                      tblPeople ON tblLeaveApplicationRequests.ConsultantId = CStr(tblPeople.PeopleId)
-- ...

Or
CODE
-- ...
                      tblPeople ON CLng(tblLeaveApplicationRequests.ConsultantId) = tblPeople.PeopleId
-- ...

though it may affect performance.

Better to fix it on the server, or create a view and perform the CAST/CONVERT on the server.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 17 2019, 08:26 AM
Post#4


UtterAccess VIP
Posts: 11,668
Joined: 6-December 03
From: Telegraph Hill


>> Also I have noticed that in table "tblLeaveApplicationRequests" in the adp file, there is data but in the same linked table in the accdb file, all the cells show #Deleted? <<

Does the table have a Primary Key?

This can also sometimes be resolved by adding a Timestamp field to the table. Search UA - it comes up regularly.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
azizrasul
post Oct 17 2019, 08:37 AM
Post#5



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Still getting an error. Please see attachment.
Attached File(s)
Attached File  Doc1.zip ( 280.24K )Number of downloads: 3
 

--------------------
Aziz
Go to the top of the page
 
cheekybuddha
post Oct 17 2019, 08:51 AM
Post#6


UtterAccess VIP
Posts: 11,668
Joined: 6-December 03
From: Telegraph Hill


Actually, the SQL syntax won't work directly in Access.

Joins must be nested within parentheses.

Try this instead:
CODE
SELECT TOP 100 PERCENT
  r.LeaveApplicationId,
  r.ConsultantId,
  p.Surname,    
  p.UserReason,
  p.AdminGroup,
  s.AuthPositionId,
  r.StartShift,
  r.StartDate,
  r.EndDate
FROM (tblLeaveApplicatrionRequests r
  LEFT JOIN tblLeaveApplicationRequestsSupplemental s
        ON r.LeaveApplicationId = s.LeaveApplicationId
)
LEFT JOIN tblPeople p
       ON r.ConsultantId = CStr(p.PeopleId)
WHERE p.UserReason = 5
  AND p.AdminGroup = 2
  AND s.AuthPositionId = 5
  AND r.StartShift = 1
ORDER BY r.StartDate;


However, I can never get the nesting that Access wants correct, so if it doesn't work then try re-creating through the query designer.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
azizrasul
post Oct 17 2019, 09:19 AM
Post#7



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


I tried putting round brackets, but still had no joy. However, as per your suggestion, I built up the query bit by bit and got it to work. Hurrah or is it Yeehaaa.

The only that still puzzles me is that although the number of records in the adp match the number of records in the accdb file for the table "tblLeaveApplicationRequests", why in the accdb file, the data in column [tblLeaveApplicationRequests]![ConsultantId] has #Deleted and in the adp file they all have values?

I have noticed that if I enter my mouse cursor in a particular record, then the data appears for that record!
This post has been edited by azizrasul: Oct 17 2019, 09:36 AM

--------------------
Aziz
Go to the top of the page
 
cheekybuddha
post Oct 17 2019, 10:13 AM
Post#8


UtterAccess VIP
Posts: 11,668
Joined: 6-December 03
From: Telegraph Hill


Did you see my post #4?

I don't know the answer off the top of my head, but I know it has been answered before.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
azizrasul
post Oct 17 2019, 10:16 AM
Post#9



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Sorry, I hadn't seen #4. Thanks.

Yes it does have a PK. See attachment.
This post has been edited by azizrasul: Oct 17 2019, 10:18 AM
Attached File(s)
Attached File  Doc1.zip ( 143.68K )Number of downloads: 3
 

--------------------
Aziz
Go to the top of the page
 
cheekybuddha
post Oct 17 2019, 01:11 PM
Post#10


UtterAccess VIP
Posts: 11,668
Joined: 6-December 03
From: Telegraph Hill


Aziz,

First, try Compact and Repair'ing your db.

If that doesn't work, then delete the linked tables.

Compact and repair again, then re-link the tables.

Make sure you delete/re-link, not just refresh link.

Also, does the PK ever contain any alpha string characters? If not, it might be better to change it to Long Integer. Perhaps using the casting function in the join has to be continually re-evaluated and is causing Access to think the records are deleted? shrug.gif

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
nvogel
post Oct 17 2019, 02:14 PM
Post#11



Posts: 1,021
Joined: 26-January 14
From: London, UK


I notice that you have this predictae on the People table:

UserReason = 5 AND AdminGroup = 2

That effectively makes your outer join equivalent to an INNER join - because any nulls produced in the outer part of the join will be eliminated by the predicate on UserReason and AdminGroup. You should probably rethink whether an inner join is really what you want here.
Go to the top of the page
 
azizrasul
post Oct 18 2019, 03:19 AM
Post#12



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Thanks David, I will try that.

nvogel, the query was designed by someone else, I'm merely trying to migrate a adp file to a accdb file. However have taken on board your comment. Many thanks.

--------------------
Aziz
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2019 - 08:07 AM