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?
Figure 1: Multiple locations 1 company.
Figure 2: All units printed for 1 address even though it would be 2.
Figure 3: Relationships
Any help will be much appreciated