February 17, 2009 at 3:36 pm
I am developing an Access 2005 "Project" (.adp) as a front-end to a SQL Server 2005 Express database.
I am basing an Access "Report" on a SQL Server "View". This report is to print a customer order, and the "View" pulls together different pieces of data relating to the order. These different pieces of data reside in different tables, and up until now, every column specified in the "View" has been "findable".
However, I have just added a new "Order Notes" table that contains some notes that can optionally be added to an order. Not every order will have a related record in the "Order Notes" table. When I add the "Order Notes" table to my view using SQL Server Management Studio Express's designer program, it adds an "INNER JOIN" clause to the SQL text underlying the View.
If a particular order does indeed have a related record in the "Order Notes" table, things work OK, however, when an order does not own a record in the "Order Notes" table, the view produces no results for that particular order and the report breaks down.
I'm a relative newbie to SQL Server and T-SQL. Can anyone advise me on how to cope with this, and what syntax I can add to my View's SQL text that would provide a "default" empty value if no related "Order Note" exists?
Many thanks.
February 17, 2009 at 11:09 pm
why do u want to create view.... you can do this conditional settings by making procedure......
first put condition that if that table have data or not ....
if there is data into that table ...then keep your query as it is
else
put query without that table....
Raj Acharya
February 18, 2009 at 1:35 am
Firstly I would not use the view designer - understanding the underying SQL is almost always better,,
I thonk all you need in this case is to change the hjoin to the notes table to a a LEFT OUTER JOIN so change the INNER JOIN to that and it should do what you need,
Mike
February 18, 2009 at 1:36 am
put query without that table....
Thanks for the prompt reply, Raj.
I'm not sure how I would "put query without that table".
I have created an Access "Report" that has my "View" as its "Record Source". That "view" pulls together all the elements (including possible notes) that are needed for a printed version of an order. From a master order Form, I have a "Print" command button that opens my "Report" with a "where clause" that identifies the current order.
Although I could easily discover if an order does not have notes before I try to print it, how would I dynamically modify the "View" to exclude the notes form being part of the (large) SQL statement that underlies it?
Certainly, if that can be done, it would be quite an elegant solution. My other thought is to "temporarily" create an "empty" note record for an order that doesn't have a true notes record, for the sake of the report, then delete it after the report is printed. Slightly cumbersome, but probably workable.
Regards. Colin.
February 18, 2009 at 1:55 am
Thanks, Mike.
You must have been posting at the same time as I was replying to Raj.
What you suggested has done the trick, and I just need to cope with the NULL values that it creates.
I had tried tweaking the "INNER JOIN" text that the View designer had created in the SQL text, but I hadn't hit on the "LEFT OUTER JOIN" as you suggested.
As an SQL novice I will need to read up a bit more on the different "JOIN" options and get my head around their effects.
Many thanks. Colin.
February 19, 2009 at 8:41 am
Remember INNER JOIN is sometimes referred to as an equijoin. By design, it only returns results that satisfy both sides of the join. OUTER JOINs return all of one side and any records that match from the other side, substituting null's for missing values.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply