A complicated query leading into a report problem.

[Christopherusly]

If you download the attached database, unzip, then run - select Frm_home to stat from then run the Mini WIP Report - you will see what i am talking about.

I am working on a database which records hours spent working on time charge jobs per person - this is for the engineering consultancy for which i work, in their infinite wisdom they have migrated over to SAP which has proven a royal pain in the ass ( i loved crystal reports as I was allowed access to the back end, so could create whatever reports i needed, oh no not with SAP tho, they have to protect from the likes of me, damn it. )

Introducing the tables.

Finance - this table hold individual transaction details for each time someone books time to a job, so one job code can have many records associated with it (in this case there is on job with over 500 transactions recorded against it)

ID
Attr: Project Managee
Field2
Job NO
Job Title
Network Activity
Work Stage
Employee No
Staff Member
Amey Fiscal Week Year
HR
£
£ / HR

My first question is .... okay, here goes - what is the best way to produce a summary query to lead into a report which says, right, okay, you have 500 records for one unique job code, i am going to sum all of the hours, and costs up and give it to you in a single line - so rather than showing the detail of the spend profile for the job, you get the total hours / total £ fee for the job.

I thought i had cracked this using the TOTAL > Group By > SUM, this is giving me some rather erratic numbers back which i know are incorrect - see attached PDF (Mini WIP Report)

This next bit might make some of your go what is he doing, but it seemed like a good idea at the time.

I have a second table - will will call this: tbl_esttime - this hold the precicted time each member of staff will book against the job - so the estimated time, which in turn gives you a total fee value, the fields are as such:

Job NO
Staff Member
Hours

Now onto the messy part:

At the moment i run query one as a make table query which gives me:

Job NO
Job Title
HR
Rate
Spend

So this table calculates what the actually value of each time charge is worth against each job no.

now this is not enough for what i want, so we move onto query two

this summarises all the transactions into a single line for each job code used - from table one we just made. (messy i know, but its the only way i know) but it gives me what i want

from this we move onto query three this takes the totals of the job transactions from the finance table, and puts them beside the totals for the estimated hours - this seemed like a good idea at the time...

but when you look at the result - it is all horribly wrong.

So this gives me see attached WIP report example, where the math is obviously incorrect, when you look at the column names you have sumofsumofsumofcost - whcih is where i suspect something has gone awry, but i am damned if i know what ...

Please feel free to treat me as a complete idiot as i really am quite stuck and my knowledge of Access is no great than being a tinker and someone who just about gets by.

Thanks for any comments and suggestions you guys are able to offer, they are appreciated.

Note: the attached ZIP file is actually a winrar achive that i have renamed the extension of - as when in the ZIP format the DB was over the 2mb limit, with the winrar it is not.

 

[vbaInet]

That's a rather long post you have here I haven't managed to read it all.

For the first bit:

Click QUERY WIZARD > NEXT
Select SIMPLE QUERY WIZARD > NEXT
Select your the fields > NEXT
Click SUMMARY > Click SUMMARY OPTIONS
Select the group you want to sum on > FINISH

 

[PNGBill]

Lets try and make this simple..

TableFinance has most or all of the data for each occurrence of work done on a job??

If so, just list the field names that are important eg, JobNo, Hours?? cost??

The Guts of this issue may well be 3 or 4 fields only.

If you need to reference another table, detail this

eg

TableFinance has JobNo, Hours, StaffID
TableStaff has StaffID, StaffName, ChargeRate

To get the cost of work done on Jobxyz you need to some the calculated costs from Hours * ChargeRate for each job using the rate for the relevant StaffID.

If I am correct, then you should supply just a few field names over 2 to 3 tables.

I will replicate this and give you an sql to get your sum of costs for a given job.

 

[PNGBill]

I did it anyway.

This sql will calculate the cost of each Job Record and sort by JobName and StaffName

SELECT TblFinance.JobNumberID, TblFinance.JobName, TblStaff.StaffName, TblFinance.HoursWorked, TblStaff.StaffChargeRate, [HoursWorked]*[StaffChargeRate] AS JobCosts
FROM TblStaff INNER JOIN TblFinance ON TblStaff.StaffID = TblFinance.StaffID
ORDER BY TblFinance.JobName, TblStaff.StaffName;

 

[PNGBill]

And this query will take the first query and sum the job costs for each jobName

SELECT QryJobCosts.JobName, Sum(QryJobCosts.JobCosts) AS SumOfJobCosts
FROM QryJobCosts
GROUP BY QryJobCosts.JobName;

 

[PNGBill]

attached is a simple relationships for the two tables used in my sql's

 

[Christopherusly]

PNGBill and vbaInet many thanks for your comments, let me try and explain what i am trying to achieve as having re read my previous post, i did not make a particularly good job of it (my bad, it was late)

tbl_Finance - this table hold individual transaction details for each time someone books time to a job, so one job code can have many records associated with it (in this case there is one job with over 500 transactions recorded against it)

ID - Auto number for index
Attr: Project Manager - Project Manager - text field
Field2 - Project Director - text field
Job NO - the unique job code for each job - text field
Job Title - the job title for each scheme relating to the unique job no.
Network Activity - the work activity that is being booked i.e. prelim design - text field
Work Stage - Alphanumerical code for Network activity
Employee No - Employee No - Number
Staff Member - Employee Name - Text field
Amey Fiscal Week Year
HR - Hours booked to the job - number field
£ - Cost - number field = HR*£
£ / HR SAP System person cost (not their charge out rate) Numberical Field

