how to join files

  • Thread starter Thread starter Abbey Normal
  • Start date Start date
A

Abbey Normal

Hello, I have a report that has query for a record source, filtered by Order
ID. But I need to include 3 different fields from my Company table. How do
include it in the query when there is no matching data to join it on? I only
have one record in the Company table, with an ID of 1. Or can I do something
other than including it in the query
Thanks,
 
Hello, I have a report that has query for a record source, filtered by Order
ID. But I need to include 3 different fields from my Company table. How do
include it in the query when there is no matching data to join it on? I only
have one record in the Company table, with an ID of 1. Or can I do something
other than including it in the query
Thanks,
Click to expand...

If there's only one record, simply include it in the query with no join line.
This kind of "Cartesian Join" query will link every record in one table with
every record in the other table... but since there is only one record to link,
that gives you just what you want.

John W. Vinson [MVP]
 
I must be missing something. I included the table, and dragged/dropped the
fields in to the desgin of the query. But when i try to open the report I get
a message saying "The SQL statement could not be executed because it contains
ambigious outer joins" Any suggestions? Thanks,
 
I must be missing something. I included the table, and dragged/dropped the
fields in to the desgin of the query. But when i try to open the report I get
a message saying "The SQL statement could not be executed because it contains
ambigious outer joins" Any suggestions? Thanks,
Click to expand...

You haven't given any indication of the SQL of the query...

If you are adding this one-record company table to an existing multitable
query, it may be necessary to save the multitable query under its own name,
and create a NEW query with the Cartesian join:

SELECT [CompanyTable].*, [SavedQueryName].*
FROM [CompanyTable], [SavedQueryName];

John W. Vinson [MVP]
 
Thank you. That worked like a charm. I created a query on my company table
including only those fields I wanted (because of field duplication) and then
I created your cartesian-joined query on that + my old query. Then I changed
the record source on my report to the cartesian-joined query. I appreciate
your answers....
John W. Vinson said:
I must be missing something. I included the table, and dragged/dropped the
fields in to the desgin of the query. But when i try to open the report I get
a message saying "The SQL statement could not be executed because it contains
ambigious outer joins" Any suggestions? Thanks,
Click to expand...

You haven't given any indication of the SQL of the query...

If you are adding this one-record company table to an existing multitable
query, it may be necessary to save the multitable query under its own name,
and create a NEW query with the Cartesian join:

SELECT [CompanyTable].*, [SavedQueryName].*
FROM [CompanyTable], [SavedQueryName];

John W. Vinson [MVP]
Click to expand...
 
Back
Top