Solved Correct builiding step (1 Viewer)

[Pat Hartman]

I have to have some fields the same becouse this way user will know what item he is using.

That isn't the way relational databases work.

We talk a lot about "normalization" which is the process of defining entities and attributes and ensuring that attributes will not be duplicated in related tables because that leads to potential inconsistencies. i.e. different values for the same attribute in different tables. For example. You would have CustomerName as an attribute in tblCustomer but it would never occur in tblOrder because tblOrder would include a foreign key (CustomerID) which points back to CustomerID which is the PK in tblCustomer. What we don't talk about specifically is how to turn all those normalized tables back into useful data. That we do by using queries. So, if you have a form where you want to show the CustomerName for an Order, you would use as the RecordSource for that form, a query that joins tblOrder to tblCustomer so you can pick up related values. For safety, you would NEVER allow columns from tblCustomer to be updated on a form which is intended to enter an Order. Therefore, on the Order form, ALL controls bound to columns selected from tblCustomer would be locked to prevent changes.

 

[Mike Krailo]

well first table on the left. This is the main table for the items named Colons. tblKolone2. here are princpial information about the items.

This doesn't make it any clearer. Why would you have principal information outside of the main table? Now if what you meant is unique information/attributes about the item that are not held by any other item is in the second table, then that makes more sense.

For example these field are urgent: becouse here i am using signature ( signof form, which analyst confirm that all the data he entered is correct, analyst must signed and approver must signed )

then another fields that maybe are the same is that every time who used the item must enter from wich department is, what is the status of the item when he make analysis and what system he used.

But you have two status fields? Unless we are talking about more than one item, there isn't a good reason to have two different status's that I can see. It just doesn't make any sense. You have one item, and that item has one status at any given time. The only reason for duplication is for historical tracking of status changes into one big history table. But that should not require user intervention at all.

Now here is important now 3 things:
We have to track correctly where the item is now -where analysis is done which department make the analysis, becouse items are like book it can be borrowed with different departments and after every use they can have different status we have to know correctly what is the status of the item ( if is in use if is relased if is destroyed) and what sistem was used for the item.
These mean that my codes in the events on the form must correcty update the statuses, department and the system.
If all that is correct the user will exatly know who has the item, what is the status of the item, and for which system is the item.
The goal is that users will know in 5 second where the desired item is. Now they are having all that in paper and they are looking for items and waste time to find who exatly have the item.

This requirement seems to suggest the use of a history of status changes and approvals. It requires that the user must update the status when they have returned/checked out the equipment/instrument after using it. It also reflects the data you are showing above about the user and status. The only concern is the approval process. If someone returns or checks out an item, does that user first have to request this from an approved user (the one who approves things)? If there is a gap in time or date when this happens, then wouldn't you want separate transactions to be recorded? Right now you have one record with the two different times and dates. It would help if you clarify the exact timing of the process of checking out one item and getting it approved, and finally getting the item transferred to the user and department that is doing the analysis.

 

[Pat Hartman]

Here's a link that might help you to understand how related forms work.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Take a look at the Employees form. The subform is bound to a junction table that connects employees to classes. The combo box in the subform is how the connection is made.

 

[lacampeona]

Hi Pat
ok I will look for you example.
Thank you very much.

 

[lacampeona]

Hi Pat
ok I see. So I have a lot of work to do now. I have to change all my forms....and repair my relationship.
I never make form in that way. Ohh ok ok I have to learn
Thank you.

 

[Pat Hartman]

Here's a 1-m sample if that is what you need. It is the same concept except that there are only two tables involved rather than three. The 1-m is the most common situation.

 

[lacampeona]

Hi Pat
thanks for you examples. I will check.