Results 1 to 8 of 8
  1. #1
    orcaa is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    19

    how to show data with select query over 5 tables

    Hi I'm new to this forum and to Access.
    I have created a database for the storage of plates in freezers with 6 tables. Plates belong to a library ( eg plaate01, plate02 are part of library with the name Serie_A)

    The plates are stored in a rack and there can be 10 plates in a rack ( so plate01 until plate10 in rack_01 and plates plate11 until plate20 in rack_02) and these racks are stored in a freezer .
    There are 6 tables (T_Plate, T_Rack, T_Freezer, T_Rack_Plate, T_Freezer_Rack and T_Library)

    If I create a select query for plates that are in a rack in a freezer the plates with platename, checkindate, rackname, freezername and libraryname are shown. But there are also plates that are not in a rack and not in a freezer but out on loan. And also these plates should show up if I run that select query.
    I understand that it is difficult to answer if you dont see the relationships or the database an therefor these attachments



    Can someone help me? Tried to attach zip file, but is too big. Is there an other way to attach zipfile
    Attached Thumbnails Attached Thumbnails Relations_vriezer_c.JPG  

  2. #2
    orcaa is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    19
    Sorry is the wrong relationpictures
    one table is missing

    This is the right one
    Click image for larger version. 

Name:	Relations_vriezer_d.JPG 
Views:	6 
Size:	40.6 KB 
ID:	10500

  3. #3
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Run a compact and repair before uploading the db. That may reduce the size. Also, you do not have to include all data. Only a reasonable sample size.

  4. #4
    orcaa is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    19
    Hi,
    This is the zipped database . In the tabel plates there are 42 plates and for plate_41 and plate_42 there are no corresponding data in the other tables. But I like to show with a query also these plates


    Thanks

    Vriezer_20121228a.zip

  5. #5
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    If I understand completely, then the attached is what you want. It was a case of replacing inner joins with outer joins.
    Attached Files Attached Files

  6. #6
    orcaa is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    19
    Sorry, I don't see a difference. Will try to explain what I want.

    In the table "T_Plates" are 42 plates with platenames "Plate01" until "Plate42".
    Plates "Plate01" until "Plate40" are stored in a rack with a racknumber. These racks are stored in a freezer. But only plates "Plate41" and "Plate42" are not stored but out on loan.
    Now I want to perform a query "Q_Select_Plate_Name" and when I put in fi "4" in this box. Then all plates with "4" in there name should appear with the corresponding data. Like in the screenshot in the attachment, but the problem is that plates "Plate41" and "Plate42" are not shown. And also the plates that are not in a rack and in a freezer should be shown.
    So I don't know if this is related to the inner or outer joiint settings or because there is no corresponding data in the other tables

    Please help
    Click image for larger version. 

Name:	result_q_plates.JPG 
Views:	5 
Size:	41.5 KB 
ID:	10514

  7. #7
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I only adjusted the query for the one that was listed as All. You did not specify which query was the issue. If you look at that one, I changed it to an outer join and it included all 42 plates where before the join change it was only showing 40 plates. Look at that example and make the same changes to all queries you wish to have this type of presentation. Here is an explanation on joins if you need a refresher

    http://office.microsoft.com/en-us/ac...001034555.aspx

  8. #8
    orcaa is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    19
    Alansidman,
    thanks for your help. The problem is solved

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

Similar Threads

  1. Replies: 2
    Last Post: 08-07-2012, 03:58 PM
  2. Replies: 1
    Last Post: 06-27-2012, 09:16 AM
  3. Show data from query in a text box
    By jeffyyy in forum Forms
    Replies: 8
    Last Post: 10-16-2010, 11:45 AM
  4. Replies: 4
    Last Post: 12-16-2009, 07:31 AM
  5. Replies: 9
    Last Post: 03-24-2009, 09:19 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