when updating the description of a part then create ECN report showing which products it has effected (1 Viewer)

[rainbows]

hi , the form below shows that i have 129 different products that contain parts taken from the stocklist. the product below shows there are 21 items to produce that product . if we take line 1 for example it has a stock number 7505048-01 . this stocknumber could also be used in other products lets say 10 others. want i would like to be able to do is when i modify the material description for stocknumber 7505048-01 i get a report ( ie an engineering change note) telling me which was effected

i really have no idea how to start this

thanks
steve

 

[Gasman]

Really depends on the structure of your DB?
I would expect a Product table linked to the Parts table, with the Product being the parent table and the Parts the child table.

 

[rainbows]

how would you trigger that that part has been changed in the say in the stocklist table ( which automatically changes the part in the product details table as you can see line 2 part is used in product id 36,19,103, 43,22,23,30 so for example if i change the description of the part in the stocklist it will change the material description in the above 7 products
should it be a macro to say after you change that part look for product effected and list them and report it ??
steve

 

[Gasman]

I would store the old description, then run a report that shows you what products that stockID is used in. Perhaps save as a pdf file as well?
Even ask if the user wants a report before creating it?
I must admit, I am not sure why you need this, if it is just a description?

 

[Pat Hartman]

Are you duplicating part related data in other tables? That what it sounds like you are trying to cater to. In a relational database, we do NOT store the same data in multiple places. That means that when you change the nomenclature for a part, you change it in one and only one place - the part table. All other places where you would want to display the nomenclature, would obtain the value by joining to the part table.

Any place where you want to refer to a part, OrderDetails or an invoice for example, would only store the ID of the part. All of the other fields associated with part are obtained by using a join to tblPart and selecting whatever columns you want to display on the current object.

 

[rainbows]

i do have a report that shows me which products use that stockid and it is a requirement that we have a full log on all the changes we do on a product. even the text change. add parts , delete parts etc but i need to then run a report that shows you what products that stockID got changed in somehow i think it needs to use the after update property looking at the stock number and somehow listing the products

steve

 

[rainbows]

i am only changing the description in the stocklist file. the stock item is then selected and placed into the product details , table

i have used the drop down box ready to add another part to that product this comes from the stocklist

 

[Pat Hartman]

I'm not sure I understand the scope of what you are trying to show. Since data is stored in only one place and therefore only changed in one place, that would be where you would do the change log. You can do this with a data macro if you are comfortable with that. Or, you can use one of audit log suggestions you will find if you search here.

But, even though data is only changed in one place, the effects of that change can be seen in multiple places. Are you trying to capture that in the audit log also?

 

[rainbows]

But, even though data is only changed in one place, the effects of that change can be seen in multiple places. Are you trying to capture that in the audit log also


Yes that change will be in the stock list table and several places in the product details table. As it is related to the products table so I want to capture all the products that that change effected

 

[Pat Hartman]

We are in a tight loop. Are you storing the same data in MULTIPLE tables?

 

[rainbows]

Yes in 2 tables
Stock list and product details

The stock list Carrys All Items to purchase even non product related parts like pens. Paper paint etc

The product detail table Carrys
All the parts related to products that was selected from the stock list table

 

[Pat Hartman]

It is time to normalize your schema so that you don't store the same piece of information in multiple places. That will resolve your problem. Products are products and you should use only a single table. You can use a type code to help you to separate the parts so they won't show up in the same pick lists.

 

[rainbows]

I am confused. It you had a table with peoples names in that's one table
If you wanted to select a group of people to do tasks them people would now be in 2 tables ? And if a certain person was selected to do many tasks his or her name would be in a table many times? Or I am missing something?

 

[Pat Hartman]

You may be using table level lookups and that could be confusing you. The task table does NOT have the name of the person doing the task, it holds the PersonID which is the foreign key to the person table. If you want to see the name of the person in the task list, you would create a query that joins the two tables. In some cases, you might want to use a combo box which will be bound to the PersonID in the task table but which shows the person's name.

Remove the table level lookups and the confusion should disappear since you will then know what is actually stored in each table.

 

[Cronk]

You're missing something - a join table between your products and parts.

In your post #3, the same part appears multiple times eg ProductDetailID 2267,1167, 542, 580 etc

If ProductID 904 had 3 of PartID 204, the join table would have an entry ProductID with 904, PartID 204 and a Quantity of 3.

Use a date to record when a particular part is changed so you can generate a report of anything changed in a particular period.

 

[LarryE]

I am confused. It you had a table with peoples names in that's one table
If you wanted to select a group of people to do tasks them people would now be in 2 tables ? And if a certain person was selected to do many tasks his or her name would be in a table many times? Or I am missing something?

Take the time to learn how to use ACCESS properly with table Primary Key and Foreign Key relationships.

In the case you sited, a peoples name table would have a Primary Key AutoNumber Data Type field that is automatically entered each time a new record is created in the people table. This Primary key field would also be added to your tasks table as a foreign key. The people table primary key field is then linked to the tasks table foreign key field creating a one-to-many relationship. And yes, the tasks table could contain many of the same peoples tables primary keys. This means one person may have many tasks defined in the tasks table, but the persons name is only entered one time in the people table.

 

[rainbows]

I believe I have everything you are all saying . And yes the name is only entered once in the peoples take . And yes the peoples name is entered many times in the task table .let's say name Fred was on 10 tasks But let's say after a week or so I discover I spelt it wrong ie freb or I wanted to use another person for them tasks say john
How do I record what a that name was or spelt wrong ie freb on all the tasks you were on

So I would like a report that told me task no 1 4 6 9 had the same freb and now is fred
I now have a audit trial for them tasks I changed

Even if it was the persons name ID it would still be in many places in the task table and if I changed that person for that task I still need a report to tell me it was say person ID was say no1 but it is now no 2

Hope this helps

Thanks steve

 

[rainbows]

I have attached part of the database that i am trying to get the report from

if you look at the query you will see there is a material id 4804 which is being used in 6 product . if i change the description on the material id
to say stainless steel. how can i create a report that says materialid or material description has been changed on this 6 product. to stainless steel and if possible from what to what it is now

thanks steve


Product Detail EGN Product Detail EGN

Product No
FC2104M1
FC3893M1
EC3244M3
EC3144M1
EC1884M1
EC4451M1

 

[Gasman]

So you already have a query that can identify products with a certain MaterialID?
You just filter that with the MaterialID

So you could base your report on that query, with the criteria. You know what it was as you can store that and pass to the report.
As the new description is going to be the same for all those records, you would be better off showing the Product Descrription with the ProductID.

So a report that states in the header
Material ID 4804 was changed from & OldDescription & " to " & NewDescription

Products affected are
Then list the productIDs and their descriptions in the Detail section.

Here is the result when you filter your query.

 

[rainbows]

but i dont know how to get the old and new description in the report as my query shows all the records for all stock items and how it knows i have just changed that materail id only