Results 1 to 15 of 15
  1. #1
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103

    Create Summary of Multiple Queries

    Hi Experts!
    I am facing a problem. I have a DB in which I have 4 categories of queries and I want to compile them in one query. But when I tried to do the same its not give the accurate result. Its give the multiple categories instead of single category
    as detailed below:


    Suppose I have 27 categories of status and when I open the combine query its shows the hundreds of states for the combine query. It just show the result of each category for one time in query.
    I wants, It just shows the query as the category of status is in Form2
    as
    Status Transfer Pending Institution Decided
    Evidence 1 2 3 4
    Argument 1 2 3 3
    but its shows dozens of rows of evidence instead of only one
    DB Sample is attached
    MultiCretariaReport.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think you want a CROSSTAB query. Try using the query design wizard. Should produce something like:

    TRANSFORM Count(Form1.ID) AS CountOfID
    SELECT Form1.Stage
    FROM Form1
    GROUP BY Form1.Stage
    PIVOT Form1.Status;
    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
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by June7 View Post
    I think you want a CROSSTAB query. Try using the query design wizard. Should produce something like:

    TRANSFORM Count(Form1.ID) AS CountOfID
    SELECT Form1.Stage
    FROM Form1
    GROUP BY Form1.Stage
    PIVOT Form1.Status;
    No June7 sorry I don't want to use cross tab query because I am going to use date criteria in this query for sorting out the data, so I have to use simple query.
    and sorry to say I dont understand the method because I am beginner. so let me understand in simple way or check the DB and change the same plz.
    Advance Thanks

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I do not understand what you are trying to accomplish. Perhaps you could describe what the data represents and the "business involved". I realize you are a beginner. Once you are more familiar with database and Access, you will not name your tables as Form1 and Form2. Tables, queries and forms are different things with different purposes.

    What is the difference of Pending_query and Query_Pending?? Why both?

    Can you tell us what either of these queries is suppose to do?
    Might be easier if you describe things in simple terms in your native language and then use Google translate to convert to English - then post the English.
    Good luck.

  5. #5
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    [QUOTE=orange;464922]I do not understand what you are trying to accomplish. Perhaps you could describe what the data represents and the "business involved". I realize you are a beginner. Once you are more familiar with database and Access, you will not name your tables as Form1 and Form2. Tables, queries and forms are different things with different purposes.
    Yes you are right I am beginner and Form1 and Form2 is just for sample names that may be table1 or tabl2
    What is the difference of Pending_query and Query_Pending?? Why both?
    I have removed Pending_Query which was making problem
    Can you tell us what either of these queries is suppose to do?
    I want the result
    If I open the query of Summary Complete it should show the result of Evidence as pending (how much) Decided (how much), Transfer (how much), Instituted (how much)
    But its shows a long list of evidence instead of single line summary. This is a problem

    This is sample that I want. But my query shows the mutiple rows of single status
    Click image for larger version. 

