By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,076 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
3 Replies


ADezii
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

Post your reply

Sign in to post your reply or Sign up for a free account.