Append SQL-duplicate record found then warned message

[xaysana]

Hi there,
I am currently creating an inventory database.
I have form called Products links to product table and subform of transaction table. The product form enables user to either add records into two columns of Sold and Subsidy in transaction table manually or click a button called Add records to Subsidy, this will append records into subsidy column. Clicking an Add records to subsidy basically runs the append SQL. Now this works fine However, this allows user to add records continuously regardless of how many time.
Now, what I want to do is that to limit of running SQL by clicking a button or if InventoryTransaction.PlantID equals to Enter PlantID then appears the message “Duplicate record found”:
For more detail, please refer to the attachment.

Any assistant would be appreciated.
Thank you in advance
Best regards,

 

[PNGBill]

If the record being appended also has the Unique field from the other table. eg InvoiceDetail table records all have a field from InvoiceHeader table.
Make "No Duplicates Allowed" in the InvoiceDetails table when combining the two fields ie, InvoiceDetail table will only allow One Record to have Unique of both ProductID and InvoiceHeaderID as Combined Primary Key.

 

[xaysana]

Thank you so much for your advice PNGBill.

I hoped you had a look at my sample database in attachment.
In my case, I have 11 products to append to InventoryTransaction table by running sql. I want to happen only if the product are subsidised to one district (one area) that is why it needs to show that those 11 products has same PlantID (Note: InventoryTransactions table has many-to-one relationship to Plants table). therefore one plant has one unique PlantID and 11 products inventoryTransactions table has same but many PlantID showing.

What I want to do is: When I run the append sql, the warned box appears to say: Enter PlantID, now in this box it allows to enter the number (depends on Plant.PlantID). If i enter number one (1) it should append to InventoryTransaction.PlantID if there is no existing number one (1) if there is then appears warning message "the record is existing".

Could you think off any workaround please?
Thank you in advanced
Best regards,

 

[PNGBill]

Opened your database but it is read only.

You should set the relationship between Products table and InventoryTransactions table. Connect ProductID. One (Products) to Many InventoryTransactions).

It is easy to add a message to a query. Just enter your message in the Criteria of the field (PlantID) and the sql will ask for the input.

As I mentioned earlier, Change the Primary Key of InventoryTransactions table to be combined Primary Key on ProductID and PlantID. This will prevent an occurrence of the same product listed twice on the same plant.

Try this on a copy Database - I can't because mine is Read Only.

If this works then the system will provide an error message.

I suggest you get it working and then get advice on each issue as you Automate it.

If the combined Primary Key works then this is a good way to prevent duplicates and the warning message can be refined as step two.

You could setup queries to check first for duplicates but why bother if the system does it for you.

 

[PNGBill]

Your Table FixedSubsidy - Shouldn't use "ID" as a field name.

You need to relate this table to another table or how will your query work.

If a District has a subsidy then have a field in your Districts table to show the connection.

 

[xaysana]

Opened your database but it is read only.

You should set the relationship between Products table and InventoryTransactions table. Connect ProductID. One (Products) to Many InventoryTransactions). I did


It is easy to add a message to a query. Just enter your message in the Criteria of the field (PlantID) and the sql will ask for the input. I did.

As I mentioned earlier, Change the Primary Key of InventoryTransactions table to be combined Primary Key on ProductID and PlantID. This will prevent an occurrence of the same product listed twice on the same plant. I don't quite understand this bit. If I am correct, once I set PlantID in InventoryTransaction table as primary key then it can not contain null value. Besides, the plantID column I also have Subsidy and sold columns. These columns might require value too. Maybe I will try to separate Order and Received columns to different table and connect to Product table. Also as you suggested that have Sold and Subsidy columns in district tables too.

Try this on a copy Database - I can't because mine is Read Only. I wonder why it appears as read only. Do you me to send you another copy? What Access version would you prefer?

If this works then the system will provide an error message.

I suggest you get it working and then get advice on each issue as you Automate it.

If the combined Primary Key works then this is a good way to prevent duplicates and the warning message can be refined as step two.

You could setup queries to check first for duplicates but why bother if the system does it for you.

Thank you so much for being with me PNGBill,
Also thank you so much for valuable advice.

I will try that your suggestion and will get back to you soon.
Thank you
Regards,

 

[PNGBill]

I suggested you use two fields for your Primary Key.

This is possible and, yes, you will need both fields to have data but won't all your records have data in these two fields ??

Why would you have a record in the InventoryTransactions table that doesn't have both a PlantID and ProductID?

You can have a Default Record. Surely all records will have a product but lets say they don't all have a plant (don't know why??) you can add a plant called "No Plant".
Use this in a query to return all the Product records for No Plant and you know which products need to be edited.