Name:	sample.jpg 
Views:	45 
Size:	54.3 KB 
ID:	43353I have attached again the file plz check it
    MultiCretariaReport.zip

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    These are the only statuses in your Form1 table???
    Status

    Decided
    Pending
    Transfer

    And these are your stages

    Stage
    Agreement
    Arguments
    Attendence
    CDR
    Copies
    Evidence
    Habious
    Latest
    Mafroor
    New commer
    Order
    Process
    Remaining Evidence
    Report173
    Starting Arguments
    Statement
    Submition
    Summon
    طلبی ملزم
    فرد جرم
    مزیدکاروائی
    نوٹس
    نوٹس/طلبی
    ہمراہ مسل




    Try this and see if it's close to what you are seeking.

    Code:
    TRANSFORM Count(Form1.[ID]) AS CountOfID
    SELECT Form1.[Stage], Count(Form1.[ID]) AS [Total at Stage]
    FROM Form1 where status is not null
    GROUP BY Form1.[Stage]
    PIVOT Form1.[Status];
    Result:

    Stage Total at Stage Decided Pending Transfer
    Agreement 2 2

    Arguments 195 191 1 3
    Attendence 2 2

    Copies 1 1

    Evidence 81 50 23 8
    Habious 23 23

    Latest 75 74
    1
    Mafroor 4 3 1
    New commer 1 1

    Order 5 3
    2
    Process 2 2

    Remaining Evidence 2 1
    1
    Report173 19 18 1
    Starting Arguments 3 3

    Statement 3
    1 2
    Submition 1 1

    Summon 145 136 4 5
    طلبی ملزم 21 19
    2
    فرد جرم 44 37 6 1
    مزیدکاروائی 3 3

    نوٹس 1 1

    نوٹس/طلبی 1 1

    ہمراہ مسل 10 6 3 1


    Good luck

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And if you want to apply dynamic filter criteria to a date/time field, use PARAMETERS clause. Review http://allenbrowne.com/ser-67.html

    This is really quite basic Access functionality. Have you studied an introductory tutorial?
    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
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by orange View Post
    These are the only statuses in your Form1 table???

    Try this and see if it's close to what you are seeking.

    Code:
    TRANSFORM Count(Form1.[ID]) AS CountOfID
    SELECT Form1.[Stage], Count(Form1.[ID]) AS [Total at Stage]
    FROM Form1 where status is not null
    GROUP BY Form1.[Stage]
    PIVOT Form1.[Status];

    Good luck
    Thanks its worked as i want but only for all data, not filtered, I have to filter the data:

    I have given the parameters but still its not working.
    I have to put the criteria for Dates.
    Date_of_Institution
    Date_of_Decided

    and no date criteria for Pending
    Now let me know what to do for this and I have also generate the Report after this query. and in the query if sometimes there is no figure in transfer or decided it may be empty in report instead of error
    again the file is attached
    MultiCretariaReport Date Parameter.zip

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How do you want to filter with these date fields? Do you want single date or a range on each? The same date range for both fields? Do you want AND or OR operator?
    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
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by June7 View Post
    How do you want to filter with these date fields? Do you want single date or a range on each? The same date range for both fields? Do you want AND or OR operator?
    I want between date formula
    Date_of_Institution the query filter the data of institution as between dates which should be written on form
    From --:--:----
    To --:--:----and the criteria should be placed on
    date between for Date_of_Decided having only status of "decided"
    date between for Date_of_Institution having status of anyone "decided, pending, transfer etc"
    while pending have no criteria it have to show all data having pending status
    The data should be same as I made the other one by one queries separately. The queries created one by one are showing correct data and also filtering data but I want to join these 4 queries in one query so that I may generate the report.
    If these 4 queries are managed in one report without creating any query its fine and sufficient for me.
    I think I am unable to define my problem due to my less knowledge:

    Query_Decided
    Qury_Instituted
    Qury_Pending
    Query_Transfer

    I have these 4 queries.
    I Just want to compile them in one query so that it show the result as you create above one but with Date Criteria as mentioned in these 4 queries
    Now I have updated the file and attached again so that you may help me in this way.

    MultiCretariaReport.zip
    Thanks in Advance

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Pending query does not have date filter criteria.

    Is Table2 the result you are looking for? What date range criteria should be used?

    You have only two date boxes so this means each query must use the same date range criteria. The Institution and Decided dates are so far apart, how could they use the same date range criteria and return correct records?
    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.

  12. #12
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by June7 View Post
    Pending query does not have date filter criteria.

    Is Table2 the result you are looking for? What date range criteria should be used?
    Yes but table one may also be understand as complete table and also filtered for the next.

    You have only two date boxes so this means each query must use the same date range criteria. The Institution and Decided dates are so far apart, how could they use the same date range criteria and return correct records?
    yes exactly but I have created 4 queries. I want to say that is there anyway to create a report for these 4 queries!!!
    I have created a report by placing the 4 queries in that report separately but I want to merge this report as you make above.

    However is there any way to give date criteria to crosstab query which you have created above so that the data may be filter???

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Again, if all 4 queries must be run at same time for same report, how can using the same date range for Institution and Decided dates return correct results?

    Yes, crosstab can have filter criteria as already described. Here is another article about crosstab queries http://allenbrowne.com/ser-67.html#Param
    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.

  14. #14
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by June7 View Post
    Again, if all 4 queries must be run at same time for same report, how can using the same date range for Institution and Decided dates return correct results?

    Yes, crosstab can have filter criteria as already described. Here is another article about crosstab queries http://allenbrowne.com/ser-67.html#Param

    Ok I think its so difficult

    1 But I have made a Report working for these for queries let me suggest if it may be compiled in single line.
    2. Let me know how to set the query that it may not skip the zero (0) result. Means Its shows all the results including zero result too.

    I just want the full queries including Zero result of Sum_Decided/Sum_Pending/Sum_Instituted/Sum_Transfer. If its done I shall prepare my query as well.

    I am attaching again my file so that it may be clear for your to help me.
    MultiCretariaReport.zip

    Thanks in Advance

  15. #15
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by ijaz8883 View Post

    Ok I think its so difficult

    1 But I have made a Report working for these for queries let me suggest if it may be compiled in single line.
    2. Let me know how to set the query that it may not skip the zero (0) result. Means Its shows all the results including zero result too.

    I just want the full queries including Zero result of Sum_Decided/Sum_Pending/Sum_Instituted/Sum_Transfer. If its done I shall prepare my query as well.

    I am attaching again my file so that it may be clear for your to help me.
    MultiCretariaReport.zip

    Thanks in Advance
    Anyone Plz?

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

Similar Threads

  1. Multiple queries or create a table?
    By pncbiz in forum Access
    Replies: 3
    Last Post: 10-12-2020, 10:05 AM
  2. Create Summary Query
    By wdyl121 in forum Access
    Replies: 1
    Last Post: 10-31-2016, 03:03 AM
  3. Replies: 17
    Last Post: 08-09-2015, 09:45 AM
  4. Summary Queries?? project due tomorrow
    By sloppyjoe778 in forum Queries
    Replies: 7
    Last Post: 11-22-2011, 09:41 PM
  5. Queries with multiple tables to create report
    By Solstice in forum Queries
    Replies: 1
    Last Post: 09-22-2011, 02:23 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