Queries with Linked Tables
in [Queries]
|
Prev: Overflow
Next: How do I left justify a date
From: PeterPfaffJCI on 15 Jan 2010 15:13 I have created two tables that are imported to an excel spread sheet which I have linked to an Access Database. I have created a field that links both tables and when I create a query for the two joined tables The joined table doesn't return any information. My question centers around what am I missing? What seems like a simple select query does not return values. My tables both have a distinct ID number that are joined when the primary table matches only the secondary table where the joined fields are matched.
From: John W. Vinson on 15 Jan 2010 15:53 On Fri, 15 Jan 2010 12:13:02 -0800, PeterPfaffJCI <PeterPfaffJCI(a)discussions.microsoft.com> wrote: >I have created two tables that are imported to an excel spread sheet which I >have linked to an Access Database. I have created a field that links both >tables and when I create a query for the two joined tables The joined table >doesn't return any information. My question centers around what am I >missing? What seems like a simple select query does not return values. My >tables both have a distinct ID number that are joined when the primary table >matches only the secondary table where the joined fields are matched. Please post the SQL view of the query. We can't fix what we can't see! -- John W. Vinson [MVP]
From: pietlinden on 16 Jan 2010 20:08 > Please post the SQL view of the query. We can't fix what we can't see! > -- > > John W. Vinson [MVP] Just ask him to move a little... then you can look over his shoulder.
From: PeterPfaffJCI on 18 Jan 2010 08:44 "pietlinden(a)hotmail.com" wrote: > > Please post the SQL view of the query. We can't fix what we can't see! > > -- > > > > John W. Vinson [MVP] >Her is My SQL Statement SELECT [PO to Back Order Link Table].[Back Order ID Number], [PO to Back Order Link Table].[Item Number], [PO to Back Order Link Table].[PO Number], [PO to Back Order Link Table].[PO Delivery Date], [Current Purchase Orders].PO_Number, [Current Purchase Orders].[Del Date_(User Fld 6)], [Current Purchase Orders].Date FROM [PO to Back Order Link Table] LEFT JOIN [Current Purchase Orders] ON [PO to Back Order Link Table].[PO Number] = [Current Purchase Orders].PO_Number; Hope I moved my shoulder enough Regards, Pete
From: John W. Vinson on 18 Jan 2010 13:24
On Mon, 18 Jan 2010 05:44:01 -0800, PeterPfaffJCI <PeterPfaffJCI(a)discussions.microsoft.com> wrote: > > >"pietlinden(a)hotmail.com" wrote: > >> > Please post the SQL view of the query. We can't fix what we can't see! >> > -- >> > >> > John W. Vinson [MVP] >>Her is My SQL Statement > >SELECT [PO to Back Order Link Table].[Back Order ID Number], [PO to Back >Order Link Table].[Item Number], [PO to Back Order Link Table].[PO Number], >[PO to Back Order Link Table].[PO Delivery Date], [Current Purchase >Orders].PO_Number, [Current Purchase Orders].[Del Date_(User Fld 6)], >[Current Purchase Orders].Date >FROM [PO to Back Order Link Table] LEFT JOIN [Current Purchase Orders] ON >[PO to Back Order Link Table].[PO Number] = [Current Purchase >Orders].PO_Number; > > >Hope I moved my shoulder enough That's fine... hope I didn't tickle when I was trying to get past... This should return all records in [PO to Back Order Link Table], regardless of what's in [Current Purchase Orders]; if there are any records in Current Purchase Orders with the same PO Number, you'll see the data for those records, otherwise NULL. Does it not do so? What's in the backorder link table if you open it by itself? I.e., what is there; what did you expect to see; and what did you see instead? Do note that you can get wierd results if you include some special characters such as parentheses in fieldnames. I'd avoid it; in fact I don't use fieldnames containing blanks either, just A-Z0-9_. -- John W. Vinson JVinson *at* Wysard Of Info *dot* com -- John W. Vinson [MVP] |