Generate a report of all "Active" Clients detailing their details

[Barry McConomy]

Hi

I have 2 databases.

Database 1, has a table with all Client details, name, address etc., (Active
and In-active)

Database 2, has a table (PurchaseOrders) with all transactions for "Active"
Clients.

What I want to do is generate a report of all "Active" Clients detailing
their details.

Database 2, table (PurchaseOrders) is linked database 1 table
(ClientDetails) by its primary key ID.

Can anybody advise how I go about doing this.

Thanks

Barry

 

[Dirk Goldgar]

Hi

I have 2 databases.

Database 1, has a table with all Client details, name, address etc.,
(Active and In-active)

Database 2, has a table (PurchaseOrders) with all transactions for
"Active" Clients.

What I want to do is generate a report of all "Active" Clients
detailing their details.

Database 2, table (PurchaseOrders) is linked database 1 table
(ClientDetails) by its primary key ID.

Can anybody advise how I go about doing this.

Thanks

Barry

So these tables are in two separate .mdb files, for some reason? Let's
assume you want to run this report in Database 1. All of the following
steps will then be performed in Database 1. In that database, create a
linked table to the PurchaseOrders table in Database 2. You can use the
File -> Get External Data -> Link Tables ... menu items to do this.

As I understand what you've written, the only indicator that a client is
active is the presence of one or more transactions for it in
PurchaseOrders. So now create a query that joins table Clients with the
linked table PurchaseOrders, using the ClientID field (or whatever the
primary key field of table Clients is called) as the linking field. The
join should be an "inner join"; that is, one that includes only the
matching records from both tables.

Make sure the query includes all the fields from both tables that you
want to see on the report. Then create a report based on this query,
and have the report grouped by ClientID (or ClientName, or both). Let
the Group Header section contain controls to display the information
about the client, and let the Detail section display the details about
each transaction.

 

[Barry McConomy]

Hi Dirk

Thank you for your help.

Can I ask another question.

The report I want, is to show the Active Clients only, a single line listing
of active clients, no purchase order transactions.

In the query I set the UniqueValues to YES, on the ClientID, is this the
correct way?

Thanks

Barry

 

[Dirk Goldgar]

Hi Dirk

Thank you for your help.

Can I ask another question.

The report I want, is to show the Active Clients only, a single line
listing of active clients, no purchase order transactions.

I guess I misunderstood you. I took your reference to "detailing their
details" to mean you wanted to see the transaction details grouped by
client.

In the query I set the UniqueValues to YES, on the ClientID, is this
the correct way?

It would be one good way. Join the tables as I said, but only select
the fields from the Clients table, and set the query's UniqueValues
property to Yes. That will give you (in SQL View) something like this
SQL:

SELECT DISTINCT Clients.*
FROM Clients INNER JOIN PurchaseOrders
ON Clients.ClientID = PurchaseOrders.ClientID;

An alternative way, which may be more efficient (or may not, I'm not
sure) would be a query with SQL like this:

SELECT Clients.*
FROM Clients
WHERE ClientID IN
(SELECT ClientID FROM PurchaseOrders);

 

[Barry McConomy]

Dirk

Thank you.

Barry

I guess I misunderstood you. I took your reference to "detailing their
details" to mean you wanted to see the transaction details grouped by
client.


It would be one good way. Join the tables as I said, but only select
the fields from the Clients table, and set the query's UniqueValues
property to Yes. That will give you (in SQL View) something like this
SQL:

SELECT DISTINCT Clients.*
FROM Clients INNER JOIN PurchaseOrders
ON Clients.ClientID = PurchaseOrders.ClientID;

An alternative way, which may be more efficient (or may not, I'm not
sure) would be a query with SQL like this:

SELECT Clients.*
FROM Clients
WHERE ClientID IN
(SELECT ClientID FROM PurchaseOrders);

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)