DataBase - Multiple Queries for Access Report

Asked By tonyrulesyal

31-May-08 10:16 PM

I am using Microsoft Access XP.

I have three queries that I would like an Access Report to pull data
from.

I have been getting difficulty putting these together.

They all tie back to a "file number", which is unique.

I would the like the report to breakdown by each company number.

These queries are already done:
1) then have a line from my crosstab pivot query which breaks down the
count of new file numbers by month for that
company number.

2) the average balance for each company number, also broken down by
month in a crosstab pivot query.

3) the total number of active file numbers broken down by month in a
crosstab pivot query.

I would like the columns all lined up.

I would like the results to be exported to Excel when completed.

31-May-08 05:32 PM

It all starts with the data...


More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

02-Jun-08 03:45 AM

Jeff:

My data looks like this:
Each company has a company code (ex. 1, 2, 3, 4, 5, etc.)
Each company has a company name.

Each company has unique customer accounts.
Each unique customer account has a balance.
Each new customer account is recorded for a specific date.
We want to show the number of new accounts for each month.

My queries are cross tab pivot table queries, that break down the data
by month.

I would like the output to look like this (totals at the top, by
month):
I would like the inactive accounts to be new accounts minus active
accounts.

I do not want the month names to show up multiple times, but I want
the data to line up for each month.


1.  Acme Company
Jan    Feb   Mar  Apr
May June Jul Aug (etc.)
5 New accounts for this year.          1     2        1     1
$200 Average balance for each     100  300   100  300
2 Active Accounts                          1      1
3 Inactive Accounts                                1      1      1

2.  Generic Company
3 New accounts for this year.          1               1     1
$20 Average balance for each        10   30      10    30
0 Active Accounts
3 Inactive accounts                         1               1     1

01-Jun-08 10:46 PM

Thanks the clarification.

I'm still a bit unclear, and how-to depends on how the data is structured,
so...

Here's an example of the kind of description I was looking for:

tblPerson
PersonID
FirstName
LastName
DOB
... (other person-specific data)

tblClass
ClassID
ClassTitle
ClassDescription
... (other class-specific data)

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate
... (other enrollment-specific data)

More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/