Multiple Queries for Microsoft Access Report

  • Thread starter Thread starter tonyrulesyall
  • Start date Start date
T

tonyrulesyall

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.
"file numbers" are tied to "company numbers", which are also 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.
 
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
 
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


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

Here is more info:

tblMaster
CompanyID
AccountID
dateopened
dob
originalbalance


tblAccounts
accountID
paydate
amountpaid
codes (used to determine if an account is active: ex. a or b means
active)
 
Perhaps one of the other newsgroup readers can offer suggestions, I'm still
at a loss for understanding how you are getting from the (?only) two tables
you described to the information you indicated you want to show.

Best of luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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