Query to include all values and nulls 1

[BrianLe]

In AC97, I have the following query. How do I get it to show results even if there is no record in the tblMWHLongRangeRev or the tblMWHLongRangeAct for the selected date?

SELECT tblMWHLongRangeOrig.MWHDate, tblMWHLongRangeOrig.MWHUnit1ProjOrig, tblMWHLongRangeOrig.MWHUnit2ProjOrig, tblMWHLongRangeOrig.MWHUnit3ProjOrig, tblMWHLongRangeRev.MWHDate, tblMWHLongRangeRev.MWHUnit1ProjRev, tblMWHLongRangeRev.MWHUnit2ProjRev, tblMWHLongRangeRev.MWHUnit3ProjRev, tblMWHLongRangeAct.MWHDate, tblMWHLongRangeAct.MWHUnit1Act, tblMWHLongRangeAct.MWHUnit2Act, tblMWHLongRangeAct.MWHUnit3Act
FROM (tblMWHLongRangeOrig RIGHT JOIN tblMWHLongRangeRev ON tblMWHLongRangeOrig.MWHDate = tblMWHLongRangeRev.MWHDate) RIGHT JOIN tblMWHLongRangeAct ON tblMWHLongRangeRev.MWHDate = tblMWHLongRangeAct.MWHDate
WHERE ((tblMWHLongRangeOrig.MWHDate)>=Forms!frmMWHLongRangeReport![tbxStartDate] And (tblMWHLongRangeOrig.MWHDate)<=Forms!frmMWHLongRangeReport![tbxEndDate])
ORDER BY tblMWHLongRangeOrig.MWHDate DESC;

There will always be a MWHLongRangeOrig.MWHUnit1ProjOrig value for each date. There may or may not be a MWHUnit1ProjRev value, and there may or may not be a MWHUnit1Act value for each date.

Thanks, Brian

 

[dhookom]

Shouldn't the joins be LEFT rather than RIGHT? Double-click the join lines and make sure you are selecting all records from MWHLongRangeOrig.

Another solution would be to create a union query to combine your tables and then build a crosstab.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BrianLe]

I had tried LEFT JOIN's, but that didn't work either.

I got it to work when I added MWHDate values for both tblMWHLongRangeRev and tblMWHLongRangeAct and used INNER JOIN's.

I'll try your suggestion about a union query tomorrow.

Thanks, Brian

 

[BrianLe]

Duane, sorry it took so long to try your suggestion again. I got your first suggestion work. I hadn't double-clicked on the join lines between the tables and selected all records.

Thanks,

Brian