Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 1st, 2005, 03:40 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help need with designing this complex query

Hi every body. I am new to creating query and report. I got database and sample
 report document that i want to generate the report in access each week. My big
diffuclty is how to design and create the query for this complex report so any
 help would be appreciated.I posted picture of my database tables with their
 reletions and sample report document below. The database is in sql server 2000
 and i linked to those tables from access 2000. I be happy if some expert help
 me create this complex query for this report.Thanks



http://i5.photobucket.com/albums/y18...rojectdata.jpg ( report sample document)

http://i5.photobucket.com/albums/y18...etionship4.jpg

(database tables)
 
Old July 1st, 2005, 05:26 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

its not particularly complex... you just need to understand how the data builds up into your report.

Now then, looking at the report sample, it would appear you want to report "The hours worked on each project by each employee"... I could give you the sql for this, but I think you would be better off (since this isnt complex at all) doing it yourself.

Here is how... open a query and then add the Hours Worked table. Click the "Totals" (sigma sign, spikey E symbol!) button... then simply add Employee, Project and Hours. You then need to set the "Total" option to Group for those you want to group and Sum for those you want to add up, ie: Hours.

Once you have the base query working how you want it to work, simply add in any other tables or fields you would like, but ensure any additional tables and fields are not going to "dis-aggregate" your groups.

This isnt complex in the slightest, assuming you know your data the best way to achieve this, imho, is to play with the group function for a while to get a feel for it.
 
Old July 1st, 2005, 05:29 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

Looks like you want the Task level grouping, so along with the Employee and Project, add in Task from your Hours Worked table too... this will add a further level of detail to the task level.





Similar Threads
Thread Thread Starter Forum Replies Last Post
complex query g_vamsi_krish SQL Language 3 February 27th, 2006 10:48 AM
query too complex liz@trinityholdings.co.za Access 3 August 28th, 2003 09:01 AM
COMPLEX QUERY PROBLEM nikosdra SQL Server ASP 15 August 4th, 2003 06:17 PM
Complex Query-Plz Help rivinjose SQL Server 2000 10 June 27th, 2003 09:25 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.