Solved Correct builiding step

[lacampeona]

Hi
thank to all for all the information.

 

[lacampeona]

Hello experts,
Ok so now I have one big table for all items.
I am "stuck" with statuses. StatusItem.
I have 4 diferent items and of course these items can have very different statuses.

I think if I just say StatusItem, that is ok?
and then I put inside tblStatusItems: StatusID (PK), typeID (FK), , StatusName

typeID1 is Book
typeID2 is Medicine...

how would you make that?

Thanks

 

[GregDataReno]

Hello experts,
Ok so now I have one big table for all items.
I am "stuck" with statuses. StatusItem.
I have 4 diferent items and of course these items can have very different statuses.

I think if I just say StatusItem, that is ok?
and then I put inside tblStatusItems: StatusID (PK), typeID (FK), , StatusName

typeID1 is Book
typeID2 is Medicine...

how would you make that?

Thanks

I think you might want to explore General-Special Extended Entity Relational design. Here's a simple, simplified example of what I understand you're trying to do. EER is worth exploring in my opinion - it surely makes for an elegant design and solves a lot of complex coding to control data entry - but be sure to understand how views on the tables make it work - views typically join the General and Special tables to get both the general and special attributes that apply to the special table - your Books, Medicines, Cars etc.

 

[mike60smart]

Hello experts,
Ok so now I have one big table for all items.
I am "stuck" with statuses. StatusItem.
I have 4 diferent items and of course these items can have very different statuses.

I think if I just say StatusItem, that is ok?
and then I put inside tblStatusItems: StatusID (PK), typeID (FK), , StatusName

typeID1 is Book
typeID2 is Medicine...

how would you make that?

Thanks

Hi

You would need 2 tables to allow you to set up the various status's

tblStatusTypes
-StatusTypeID - PK - Autonumber
-StatusType - Text

tblTypeStatus
-TypeStatusID - PK - Autonumber
-StatusTypeID - FK - Long Integer
-StatusType - Text

See the attached example

Then you can use Cascading Combobox's on your required data Input Form.
The 1st Combobox would be based on tblStatusTypes and the 2nd Combobox would be based on tblTypeStatus
The 2nd Combobox would only display those Status's associated with the Type selected using the 1st Combobox

 

[lacampeona]

Hi Mike

Thank you. I will look the example

 

[lacampeona]

Hi

think you might want to explore General-Special Extended Entity Relational design.

Ok I never do that. Ok I will study also that to see how that work.
and also the example from Mike.
Thank you all

 

[Isaac]

I think it is very "iffy" to try to take your original description and make a recommendation, but Tom's suggestion seems like a very sensible one given the limited information we have.

I have found the following to serve me pretty well:

- Identify ENTITIES.
- Identify ATTRIBUTES
- Identify EVENTS or TRANSACTIONS or 'HAPPENINGS' of some kind.

One entity type, like PEOPLE, may have a table, with attributes like height and weight.
One event-type, like SALES, may have a table with foreign key columns designed to hold the Primary Key in the table PEOPLE.

The recommendation to study NORTHWIND is a very good one. MS seems to think Northwind is old fashioned, but I still think it's a great starting point.

 

[lacampeona]

Hi

The recommendation to study NORTHWIND is a very good one. MS seems to think Northwind is old fashioned, but I still think it's a great starting point.

Yes I am now resarching the database, and yes is good starting point
thank you

 

[jdraw]

What I am doing wrong?

What did you expect? Can you be more specific on what you want as output?
Can you post the database with instructions and latest requirements?

 

[mike60smart]

Hi
Are you doing Data Entry directly to each Table?

This is a no no as you should be doing Data Entry with Related Main Form & Subform

 

[lacampeona]

no
always from the form

 

[Gasman]

Hello experts,
I need somebody to check what I am doing wrong. I am crazy. I look relationship in another tables and I understand it. I see so many examples and suggestions and I say yes yes I understand that. I check the tables which I see in another databases and they are correct and relationship is correct.

