Exercices to practice relationship

[MajP]

i dont have no relationship inside. ok have some query with relationship but this is not what i see on other databases.

That may be fine, not to have any relationships established. Having relationships established in your relationship window is not always required. It is also not the same as joins in a query. You do not need to build a relationship just to do a simple join.
You need a relationship to ensure referential integrity. And you do not always do you need it or even want it. You need it when you have child records that would not make sense to be in the database without a parent record. For example if you had a database that tracks your suppliers and their products. You would not want to add a product to your database if you did not have a supplier to order it from. You have to add the supplier first then you are required to assign a new product to a supplier. So if you enforce referential integrity you cannot do the following
1) Add a product without a foreign key related to a supplier. You cannot leave it blank and cannot assign an incorrect key
2) Cannot delete the supplier with out deleting the children first
This will ensure you never get orphans in your database. That would be a product not related to a supplier

CASCADE DELETE
3) If you choose allow cascade deletes it will allow you to delete a parent that has children. It will prompt you that there are X child records and they will be deleted. This overrides number 2 above.

CASCADE UPDATES
4) Not used as much since most peope use an autonumber for a primary key. But assume you made your own primary key and the key for your supplier "ACME Industries" is AI. All the child records would be related by AI. If you change the Primary key to ACME, the related children foreign keys will change from AI to ACME keeping the relationship

If you create a relationship in the relationship window it creates defaults. So when you create a query it will automatically create a join. However you can edit or delete this join in the query window. If a relationship was not created prior you can still make a join on your own.

 

[MajP]

How to update ste status automaticly? I want to make that that ststus changed automsticly... Is that possible.? With query? Do you have somr example how to uptade status automsticly?

Somewhere on this site I built an example of a library check in and check out.

However if you have a single table for item actions
TblItemActions
ItemID
ActionDate
Action - (Checked in, Checked out, destroyed)

If you query the top Date per item you know the status of that item on that most recent date.

 

[isladogs]

See Relationships and referential integrity

 

[MajP]

@lacampeona
Make sure to read Isladogs post above. That is the best discussion I have ever seen on the use of relationships. It is very clear and very thorough. You will be surprised how many advanced Access users do not really understand this.

I am in the latter case where i only create saved relationships when I need to enforce referential integrity.

 

[The_Doc_Man]

I will add one more case to MajP's idea. I also create stored relationships when I am building queries that require JOINs to occur. If I have the relationship, the query wizard will correctly build the JOIN for me. I can come back and embellish it later, but I'm the kind of guy who uses wizards to build the infrastructure so that my first real task is polishing, not building.

 

[vba_php]

(I am, after all, old.)

no argument. =) sorry, I had to Richard.

 

[The_Doc_Man]

Adam, I have not denied that I am at this point closer to the end than to the beginning.

 

[vba_php]

Adam, I have not denied that I am at this point closer to the end than to the beginning.

well I know that Richard, but you still have a lot to offer. and if you listened to ME, and more importantly the king, you would have no end. I don't know why your mind is blocking you from seeing that. It makes me sad. =(

 

[MajP]

@lacampeona
Microsoft has a lot of templates that you can download. When you create a new database you should get a lot of choices and you can search. Here is the library template. This would be good to look at the tables and how they are related. One CAUTION. Microsoft templates use a lot of table lookups. That means there are pulldown choices in the table. Most developers consider this bad. Pulldowns are great, but should be in the form and not the table.

However, this template has what I would consider a good table design.

Assets (the things that get borrowed)
Contacts (the people that borrow things)
Transactions (the date and status of the asset. Checked in or checked out)

Those are the tables you need. Not the five you listed.

 

[lacampeona]

Helloo

thanks boys to all new answers and ideas.

MayP thank you for database, I alredy see that database is ok but in my case i need different things.

I manage to make something with my database but i am still creating new things inside,...and all new ideas and articles are very welcome thanks.

best regards to all of you
Elena

 

[lacampeona]

Thank you Isladods I will read all that article on your webpage.

 

[MajP]

Needing additional or different things is OK, but if you are still planning to use the 5 tables

i have 5 tables.
1. available
2. in use
3. sending
4. longer time
5. destroyed

You will make this overly complicated, and it will be a mess. As long as you have a single "transaction" table it will work. If not you will be doing workarounds on workarounds.

 

[lacampeona]

hello
yes i know ...i complicated everythink that is possible to complicate

now i make one table and put inside
1. date when item was borrowed
2. and date when item was return...

then i make connection.. for now is what i want..but now i have problems with the date...
if user took item A i put automatily date =now()...so user dont have to choose the date.
i also then create date for return in the same way...with =now()...and now...when user take item A...and then return the item A..is the same date...becouse i dont want make option to user to choose the date...date has to be automaticly when you borrwwd and when you return...

now i have to find another way about the dates

 

[Steve R.]

Why dont we let iacampeona speak for her/himself instead of presuming to know what he/she is capable of?

Adam, I am still in "triage" mode trying to estimate where it is proper to start with Elena. By providing her with topics to read about at her leisure, I hope to lead her to better understanding. I also hope to determine where she needs help most.

Elena, it is not our intention to start an argument in your thread, but certain elements on this forum take a different view of how help should be offered. The members of this forum are not uniform in our backgrounds or viewpoints. When considering what you hear, please include that fact in your consideration. You WILL get different advice from each of us. Only you can decide whose advice is more helpful and whose advice is not helping. My comments about "non-uniform backgrounds" actually includes YOU because part of our job is to decide not only what to say but how to say it in a way that doesn't make things worse.

The intervention of Adam in this discussion has not lead to him providing any useful guidance to Elena, distracting from the purpose of this forum. and is quite embarrassing as a consequence.

 

[vba_php]

The intervention of Adam in this discussion has not lead to him providing any useful guidance to Elena, distracting from the purpose of this forum. and is quite embarrassing as a consequence.

i'm sorry about that, Steve. But it has been provided in other ways. It was just not mentioned in this thread. trust me, she's well taken care of. and her issues have been solved.