How to have at most n number of duplicates in a field

Question

My question is somewhat unique I assume. Everybody wants to get rid of duplicates, but me on the other hand, I want to allow a specified number of duplicates to appear.

I'm creating a database for reserving dates for Blood donation campaigns.

My database consists of the

Date of campaign,
Name of organizer,
Contact info & venue.

Additionally every campaign will get a unique ID number. The catch is, we can accommodate only five campaigns per day, that is: The date column should be allowed to have at most five duplicates

Any ideas on how this can be achieved?

My table contains only limited details. Date of campaign, Name of organizer, contact info & venue. Every campaign will get a unique ID number. The catch is, we can accommodate only five campaigns per day. So the date field will have to hold 5 duplicate values & no more. Is this possible? — Jeevendra Bandara Ranasinghe, Aug 18 '14 at 08:41
Hi, how are you getting this information? Is it using a Form? If so is it bound or unbound. If unbound, what is the event associated with the commit of the Data to the table. — PaulFrancis, Aug 18 '14 at 11:00
Hi Paul... We input data directly into the table. We receive calls from people who are interested in organizing a campaign. We reserve the date as per their request. But we can schedule only 5 per day. — Jeevendra Bandara Ranasinghe, Aug 18 '14 at 11:25

score 1 · Answer 1 · answered Aug 18 '14 at 13:37

1

If you are entering the information directly into the table from within Access 2010 itself then you can add the following Before Change data macro to the table:

For more information on Data Macros see

Create a data macro

answered Aug 18 '14 at 13:37

Gord Thompson

116,920
32
215
418

score 0 · Answer 2 · answered Aug 18 '14 at 11:38

This is what you need to do. Create a New Form, based on the table. A lot more neater and also can capture all the information you need along with special conditions, like the one you have right now. The form can have all or some fields that are present in the table. Once the forms has been created, we will call it frm_EventMgm

You can create another Dashboard/welcome form where you can have one TextBox, for collecting the date you want to enter (dateEntryTxt) in the table and one button (addNewEntryBtn), which on lick will have this.

Private Sub addNewEntryBtn_Click()
    If Not IsDate(Me.dateEntryTxt) Then
        MsgBox "Please enter a valid date before you proceed.", _
                vbCritical, "Missing Information !"
        Exit Sub
    End If

    If DCount("*", "tbl_EventList", "DateFieldName = " & _
                Format(Me.dateEntryTxt, "\#mm\/dd\/yyyy\#")) > 5 Then
        MsgBox "The Date you selected has already have 5 bookings. " & _
                "So please choose another date and try again.", vbInformation, _
                "Cannot Add info. !"
    Else
        DoCmd.OpenForm "frm_EventMgm", DataMode:=acFormAdd
    End If
End Sub

This is the best and easy way to get around your issue. Hope this helps.

How to have at most n number of duplicates in a field

2 Answers2