Requirement change? (1 Viewer)

Hi everyone,

In one of my projects I have a table that holds commission data.
Table is
CommissionID
EmployeeID
CommType
CommAmount
CommValue
Reason
ProcessedDate

From a previous thread I was expecting to look for all records with a ProcessedDate of the previous month as records would be added weekly and each new month I would need to total CommValue for each employee and Commission category.

However I have now been informed that the commission records would not be added as I expected (weekly), but in an adhoc manner.
The key dates to look for now would be a risk date, yet to be added somewhere.
This date only applies (currently) to a one category of the commission records.

I know I could add another field to the table for the risk date, but that would be null/empty for a large set of records? Easy to implement though? (for me at least). At present the records are added via a datasheet subform linked to the Employee combo box on mainform.

I believe I could create another table to hold the risk data and now also the CommAmount data from the Commission record, but the relationship would be 1 to 1? Then I would have the problem on how do I implement this in the form/subform?:banghead: At present mainform/subform are simply based on tables linked by EmployeeID.

We are not talking about a larger amount of data here, but I am keen in trying to 'do it right' if possible and learn more?

How would the experts implement this requirement please?

TIA

keep it in the same table and leave the date null or populated with some date or other, it doesn't really matter because your criteria will be filtering by category and risk date where category equals a particular value. If category doesn't equal that particular value, the risk date will be ignored.

You can modify the index for the risk date to ignore nulls (click on indexes in the ribbon, select the relevant index and change the ignore nulls property from no to yes)

Thank you CJ.

Thanks also for the tip on the nulls and an index.

just an aside


do not try to include the risk date in the primary key, and also watch out if including it in any multi-column key.

if you set a multi-column index to unique, you can still get duplicates where the risk date is null. ie all the other fields are the same, except for the nulls.

Hi Dave,

TBH, I'd forgotten about setting various indices.
I will just be using a query to look for a risk date now in the previous month instead of the processed date. Just need to add the risk date to the table and amend a little code.