• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Exercices to practice relationship (1 Viewer)

lacampeona

Registered User.
Local time
Today, 13:26
Joined
Dec 28, 2015
Messages
75
Hello
maybe somebody know where I can find some exercises to practice creating relationship?

Thank you
Elena
 

vba_php

Forum Troll
Local time
Today, 06:26
Joined
Oct 6, 2019
Messages
2,884
Elena,

Google is the place to go. I have a section on my website on how to google properly. for instance, start here:


then you can look up the same thing for almost any other DB system that's out there once you learn access. sql server, mysql, oracle, etc, etc....

do you need any more detail than that? I would give you the link to my website page, but it is not finished yet as a whole. if you want to learn how to google properly, send me a PM here and I'll let you in and you can look at it.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:26
Joined
Feb 28, 2001
Messages
17,964
Elena, the key to creating a relationship is to understand the implications of declaring one. I don't know your background, but I can offer a couple of thoughts.

First, you must understand normalization. To that end, either search this forum for "Normalization" or search the general web for "Database Normalization" (because normalization also applies to general math, politics, chemistry, and statistics.) Our search option is near the top of the screen, right-hand side, in the bar that also contains your login name. If you go for the general web search, limit what you read at first by only looking at results from .EDU domains, as the .COM sites have something to sell you and will be pushing their own product's proprietary viewpoints.

Once you understand normalization, you perhaps will realize that you are building a "model" of your business data flow. The three kinds of direct relationships supported by Access are one-to-one (or 1:1), one-to-many (or 1:M), and many-to-one (or M:1). The kind of relationship to use in any case will depend on what your business model suggests. (This even includes home users whose purpose is keeping recipes.)

It is exceedingly rare but not impossible to define 1:1 relationships because of what it implies. It is difficult to work with true 1:1 relationships because of Access requirements when working with them. They are unwieldy and usually occur only for special cases.

This leaves 1:M and M:1 relationships. They are the most common practical relationships. Suppose you had an invoice table and a (single) table of line items associated with each invoice, differentiated by invoice number. Your invoice:line-item table would be 1:M, one invoice to many line items. Suppose you had machinery that per your business rules could only be in one of several states. You might have a table of allowed states, in which case there would be a status code in the machinery table and each different machine would point to a single current state, or 1:M.

One minor difference between 1:M and M:1 is that it is more likely to have 0 matching records for a 1:M case without that being a structural flaw. In most M:1 cases, having no matching record on the "1" side of M:1 cases usually means you forgot something.

There is such a thing as many-to-many relationships, or M:M. For example, at a school, a student goes from class to class and each class has a different teacher. One teacher has many class sessions each day with different students in each. So this would be an M:M relationship, many students and many teachers, with multiple contacts either way. This would require what we call a junction table, which is another topic you can look up.

This is a lot for folks to digest. Take your time and look at my reading list, which includes keywords for searching: In order "database normalization" and "data relationships" and "junction table."
 

vba_php

Forum Troll
Local time
Today, 06:26
Joined
Oct 6, 2019
Messages
2,884
That's quite a lot for her to swallow in one take Richard. I think you overdid a little my friend
 

lacampeona

Registered User.
Local time
Today, 13:26
Joined
Dec 28, 2015
Messages
75
Hello
first thank you both for your answers.
I can say I am total amateur ( i never study nothing about programing) nothing in ms access and nothing in vba, but I learn a lot by myself. I can find what I need (some codes on the internet ) and then i will manage to change it and used in my database. I know that is not ok but this was my start. I am always looking on the internet to find some exercies to practice but never find something very useful. so what i do is find some free database and then i read the codes and with the google try to understand it.
my wish is to try to write something spme vba by mysefelf one day and no only copying.

I create my database and is working but now i thinkg that maybe i dindt create correct the relationship. i dont have no relationship inside. ok have some query with relationship but this is not what i see on other databases.

i know is different and depend on what you want to create but i dont know...i try ecerytink and i cant connect nothing. is like i am stupid and cant put the correct fields together.

maybe is becouse i have special database.

I will explain you my database.
imagine you have 100 items to borrow. in my database only one user can borrow only one item at time becouse this item has special serial number and the item that was borrowed nobody can borrowed until is returned by the same user that borrowed. when the item is returned or borrowed the item go on the status lets say available., when is used in status in use, when is send is in status sending and when is borrowed for longer time i have status for longer time.
i have 5 tables.
1. available
2. in use
3. sending
4. longer time
5. destroyed

what is my problem? i cant connect all thiese 5 tables together.
i want to know who borrowed and who and when returned the item

imagine item 1 was borrowed 10.03.2020 and returned 13.03.2020 it was on status longer time

then came person B and borroed the item for sending purpose. imagine 14.02.2020 and returned it 17.02.2020

i cant connect all that in simple audit trail. i now create 5 tables and every table has his audit trail...

do you have any suggestion?

thank you
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:26
Joined
Jan 23, 2006
Messages
13,359
See the examples at this RogersAccessLibrary site.
If you work through the tutorial examples you will learn database design,normalization and relationships.
Good luck.
 

vba_php

Forum Troll
Local time
Today, 06:26
Joined
Oct 6, 2019
Messages
2,884
Good luck.
why don't you guys refrain from saying GOOD LUCK to a beginner!? I've told you multiple times. are you guys not listening? that makes people feel worthless. just a word *from* the wise on this one, gents!
 

