QRY Linked TBLS, bring up info on TBL 1, and any info on TBL 2 with certain status

[neockder]

Hi there,

I've created a report that brings up all associated information for a selected record, and any information from a second table with a certain status. When there isn't any information on the second table with that status, it won't display any of the information from the first table..



What I'm trying to achieve is to display the information from the first table, and IF there is any associated records on the second with that stauts, bring that up too, but if not, then just display the info from the first table and leave the other fields blank.

Essentially the report is blank if there isn't any information on the second table with that status. I've messed with the JOINS as well as pretty much everything else I could think of to get it to work - to no avail..

Please Help!! Here is an example of the code:

SELECT brews.brewID, brews.style, brews.KOs, [recipeDefinitions].[quantity]*[brews].[KOs] AS [DryHop-lbs], recipeDefinitions.ingredientName, recipeDefinitions.quantity, recipeDefinitions.recipeID, recipeDefinitions.stage
FROM brews LEFT JOIN recipeDefinitions ON brews.recipe = recipeDefinitions.recipeID
WHERE (((recipeDefinitions.stage)="DH"));

[June7]

Query is working properly. The WHERE criteria applies to each record of the joined dataset, not just the recipeDefinitions table.

Do a query that filters recipeDefinitions.

Join that query to brews table with join type "Include all records from brews ..."

[neockder]

Sweet, I'll give that a shot right now.. thanks for the quick reply!

[neockder]

Woohoo! Thanks June. Works exactly how I need it to. I owe you a beer

mark as Solved?