Parameter based query on one table but 5 fields

[P5C768]

I need to create a query that allows the user to enter a start date and end date (will usually be the beginning and end of a month) that pulls in records for commissions to be paid in that time period.

The monkey wrench is that the query will be based on an effective date AND a combobox dropdown that specifies whether the commission will be paid annually, semi-annually or monthly. So for example, if I wanted to run the report for this month, it would find any commissions with an effective date in June for annual, December for semi-annual and all commisions paid monthly.



To top it off, this needs to happen on 5 different effective date/renewal frequency combinations (a total of 10 fields). I would evetually like to base a report on this query to pull in any commissions to be paid.

I have attached a sample of my database for reference. Thanks in advance!

[kimmer]

To top it off, this needs to happen on 5 different effective date/renewal frequency combinations (a total of 10 fields). I would evetually like to base a report on this query to pull in any commissions to be paid.

Yes, you do have 10 commission fields and many other repeating groups of fields in the single table file. I think you are going to have trouble after trouble unless you use related tables instead of a single flat table.

First Normal Form: a table contains no repeating groups of fields.

http://support.microsoft.com/kb/100139