lacampeona

Registered User.
Local time
Today, 13:26
Joined
Dec 28, 2015
Messages
75
Thank you for your words vba_php. Dont worry for that. I understand.
Thank you jdraw for your suggestion. I will check that page.

Is diffult to start but i will try to see what and how much i will understand.
Best regards Elena
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:26
Joined
Jan 23, 2006
Messages
13,359
Elena,
Those examples at RogersAccessLibrary will lead you through a process that you can use with any database. Each example has a problem statement, an approach, and a solution. The examples are provided in word. If English is not your mother tongue, then use google translate and convert the exercise into your language. I hope you enjoy and learn from these examples.
The database planning and design link in my signature has many articles that may be helpful to you. But the RogersAccessLibrary examples are a great base.
 

lacampeona

Registered User.
Local time
Today, 13:26
Joined
Dec 28, 2015
Messages
75
Ok thank you.
I will look in that databases.
Elena
 

vba_php

Forum Troll
Local time
Today, 06:26
Joined
Oct 6, 2019
Messages
2,884
Elena,

If you get what you need from Rogers Library let me know because I'm not willing to put something together for you if you can get it elsewhere. Thank you
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:26
Joined
Feb 28, 2001
Messages
17,964
Elena, I will offer a comment of a more specific nature.

You have tables for five different statuses. You should have one table that includes a field that could represent one of five statuses. Then you would never have to move something from one table to another. You would only update the status. You would still have only the one item that could be in one of those five statuses. But handling one copy with five states is FAR easier than handling five tables.
 

lacampeona

Registered User.
Local time
Today, 13:26
Joined
Dec 28, 2015
Messages
75
Elena,

If you get what you need from Rogers Library let me know because I'm not willing to put something together for you if you can get it elsewhere. Thank you


Now here in europe is time to sleep. I will look tomorrow in roger library.
If you have something alrrady prepared you can send me. But if you will make some execises i will be very gratefull. It will not be spendung time for nothing. Iwill definitly look it. More examples more changes to learn.
Thank you
 

lacampeona

Registered User.
Local time
Today, 13:26
Joined
Dec 28, 2015
Messages
75
Elena, I will offer a comment of a more specific nature.

You have tables for five different statuses. You should have one table that includes a field that could represent one of five statuses. Then you would never have to move something from one table to another. You would only update the status. You would still have only the one item that could be in one of those five statuses. But handling one copy with five states is FAR easier than handling five tables.

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?
Thank you
Elena
 

vba_php

Forum Troll
Local time
Today, 06:26
Joined
Oct 6, 2019
Messages
2,884
Elena,

Have a look at what has been posted in this thread so far first. It's better to learn from those examples then from somebody like me who has Common Sense language to teach
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:26
Joined
Feb 28, 2001
Messages
17,964
Elena, it would seem that you are starting from a very low level. To make things happen "automatically" is an interesting term. The answer to your question "I want to make that that ststus changed automsticly... Is that possible.?" - an absolute "yes" - but qualified by this fact.

NOTHING is automatic. What you CAN do is make a command button to run code. The code can, in turn, run a query that does updates. There IS such a thing as making an app that can recognize that a change in item A would trigger a change in item B - but that is usually a very advanced process. With no disrespect intended, the way you talk about what you want done makes me believe you might still be working on basics and might not be ready for some of the really advanced techniques. Oh, don't worry - you CAN get there from here. It is just that Access has a non-trivial learning curve.
 

vba_php

Forum Troll
Local time
Today, 06:26
Joined
Oct 6, 2019
Messages
2,884
what you CAN do is make a command button to run code. The code can, in turn, run a query that does updates. There IS such a thing as making an app that can recognize that a change in item A would trigger a change in item B - but that is usually a very advanced process. With no disrespect intended, the way you talk about what you want done makes me believe you might still be working on basics and might not be ready for some of the really advanced techniques. Oh, don't worry - you CAN get there from here. It is just that Access has a non-trivial learning curve.
well you did better with that post Richard but I still think you went overboard. I'm pretty sure she won't be able to follow all of that either.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 07:26
Joined
Apr 27, 2015
Messages
3,009
well you did better with that post Richard but I still think you went overboard. I'm pretty sure she won't be able to follow all of that either.
Why dont we let iacampeona speak for her/himself instead of presuming to know what he/she is capable of?
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 06:26
Joined
Feb 28, 2001
Messages
17,964
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.
 

lacampeona

Registered User.
Local time
Today, 13:26
Joined
Dec 28, 2015
Messages
75
Hello everybody
thanks for all your advices and answers.

with term automaticly i mean that when user return the item that the status changes automaticly to avaliable...now i create that with s
statement insert into table...becouse of that i have 5 separates tables and i complicated all by myself...
i dont want to have combobox and then user will choose available...in use or longer time...
i want that will change automaticy...and with that i create it is.. is working but i am thinking that this was not good idea...


i will look on rogers databases to see what is and how much i will understand.

like i say i am total amateur to writing vba codes by myself......but i can understand how to implement that then in button and the query.
i can create complete database with buttons and treeviews , audit trail, forms reports excel pivot connections..i know how to do that

my problems is that i want to learn wrting codes by myself and practice that..to be able write that by my own and create relationship and queries ...i want to start in the heart of the database this is create realtionship..everything else i know how to do and how to find

thanks again to all of you
have nice day
Elena
 

Users who are viewing this thread

Top Bottom