Results 1 to 13 of 13
  1. #1
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194

    howt o build this query!!! :(

    SO i think this query is going to be complex and i just cant work it out

    i have
    [tblpack]
    -packid
    -packuser
    checkuser
    collectuser

    [tbluser]
    userid
    username

    So i want this:

    Name Count Packed Count checked count Collect
    John 5 4 1
    Paul 7 55 5
    Ringo 1 1 2



    i literally dont have a clue how to get here!!
    harder than this i have [packdate] in the tblpack that i want to use as awhere clause.

    Now tbl user is connected to each other count queries, so i want to count how many times each user has appeared in the field of tblpack

    SQL for ONE is easy..
    SELECT tbluser.userfirstname, Count(tblpack.packid) AS CountOfpackid
    FROM tbluser INNER JOIN tblpack ON tbluser.userid = tblpack.packeduser
    GROUP BY tbluser.userfirstname;

    But i really need a hand getting the larger one

    Any help would be amazing.

    This will eventually be put into a matrix plot on RDLC in C# on visual studio..

    Gangel

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    First, build a UNION query:

    SELECT packid, "Packed" AS Category, packuser AS User FROM tblpack
    UNION SELECT packid, "Checked", checkuser FROM tblpack
    UNION SELECT packid, "Collected", collectuser FROM tblpack;

    Then build a CROSSTAB query using the UNION as source:

    TRANSFORM Count(packUNION.packid) AS CountOfpackid
    SELECT packUNION.User
    FROM packUNION
    GROUP BY packUNION.User
    PIVOT packUNION.Category;

    Include tbluser in the CROSSTAB to retrieve user names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    June you are amazing.
    I'm relatively new (self taught) and i ahve NEVER known about union queries. Thanks so much for that

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    You can get same with a single query:
    SELECT
    u.Name,
    COUNT(p1.PackID) as PackCnt,
    COUNT(p2.PackID) as CheckCnt,
    COUNT(p3.PackID) as CollectCnt
    FROM
    tblUser u
    LEFT OUTER JOIN tblPack p1 ON p1.PackUser = u.UserID
    LEFT OUTER JOIN tblPack p2 ON p2.CheckUser = u.UserID
    LEFT OUTER JOIN tblPack p3 ON p3.CollectUser = u.UserID
    GROUP BY
    u.Name

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Arvi, did you test that query? I tried copy/paste your statement and it errors.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    tbh i find Junes a bit more straightforward.

    I just came ot my next snag! haha DAMN i thought i was going so well!!
    I tried again for an hour and failed so im back

    Within [tblpack] i also have [errorcount]

    this is a count of how many errors people made whilst packing the 'packs'
    (btw this is for my pharmacy, so people making errors in packing medications is BAD! lol)

    Now if i just add

    UNION SELECT packid, "Error", packerrors FROM tblpack

    The final cross tab will only count "how many packs had errors"
    but what i need is "how many ERRORS are in each pack" COUNTED and then grouped by user.

    I tried doing a second 'category' and had the sum(packerrors) in it, and then 0 for others, but obviously that gave me a whole bunch of each columns with names "0" (fail )

    i also cant SUM the column in the crosstab, as that is the pack id, so onceagain.. thats not right.

    More advice please, im learning fast, just cant figure the specifics of this one out.





  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You want all users who handled a pack to get the error count? Or just associate count with packuser? What is the errorcount - just discrepancy in number of pills? Do you want to sum the discrepancy or count (each occurrence counts as 1 regardless of how many pills miscounted)?

    Revise the UNION:

    SELECT packid, "Packed" AS Category, packuser AS User, errorcount FROM tblpack
    UNION SELECT packid, "Checked", checkuser, null FROM tblpack
    UNION SELECT packid, "Collected", collectuser, null FROM tblpack;

    Build an aggregate query (change Sum to Count if you prefer):

    SELECT packUNION.User, Sum(packUNION.errorcount) AS PackError
    FROM packUNION
    GROUP BY packUNION.User;

    Include the aggregate in the CROSSTAB:

    TRANSFORM Count(packUNION.packid) AS CountOfpackid
    SELECT packUNION.User, tblUser.UName, PackErrorcount.SumError
    FROM PackErrorcount INNER JOIN (tblUser INNER JOIN packUNION ON tblUser.UserID = packUNION.User) ON PackErrorcount.User = packUNION.User
    GROUP BY packUNION.User, tblUser.UName, PackErrorcount.PackError
    PIVOT packUNION.Category;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by June7 View Post
    Arvi, did you test that query? I tried copy/paste your statement and it errors.
    I tested it now (I don't have MS Office at home). I forgot about one MS Access query oddity (multiple joins must be grouped with brackets) - the working one will be:

    SELECT u.UserName, Count(p1.PackID) AS UserCnt, Count(p2.PackID) AS CheckCnt, Count(p3.PackID) AS CollectCnt
    FROM (((tblUser u LEFT JOIN tblPack p1 ON u.[UserID] = p1.[PackUser]) LEFT JOIN tblPack p2 ON u.[UserID] = p2.[CheckUser]) LEFT JOIN tblPack p3 ON u.UserID = p3.CollectUser)
    GROUP BY u.UserName;

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It almost works but the results for CollectCnt are not correct. I added in ErrorCnt and it also has wrong results.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    OK. Multiple join's cause records appearing multiple times, so it doesn't work at all here. Try a Raw Force Method instead:

    SELECT
    u.UserName,
    (SELECT COUNT(p1.PackID) FROM tblPack p1 WHERE p1.PackUser = u.UserID) AS PackCnt,
    (SELECT COUNT(p2.PackID) FROM tblPack p2 WHERE p2.CheckUser = u.UserID) AS CheckCnt,
    (SELECT COUNT(p3.PackID) FROM tblPack p3 WHERE p3.CollectUser = u.UserID) AS CollectCnt
    FROM tblUser u

  11. #11
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    thanks everyone!
    The errors worked well.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Arvil, that does work - not quite sure how. Don't recall ever nesting queries like this. Learned something new. One SQL statement and avoids UNION and CROSSTAB.

    The table aliases aren't essential, runs fine without. I modified to include ErrorCnt.

    SELECT
    UserName,
    (SELECT COUNT(PackID) FROM tblPack WHERE PackUser = UserID) AS PackCnt,
    (SELECT COUNT(PackID) FROM tblPack WHERE CheckUser = UserID) AS CheckCnt,
    (SELECT COUNT(PackID) FROM tblPack WHERE CollectUser = UserID) AS CollectCnt,
    (SELECT Sum(ErrorCount) FROM tblPack WHERE PackUser = UserID) AS ErrorCnt
    FROM tblUser;

    I wonder how well any of these will perform with a very large dataset.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    You can make the query with joins working too - it needs only a bit tweaking. Something like:

    SELECTu
    .Name,
    SUM(1/(Iif(Nz(p1.PackUser,0)=0,0,1)+Iif(Nz(p1.CheckUser, 0)=0,0,1)+Iif(Nz(p1.CheckUser,0)=0,0,1))) as PackCnt,
    SUM(1/(Iif(Nz(p2.PackUser,0)=0,0,1)+Iif(Nz(p2.CheckUser, 0)=0,0,1)+Iif(Nz(p2.CheckUser,0)=0,0,1))) as CheckCnt,
    SUM(1/(Iif(Nz(p3.PackUser,0)=0,0,1)+Iif(Nz(p3.CheckUser, 0)=0,0,1)+Iif(Nz(p3.CheckUser,0)=0,0,1))) as CollectCnt,
    FROM tblUser u (((LEFT OUTER JOIN tblPack p1 ON p1.PackUser = u.UserID) LEFT OUTER JOIN tblPack p2 ON p2.CheckUser = u.UserID) LEFT OUTER JOIN tblPack p3 ON p3.CollectUser = u.UserID)
    GROUP BY u.Name

    It is unlikely this query will be the faster one, but you an test it, when another takes a minute or more.

    This query takes into account, that you have different types of same parameter in separate fields in same row. This is the reason, every row of tblPack is added into join query 1, 2, 3, ... times - depending how many non-zero parameter values are in row.

    You can have a separate table tblPackOperations, with structure PackID, OpType, UserID instead, where OpType has values p.e. 1, 2, 3, ... corresponding with Packing, Checking, Collecting, ..., and register pack operations there instead. Then the regular join query I adviced as first will work (after you edit join conditions).

    Btw., maybe you consider changing the table structure and adding table tblPackOperation, and add an additional table for operation types (p.e. tblOpTypes: OpType, OpName) - then later you don't need to redesign your forms whenever you decide you need an additional operation (of course it means, that now you have to redesign your form(s)).

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

Similar Threads

  1. VBA Query Build
    By jo15765 in forum Programming
    Replies: 4
    Last Post: 03-20-2017, 08:14 AM
  2. Build Expression in Query
    By rwpspackett in forum Queries
    Replies: 1
    Last Post: 11-23-2015, 02:16 PM
  3. Build Query
    By Kyoshikawa in forum Queries
    Replies: 7
    Last Post: 10-06-2015, 10:03 PM
  4. How to build query
    By kashif.special2005 in forum Queries
    Replies: 2
    Last Post: 03-02-2012, 02:18 AM
  5. Hellp with Query Build
    By zarfx4 in forum Queries
    Replies: 0
    Last Post: 05-28-2009, 08:21 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