Comparing Data in a Query (1 Viewer)

[Christopherusly]

I have two tables: tbl_finance actually records the hours worked on a job, whereas tbl_esttime records the hours that I think are going to be worked on a project (for estimating budget purposes)

When i run qry_estimatedhours - i get a nice total of the number of hours by grade that are estimated.

When i run qry_actualhours - i get a nice total of the number of hours that have actually been worked (although i think the PM's are taking the piss)

What i want is a query that actually looks at both sets of data and then gives me a side by side comparision by grade

i.e.

Engineer in tbl est time value next to tbl_finance engineers time.

So i thought, ah ha, join the two queries - and then use the result: see qry_sidebysidelookup, ah no.

Any suggestions i am stuck

thanks you guys, see attached DB

 

[Christopherusly]

Okay so i have changed the relationship from JobNO - Job NO to Grade - Grade, and as long as both tables contain the list of grades, it seems to work ... is there anyway of doing this so that i do not need to have a list of all the grades that *might* appear in tbl_finance in tbl_estime with 0 hrs allocated ?

 

[Brianwarnock]

Change the join to an outer join left or right to pull all the records from finance.

Right click the join line and select as appropriate .

Brian

 

[Christopherusly]

SELECT qry_actualhours.[Job NO], qry_actualhours.Grade, qry_actualhours.SumOfHR, qry_estimatedhours.SumOfHours, [SumofHours]-[SumofHr] AS Total, tbl_projectdetails.LiveProject
FROM (qry_estimatedhours INNER JOIN qry_actualhours ON qry_estimatedhours.Grade = qry_actualhours.Grade) INNER JOIN tbl_projectdetails ON qry_estimatedhours.[Job NO] = tbl_projectdetails.[Project Code]
WHERE (((qry_actualhours.Grade)="Project Manager") AND ((tbl_projectdetails.LiveProject)="YES"));

seems to do the trick, using an inner join, i have not used an outer join before, i do not think anyway.... any pointers ?

 

[Brianwarnock]

So you have added a new table to the DB, I just answered your original question.

 

[Brianwarnock]

thanks for the negative rep points.
Don't you think that it is a bit childish, and bad manners to give negative rep points to somebody that tried to help?

You totally changed your requirements making my response of no value, that's hardly my fault.

Brian

 

[vbaInet]

thanks for the negative rep points.
Don't you think that it is a bit childish, and bad manners to give negative rep points to somebody that tried to help?

Are you talking about the rep in blue? If it is, then it's not negative. Apparently, newly registered users can click on the scales and write a comment but cannot give points until after certain criteria are met. In that case it will be blue.

 

[boblarson]

Are you talking about the rep in blue? If it is, then it's not negative. Apparently, newly registered users can click on the scales and write a comment but cannot give points until after certain criteria are met. In that case it will be blue.

vbaInet the user has been registered on the board long enough to effect negative points. And based on what I saw there should not have been a disapproval given by the OP.

 

[vbaInet]

vbaInet the user has been registered on the board long enough to effect negative points. And based on what I saw there should not have been a disapproval given by the OP.

I see. I thought they had to have posted a certain number of posts etc... to be allowed to give rep points.

 

[boblarson]

I see. I thought they had to have posted a certain number of posts etc... to be allowed to give rep points.

You also get 1 point for each year on the board regardless of post count.

 

[vbaInet]

You also get 1 point for each year on the board regardless of post count.

1 point. Yay!!! How generous of AWF!

 

[boblarson]

1 point. Yay!!! How generous of AWF!

That is - number of rep points that are given to whomever you give positive rep to. Negative rep comes off at half as much. So for example when I rep someone they get 36 points currently. If I give them negative approval then they would have 18 points taken away. And of that 36 point rep 9 points are because I've been registered for 9 years. And, while 1 point may sound insignificant, since the whole rep system here is based on that and the other points like the number of posts, and even your reputation, I wouldn't count it generous or not but what it is...

 

[vbaInet]

So for example when I rep someone they get 36 points currently

Favouritism

And, while 1 point may sound insignificant, since the whole rep system here is based on that and the other points like the number of posts, and even your reputation, I wouldn't count it generous or not but what it is...

How does that one work? You get rep points based on the number of posts you've accumulated within a year?

 

[boblarson]

How does that one work? You get rep points based on the number of posts you've accumulated within a year?

Not within a year but just as you hit whatever level vBulletin has set. I don't think Jon ever modified those default settings.

 

[vbaInet]

I get you. You're right, I don't think he has. I wonder how much they weigh if it were active.

 

[Christopherusly]

thanks for the negative rep points.
Don't you think that it is a bit childish, and bad manners to give negative rep points to somebody that tried to help?

You totally changed your requirements making my response of no value, that's hardly my fault.

Brian

I am very sorry to have some greiviously offended you Brian, for whatever reason at the time, i thought the comment left was reasonable, so i do not believe i was acting in a childish manner nor with any particulary bad manner, obviously this is something that you have taken to heart - it was never my intention to upset you.

My apologies.

Christophe.

 

[Brianwarnock]

I accept your apology, but will remind you that the people posting on here are volunteers giving freely of their time and doing their best to help, not always successfully.

If you have a problem with a poster raise it with them first, and if serious leave a message for the moderators by clicking on the red triangle next to the scales.

I consider this matter closed and will add to the other thread.

Brian

EDIT I see that you have deleted the other thread. You can post again knowing that I will not say anything.