Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53

    Access queries for left join

    SELECT StationQuery.Tooling_Station, manu.Man, autom.Auto, manu1.Man1, autom1.Auto1


    FROM (((StationQuery LEFT JOIN manu ON StationQuery.Tooling_Station = manu.Tooling_Station) LEFT JOIN autom ON StationQuery.Tooling_Station = autom.Tooling_Station) LEFT JOIN manu1 ON StationQuery.Tooling_Station = manu1.Tooling_Station1) LEFT JOIN autom1 ON StationQuery.Tooling_Station = autom1.Tooling_Station1;

    this was my query to get a result for Man,Auto,Man1,Auto1 ,Now I want to add man and man1 but the result what am getting is partial i.e. if I have values in both man and man1 its showing result but remaining fields are blank if either man or man1 is blank ????

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not quite sure what your problem is, sounds like the left join is doing what is expected - displaying stationquery if no matching records in manu and/or autom and/or man1 and/or autom1.

    perhaps show some example data of what is in each table and the outcome from the query to demonstrate the problem

  3. #3
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53
    thanks for the reply ,

    my data is as follows


    Lead time [h] Manual [m] or automated [a] Tooling / Station Tooling / Station
    0,12 a Skin Mould Cleaning
    0,05 a Skin Mould Cleaning
    0,37 m Demoulding
    0,19 a Skin Mould Cleaning
    0,21 m Skin Mould Cleaning
    0,57 a ATL
    2,14 a ATL
    1,33 a ATL
    0,50 m ATL
    39,41 a ATL
    2,00 m ATL
    0,72 a ATL
    2,47 a ATL
    1,00 m ATL
    2,50 m Bagging
    1,00 m Bagging
    8,00 a ACL
    3,33 m Debagging
    0,66 m Debagging
    0,33 m Bagging
    4,00 m Bagging
    1,00 m Manual work place
    2,14 a Stringer tool cleaning
    1,03 a Stringer tool cleaning
    2,00 m FLU
    25,95 a FLU
    3,57 a Cutter
    2,50 a Pressforming
    0,75 a Pressforming
    3,20 a Stringer tool set up
    0,83 a Stringer tool set up
    3,57 a Stringer tool set up
    1,07 m Stringer tool set up
    1,07 m Stringer tool set up
    1,07 m Stringer tool set up
    0,25 m Stringer positioning
    2,22 a Stringer positioning
    1,83 m Stringer positioning
    3,75 m Bagging
    1,00 m Bagging
    8,00 a ACL
    3,33 m Debagging
    0,66 m Debagging
    2,52 a Demoulding
    1,00 m Demoulding
    0,50 m Demoulding Machining
    1,00 m Machining
    1,00 a Machining
    11,34 a Machining
    7,77 a Machining
    14,80 a Machining
    0,85 m Machining
    0,50 m Machining Turning station 90°
    1,00 a Turning station 90°
    8,00 a Dimensional Inspection
    2,68 m WS A
    2,14 a NDT
    0,89 a NDT
    6,70 m WS A
    2,22 m WS A
    0,13 a Painting
    11,80 a Painting
    11,80 a Painting
    4,65 m Painting
    2,68 m Painting
    0,43 m Painting
    5,83 m WS SC
    3,00 m Shipping area Turning station 90°

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how does this relate to your query? is this the result or the tables? if the latter I would expect to see 5 tables. Use headers as per your query

  5. #5
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53
    and I want data to be like this

    tooling/Stationen Manual Lead time automatedl Lead time Manual Lead time automated Lead time
    (Unique values ) for 1st tooling station for 1st tooling station for 2nd tooling station for 2nd tooling station
    WRT tooling station WRT tooling station WRT tooling station WRT tooling station



    eg

    Skin Mould Cleaning 0,2 0,4 0,0 0,0



    Demoulding


    Atl





    this way I want a result

  6. #6
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53
    sorry ,
    1) I have written a query using join to first list down all station
    2) then I have written query to list all manual for 1st station
    3)then I have written query to list all automated for 1st station
    4) then I have written query to list all manual for 2nd station
    5)then I have written query to list all automated for 2nd station


    now I have left joined
    1 st query with 2,3,4,5 where station is made equls to all of them

  7. #7
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53
    that table is my main table using that I have to list
    Stationen

    manuel lead time for 1 st station


    automated lead time for 1 st station

    manuel lead time for 2 nd station

    automated lead time for 2 nd station

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry for not responding - been to a client. Regret I do not understand your posts, from what I can see your initial query should provide the data your require. although what you want is 9 columns and your query only returns 5

    if necessary, mock you data up in excel and post a screenshot - include column headers as they are in your query

  9. #9
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53

    To ajax

    Very good Morning!!!!!!
    No problem thanks for your precious time .

    I have attached the screenshots

    In sc1.png I want to select only Lead_time , Manual_Automated , Tooling_station , Tooling_station1( that is my main table ) to get sc2.png

    to get sc2.png

    a) I have written a query to get all station listed using join and query name is StationQuery
    b) I have written a query to get all manual from tooling station of sc1.png query name manu
    c) I have written a query to get all Automated from tooling station of sc1.png query name autom see sc3.png
    d) I have written a query to get all manual from tooling station1 of sc1.png query name manu1
    e) I have written a query to get all Automated from tooling station1 of sc1.png query name autom1

    Now I want to add manu and manu1 , autom and autom1 in sc2.png but its not working
    Attached Thumbnails Attached Thumbnails sc1.png   sc2.png   design view of sc2.png  

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it looks to be working to me - if you mean because the Auto1 column is completely blank - show me a record in Autom1 that has a toolingstation1 value which matches one of the toolingstation values in stationquery displayed in your example. Same for man1/manu1 - any record except Machining and TurningStation 90.

  11. #11
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53
    yes , you are right as of now in my table (1st screenshot) their is no tooling station1 that is automated but in future their might be so I have kept that one
    now after getting the result (i.e. screenshot2 ) in same table (i.e. query) I want to add man and man1 as something like Manual also auto and auto1 as automated

  12. #12
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53
    this what I want as my final result

    i.e my final manual contains adition of man and man1 same for automated auto and auto1
    Attached Thumbnails Attached Thumbnails final.png  

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK - so you want a zero to display if there is no value?

    if so, you have a number of choices.

    1. change the selection of the field from say

    auto1

    to

    Automated1:nz(auto1,0)

    easy solution, but you then lose the ability to see where there is no value (as opposed to a value of 0) - light matter if you are counting records for example. Note that you cannot call it the same as an existing field in your data source

    2. leave field selection as is and use the format property

    right click on the column and select properties>format and enter the following

    ;;;0.00

    number have 4 formats separated by semi colons - positive;negative;zero;null - so you may want to also use the first one and highlight negative values in which case you could use 0.00;[red]0.00;0.00;0.00

    note that format properties will not be carried forward to a form or report - you set the property there instead (normally you do not worry about presentation in a query since users never see them.

    3. use the round function

    Automated1:round(nz(auto1),2)

  14. #14
    kiranair is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    53
    as you said am not in need of "0" , but my problem is am unable to add Man and Man1 also Auto and Auto1

    Here is how I am getting the result but I need like in excel previous message ( zero is not in my interest)
    Attached Thumbnails Attached Thumbnails abc.png   def.png  

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK - think I understand

    you need to use the nz function

    Manual:nz(manu.Man,0) + nz(manu1.Man1,0)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-05-2015, 02:28 PM
  2. Replies: 3
    Last Post: 06-30-2014, 12:53 AM
  3. Left Join Query Issue - MS Access
    By mkc80 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 04:17 PM
  4. Replies: 7
    Last Post: 09-06-2012, 06:04 AM
  5. Replies: 3
    Last Post: 02-02-2011, 01:00 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