AnsweredAssumed Answered

Letters - Companies with multiple location

Question asked by globe11123 on Aug 23, 2016
Latest reply on Aug 24, 2016 by globe11123

Every month we send out letters to remind customers that the products they have purchased of us are due for refurbishment.

 

I've created a letter which prints out all of the companies that are due for a certain month and year. This lists whatever units they have including serial numbers etc.

 

Problem: Some companies have more than one depot or have multiple addresses, I am only able to currently print out whatever units are due that month for the company for 1 address.

 

Units table / Units#Location - which includes serial number, product code etc.

Customers_Units table - standard company information. (The Letter only looks at the one address from the customer. "RefurbLetterAddressIDFK")

Addresses#Refurb_Letters table - multiple addresses can be stored in here for customers/

 

Customers is linked to units by a field called last known owner which is basically a temporary field which gets updated by a script every time a unit comes back for refurb. Addresses is linked to customers via Customer_IDFK.

 

My letter has a sub-summary which is stored by Customer_ID.

 

On units I have a field called Location which is basically address line 3 i.e London. Maybe this could be used as a join between units and an address?

 

How would I be able to change this so that it prints a letter out per address per company?

 

address 3.png

Figure 1: Multiple locations 1 company.

 

letter.png

Figure 2: All units printed for 1 address even though it would be 2.

 

letter.png

Figure 3: Relationships

 

Any help will be much appreciated

Outcomes