Results 1 to 11 of 11
  1. #1
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408

    relationship - ID

    Hi,
    i'm studying access right now, and i have just a very stupid question



    i understood relationship, but how can i handle a record by an ID?

    i mean, look at the image: in the child table i can see only the ID of the employee. How can i know his name? and if i input another order, i have to know the ID of the person? or i can use a field in the form?

    i searched a lot of time on the internet, but still i didn't get this
    Attached Thumbnails Attached Thumbnails Cattura.PNG  

  2. #2
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I not the best to answer this simple question but..

    When you build a query it joins the tables together using the fields that you need. So the query rebuilds the data back into understandable records, with repeating data.

    Does that make sense?

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    For user tables are fully on background. User operates data through forms, and gets visible and printed outputs from reports. So keep it simple with tables - no need for combos or formats etc. All this you set in forms and reports.

    Relationships at table level are not obligatory. Some use them always, some (like I myself) rarely. You can always set them at query design. The only real use for them is referential integrity, and I mostly prefer to achieve the same result through code. So you have to decide yourself about them.

    Forms are built based on tables, or on queries (but beware - not all queries are editable).
    Reports are mainly built based on queries, but you can base a report on tables too. Simply queries are more flexible.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    diego,

    You should review Normalization. Here's a link.
    There are many videos on youtube that deal with Normalized tables and queries.

    I recommend you watch a few of these to get some appreciation of database concepts.
    https://www.youtube.com/watch?v=hMzf6u8hWqk

    Queries are what brings the data from different tables together to make sense.
    Data storage (tables and relationships) is quite different than data presentation (queries/forms/reports).

    His is a link to more info on Database Planning, Design and Concepts.

  5. #5
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    Quote Originally Posted by Western_Neil View Post
    I not the best to answer this simple question but..When you build a query it joins the tables together using the fields that you need. So the query rebuilds the data back into understandable records, with repeating data.Does that make sense?
    yes, thanks, so generally in tables are displayed IDs, and you can look the value in queriesand what about forms?

  6. #6
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    So a form is a designed way of showing a query (and more). The IDs drop when the query joins the tables (or I just glaze over them)

  7. #7
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    Hi, now i'm good enough thanks to yours advices.
    i'm now studying many to many relationship, and i have one question:

    looking at the image, i do not understand how you can enter an order in this database. there is a "order" table and an "Order detail" table. Which is the difference?

    and speaking about forms, obiovsly i can input data with a combined textbox with a query, right? and in which table the data will be stored?
    your help is precious



    Quote Originally Posted by orange View Post
    diego,

    You should review Normalization. Here's a link.
    There are many videos on youtube that deal with Normalized tables and queries.

    I recommend you watch a few of these to get some appreciation of database concepts.
    https://www.youtube.com/watch?v=hMzf6u8hWqk

    Queries are what brings the data from different tables together to make sense.
    Data storage (tables and relationships) is quite different than data presentation (queries/forms/reports).

    His is a link to more info on Database Planning, Design and Concepts.
    Attached Thumbnails Attached Thumbnails Cattura.PNG  

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    In Orders table you have the info about certain order, which you an present as a single row in Orders table. I.e. when the employee is making an order, it is one certain employee. And the order is made for one certain customer at one certain date, etc.

    In OrderDetails table is info which you can't have as single row. P.e. when you send to customer the order about 10 different items/services, and every one of them has different quantity and unit price, then you have accordig 10 rows into OrderDetails table with all this info a row for every item/service, and link those rows with a row in Orders table through OrderID.

    Btw, I think it is better to have an additional field OrderDetailID in OrderDetails table as primary key. It is possible a situation, where you need 2 rows for same product for an order, p.e. in case you are giving discount only for quantities above certain level. With current 2-field primary key it is not possible, as you get primary key violation.

    To control effectively the data entry, you have to use user interface - forms. To register an order, the best setup is a single form based on Orders table, where you can register new orders and edit existing ones when there is a need for this.
    On this form you have a continuous subform based on OrderDetails table. The subform is linked with main (Orders) form through OrderID field in both tables. When you select some order in main form, all order details rows for this order are displayed in subform. And when you add a new row into subform, it is automatically linked with main form (OrderID from main form active row is entered into subform field linked with OrderID in OrderDetails table).

    When user is entering data manually into tables, then all entries must be done manually - and the risk to type in something wrong, or to forget something, is very high. So an advice almost everyone is giving here - never let user to dabble with your tables.

  9. #9
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    man, sorry for late response....but thanks, you are great.
    great content, great explanation
    now, in just two weeks, i'm pretty good on access, and you were very super good for me


    Quote Originally Posted by ArviLaanemets View Post
    In Orders table you have the info about certain order, which you an present as a single row in Orders table. I.e. when the employee is making an order, it is one certain employee. And the order is made for one certain customer at one certain date, etc.

    In OrderDetails table is info which you can't have as single row. P.e. when you send to customer the order about 10 different items/services, and every one of them has different quantity and unit price, then you have accordig 10 rows into OrderDetails table with all this info a row for every item/service, and link those rows with a row in Orders table through OrderID.

    Btw, I think it is better to have an additional field OrderDetailID in OrderDetails table as primary key. It is possible a situation, where you need 2 rows for same product for an order, p.e. in case you are giving discount only for quantities above certain level. With current 2-field primary key it is not possible, as you get primary key violation.

    To control effectively the data entry, you have to use user interface - forms. To register an order, the best setup is a single form based on Orders table, where you can register new orders and edit existing ones when there is a need for this.
    On this form you have a continuous subform based on OrderDetails table. The subform is linked with main (Orders) form through OrderID field in both tables. When you select some order in main form, all order details rows for this order are displayed in subform. And when you add a new row into subform, it is automatically linked with main form (OrderID from main form active row is entered into subform field linked with OrderID in OrderDetails table).

    When user is entering data manually into tables, then all entries must be done manually - and the risk to type in something wrong, or to forget something, is very high. So an advice almost everyone is giving here - never let user to dabble with your tables.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by diegomarino View Post
    now, in just two weeks, i'm pretty good on access
    Main steps of gaining knowledge:

    1. I don't understand nothing!
    2. I understand everything!
    3. I don't understand nothing!
    4. I understand something!

  11. #11
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    just socratic, i'll keep in mind :-D
    Quote Originally Posted by ArviLaanemets View Post
    Main steps of gaining knowledge:

    1. I don't understand nothing!
    2. I understand everything!
    3. I don't understand nothing!
    4. I understand something!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Many to one Relationship?
    By TastyOs in forum Queries
    Replies: 1
    Last Post: 11-09-2016, 05:46 PM
  2. Relationship Help
    By AndyC121 in forum Access
    Replies: 4
    Last Post: 01-11-2016, 09:08 AM
  3. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  4. Replies: 1
    Last Post: 12-06-2014, 12:49 PM
  5. Relationship
    By ClownKiller in forum Database Design
    Replies: 13
    Last Post: 12-21-2010, 05:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums