Pull records from an elements table using date ranges in another table

[Smitoris]

I'm trying to develop a database with three tables.

I need to write a query to pull a monthly list of element data based on multiple date ranges from another table. If the element lives within the date range specified for that division then I need it to show in the query. The date ranges differ by Division but I need them all to pull into one query so I can link it to SharePoint and filter the data.

I then need a calculated field added to the query that tells me if an element is new based on a set date in a third table. If the element StartDate is equal to one of the SetDates for that division in the SetDate table then it is "New." Otherwise it is referred to as "Carry Forward."

Attached is a pdf showing the proposed structure of the tables and the information the query would pull.

Any help would be greatly appreciated, whether advice on the DB structure or just some Select statements to get me started. I am familiar with VBA code in Excel but don't have much experience using Access other than simple queries.

Thanks,
Neal

[rpeare]

Assuming your tables and field names are exactly as you posted them:

SELECT Elements.Division, Elements.StartDate, Elements.EndDate, Elements.PartNo, Elements.Description, Elements.Etc1, Elements.Etc2, Elements.Etc3, IIf(IsNull([SetDate]),"Carry Forward","New") AS CalculatedField
FROM (Elements LEFT JOIN Ranges ON Elements.Division = Ranges.Division) LEFT JOIN SetDates ON (Elements.StartDate = SetDates.SetDate) AND (Elements.Division = SetDates.Division)
WHERE (((IIf([elements].[startdate] Between [ranges].[startdate] And [ranges].[enddate],1,0))=1)) OR (((IIf([elements].[enddate] Between [ranges].[startdate] And [ranges].[enddate],1,0))=1)) OR (((IIf([elements].[startdate]<[ranges].[startdate] And [elements].[enddate]>[ranges].[enddate],1,0))=1));

I left the selection of the dates as three separate fields to show you what I did without creating one long formula. But basically when you check the date you're checking three sets of possibilities

Is the start date in elements between the start and end date of ranges
Is the end date in elements between the start and end date of ranges
Are the start date and end date of elements completely contained within a single span of ranges.

[Smitoris]

Thanks for taking the time to look at this rpeare. I'm still waiting on element information so I'm not able to test the statement. I'll follow up if I have any questions. Again, thank you.