434,677 Members | 1,076 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Need help with graph

 P: 57 Hi, I need to create a graph report that showed total hours and unattended hours based on certain months and operation code that user input into the form. I have two queries: one to calculate the total hours worked and the other is to calculate total unattended hours. I also have a form, where user input what months and what operation code that user wants to see on the graph. now I need the graph to show on the primary axis is the total from total hours query and on the secondary axis is total from unattended hours query. I can't combine the 2 queries together since it gave me duplicated total and unattended hours (that's why I have to seperate the query into 2). can we use two queries on the graph? Could you help me what is the best way to do this report? This is the first time I'm using graph. Thank you Jul 23 '07 #1
3 Replies

 Expert 5K+ P: 8,634 Hi, I need to create a graph report that showed total hours and unattended hours based on certain months and operation code that user input into the form. I have two queries: one to calculate the total hours worked and the other is to calculate total unattended hours. I also have a form, where user input what months and what operation code that user wants to see on the graph. now I need the graph to show on the primary axis is the total from total hours query and on the secondary axis is total from unattended hours query. I can't combine the 2 queries together since it gave me duplicated total and unattended hours (that's why I have to seperate the query into 2). can we use two queries on the graph? Could you help me what is the best way to do this report? This is the first time I'm using graph. Thank you Post the SQL from the 2 Queries. How are the values for Month and Operation transferred to the 2 Queries? Jul 23 '07 #2

 P: 57 Post the SQL from the 2 Queries. How are the values for Month and Operation transferred to the 2 Queries? I have a form called selection that has 3 textboxes which are txtstartdate, txtenddate, and txtopcode for user to input the information. here is the query to calculate the total hours: (I create regular query, but here is the SQL view): SELECT labor_ticket.DATE, Sum(labor_ticket.HOURS_WORKED) AS SumOfHOURS_WORKED, Opcode.Department, Opcode.Opcode, (Format([DATE],"mm"" '""yy")) AS months_total FROM LABOR_TICKET INNER JOIN Opcode ON LABOR_TICKET.RESOURCE_ID = Opcode.Opcode GROUP BY LABOR_TICKET.DATE, Opcode.Department, Opcode.Opcode, (Format([DATE],"mm"" '""yy")) HAVING (((LABOR_TICKET.DATE) Between ([forms]![selection]![Txtstartdate]) And ([forms]![selection]![Txtend])) AND ((Opcode.Opcode)=[([forms]![selection]![Txtopcode])) ORDER BY LABOR_TICKET.DATE, Opcode.Department; and here is the query to calculate the total unattended hours: SELECT LABOR_TICKET.DATE , Sum(LABOR_TICKET.HOURS_WORKED) AS Unattended, Opcode.Department, Opcode.Opcode,(Format([DATE],"mm"" '""yy")) AS months_unattended FROM LABOR_TICKET INNER JOIN Opcode ON LABOR_TICKET.RESOURCE_ID = Opcode.Opcode WHERE (((LABOR_TICKET.EMPLOYEE_ID) Like "8*")) GROUP BY LABOR_TICKET.DATE, Opcode.Department, Opcode.Opcode, (Format([DATE],"mm"" '""yy")) HAVING (((LABOR_TICKET.DATE) Between ([forms]![selection]![Txtstartdate]) And ([forms]![selection]![Txtend])) AND ((Opcode.Opcode)=[([forms]![selection]![Txtopcode])) ORDER BY LABOR_TICKET.DATE, Opcode.Department; what I need is to create a chart report that consists total hours and total unattended hours values. could we use 2 queries in the chart report? Please help. Thank you so much Jul 24 '07 #3

 P: 57 I have a form called selection that has 3 textboxes which are txtstartdate, txtenddate, and txtopcode for user to input the information. here is the query to calculate the total hours: (I create regular query, but here is the SQL view): SELECT labor_ticket.DATE, Sum(labor_ticket.HOURS_WORKED) AS SumOfHOURS_WORKED, Opcode.Department, Opcode.Opcode, (Format([DATE],"mm"" '""yy")) AS months_total FROM LABOR_TICKET INNER JOIN Opcode ON LABOR_TICKET.RESOURCE_ID = Opcode.Opcode GROUP BY LABOR_TICKET.DATE, Opcode.Department, Opcode.Opcode, (Format([DATE],"mm"" '""yy")) HAVING (((LABOR_TICKET.DATE) Between ([forms]![selection]![Txtstartdate]) And ([forms]![selection]![Txtend])) AND ((Opcode.Opcode)=[([forms]![selection]![Txtopcode])) ORDER BY LABOR_TICKET.DATE, Opcode.Department; and here is the query to calculate the total unattended hours: SELECT LABOR_TICKET.DATE , Sum(LABOR_TICKET.HOURS_WORKED) AS Unattended, Opcode.Department, Opcode.Opcode,(Format([DATE],"mm"" '""yy")) AS months_unattended FROM LABOR_TICKET INNER JOIN Opcode ON LABOR_TICKET.RESOURCE_ID = Opcode.Opcode WHERE (((LABOR_TICKET.EMPLOYEE_ID) Like "8*")) GROUP BY LABOR_TICKET.DATE, Opcode.Department, Opcode.Opcode, (Format([DATE],"mm"" '""yy")) HAVING (((LABOR_TICKET.DATE) Between ([forms]![selection]![Txtstartdate]) And ([forms]![selection]![Txtend])) AND ((Opcode.Opcode)=[([forms]![selection]![Txtopcode])) ORDER BY LABOR_TICKET.DATE, Opcode.Department; what I need is to create a chart report that consists total hours and total unattended hours values. could we use 2 queries in the chart report? Please help. Thank you so much never mind I got it... Thank you.... Jul 25 '07 #4 