Access IIF Statement? (or maybe a "group by") query

[jschlapi]

Below is a portion of an Access query result I am working with:

Dig ID Address Date Purpose Equipment used
2 131 Frontier 08/08/2015 Main Repair Backhoe
2 Dump Truck
2 Vactor

3 555 Windy Rd 07/04/2016 Valve Repair Backhoe
3 Utility truck

I want to try to capture only year 2015 for my report. Does anyone know how I can write an if statement in Access that will essentially say: If date = 2015 AND if Dig ID is the same as the line above, then I want it in my result.
I tried to GROUP BY Dig ID # but the only line I can see in my report is the single row that has the date.

Thank you for your time & advice.

 

[Joe4]

That is bad design. According to the Rules of Normalization, each record should be complete and independent of other records in the table. You may be able to work with it, but simple tasks (like this) are made to be much more difficult than they have to be because of the design.

See this for details on Normalization Rules: https://support.microsoft.com/en-us/kb/100139

If you are "stuck" with this design, here is how I would go about it:

1. Create a query that returns just the Dig ID and Date fields, and just returns the 2015 dates, i.e.
Year([DateField]) = 2015

2. Now, create a second query which links your main data table and the query you created above on the Dig ID field, and return all the fields that you want from the main data table.
That will return records for any Dig ID with a 2015 date.

 

[jschlapi]

Thank you so much!! Your idea worked & it will get me thru to at least finish this, but I will definitely check out the link. Thanks again.