| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-27-10, 16:52
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Londo, UK
Posts: 20
|
|
|
Missing records in query
|
|
Hi. I have a database with three tables, all in a relationship. This is the SQL from the query
SELECT tblBook.BookID AS tblBook_BookID, tblBook.Book, tblBook.Price, tblBook.Frequency, tblName.NameID AS tblName_NameID, tblName.Fname, tblName.Surname, tblName.TelNo, tblOrder.ID, tblOrder.NameID AS tblOrder_NameID, tblOrder.[No], tblName.Cancelled
FROM tblName INNER JOIN (tblBook INNER JOIN tblOrder ON tblBook.BookID = tblOrder.BookID) ON tblName.NameID = tblOrder.NameID
ORDER BY tblName.Surname;
What I am finding is that not all the records are showing in the query. From looking at previous posts and at this tutorial SQL Tutorial [SELECT Statement Extended] I think it may be something to do with the Join function. Perhaps it should be FULL OUTER JOIN? But I am not sure how to code this to replace the two join statements above. If I replace INNER JOIN with FULL OUTER JOIN an error message says Syntax error and highlights OUTER.
Perhaps someone can explain this to me.
__________________
Janet
|
|
01-27-10, 17:17
|
|
Computer Monkey
|
|
Join Date: May 2005
Posts: 1,191
|
|
It appears your tutorial site you linked to does a decent enough job of explaining the differences, but I don't think Access supports a Full Outer Join; I think you'll want either a LEFT JOIN or a RIGHT JOIN.
__________________
Me.Geek = True
|
|
01-27-10, 17:29
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Londo, UK
Posts: 20
|
|
|
|
OK. So I tried LEFT OUTER JOIN and LEFT JOIN (as you suggested nckdryr) both gave the error message "Join expression not supported".
If that is the case, how do I get these records visible?
__________________
Janet
|
|
01-27-10, 17:38
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Londo, UK
Posts: 20
|
|
I've just seen your reference to the ACCESS tutorial on joins. I have tried what they suggest, but it is telling me I need to create a query on one join and then insert it into the other query with the other join. I shall have to have a go at that tomorrow. I am not sure how to do it, but I will give it a try.
__________________
Janet
|
|
01-27-10, 17:48
|
|
Computer Monkey
|
|
Join Date: May 2005
Posts: 1,191
|
|
Try something like the following:
Code:
SELECT tblBook.BookID AS tblBook_BookID, tblBook.Book, tblBook.Price, tblBook.Frequency, tblName.NameID AS tblName_NameID, tblName.Fname, tblName.Surname, tblName.TelNo, tblOrder.ID, tblOrder.NameID AS tblOrder_NameID, tblOrder.[No], tblName.Cancelled
FROM tblName LEFT JOIN (tblBook LEFT JOIN tblOrder ON tblBook.BookID = tblOrder.BookID) ON tblName.NameID = tblOrder.NameID
ORDER BY tblName.Surname;
See if that gets you what you're after.
__________________
Me.Geek = True
|
|
01-28-10, 10:42
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Londo, UK
Posts: 20
|
|
Tried that and the error "Join expression not supported" comes up
__________________
Janet
|
|
01-28-10, 10:50
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Code:
FROM (
tblOrder
INNER
JOIN tblBook
ON tblBook.BookID = tblOrder.BookID
)
INNER
JOIN tblName
ON tblName.NameID = tblOrder.NameID
they ~have~ to be INNER joins, unless you have a situation where (1) there's an order with a BookID for a non-existent book, or (2) there's an order with a NameID for a non-existent name
|
|
01-28-10, 11:01
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Londo, UK
Posts: 20
|
|
BINGO. That worked. Many thanks.
It is basically the same as I had originally but with the brackets in a different place.
'Tis a puzzlement!
__________________
Janet
|
|
09-01-10, 10:13
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 1
|
|
|
MS Access 2007 - Query problem
I have a similar problem as mentioned above and the solution does not work for me. I have two tables; one of them has 1400 records and another about 100. It seems that very randomly, query does not give the record I call for,
Here is the SQL code for my query. Please give me any idea what is wrong or what should I change to make it work. MS Access 2007
Code:
SELECT MasterListing.OWC, MasterListing.ID, MasterListing.PartNumber, MasterListing.SerialNumber, MasterListing.WUC, MasterListing.DateDueCal, MasterListing.DateLastCal, MasterListing.Location, MasterListing.Comments, MasterListing.Nomenclature, MasterListing.JobControl, MasterListing.CalInterval, MasterListing.DateFromPmel, Master_PN.PartNumber, Master_PN.Nomenclature, Master_PN.CalInterval, Master_PN.LastUpdate, Master_PN.Remarks, Master_PN.WUC, MasterListing.Status, MasterListing.DateToOWC
FROM MasterListing INNER JOIN Master_PN ON MasterListing.PartNumber = Master_PN.PartNumber
WHERE (((MasterListing.ID)=[Enter ID]));
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Missing records in query
