Results 1 to 15 of 15
  1. #1
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10

    How to make unique (ID) filed that will connect more than 3 tables

    Hello

    I have a problem. I want to view results from more than 3 tables. My problem is: what primary key i have to write in single table that in the final I can connect all 5 tables together?

    I was thinking of creating one big table that will contain all the data. but reading on internet and forums i find out that is better to have separate tables with primary and foreign keys.

    Imagine. you are doing some analysis. you have batch number. and you have to do 5 different analysis on that batch number. every analyisis will give you some results. your object in the final is : be able to see for every batch number the results for all 5 analyis together.

    batch number has to be short text. batch number is like a model of mouse. exp. B145864. and that mouse have 5 analysis.

    in every table i have batch number and depending on table field that give special results.

    example.

    I have 5 tables.
    1. tblDsd give me results for Dv10, Dv50, Dv90 particle distrubution
    2. tblSp give me results for DMax , DMin


    3. tblVisko give me results for viscosity90, viscosity100, viscosity150
    4. tblNozzle give me results for nozzle
    5. tblApi give me results for Mean, stdev

    what primary and foreign key shuld I write in every table?

  2. #2
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    if someone can help me i appreciate so much

    thanks

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure we know enough to answer. Is batch number common to all? Can you attach the db here with some sample data?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    yes,,,,the batch number (KontrolnaStevilka) is common to all. imagine you have batch number B444777 and then five diferent analyisis that give results...the results are in the tables...tbldsd,tblsp,tblapi...

  5. #5
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    ok i will attach my database..
    Attached Files Attached Files

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There are only 3 tables here, not 5. I'm confused, because while each table has that field, each table has multiple records with the same value, and no tables have the same values. I'd expect your example value of B145864 to be in each table, and not to be repeated. I guess I don't understand the data or how the tables relate to each other. What result would you expect to see?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    ok i see. i will try again with more specific explanation and another sample. i put 3 tables becuse the file was very big. i will try again.

    thank for you cooperation. i will make another exapmle..dont go away...

  8. #8
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    ok let see. my batch number is 17MAA502A. that batch number can have 5 diferent analysis or just one or just two...depends what custumer wants. every analyis has her own table. and results for that anylsis are in 5 tables. so we have results for dsd in tbldsd, results for sp in tblsp...

    and when you are entering results ( and in the practice you have to enter the name of the sample,the batch number,the code on every instrument that you are doing the anaylsis..you dont have instument for 5 anaylis in one instrument...you have 5 instruments or more so you have 5 anayliss or more and 5 different resilts or more) in the table you have to enter the name of the sample, code, batch number, elogis number....becouse of that i have this field all with the same name becouse they have to have all that...the only diference in that table are the results of each analyis. imagine one anylsis give you results in Kelvins,. another give you mass, another pH....another relative humidy....you goal is if someone ask you what is results for temp ( kelvins), mass, pH,,,Relative humidy you can just click on the table and see all that results together connected for specific batch number
    in my case i have nother values for my results..like dv10..viscosity,,,mean,,,,stdev....

    so if i click batch number 17MAA502A i want to see resutls for dv10, dv50, dv90 ..( see table dsd), results for shotwight mg ( see tblShotWeight), resuilts for mPas ( see tblViskoznost)...

    i hope i explain now better....
    i

    thank you

  9. #9
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    and now my database
    Attached Files Attached Files

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I was on the road. Is there a table with a single record for each batch number, and presumably information about the batch? Normally I'd expect this to be displayed with a form and subforms. The main form would be based on the "batch" table, 5 subforms on your 5 tables. KontrolnaStevilka would relate them. That would allow the subforms to display the varying number of tests.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    Hello

    dont wory for late reply...today I destroy all my tables all my database...now i am doing again from begining... i am very confused now...i dont know anymore what is pk and what fk....what to do....

    yes i have table called tblAnalysis...inside i have AnylsisID and batch nmber....but i dont know anymore how to connect .... when i try to connect relationship i dont see what i want to see,..

    can you make me some example with what you say? some example of making relationship with 5 ore more tables...so then i will study and try to male my database in this why...

    The main form would be based on the "batch" table, 5 subforms on your 5 tables. KontrolnaStevilka would relate them. That would allow the subforms to display the varying number of tests.

    thank for you help

  12. #12
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    I think you want to ignore the formal Access Relationships and instead use Queries to connect your tables. The Primary Key (PK) is only a Primary Key for one table (unless you are building extension tables but I don't think you are). The Foreign Key is a field in the table that usually contains the Primary Key of another table.

    See the attached picture for an example. In this query, the data from all three tables will be pulled back based on the KontrolnaStevilka. If you wanted to use subforms, then the Parent and Child fields would be KontrolnaStevilka.

    Click image for larger version. 

Name:	Access_Keys.jpg 
Views:	16 
Size:	67.2 KB 
ID:	36058

  13. #13
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    Heloooo
    yes yes yess this is what i want. thank youuu.
    now i was testing 5 tables...and I realise if i have the same (batchnumber) KontrolnaStevilka and five tables...and maybe in one table i dont have the results..then the query will show no data. is that true?
    only if all 5 tables have insdide data for the same KontrolnaStevilka the query will show data for that batch. is my thiknikg correct?

    thank you again

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It's up to you. You can edit the join by right or double clicking on it and select the desired option.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    Lenche is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    10
    yes

    thank you both. now i have plan what i have to do.

    thank you

    best regards

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

Similar Threads

  1. Replies: 4
    Last Post: 03-08-2017, 08:56 AM
  2. Replies: 2
    Last Post: 10-21-2013, 03:50 PM
  3. Replies: 3
    Last Post: 10-25-2012, 11:21 AM
  4. Replies: 5
    Last Post: 12-23-2011, 12:03 AM
  5. Query: Two tables With Yes/No Filed
    By Judasdac in forum Queries
    Replies: 6
    Last Post: 10-17-2011, 12:24 AM

Tags for this Thread

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