Then I say ok lets go now I will do that in my database. I make relationship and I check the tables and the relationship and the data in the table is not correct. Only one table show correct.
I am really really stupid.
What I am doing wrong? Like I say in posts before I have 4 different type of items. Now I am here only showing one type of items. I want first to understand that situation and then I will make also for the another 3 type of items.

Ok so This is my idea how the relationship is :

StatusPredmetaID= Status of the item ( 1avaliable, 2in use, 3relaesed

OddelekID=Departments ( I have different departments)

SistemPredmetaID= item system,, here are only 3 systems...Empower, Chromeleon, Chemstatoion

Here I have to say that only SistemPredmetaID is showing correct in the table...everything else is not ok

View attachment 109131

View attachment 109133



View attachment 109134

View attachment 109135


View attachment 109136

What I am doing wrong?
Thanks in advance

You are very unhelpfully cutting off the names of the tables in the pics.
I for one am not going to try and match up each pic from your relationship diagram.

 

[GregDataReno]

Can you provide some examples of the outputs (reports) that you have or want to produce? Also, a conceptual model might help - no cardinality, just linked things. Maybe some simple statements like "Each Thing X is one three types of Thing", "All the Thing Zs sell the Thing Xs" etc.

It can't be emphasised enough that relational modeling is a powerful technique able to describe all structures in the known universe. And as a technique it is independent of, and I think precedes all, relational databases, so it's unreasonable to expect that knowing how to join tables in Access means you can do 'relational modeling' - doing it ain't knowing it.

I recommend back to basics - what 'things' (entities, tables) do you care about, what information about them do you need to know (attributes), and how is each thing related to, dependent upon, or affected by all other things (relationships) ?

(Also, should Iacampeona's post be a new topic?)

 

[GregDataReno]

(An aside: I'm in the camp of 'only use AutoNumber IDs if there is absolutely no alternative' . Am I alone?

Why use natural keys?
1. self-documenting - at least for subject matter experts
2. encourage deeper understanding of the data 'model'
3. support bulk imports with no need to track new IDs
4. remove the need for an extra unique index

Downside?
1. if using codes, the codes can become out-of-sync with their interpretation when the interpreation changes eg zoological nomenclature
2. can't think of any others.
)

 

[lacampeona]

Hello

I am attaching the database. in the database is now only table for one type of the items. If we managed to do that correct then I think I will also know how to do it for the 3 different types of items more.

In the tables I make a translation into english what means every field.

The database should work in that way:

1. User enter new items into database

Where entering data into form frmVnosKolone-Here the user he can choose on which system on the instrument ( Empower, Chromeleom, Chemstation) he will use the item.

Then he can also choose which deparment will be the owner of the item.
When entering new items into database all items gets automatitcly status In stock.
This all what user must do in the first form. Enter all the field and save the record.

2. Now User wants to use that new item for some analysis. He can always use one item for one analysis- that mean one item he choose from tjhe list he will create one record for the usage for that item.
Now:
-User open the list of all the items that are in database and choose which item he want
-He choose the item and make some analyis. Here is now opened the second form frmKolonaUporabaSUB
This all what user must do in the second form. Enter all the field and save the record.

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.

 

[Mike Krailo]

What do these two tables represent? The thing that jumps out at me is the fact that you have much of the same data in both tables. Why are you doing that?

 

[lacampeona]

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

And the second table on the right side is "analysis window". I have to have some fields the same becouse this way user will know what item he is using. here are most field about the analysis,...what analysis, where are the notes, if analysis was succescful if we have any remarks...

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.

In the begining user must know who has the item which department what is the status and the system ( tblKolone2) then after analysis (tblUporabaKolon) user also must know where the item is now ( if was used from another department) and what is the status of the item...user must know if the item was new and then used - some analysis was done in what state is the item, if is still apropiete to make analysis or not, or the item was sended to some another department or another country...

 

[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.

 

[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.