Query Join syntax...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

I have the following SQL statement but it doesn't return what I want:

SELECT FormFiller.InvNo, FormFiller.CustCode, FormFiller.InvDate,
FormFiller.InvAmt, FormFiller.BatchAR, FormFiller.Name,
ExportLog.ExportFilename, ExportLog.ExportDate, FormFiller.Route
FROM FormFiller LEFT JOIN ExportLog ON (((ExportLog.ExportFilename)="c:\" &
[FormFiller]![BatchAR] & ".txt";

What I want are all records from FormFiller and include data in the two
fields from ExportLog, if provided. Any ideas?

Thanks,

Trevor
 
Jesterhoz said:
Hello All,

I have the following SQL statement but it doesn't return what I want:

SELECT FormFiller.InvNo, FormFiller.CustCode, FormFiller.InvDate,
FormFiller.InvAmt, FormFiller.BatchAR, FormFiller.Name,
ExportLog.ExportFilename, ExportLog.ExportDate, FormFiller.Route
FROM FormFiller LEFT JOIN ExportLog ON (((ExportLog.ExportFilename)="c:\" &
[FormFiller]![BatchAR] & ".txt";

What I want are all records from FormFiller and include data in the two
fields from ExportLog, if provided. Any ideas?

Thanks,

Trevor
Click to expand...

The left join is the correct join type for what you want to do. It's
impossible to help further without more information e.g. what DOES it
return? What is in the field ExportLog.ExportFilename? What is in the
field FormFiller.BatchAR?
 
There are 3 opening brackets and only 1 closing bracket in this part:
LEFT JOIN ExportLog ON (((ExportLog.ExportFilename)="c:\" &
[FormFiller]![BatchAR] & ".txt";

Apart from that, may be use a query to get the required fields from
FormFiller and include the calculated column for the file path/name.
Then link between the fields directly, rather than using the calculated
expression as part of your join.

Regards,
Andreas

Hello All,

I have the following SQL statement but it doesn't return what I want:

SELECT FormFiller.InvNo, FormFiller.CustCode, FormFiller.InvDate,
FormFiller.InvAmt, FormFiller.BatchAR, FormFiller.Name,
ExportLog.ExportFilename, ExportLog.ExportDate, FormFiller.Route
FROM FormFiller LEFT JOIN ExportLog ON (((ExportLog.ExportFilename)="c:\"
&

[FormFiller]![BatchAR] & ".txt";

What I want are all records from FormFiller and include data in the two
fields from ExportLog, if provided. Any ideas?

Thanks,

Trevor
Click to expand...


The left join is the correct join type for what you want to do. It's
impossible to help further without more information e.g. what DOES it
return? What is in the field ExportLog.ExportFilename? What is in the
field FormFiller.BatchAR?
Click to expand...
 
I have never seen the ON clause like yours. Normally, ON clause relate
Fields from 1 Table to Fields in the other Table. In your SQL String, there
is no such relationship and it seems to relate a Field to a Text String???
 
Van T. Dinh said:
I have never seen the ON clause like yours. Normally, ON clause relate
Fields from 1 Table to Fields in the other Table. In your SQL String, there
is no such relationship and it seems to relate a Field to a Text String???
Click to expand...

Hi Van,

He isn't exactly doing that: the text string is constructed around a field
in one of the tables. I don't think it's any different in principle to,
say, using a type conversion function e.g.

table_a LEFT JOIN table_b ON Cstr(tableA.some_field) = table_b.some_field

which works perfectly well, except the query designer pukes on it and it
needs to be maintained in the SQL window.

Apart from the unbalanced parentheses (as Andreas spotted, and which are
presumably a transcription error since it appears the query actually runs) I
suspect the join is fine in principle. I suspect that the data is not quite
as the poster expects, hence his unexpected results.
 
Brian

You are correct. I didn't read it properly and thought that O.P. referred to
a Control on the Form "FormFiller".

Assuming the parentheses are correct, then he should use the dot . as the
qualifier operator rather than the exclamation !.
 
Van T. Dinh said:
Brian

You are correct. I didn't read it properly and thought that O.P. referred to
a Control on the Form "FormFiller".

Assuming the parentheses are correct, then he should use the dot . as the
qualifier operator rather than the exclamation !.
Click to expand...

Yes, I initially thought that too! A very confusingly-named table!
 
Get it working first, then make it "nice".
I don't see anything wrong with using the GUI and using intermediate
queries (at least temporarily).

As per my previous post
- use a standard select query to construct the calculated join field.
- use the result of that query to join directly on the calculated field

If this does not work, open both queries side by side and identify the
differences in field values.

Regards,
Andreas
 
Back
Top