Wrox Programmer Forums
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 February 9th, 2004, 02:17 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Group by in Access Report

I am using this query (report) in access.

SELECT contracts.projtype,contracts.rec_id AS contracts_rec_id, contracts.contractno, contracts.contname, contracts.contamt, contracts.jobno, contracts.strdate, contracts.comdate, contracts.extdate, contracts.contr, contracts.pstatus, employees.rec_id AS employees_rec_id, employees.empno AS employees_empno, employees.name, employees.mname, employees.lname, employees.posit, employees.dept, employees.bstatus, approvals.rec_id AS approvals_rec_id, approvals.empno AS approvals_empno, approvals.appsta, approvals.appstrdate, approvals.munref, approvals.munletdate, approvals.dcilref, approvals.dcilletdate, approvals.apppos, approvals.appcont, assignments.rec_id AS assignments_rec_id, assignments.empno AS assignments_empno, assignments.aposit, assignments.alocat, assignments.astrdate, assignments.aenddate, assignments.cshift
FROM contracts,employees,approvals,assignments where contracts.rec_id = employees.rec_id and employees.empno = approvals.empno and approvals.empno = assignments.empno

Above access query create report like this.

Addition work

06367 Martin Manager Inspector ……

Road Widening

12121 Jones Salesman RE ….
12121 Jones Salesman RE ….

Tunnels and Intersections

10100 Peter Accountant Inspector ….
10100 Peter Accountant Re…..

Problem is that above query retrieve only one employee’s information in each projtype field.
why above query does not retrieve other employees information which are also working same projtype (field)
ie Addition work, Road Widening and Tunnels and Intersections ?
(Group by base on projtype)

table fields

rec_id int 4
contractno varchar 10 (PK)
contname varchar 50
projtype varchar 50
rec_id int 4
empno varchar 10 (PK)
name varchar 50
bstatus varchar 50
rec_id int 4 (PK)
empno varchar 50
appsta varchar 50
appstrdate datetime
rec_id int 4 (PK)
empno varchar 10
aposit varchar 50

Please help.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change group at run time in Crystal Report 9.0 by priyank Beginning VB 6 0 January 6th, 2006 06:11 AM
Group total in a report tsimsha Classic ASP Databases 1 February 28th, 2005 05:40 PM
Getting Access to the Group Header Imar Crystal Reports 2 November 6th, 2004 05:37 AM
Report problem in group by mateenmohd Access 3 May 24th, 2004 12:16 AM
group header in data report nebujacob2000 VB How-To 1 January 12th, 2004 04:41 PM

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