Hi Scott,
Glad that we could be of help.
In answer to your question, no, an existing query will not remember a
previous join, as long as you saved your changes. However, it is possible to
have a SQL statement as the recordsource for a form or report, instead of a
saved query. If you have a recordsource that starts out with the SELECT
keyword, then the form or report does not depend on an external query for its
source of records. In that case, changing the join in an external query would
have no effect on the recordsource for the form.
Thank You for marking my response as an answer. Much appreciated.
Tom
______________________________________
:
Thank you Duane and Tom.
That was exactly what the problem was. Learn something new every day (grin).
Another interesting quirk was that changing the relationship to Left Join
didn't work until I recreated a new Query based on that new join. Somehow the
existing query "remembers" the previous join ?
Thanks again !
Scott in Texas
______________________________________
:
Hi Scott,
It sounds like the query that serves as the recordsource for your report
includes the default inner join between the Customer table and the
Transaction table. As such, the recordset will only include records where the
joined fields from both tables are equal. Try changing the join to an outer
join. In query design view, double-click on the join line that represents the
relationship. Change the join type to #2, which should read something like:
"Include all records from 'Customer' and only those records from
'Transaction' where the joined fields are equal."
An example of the differences produced by an Inner Join versus an Outer Join
can be seen by copying and pasting the following two queries into the sample
Northwind database:
Inner Join returns 831 records using original unaltered data:
SELECT Customers.CompanyName, Orders.OrderDate
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Left Join returns 833 records using original unaltered data:
SELECT Customers.CompanyName, Orders.OrderDate
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
The difference, in this case, is that there are two customer records in the
sample Northwind database who have never placed an order.
Tom
________________________________________
:
I have two simple tables. The first is a customer table, the second is a
transaction table. All customers have an Account Balance in the Customer
Table but one of those customers does not have any transactions in the
Transaction Table. I am doing a report grouping on Customer ID and performing
a SUM of detail transactions for each customer. Everything works great
(including correct totals) except for that one customer with no transactions.
I would expect his record (including the Account Balance) to still print -
but it is not printing at all. If I create a quick report of all customers
and their account balances from the Customer Table, that customer will print
correctly.
Suggestions ?
Scott in Texas