this table links the STAFF MEMBER fields between tbl_Finance and tbl_graderates

tbl_graderate

Staff Member - Staff name - text field
Grade - Staff grade - text field
Rate - Normal Cost Rate - numerical field
PCCRate - Special Cost Rate - numerical field
HCCRate - Special Cost Rate - numerical field
PRoWRate - Special Cost Rate - numerical field

So when i run a select query i use the following:

tbl_Finance, Job NO
tbl_Finance, Job Title
tbl_Finance, Network Activity
tbl_Finance, Work Stage
tbl_Finance, Employee No
tbl_Finance, Staff Member
tbl_Finance, Amey Fiscal Week Year
tbl_Finance, HR

tbl_graderate, Rate

then an expression Cost:[Rate]*

---

which give me the actual cost per transaction, now so far so good - i have all the number but what i need to do is total the numbers for each job no - to return a single line per job no.

so to do this, I have a few more steps to go through.

Firstly, I run an action query to create a new table – my query is called 1-qry_financetablecosts

And this takes the following fields and puts them in a new table for me:

tbl_Finance, Job NO
tbl_Finance, Job Title
tbl_Finance, Work Stage
tbl_Finance, HR
tbl_graderate, Rate

and expression:

Spend:

---

*[rate]

The second step involves

Tbl_esttime – this is a table which links to the finance table for all the hours that are estimated to be spent on the job – as you will see from my attached database it seems to work okay at the moment, this comprises of:

Tbl_esttime, Job NO
Tbl_esttime, StaffMember
Tbl_esttime, Hours
Tbl_esttime, Rate

And expression: Fees:Sum([hours]*[rate])

This is also an action query to make a new table for me - 3-tbl_fees which contains the calculated values for all the job transactions.

Now we come to the tricky part – pulling the two tables together to give me a single line PER job no from the finance table – total spend and from the fees table, estimated spend, to do this, I have the following:

A query called qry_feesvsspend

In which I have the following:

1-tbl_financetablecosts, job NO
1-tbl_financetablecosts, job title
1-tbl_financetablecosts, spend – this is totalled using the SUM

And finally

3-tbl_fees, Fees.

Now if I also SUM the fees from 3-tbl_fees, I get some really bizarre numbers in the millions of pounds, which I know cannot be right, as the fee totals should only be a couple of K.

At the moment when I run the report, provided there is only a single record in 3-tbl_fees, I only get a single record in the report which is run from this query, however if there is more than one entry per job no in tbl_esttime, I get multiple lines in my report, this is where I am coming unstuck,

I hope this all makes sense, if you have any questions or need more information please let me know.

oh wait a second it seems to be working ... will post what i did and the DB as wel, thanks guys you are the greatest KUDOS

second thoughts, no its not any other suggestions are very welcome... thanks guys for your patience - feeling like a complete idiot at the moment.

 

[Christopherusly]

help ? anyone please . ?

 

[PNGBill]

I am unable to download the database and the story is quite a lot to grasp.

One thought that may assist is that sometime when you query data you may get a Sum and keep this on each record when in fact it is the sum of all the records.
To look at the query data you could imagine it is not correct but so long as you don't sum this value again, then it is ok, just don't use it in the detail part of a continuous form or report, place it in the Header or footer. Just like all the records for New York have New York in the State Address but if you set up your Form or Report, New York only shows once in the Header.

Not sure if this is of help but surely you can make a query that purely Sums the charges of a Job and then join this to your main query and if this means it is repeated, so be it, just don't sum it again.

Another concept is to have the Form's or Report's Header or Footer do the Sum by Group rather then the query. Again, difficult to give exact advise without spending a lot of time on your database. Place an unbound control and =Sum([textfieldname]) in the Source of the property.

The Grouping Totals on Reports can do wonders with sub totals and overall totals.

Sorry again I am unable to spend enough time.

It can help if you keep your questions focused to a particular Problem ie, get what you can of the query to work and then post the sql and ask how to then bring in this one other field rather then have >1mb database that can seem quite intimidating

When attaching a database, if you can makeup a sample with just the 1 or 2 odd tables and queries etc that matter and some non confidential data and when compacted and zipped it may well be 200kb or less and easy for someone to open and assist with quickly - with luck.

 

[vbaInet]

A rather long explanation like PNGBill rightly mentioned. Short and sweet is always best. All that was needed was this:

then an expression Cost:[Rate]*

---

which give me the actual cost per transaction, now so far so good - i have all the number but what i need to do is total the numbers for each job no - to return a single line per job no.

---

If you want a report that is JUST a summary of "the total numbers for each job no" then my initial post addresses how to create a query for that purpose. You will need to GROUP BY Job No and SUM that calculated Cost field.

So the query you create will be based on tbl_Finance table.

 

[Christopherusly]

bangs head on desk repeatedly, thank you ever so much for your help guys, havign gone back to basics and checked all my relationships in the DB i have finally ruted out the problem and something that was actually quite easy to do, has indeed now been done.

THANK YOU for your patience and time spent helping me, please find that i have added kudos once again, thanks guys this is definately the best access forum.