Composite indexes

[dalebeitz]

Greetings!

I currently have a table for salesman call tracking as follows:

tblMaster
MasterID autonumber (unique ID)
SalemanID longint (FK to salesman table)
CallDate date (the date the call was made)
<other info fields>

I have a no-duplicates index on MasterID, I have duplicates-ok indexes on SalesmanID and CallDate. Plus, the combination of MasterID, SalesmanID, and CallDate is the primary key. What I'm trying to accomplish is to prevent entry of two records with the same SalesmanID and CallDate. From what I've read in the help file I thought setting it up as above would accomplish this, but it doesn't. If I make the SalesmanID index no-duplicates, then it won't let me enter two records with the same Salesman ID even if the call date is different. Ditto on making the Date index no-duplicates.

I think I know how to do the same thing with VB validation code tied to the events in the data entry form I've created for this table, but I'd rather handle it at the table level if I could.

Any ideas greatly appreciated!
Dale Beitz

 

[r937]

try this to create the index

CREATE UNIQUE INDEX idxSalesmanCallDate
ON tblMaster (SalesmanID,CallDate)

your duplicates-ok index on Salesman alone is then possibly redundant

your duplicates-ok index on CallDate alone is probably still needed, if the table has many distinct dates and you frequently want rows for all salesmen for a given date

rudy

http://rudy.ca/

 

[dalebeitz]

Rudy:

Thanks for the tip! I'm not sure where I would enter the CREATE INDEX code you suggested. Is that something I would have to put in an event trigger as the application loads? I don't see anywhere on the table design page that would allow me to enter it.

Anyway, your tip gave me an idea that let me solve the problem a different way. I changed the primary key so that it was only the MasterID. Then I created a new composite index on the salesman ID and CallDate, making that index unique. That did the trick! For whatever reason, the presence of the master ID as the first element of the triplet primary key was causing a different behavior than I expected.

Thank you very much!
Dale Beitz