duplicate date for the same client number

[Jimcb8]

Context:
I have two tables a Customer Master and Attendance Master
The Attendance Master is a subform within the main form.

The user will select the customer to be processed and all the dates that the customer has attended will be displayed in the subform. The subform is now added with the new date of attendance

Each customer can only attend once per date, no duplcate dates.
The customerID is the key field in both tables, I allow dups for the customerID in the Attendance Master.

I tried to index the attended filed in the Attendance Master, with no dups, but that only allows one date for the entire table.

How do I allow for only 1 date to be entered for the same customerID?
I need to somehow look at all the records in the table that are already posted for that customerID, and if the date is same as I am about to add, do not allow it. I don't know basic or sql that well to do this.
This is a common programming issue, but in Access, I don't know how to accomplish this check

Many thanks,

Jim

 

[AccessMSSQL]

You need to modify the actual table. Go to design view of the table and choose the date field you are trying to keep unique. In the GENERAL tab set Indexed = Yes (No Duplicates).

If you already have dups in the table you won't be able to set this unless you delete the duplicate records first.

Hope this helps!

http://www.accessmssql.com/

 

[Jimcb8]

Thanks, but I tried that and it did not give what I needed. I can have the same date in the table, but I cannot have the same date for the same Customer in the table.

'Thanks so much for your thoughts

Jim

 

[jdraw]

In your table, make the CustomerId and the RelevantDate a compound unique index.

Search for Access unique index.

If you could show us your table design, that would help.
Can you post a jpg of your tables and relationships?

 

[Jimcb8]

I made the two fields a compound index like you suggestedand that did the trick.
Thank you Thank you Thank you

I really appreciated your help.

Jim