How to add mailing addresses to an invoice report (1 Viewer)

[wire_jp]

I have to prepare invoice statements to send to my clients. I have managed to generate the invoices as a report.

However, I want to add two types of mailing addresses to this invoice report: "From" mailing address (my company's mailing address) and a "To" mailing address (the recipient's mailing address).

I have created a Clients table which contains the company's mailing address details and a separate ClientContacts table which contains the primary contact name and the secondary contact name (along with their email addresses) for the respective company.

My problem is how to incorporate this information on the invoice report. What I have done is to create an unbounded text box on the invoice report and manually type in this information. Is there a way to add my company mailing address as a "From" mailing address and the recipient's mailing address as a "To" mailing address on the report automatically?

 

[Minty]

You will need to link the client data to your invoice report.
Presumably the client data has a unique primary key that is their account number?
Assuming it has and that same account number is available in the invoice query simply join them in the invoice report query and add the fields to your report.

 

[wire_jp]

Hi Minty, thank you for your prompt response. Previously, but I added the clients and client contacts fields to the query and I noticed that the invoice data had doubled. As an example, the primary contact person was linked to an invoice A on one line item, and then the secondary contact person was linked to same invoice A data on the next line. Is this what is expected to happen?

I wanted the mailing addresses to appear in the page header of the report and the invoice data to appear in the detail section of the report.

 

[Minty]

If you have 2 records in an underlying table that match the invoice query you will get both showing up.

You need to only pick the relevant contact per customer. You can't address an invoice to a variable number of people.

Your contact structure should be maybe contain an contact type id;

ContactTypeID   ContDesc
1               Sales
2               Accounts
3               Serivce

Then only include contact type 2 in your linked query.
To get your detail and header correct look at grouping on the customers in the report.

 

[wire_jp]

Thank you, Minty for the your help. Could I add an unbound boxes for the contact name to the report and perform a Dlookup to find the contact name?

 

[Minty]

You could, but it would be more efficient to simply bring them into to your invoice querying. Dlookup is not very efficient, as it runs another query in the background.

 

[AccessBlaster]

Open any version of Northwind Trader database that ships with Access. Open the "Invoice report" in design view. Look at the design and layout, and the record source in the QBE.

HTH

 

[wire_jp]

@Minty: Thank you for your response.

@AccessBlaster: I am using Microsoft access 2010. The invoice report of Northwind Trader sample do not opened, as a message appears stating "Invoice is not designed to be opened without a filter."

 

[AccessBlaster]

@Minty: Thank you for your response.

@AccessBlaster: I am using Microsoft access 2010. The invoice report of Northwind Trader sample do not opened, as a message appears stating "Invoice is not designed to be opened without a filter."

That's why I suggested you open the report in design view.

 

[wire_jp]

@AccessBlaster: - Ah, I see. Thanks!