One Record Per Staff Per Day

[Boltie]

Hello,

Hopefully what I am about to ask is easily implemeneted. I'm fairly new to Access, I haven't touched databases since I left college.

I currently have two tables, Staff and Daily Record. One relationship between Staff_ID, one-to-many the one being on Staff table and many on the Daily Record table.

Staff Table
Staff_ID*
Forename
Surname
Team
WTE
LOGON

Daily Record Table
EPLOG_ID*
Staff_ID
Report_Date
Errors_Cleared
Errors_Time
Validations_Cleared
Validations_Time

I'd like to make it so that the Daily Record table can't contain duplicates in regards to a Staff_ID being input twice for one Report_Date.

Any help is appriciated.

 

[namliam]

You could try working with the Validation rule inside the table properties, or if you are inputting data via a form use a bit of code with a DCOUNT to make sure the StaffID/Date combo doesnt exist yet at the point of creation.

 

[Boltie]

Hi namliam,

I think the check when a user is entering the info on a form is the best option. I just started setting a form up after I posted so will give it a try on Monday when back at work.

Thanks for your reply.

 

[Mihail]

Create an index with the property UNIQUE on this 2 fields (at table level).

 

[Boltie]

Hi Mihail,

Unfortunately, I don't think your solution will work. I want to be able to have duplicates. For example, I don't want the table to allow the second highlighted record below.

EPLOG ID----Staff_ID----Report_Date----Errors_Cleared----Errors_Time
1-----------1-----------01/10/2013--------10---------------50
2-----------2-----------01/10/2013-----------12---------------55
3-----------3-----------01/10/2013-----------18---------------80
4-----------1-----------01/10/2013--------12---------------60
5-----------2-----------02/10/2013-----------8----------------40

 

[namliam]

Why would you want to prevent the dupe? Is the total for Staff_ID 1, 12 or 22?

You can do a DCount before saving the data into your table to preventt his from happening.

 

[Boltie]

A Staff_ID should only have one record for each Report_Date. In the above example, Staff_ID "1" has created two reports for "01/10/2013". I want many records with the same Staff_ID but also with unique Report_ID dates.

I'm struggling to use the DCount function. As I said, I'm new to Access but I do have knowledge on VBA from Excel.

I want something on the form along the lines of;

If DCount([Staff_Id] & [Report_ID], "Daily Record") > 0 Then
    Response = MsgBox("Record already present for this date by this user.", vbOKOnly, "Record Already Present")
End If

I also tried using DCount in the validation field of the table but I discovered it can't be used there. I need to look into learning the expression builder I think.

 

[Mihail]

Hi Mihail,

Unfortunately, I don't think your solution will work. I want to be able to have duplicates. For example, I don't want the table to allow the second highlighted record below.

EPLOG ID----Staff_ID----Report_Date----Errors_Cleared----Errors_Time
1-----------1-----------01/10/2013--------10---------------50
2-----------2-----------01/10/2013-----------12---------------55
3-----------3-----------01/10/2013-----------18---------------80
4-----------1-----------01/10/2013--------12---------------60
5-----------2-----------02/10/2013-----------8----------------40

Exactly what the indexes do.
Define an index on this 2 fields (Staff_ID and Report_Date) and set this composed index to UNIQUE.

 

[Boltie]

Hi Mihail,

When I do that, I can't save the table as there are conflicts with the records already present. This is becuase I have duplicates for Staff_ID and Report_ID but no duplicate with both Staff_ID & Report_ID together. Setting these fields to UNIQUE in the Indexes window states that it won't allow duplicates in just those fields alone.

If it is meant to work, I'm missing something somewhere. Am I not meant to use the Indexes window to set this up?

Thanks for the help.

*EDIT*
Thanks Mihail! All sorted now. I didn't realise I wasn't meant to enter an Index Name for all the following fields in the Indexes window.

 

[Mihail]

Uffff .
I say that the index should be defined on BOTH fields at the same time.

Look:
Open the table in design view.
On the ribbon you will see a button (the pic on this button is like a lightning). The tip text is INDEXES.
Use this button to establish a SINGLE index on both fields.

Sorry, but my English is not enough to explain step by step "how to".
I'm pretty sure that if you search the Google you will be able to learn how to establish an index on multiple fields.

 

[Mihail]

If it is meant to work, I'm missing something somewhere. Am I not meant to use the Indexes window to set this up?

Sorry again
Now I see this.
YES YES YES. That is. The INDEXES WINDOW.