Solved

SQL Problem (Revisited)

Posted on 2002-06-09
7
134 Views
Last Modified: 2012-05-04
Hello,

I can't get the examples that you all have provided to work. Here is the sql I'm working with. I'm trying to select all records from from one table, to all the records of anonther table.

All the fields are (named) correctly, but it won't work. The RecordSet won't open using this sql statement, but will open using the simple "Select * from tbl_DR_Records" statement. So, I know the connection and openning (a) recordset is working. Something in the sql below is incorrect.

Both the tbl_DR_Records table and the tbl_DR_ExplainAction table has a record (8729). All fields in both tables have information.

g_iLinkReference = 8729
           
sSql = "Select Recs.DReferID, Recs.DDate, Recs.DTime, Recs.DPage, Recs.DStation, " _
        & "Recs.DProgram, Recs.DSubject, Recs.DBrief, Recs.DContact, Recs.DDepartment, " _
        & "Recs.DNotified, Recs.DStatus, Recs.DAuthor"
sSql = sSql & " Action.DEAReferID, Action.DExplain, Action.DAction, Action.DOpComment, " _
        & " Action.DSuperComment"
sSql = sSql & " From tbl_DR_Records Recs Left Outer Join tbl_DR_ExplainAction Action On Recs.DReferID = Action.DEAReferID " _
& "Where Recs.DReferID = " & CStr(g_iLinkReference)

I've used many points trying to get this to work. Please, any ideas are appreciated.

ADawn
0
Comment
Question by:ADawn
7 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
Your query is looking good. At least for Access and MS-SQL.

Are your fields numeric?

What is your database engine?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
if yu SQL is asyou wnat it, then the problem is in the Where Clause, as you indicate the useof cStr(), so your Whare clause should look like this:

"Where Recs.DReferID = '" & CStr(g_iLinkReference) & "' "

(enclose the CStr(g_iLinkReference) in single quotes)


Are you sure that Recs.DReferID is a TEXT value?

if it is not, then you do NOT need the cSTr() function:

"Where Recs.DReferID = " & g_iLinkReference)

ethr way, the SQL String wouldbe WRONG as you hav shown it.

Arthur Wood
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Agree

mlmcc
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 18

Expert Comment

by:deighton
Comment Utility
what is your backend database, and what error message do you receive?
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
The SQL looks OK, but if it's not returning any rows, then you have to check each column that is part of the where clause and also each column in the join clauses.  Since DReferID in the Recs table is in both, start there, check it's data type.  If it's a numeric column then you don't have to wrap single quotes around it, but if it's a text or memo column you will.  

Next, check the DEAReferID column in the Action table.  Is it defined exactly the same as the DReferID column in the Recs table?  If they are different data types or lengths, then the join may not be working (however, you should at least get all the qualifying records from the Recs table).

Just to make sure your join is working, you should run the query after commenting out the Where Clause entirely.  This should give you every row in the Recs table, and in the result set you should see the Action columns populated for every Recs record where there was a match.  If all of your Action columns are null, then you know your Join is not working right.

Lastly (or maybe you want to do this earlier), manually sift through the data and compare the ID fields in both tables to ensure that you do, in fact, have Actions that map to Recs.  Look carefully at the data in the column to ensure that one doesn't have an embedded space at the beginning or end.  Sometimes you can pull your hair out becuase the keys look equal, but one has an extra space at the end which the database sees but you do not....

0
 
LVL 18

Accepted Solution

by:
mdougan earned 50 total points
Comment Utility
Also, I noticed that you don't have a comma after the DAuthor field in the select criteria list.  This may be generating a SQL error.  Change this code:

       & "Recs.DNotified, Recs.DStatus, Recs.DAuthor"
sSql = sSql & " Action.DEAReferID, Action.DExplain, Action.DAction, Action.DOpComment, " _

To This:

       & "Recs.DNotified, Recs.DStatus, Recs.DAuthor,"
sSql = sSql & " Action.DEAReferID, Action.DExplain, Action.DAction, Action.DOpComment, " _
0
 

Author Comment

by:ADawn
Comment Utility
Again, thanks for the help. ADawn
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now