Form field problem

[wailingrecluse]

Hi there,

I'm working on a database just now which will store user information, and headset details - for a call centre.

Currently everything is working fine, except one thing which is driving me mad: -

I have a form to add new records, containing the following fields:

Agent Novell ID - their user ID
First Name
Surname
Dept ID - when selected from combo box, the Dept name file auto populates
Site ID - as above, auto populates the site name
Manager ID - As above, populates manager name fields

There are 2 remaining fields, one (or more) of which may be causing the problem:

Headset ID - I have set this as non required so that a record can be completed, without a headset being assigned, for various reasons.
Date Assigned - Again this is not required, because it should be completed when a headset is assigned to an agent.

If i create a new record, and complete all fields, i receive an error telling me that this is creating duplicates.

If i create a new record, leave the date blank, i can save the record, including the headset id.. If i try to add the date, i get the same duplicate error message

If i create record, with headset id, save it, close the form, open the edit mode form (amend record i have named this) and add the date, it accepts it, and attaches it to the headset in the headset table.

In the design of the headset table, the date assigned field is not required, and also i have set to indexed(duplicates ok), and have also tried setting to indexed no...

Can anyone help?

Thanks,

WailingRecluse

 

[namliam]

"Creating duplicates"
What is the primary key for the table??
Are there any indexes on the table set to "allow duplicates" No

 

[wailingrecluse]

Hi

Thanks for replying

The fields for the 2 related tables are as follows:

Agents table -
Agent Novell ID; Primary key, required, indexed (no duplicates)
Agent First Name; Required, no indexed
Agent Surname; Required, not indexed
Dept ID; Required, indexed (duplicates ok)
Manager ID; Required, indexed(duplicates ok)
Headset ID; not required, indexed(no duplicates)
Site ID; Not required, indexed(duplicates ok)

Headset Table:

Headset ID; Primary Key,Not required, indexed(no duplicates)
Make; Not required, not indexed
Model; Not required, not indexed
Date Assigned; Not required, indexed(duplicates ok)
Site ID; Not required, indexed(duplicates ok)

Hope this helps, if you need more info, let me know.

WR

 

[Rabbie]

Hi

Thanks for replying

The fields for the 2 related tables are as follows:

Agents table -
Agent Novell ID; Primary key, required, indexed (no duplicates)
Agent First Name; Required, no indexed
Agent Surname; Required, not indexed
Dept ID; Required, indexed (duplicates ok)
Manager ID; Required, indexed(duplicates ok)
Headset ID; not required, indexed(no duplicates)
Site ID; Not required, indexed(duplicates ok)

Headset Table:

Headset ID; Primary Key,Not required, indexed(no duplicates)
Make; Not required, not indexed
Model; Not required, not indexed
Date Assigned; Not required, indexed(duplicates ok)
Site ID; Not required, indexed(duplicates ok)

Hope this helps, if you need more info, let me know.

WR

Your data structure should be modified.
1)Every record in the Headset table MUST have a primary key so change that to Required.
2)HeadsetID in the Agent table is set to No duplicates. This will give errors when you have more than 1 agent with no headset allocated.
3) Since you can have Agents with no headsets and headsets with no agent you should consider having a junction table to show which agent has which headset

Its structure would be

tblAgentHeadset
AgentHeadsetID(PK)
AgentID(FK) from Agent
HeadsetID(FK) from headset

4) A general point. It often make things easier to have a nonmeaningful primary key rather than a meaningful one. Just create an extra field set to autonumber in each table. This can avoid problems if say the Agent Novell ID changes.

 

[namliam]

Rabbie said it all I think, thanks

 

[wailingrecluse]

Hi there

Thanks for your response.

The reason I have set the headset ID to not required is that if it is required, this means that an agent can only exist in the agents table, if they have a headset assigned to them.

I suppose this is really the crux of the database, in that an agent SHOULD only exist if a headset is assigned, but this might make the users work more difficult... and also what happens when a headset is unassigned?

Is there any way round this?

WR

 

[Rabbie]

I believe that if you follow my suggestions you will be able to handle all agent and headset combinations. You will be able to run a query to show which agents have which headsets. You will be able to run a query to see which agents don't have a headset and you will be able to run a query to see which headsets are available to be allocated.

Every record in any table should have a primary key if it can be linked to another table. Headsets come into this category. Primary keys should be and are by default both unique and required.

 

[boblarson]

Follow what Rabbie said about the "junction table" in post #4:
http://www.access-programmers.co.uk/forums/showpost.php?p=805204&postcount=4

 

[wailingrecluse]

So you've actually given me more than I was after, haha, brilliant!!

Ok, I'll give this a blast later, and get back to you.

Thanks again for your replies chaps

WR

 

[wailingrecluse]

Hi there

I have made the changes suggested:

When I create a new record, I must now select a headset ID from the combo box, as it's now set to required. But when I enter a date, I'm still getting the duplication error "The changes you requested to the table were not sucessful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again"

Regarding the AgentHeadset table - I don't really understand this?

WR

 

[Rabbie]

Can you post your database with some sample data so we can see what your data structure actually is.

 

[wailingrecluse]

Can you post your database with some sample data so we can see what your data structure actually is.

Hi Rabbie

Not sure the best way to do this... I've attached a screen dump of the relationships...

I have exported all the tables designs to a word document, which im happy to email you if it helps?

 

[Rabbie]

Looking at your relationship diagram you have not implemented the junction table between Agents and headsets. At present you have no way of showing a headset with no agent or an agent with no headset.

You should be able to post the DB in the same way as you posted the relationship diagram. Run compact and repair on it and then ZIP it before posting.

 

[wailingrecluse]

Looking at your relationship diagram you have not implemented the junction table between Agents and headsets. At present you have no way of showing a headset with no agent or an agent with no headset.

You should be able to post the DB in the same way as you posted the relationship diagram. Run compact and repair on it and then ZIP it before posting.

Hi Rabbie... I have attached the jpg to show the inclusion of the juntion table...

I have also attached a zipped copy of the Db - please excuse the long path; because ive moved it back and forth between home and work a few times... will sort it when im finished.

 

[Rabbie]

Hi

I have had a quick look at your DB and it needs some tweaks to the design. For efficiency reasons the PK of a table should be an Autonumber rather than a text field.(it makes the indexes less bulky). Also you have no code to update the AgentHeadset table when you add an agent.


I will try and update the design and post the results back to you this evening. A bit busy at work to sneak it in this pm I am afraid.

 

[wailingrecluse]

Hi Rabbie

Yeah, I hadn't added anything into that joint table, because i dont fully understand it in all honesty....

Ok, i'll hopefully speak to you soon.

WR