Challenging Access question: conditional date range query between two tables

Posted on 2012-09-03
Last Modified: 2012-09-04
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!

Question by:kbdaemon
    LVL 119

    Accepted Solution

    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))

    Author Comment

    Will do in about an hour just left office

    Author Closing Comment

    Thanks! that gets me exactly what I need.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now