Query Null rows !

[ma77smith]

Hi All,

Got a prob with my Query, I have 7 tables - three of which are tables which just break the many to many relation of the other four tables (as u can see from attached screen shot)

Not many drawings have both project ID / Cad ID and spec ID data related to them - but the query is only pulling in the data where all these relationships exist (17 rows) - when there are like 400 drawing names.

How can i change this query to list all the drawing names wether they have cad/spec/ or project data related to them or not !!

SELECT [Drawing Detail].[Drawing Name], [Drawing Detail].[Drawing Description], [Drawing Detail].[Issue Number], [Specification Detail].[Specification Name], [Project Detail].[Project Name], [CAD Data Detail].[CAD Data Name]
FROM ([Project Detail] INNER JOIN (([Specification Detail] INNER JOIN ([Drawing Detail] INNER JOIN [Link Drawing And Specification No Obsolete] ON [Drawing Detail].[Drawing ID] = [Link Drawing And Specification No Obsolete].[Drawing ID]) ON [Specification Detail].[Specification ID] = [Link Drawing And Specification No Obsolete].[Specification ID]) INNER JOIN [Link Drawing And Project No Obsolete] ON [Drawing Detail].[Drawing ID] = [Link Drawing And Project No Obsolete].[Drawing ID]) ON [Project Detail].[Project ID] = [Link Drawing And Project No Obsolete].[Project ID]) INNER JOIN ([CAD Data Detail] INNER JOIN [Link CAD Data And Drawing No Obsolete] ON [CAD Data Detail].[CAD Data ID] = [Link CAD Data And Drawing No Obsolete].[Cad Data ID]) ON [Drawing Detail].[Drawing ID] = [Link CAD Data And Drawing No Obsolete].[Drawing ID]
GROUP BY [Drawing Detail].[Drawing Name], [Drawing Detail].[Drawing Description], [Drawing Detail].[Issue Number], [Specification Detail].[Specification Name], [Project Detail].[Project Name], [CAD Data Detail].[CAD Data Name]
ORDER BY [Drawing Detail].[Drawing Name] DESC;

Tried to explain the best I could, any help will be appreciated thx

 

[SilentBreaker]

This is not a "KISS" (Keep It Stupid and Simple) stuff. To reduce the complexility, try to remove three little tables (Link Drawing And Spe.. , Link Drawing And Proj.. and Link CAD D...) and add three columns of Specification ID, Project ID and Cad Data ID to the Drawing Details table. And connect them (fields' IDs) to the necessary tables. Click on the join properties and choose the "Include ALL records from 'Drawing Detail' and only those records from 'little table' where the joined fields are equal".

Hope this help you, cheers

 

[AncientOne]

To display records from the drawing details table that do not appear in any of your join tables, you have to create left outer joins between the main and join tables. However, you will then get an error about "ambiguous outer joins".

So the answer is to run separate queries combining the data you require from the tables on the right of your diagram and the link tables and then to run a query combining those queries with the main table using the left joins I described.

 

[ma77smith]

Thanks Ancient- suspected this was the solution but I thought I was doing it wrong because of the join error.

I'll have to learn how to nest these three queries then !!

Unless you can give me a bit more info or point be to any good resources on the net !!

Cheers

 

[AncientOne]

Yes, I understand what you are saying. Have you understood my explanation? With your setup only matching rows will appear. If you follow my plan, matched and unmatched rows will be displayed. Therefore, I don't think you can have done what I suggested.

 

[AncientOne]

OOps, we seem to be at cross-purposes here. Who's Wolf? did you delete a previous post? NB-you don't need to nest queries!

 

[ma77smith]

To display records from the drawing details table that do not appear in any of your join tables, you have to create left outer joins between the main and join tables. However, you will then get an error about "ambiguous outer joins".

So the answer is to run separate queries combining the data you require from the tables on the right of your diagram and the link tables and then to run a query combining those queries with the main table using the left joins I described.

Sorry about getting your name wrong (late night)

I have tried doing what you suggested yet as I'm not sure how to combine these queries

 

[AncientOne]

The simplest solution is for you to zip up your db and post it. you can send to my email address if it's too large for this forum.

If that's not possible, start by doing the following:

qrySpec

both fields fom Link Drawing and spec, Specification name from Specification details

qryProj

both fields fom Link Drawing andProj, Project name from project details

qryData

both fields fom Link CAD, CAD Data Name from CAD Data

QryCombined

All the fields you require from drawing detail, each of the name fields from the three queries above. Each Drawing ID field in the three queries to be joined to the DrawingID field in the drawing details table by an arrow pointing out of the drawing details table.(to edit joins click on them in design view, select "select all records from drawing detail....)

Let's see how that goes.

NB- your query was a totals query, but it doesn't need to be!

 

[ma77smith]

AncientOne

You're a star, worked great !!


Thanks


- Thx to everyone else who helped as well

 

[Scanner29Y]

I Thank the Lord for you guys!

I had the same problem and was driving me nuts!! We tried Union Queries, Left Joins, all flavors of outers (though it got the closest still missing something), until we finally prayed and BOOM!, there was your answer....


Ancient One, keep it up dude! 'cause ya'll make the difference!!

Thanks everybody!.