report / Query

[Chris]

hello group. I'm trying to do something strange and I'm not sure how to go
about it.

I need a report to print out all the places (creditors) a person owes money
to. The report is set up to print on a label format (name , branch
address1 ,2, 3 and Postcode)

The problem is this ; If the person owes money to the Halifax it is often
the case that this will be sent to a debt collection agency. If a debt
collection agency is involved, 3 different addresses can be printed out
(creditor, Debt Collection Agency or Both.) I've set up an 3 combo boxes to
store the values so the user can input where the correspondence is to go.

I'm assuming that I need a query and a report setup. So how do I set up the
report to print the desired correspondence?

 

[John Nurick]

Hi Chris,

Probably this can be done by setting up a query so to collect the
parameters from the comboboxes and use them to get the right addresses.

Definitely the aim should be to have the query return one record per
address that needs to be printed, then base the report on the query.

Can't say much more without knowing how your data is structured.

 

[Guest]

Hi, Chris.

This may involve changing the fields in your table, or adapting my strategy
to your fields.

My approach would be to first add 2 fields to the Customer table--a numeric
CreditAgency field to store the CreditAgency ID number from a CreditAgency
table (that would have similar address fields), and a checkbox indicating
whether to send it to the Credit agency only (it would send to both by
default).

Then I would create a union query, which is simply two independent queries
conjoined by "UNION" (which returns only unique records) or "UNION ALL"
(which returns all records, including duplicates).

The first query would select all records from the Customer table where the
checkbox was False, and the second would select records from the CreditAgency
table, joined to the Customer table (Customer->CreditAgency to
CreditAgency->CreditAgencyID), with the criteria of "Is Not Null" in the
Customer->CreditAgency field.

UNION queries must match up field-for-field, in the same order. Once you've
created both queries, switch to SQL View (View, SQL View) in one of them and
copy the SQL to the clipboard. Start a new query, switch to SQL View and
paste it. Do the same for the 2nd query, then insert the words "UNION ALL"
between the two SQL statements, and save the 3rd query.

Then base your label report on the 3rd query.

Something like the following:

SELECT Customers.CustomerID, Customers.CustomerName, Customers.Address1,
Customers.Address2, Customers.Address3, Customers.PostalCode
FROM Customers
WHERE (((Customers.UseAlternateOnly)=False));

UNION ALL

SELECT Creditors.CreditorID, Creditors.CustomerName, Creditors.Address1,
Creditors.Address2, Creditors.Address3, Creditors.PostalCode
FROM Customers2 INNER JOIN Creditors ON Customers.CreditAgency =
Creditors.CreditorID
WHERE (((Customers2.CreditAgency) Is Not Null));

HTH
Sprinks