Primary key and foreign key-what I am doing wrong?

[lacampeona]

Hello

In attachments please see my 3 tables.
Can somebody look and tell me why I cant create a relationship beetween them? What I am doing wrong? the most important thing is the field SerijskaStevilka ( serial numner of the colon)

1. tblKolone ( table Colons)
2. audKolone ( audit table for table Colons)

3. tblPrevzemKolon ( table borrow Colons)
4. audPrevzemKolon ( audit table for borrowing Colons)

5. tblVraciloKolon ( table of returned colons)
6. audVraciloKolon ( audit table for returned the colons)

how it works?
Imagine you are user and you want to use colon with serial number 006A. you take the colon 006A on 24 the february and on the 26 of the february you are returning back the colon. you have to return the colon so next time another user can take the same colon.
i want to create query that will tell me...who on which day take the colon with serial number xxx and when and who returned back the colon with the serial number xxx.
note: only user who take the colon with xxx can return the same colon back,

I create separate and is working...but i want to make one large query that will tell me all?

how would you create the tables? what you reccomend me?

thank in advance
Elena

 

[lacampeona]

Here is the database

 

[jdraw]

I have no idea what this really means.
"Imagine you are user and you want to use colon with serial number 006A. you take the colon 006A on 24 the february and on the 26 of the february you are returning back the colon. you have to return the colon so next time another user can take the same colon.
i want to create query that will tell me...who on which day take the colon with serial number xxx and when and who returned back the colon with the serial number xxx."

We do not know your business, nor the terminology you are using. "colon" means something to you?
What does "colon" mean in English?
It might be more efficient if you wrote your description in your native language(Bulgarian/Slovenian?), used Google translate to get an English version, and post that.
Good luck.

 

[lacampeona]

Hello
yes Colon is some thing that is using in laboratory for some analysis. see the picture. every column ( colon) has his serial number. is like some part of the let say car.
so user A take the colon 006A, they returned back,,and then user B take the colon 008A and so on..

i want to create query that will tell me all that..
Elena

 

[jdraw]

As an analogy, consider your "colon" as an IT asset with a serial number. The asset can be borrowed, used, and returned. Here is a data model that shows the major entities and how they are related.
Another analogy may be a library where books are stored, borrowed, used and returned (data model).

Via Google translate:
Kot analogija upoštevajte svoje "debelo črevo" kot sredstvo IT s serijsko številko. Sredstvo je mogoče izposoditi, uporabiti in vrniti. Tu je podatkovni model, ki prikazuje glavne entitete in kako so povezane.

Druga analogija je lahko knjižnica, v kateri se knjige hranijo, izposojajo, uporabljajo in vračajo.

 

[lacampeona]

Thank you very much
I will study that...
yes sometimes like that... i have everythnk done...but now i am thinking of i make that correct...becouse i also have an audit trail for every table....
when i want to put all together the normal table and audit then i have problems of reperating the same date 20 times...
thank you

 

[plog]

The language barrier is too much for me to help specifically as well; but I think I can help in general.

1. Credits (returns) and debits (borrows) should usually be in the same table. You just update the record with the return info. With your structure you could have more returns than borrows, or double returns of individual borrows.

2. Why do you need an audit table? The database itself is an audit of the items borrowed/returned--you are auditing an audit. If anything you simply need to backup the database regularly to maintain the data.

3. Every table has a SerijskaStevilka and Opombe field. I don't know what those fields represent, but it is unusual you have those 2 fields in every table, sems like you are duplicating data.

 

[lacampeona]

Hello
yes me too are thinking that I complicate everytink with the audit trail. SerijskaStevila is serial number and Opombe is comments...
thank for your anser...i think i made a mistake with so much tables...and auditing

 

[lacampeona]

How do I get the user name in the table? how i will knot who use the column?

becouse of that i think create audit trail

is it possible to get user name in the table from another way?
thank you

 

[The_Doc_Man]

If you are using the "borrow" and "return" tables (together or separately) you would put the username of the person who borrowed the column (colon) and / or the person who returned it. That is how you would remember it - in the table that remembers the transaction of borrowing or returning. The column obviously has no owner. But the act of borrowing or returning IS associated with a person, so that is where you record the person and the date of the action.

 

[lacampeona]

thank you, yes this is also one way,,you are thinking about one combo box so user can choose his name? hmmm
i was thinking of automatic way..that access will put the name..in the audit trail i use the environ user name function....
i have to prevent that other user can say that user A take the column and this is not truth....hmm

 

[The_Doc_Man]

If you generally trust your users and they do not have adequate privileges to directly update the environment variables, the Environ("Username") function is OK. If you feel there is a reason to still not trust them, our member Isladogs has posted some good comments on using an API function to determine the username without resorting to trusting the environment value. Of course that is up to you.