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 October 8th, 2003, 08:54 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default multiple tables on one report?

Does anybody know how to add multiple tables on one report?

I get relation error when I try...

Dan
 
Old October 8th, 2003, 11:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Use a query as your reports record source. :) Some more detail would help clarify your difficulty.

Regards,

Bob

 
Old October 8th, 2003, 12:03 PM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK I have tables with these fields

table1: locatin | ext. | fname | lname | Dept|

there are three loctions under the location field L1,L2,L3.
I had to creat 3 diffrent reports to print by each location, thats the way it has to be printed.

Table2: ext. | Dept

created another report for this table.

Table3: store# | address | Manager |etc...

created another report...

What I would like to do is keep the report formats that I have created, but place them all in one report?

The data will be displayed on an 11x17 page...

Your help is greatly appreciated!
Dan
 
Old October 8th, 2003, 01:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Your tables structures need to contain common fields that will allow you to create joins and relationships among them like (roughly):

tblEmployees

EmployeeID (Primary Key)
- EmpLName
- EmpFName
- EmpExt
- DeptID (Foriegn Key)
- StoreID (Foreign Key)

tblDepartments
- DepID (Primary Key)
- DepName

tblStores
- StoreID (PK)
- Address
- Manager (actually redundant because managers are employees too
           which you could indicate with a 'Reports To' field in your
           employee table and use the employee ID to identify the
           manager (called a 'self-join' in you employees table))

Once the common fields are in place, establish referential integrity between them (there are two 1-to-many relationships suggested above), then write a query that joins the tables and base your report on it. The query would look like (very roughly):

SELECT
   tblEmployees.EmployeeID,
   tblEmployees.EmpLName,
   tblEmployees.EmpFName,
   tblDepartments.DepName,
   tblStores.StoreID,
   tblStores.Address,
   tblStores.Manager
FROM
   tblStores
INNER JOIN
   (tblDepartments
   INNER JOIN
      tblEmployees
   ON blDepartments.DepID = tblEmployees.DeptID)
ON tblStores.StoreID = tblEmployees.StoreID;

The basic idea is to establish common fields in your tables that you can join them on, then write queries that create those joins.

HTH,

Bob

 
Old October 8th, 2003, 03:41 PM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Bob,

The problem is my tables don't relate at all, they all have separate data. The tables look like they would have similar info but they don't. That's were my problem occurs!!!!

I guess I'm SOL...

Dan





Similar Threads
Thread Thread Starter Forum Replies Last Post
distinct from multiple tables neon20 SQL Server 2000 6 May 12th, 2007 08:36 AM
Importing Multiple files in Multiple tables Versi Suomi Access 6 June 1st, 2005 08:47 AM
Create a report from multiple tables in a Dataset verdun Crystal Reports 4 May 12th, 2005 09:20 AM
Multiple tables with relationships Please Help Lesviper Access VBA 6 February 3rd, 2005 08:20 PM
Multiple Recordsets from Multiple Tables TSEROOGY Classic ASP Databases 2 December 28th, 2004 12:45 PM





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