I must say the issue is a little complicated so just be careful about taking advice when there may be confusion.

Your earlier post is talking about adding fields to tables. I only suggested one field to add, I think. This is because I could see any field in your District Table to relate to Subsidy yet I note your post mentions adding more then one field.

 

[boblarson]

Opened your database but it is read only.

It isn't for me, so it would be a windows setting you would need to remove. Right click on the file and then there should be a security setting which says something like it came from an untrusted source and you can uncheck the box and it should not be read-only anymore.

 

[boblarson]

As I mentioned earlier, Change the Primary Key of InventoryTransactions table to be combined Primary Key on ProductID and PlantID. This will prevent an occurrence of the same product listed twice on the same plant.

For a transaction table you do NOT want to have that composite key. You would NEED to have the same product listed many times for the same plant. However if you ADD the Transaction DATE on as a THIRD part of that, then it would be fine (although I would just use a multi-field INDEX to enforce that and not use a composite key - it will simplify things durilng reporting).

 

[xaysana]

Thank you so much guys for all those in puts.
There is so much confusing here that is why I would you to take a look at the sample sent. COZ that would help sorting out the issue.

How are you going with opening a copy I sent? Did you manage to open as not read only?

Thank you
Best regards,

 

[xaysana]

For a transaction table you do NOT want to have that composite key. You would NEED to have the same product listed many times for the same plant. However if you ADD the Transaction DATE on as a THIRD part of that, then it would be fine (although I would just use a multi-field INDEX to enforce that and not use a composite key - it will simplify things durilng reporting).

Thanks BOBLARSON,
I will that in mind.
Regards,

 

[PNGBill]

Yes, Thanks Bob, I can open it now.

xaysana, If you have made changes and still need help, attach an updated database.
I use 2000 so no problems with what I have.

If the changes are simple, set them out and I will replicate them on the copy I have.

 

[xaysana]

Hi PNGBIll,
Again, thank you for bearing with me.
I did not change much apart from adding main switchboard and report. I think you can go ahead with a copy you have. Please keep in mind that the most priority is to search for same number in InventoryTransaction.PlantID with a number in SQL (Enter PlantID) keyword, if same number found then show message.

Again, thank you in advance
Best regards,

 

[PNGBill]

Am playing with your Database and it is confusing.

Do Products/Plants?? always have Subsidy?? What has he Subsidy, The Product or the Plant or the Province or the District??

Please explain your Products Table.

If you have Provincial Code, you don't need Provincial Name as the system can find this when req'd.

Can One Product have more then one Province??

What are the Plants?? do they manufacture or just warehouse the products?

Can More then One Plant have the Same Product ?

If the Plants have Products then you should have ProductID in the Plant Table.
This way, Product is One to Many Plants. Often the case.

Then a query can list all the records where Product 001 is shown as In Stock in the Different Plants.

Confirm the above questions please so I can try and understand what is supposed to happen.

 

[xaysana]

Am playing with your Database and it is confusing.

Do Products/Plants?? always have Subsidy?? What has he Subsidy, The Product or the Plant or the Province or the District??
Plants always have subsidy. Where Products table only holds product list for each province and link Inventory transaction table so that they are connected to subform in products form. The subsidy occors when we setup a plant (Biogas hole) for free. One hole used 11 products (from product table). In Inventory Transaction table, there has sold and subsidy columns. all 11 products are ideally filled in to subsidy column however, if villager needs to buy more product e.g (because pipe is not long enough or need one stove)then product will be put in to Sold column.

Please explain your Products Table.

If you have Provincial Code, you don't need Provincial Name as the system can find this when req'd.
Basically, I can remove provincal name but then it will only show provincal ID in the products form. I would rather like to have Provincalname code.

Can One Product have more then one Province??
Yes, because one is ordered and received by one organisation distribute to many provinces in one country.

What are the Plants?? do they manufacture or just warehouse the products?

Can More then One Plant have the Same Product ?
yes, Firstly products are subsidized (give for free) put in Subsidy column if same product required (purchased by villager) then goto Sold column.

If the Plants have Products then you should have ProductID in the Plant Table.
This way, Product is One to Many Plants. Often the case.

Then a query can list all the records where Product 001 is shown as In Stock in the Different Plants.

Confirm the above questions please so I can try and understand what is supposed to happen.

First of all, I like to say thank you to you guys for baring with me.
and I am so sorry for not giving you clear detail of what am trying to do.

Thank you so much for your kind assistannnnnnnnce.
Best regards,