Solved Correct builiding step

[Pat Hartman]

Using an EAV model can simplify your life and dramatically reduce the number of fixed columns, but it will work best if the "data" isn't actually used for anything except data entry and display. Typically, you will have a high level table that has all the common or semi/common fields. Plus the fields that need to be indexed or searched on. Then there would be a child table using the EAV pattern which can be used to describe pretty much anything. Keep in mind that the EAV table will usually have only a text field to hold all types of values including dates. That means you need a way of identifying what type of data the value is. I always have a table that defines the EAV table. So, you define all the data fields and their types. Then when the user enters data, they pick the column name from a list and enter the data. You validate based on the type. So, if the data is supposed to be a date, YOU need to validate that it is a valid date since Access can't help you. Same for numeric values.

 

[GregDataReno]

Hmmm - no mention of Extended Entity Relation modelling ie. the use of General and Special entity types.?

If each item shares one or more common attributes, then create a 'General' table that has only those attributes eg.

tblItem: "ItemID (PK), ItemTypeCode (FK tblItemType, NOT NULL, UNIQUE), <SupplierID> or similar".

If each type of item has attributes specific to it - like a special form of ID - then create a table unique to each Item eg.

tblBook: "ItemID (PK), ItemTypeCode (FK, NOT NULL = "BOOK"), <special attributes like "ISBN NULLABLE", CoverType (FK)>

tblMedicine: "ItemID (PK), ItemTypeCode (FK tblItem, NOT NULL = "MEDICINE"), <special attributes like "SpecialItemID", "BatchNumber", ManufacturerCode (FK tblSupplier)>

(for completeness, I'd need to add the reference tables: tblItemType, tblSupplier/tblManufacturer (note this could be a Gen/Spec model too - Party->Supplier, Party->Manufacturer etc), tblCoverType etc.)

The magic sauce here is getting the relationships correct AND building views that ALWAYS include the General table which then returns both the general and specific data for each item type - make them base queries that then serve every other query --- I use vtblItem, vtblBook etc. so I never forget.

Good intro here: https://www.tutorialspoint.com/Extended-Entity-Relationship-EE-R-Model

Also "Data Model Patterns. David Hay (2006), Morgan Kaufman" https://a.co/d/5mYwFUF


Good luck
Greg

 

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

 

[Pat Hartman]

The EAV model works for attributes that stand alone. All you do is save them and display them. Once an attribute has dependencies, you have to go back to a "normal" "flat" model.

 

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