Doesn't show all records from a table

janise · July 6th, 2005, 04:49 AM

I made a query from a few tables, selecting song's title from one table, it's performer from another and the lyrics author from yet another.

In the resulting query it shows only the songs for which there are at least one performer and at least one lyrics author entered, but the rest of the songs are hidden.

What should I do for it to display all the songs regardless wether the other items are entered (if not, they should appear simply blank or with a pre-defined value instead)?

Thanks!

mmcdonal · July 6th, 2005, 10:18 AM

try redefining the joins in the design view and see if that helps. You want all records from tblSongs and only matching records from the other two tables.

mmcdonal

janise · July 6th, 2005, 03:06 PM

Yes, I tried that. But it says that "the SQL statement could not be executed because it contains ambiguous outer joins... :S

Loralee · July 6th, 2005, 05:40 PM

Janise,
How about trying to write the SQL using the WHERE clause, so the WHERE clause matches both the lyrics author and performer. Access gets cranky sometimes around outer joins.

HTH,
Loralee

janise · July 7th, 2005, 04:58 AM

OK, I've now rewritten the query with "Where" clause (and I don't know anything about joins, so I couldn't fix them manually anyway), and it shows the same - only the rows where the song have both the performer and aythor known.

When I view that manual query in the design view, it shows all the tables without any relationship. So, I tried to create one, and in the query the "from" clause got automatically complemented with that "join" command... And when I tried to view the datasheet, it said that the outer join is ambigous again!

leehambly · July 7th, 2005, 07:16 AM

This sounds like the kind of error which occurs when you try mixing the join types... can you post the sql for the query?

janise · July 7th, 2005, 07:36 AM

Here it is. When the query is as it is now, it doesn't give the error, but doesn't show all the songs (Dziesmas). The ambigous error appears when I set the relationships.

Thanks!

Code:

SELECT Dziesmas.Nosaukums, Dziesmas.Stils, Dziesmas.Muzika, Dzejnieki.V#257;rds, Dzejnieki.Uzv#257;rds, Dzejnieki.Grupa, Dziesmas.Vardi, Izpilditaji.Vards, Izpilditaji.Uzvards, Izpilditaji.Grupa, Dziesmas.Izpilditajs, Dziesmas.KasetesID, Dziesmas.Puse, Dziesmas.Numurs
FROM Izpilditaji, Dzejnieki, Dziesmas, [saite_Dziesmas-Dzejnieki], [saite_Dziesmas-Izpilditaji]
WHERE Dzejnieki.IDdzejnieks=[saite_Dziesmas-Dzejnieki].DzejniekaID And Izpilditaji.IDizpilditajs=[saite_Dziesmas-Izpilditaji].IzpilditajaID And Dziesmas.IDdziesma=[saite_Dziesmas-Dzejnieki].DziesmasID And Dziesmas.IDdziesma=[saite_Dziesmas-Izpilditaji].DziesmasID;

leehambly · July 7th, 2005, 09:15 AM

Its the relationships I wanted to look at... this is what would be causing your issues, could you post with the sql after you have set the relationships please.

janise · July 7th, 2005, 02:53 PM

So, when I add a relationship where I select the radio-button "Include all records from 'Dziesmas' and only those records from 'saite_Dziesmas-Dzejnieki' where the joined fields are equal.", the query automatically changes to this:

Code:

SELECT Dziesmas.Nosaukums, Dziesmas.Stils, Dziesmas.Muzika, Dzejnieki.V#257;rds, Dzejnieki.Uzv#257;rds, Dzejnieki.Grupa, Dziesmas.Vardi, Izpilditaji.Vards, Izpilditaji.Uzvards, Izpilditaji.Grupa, Dziesmas.Izpilditajs, Dziesmas.KasetesID, Dziesmas.Puse, Dziesmas.Numurs
FROM Izpilditaji, Dzejnieki, [saite_Dziesmas-Izpilditaji], [saite_Dziesmas-Dzejnieki] RIGHT JOIN Dziesmas ON [saite_Dziesmas-Dzejnieki].DziesmasID = Dziesmas.IDdziesma
WHERE (((Dzejnieki.IDdzejnieks)=[saite_Dziesmas-Dzejnieki].[DzejniekaID]) AND ((Izpilditaji.IDizpilditajs)=[saite_Dziesmas-Izpilditaji].[IzpilditajaID]) AND ((Dziesmas.IDdziesma)=[saite_Dziesmas-Dzejnieki].[DziesmasID] And (Dziesmas.IDdziesma)=[saite_Dziesmas-Izpilditaji].[DziesmasID]));

sdilucca · July 26th, 2005, 08:57 AM

you should read up on FULL JOINS. This join will display desired fields regardless if associated fields are not available. The latter will be displayed with NULL value.

"Life is a Database"