Right Join (?)...

[Guest]

Hello All,

I have a query that pulls a field from the results of another query and
looks in a table to find a date value associated with it. Easy enough if the
two fields from each table had the same format. However, one table contains
data like "x11083" and the other contains data like "c:\x11083.txt". I have
written the following SQL statement and it works fine except that when the
original query returns results in which that particular field is blank, I get
no results at all. I would like to see the results of the first query
regardless. Does anyone have any ideas?

Here is the code:
SELECT GetInvoiceData.InvNo, GetInvoiceData.CustCode,
GetInvoiceData.InvDate, GetInvoiceData.InvAmt, GetInvoiceData.MarketCode,
ExportLog.ExportDate, GetInvoiceData.AspenARType, GetInvoiceData.BatchIn,
GetInvoiceData.Route, GetInvoiceData.AcctPeriod, GetInvoiceData.BatchAR,
GetInvoiceData.AspenCompany, GetInvoiceData.AspenARType, [Master
Table].Master, [Master Table].Customer, [Master Table].Name,
ExportLog.ExportFilename
FROM [Master Table], GetInvoiceData, ExportLog
WHERE ((([Master Table].Customer)=[GetInvoiceData]![CustCode]) AND
((ExportLog.ExportFilename)="c:\" & [GetInvoiceData]![BatchAR] & ".txt"));

Any help would be much appreciated.

Thanks,
Trevor

 

[Sylvain Lafontaine]

You're right with the right join (or left join). Your problem probably come
from the fact that you are using the old syntax for your join. With this
older syntax, you must write *= and =* instead of Left Join and Right Join.

Also, you must be aware that using string like "x11083" and "c:\x11083.txt"
as primary and secondary keys will bring you many kinds of problems.

S. L.

 

[Douglas J. Steele]

I don't believe *= and =* work with Access SQL, though, do they? I thought
that was a SQL Server dialect only.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You're right with the right join (or left join). Your problem probably
come from the fact that you are using the old syntax for your join. With
this older syntax, you must write *= and =* instead of Left Join and Right
Join.

Also, you must be aware that using string like "x11083" and
"c:\x11083.txt" as primary and secondary keys will bring you many kinds of
problems.

S. L.

Hello All,

I have a query that pulls a field from the results of another query and
looks in a table to find a date value associated with it. Easy enough if
the
two fields from each table had the same format. However, one table
contains
data like "x11083" and the other contains data like "c:\x11083.txt". I
have
written the following SQL statement and it works fine except that when
the
original query returns results in which that particular field is blank, I
get
no results at all. I would like to see the results of the first query
regardless. Does anyone have any ideas?

Here is the code:
SELECT GetInvoiceData.InvNo, GetInvoiceData.CustCode,
GetInvoiceData.InvDate, GetInvoiceData.InvAmt, GetInvoiceData.MarketCode,
ExportLog.ExportDate, GetInvoiceData.AspenARType, GetInvoiceData.BatchIn,
GetInvoiceData.Route, GetInvoiceData.AcctPeriod, GetInvoiceData.BatchAR,
GetInvoiceData.AspenCompany, GetInvoiceData.AspenARType, [Master
Table].Master, [Master Table].Customer, [Master Table].Name,
ExportLog.ExportFilename
FROM [Master Table], GetInvoiceData, ExportLog
WHERE ((([Master Table].Customer)=[GetInvoiceData]![CustCode]) AND
((ExportLog.ExportFilename)="c:\" & [GetInvoiceData]![BatchAR] &
".txt"));

Any help would be much appreciated.

Thanks,
Trevor

 

[Sylvain Lafontaine]

Yes, this is true; I got mixed with Access ADP. One more (very) good reason
for him to forget about this old syntax for joining tables.

S. L.

I don't believe *= and =* work with Access SQL, though, do they? I thought
that was a SQL Server dialect only.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You're right with the right join (or left join). Your problem probably
come from the fact that you are using the old syntax for your join. With
this older syntax, you must write *= and =* instead of Left Join and
Right Join.

Also, you must be aware that using string like "x11083" and
"c:\x11083.txt" as primary and secondary keys will bring you many kinds
of problems.

S. L.

Hello All,

I have a query that pulls a field from the results of another query and
looks in a table to find a date value associated with it. Easy enough
if the
two fields from each table had the same format. However, one table
contains
data like "x11083" and the other contains data like "c:\x11083.txt". I
have
written the following SQL statement and it works fine except that when
the
original query returns results in which that particular field is blank,
I get
no results at all. I would like to see the results of the first query
regardless. Does anyone have any ideas?

Here is the code:
SELECT GetInvoiceData.InvNo, GetInvoiceData.CustCode,
GetInvoiceData.InvDate, GetInvoiceData.InvAmt,
GetInvoiceData.MarketCode,
ExportLog.ExportDate, GetInvoiceData.AspenARType,
GetInvoiceData.BatchIn,
GetInvoiceData.Route, GetInvoiceData.AcctPeriod, GetInvoiceData.BatchAR,
GetInvoiceData.AspenCompany, GetInvoiceData.AspenARType, [Master
Table].Master, [Master Table].Customer, [Master Table].Name,
ExportLog.ExportFilename
FROM [Master Table], GetInvoiceData, ExportLog
WHERE ((([Master Table].Customer)=[GetInvoiceData]![CustCode]) AND
((ExportLog.ExportFilename)="c:\" & [GetInvoiceData]![BatchAR] &
".txt"));

Any help would be much appreciated.

Thanks,
Trevor