cant do duplicate entry... But nothing is written as duplicates (No)

[gangel]

Hey pplz!
Im a noobie at all this but creating one of my first databases.



Pretty much I have
Table Patients
Table Scripts
(For a pharmacy)

Patients have a ScriptID field which relates to ScriptID (Autonumber) primary key of the scripts table.

Scripts table has
Name of Drug
Date of dispensing
Cost of Drug

Now the issue is that if I create a script for a patient with the same Name, its ok, same date its ok, but if the COST is the same it comes up with "changes would create duplicates in the index, primary key or relationship"
If I make one cost $23.00 and the next 23.01 then I have no issue... so that is my current work around, but how can I let there be same entries (even though different scriptIDs... which I would have thought would stop this problem...

No items are being indexed expect primary key, and changing the cost to Indexed "duplicates OK" doesn't fix the issue either.

Please help its driving me nuts!

[ItsMe]

You are correct to suspect Indexed fields as the culprit. Unless you made edits, the only field/column that should be indexed would be the PK. As you said, the PK is the only one indexed. This sounds normal.

That leaves referential integrity rules. It sounds as though you are doing something in a form (maybe a query or table) that is creating a new record. This new record requires a value in a field (a field in the table with the new record or another table) that has a relationship, a relationship you created and asked Access to enforce "Referential Integrity".

[gangel]

Hye ItsMe,
So ive checked again and I did have a few fields as Indexed (Dup OK), so for the sake of that I changed back to not indexed.. still same issue

I think went to all relationships and changed all to not tick "referential integrity". Issue contrinues

Deleted ALL relationships to that table. Issue continues.

Am I on the corret way to test this idea or am I misunderstanding your explanation.

thanks again

[ItsMe]

...
Am I on the corret way to test this idea or am I misunderstanding your explanation.
...

Yes, you are. Deleting the relationships can have an adverse affect on the data, though. If Access does not maintain referential integrity for you, you will have to create code to manage it. Myself, I do not use the Relationships window. I prefer to use code to create new records and populate Foreign Key fields with PK values. This works for me because I use VBA and I do not typically allow deletion of records. Not being able to cascade delete records does not create additional work for me because I would not have a need to delete multiple records.

So, you need to understand what removing the relationships from within the Relationships Window does.

[orange]

Please post a jpg of your table designs. Readers need to know your table structures --fields, indexes. Unlike ItsMe, I use referential integrity and the relationships window -most of the time.
It would be helpful to readers, and you, if you would list the "business facts" involved.
Please provide a description for:
-patient
-scripts
-cost
-dispensing.

You will get more focused answers once we are all understand what you are trying to do.

As for names in Access, you will do yourself a big favor by using only alphanumerics and "_"(underscore) characters and do not allow embedded spaces in names.