Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

Challenging Access question: conditional date range query between two tables

I have two tables in Access 2010 (database4 is attached) with test data.  One table contains agents by code and the amount of commission they earn for selling product "WLLB".  The other table contains commission levels by date.  Commission contracts change over time and we are reconstructing some really old data for export to a newer system (for records purposes).

I need to create a query to return records where:
1) [AgentTestRecords].AgtComm = [CommLevelsByDate].CommPctYr1 AND
2) [AgentTestRecords].PolType = [CommLevelsByDate].ProductCode AND
3) [CommLevelsByDate].ANBTo = 70 AND
4) [AgentTestRecords].ContractDate => [CommLevelsByDate].ValidFrom and <=[CommLevelsByDate].ValidTo

The result set would be: [AgentTestRecords].Agt as AgentCode, [CommLevelsByDate].ProducerTier as ProducerTier, [CommLevelsByDate].ValidFrom as CommScheduleDate and [AgentTestRecords].ContractDate as ContractDate

So, I know what I want but I am in over my head on doing something that looks like a union with a bunch of conditionals versus my simple select query experience.  Thanks in advance for any help!

  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:
try this query

SELECT AgentTestRecords.Agt AS AgentCode, CommLevelsByDate.ProducerTier, CommLevelsByDate.ValidFrom AS CommScheduleDate, CDate([ContractDate]) AS Contract_Date
FROM AgentTestRecords INNER JOIN CommLevelsByDate ON (AgentTestRecords.PolType = CommLevelsByDate.ProductCode) AND (AgentTestRecords.AgtComm = CommLevelsByDate.CommPctYr1)
WHERE (((CDate([ContractDate])) Between [CommLevelsByDate]![ValidFrom] And [CommLevelsByDate]![ValidTo]) AND ((CommLevelsByDate.ANBTo)=70))
kbdaemonAuthor Commented:
Will do in about an hour just left office
kbdaemonAuthor Commented:
Thanks! that gets me exactly what I need.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now