Why dont you have a ID for UnitLocation in your Unit table and then you create a record for each address where you have your units and link them through a relationship to your Unit. In your Report you use the Sub Summary from this new relationship
UnitLocation is a TO of Unit.
I was going to try use Archive_Location and link that to Address_Town but this was still only printing one page per company.
Could one Company (Customer) have several addresses, I would set up a Address table for Customer and then link that new table to Unit
I do have a table for addresses.
Addresses#Refurb_Letters = Addresses and Customer_Units = Customers. Sorry if the TO names are confusing.
This information is joined to Units table via Last Known Owner
The problem is down to in the old system they were never storing whole addresses against the unit. Just the City or Province.
So the only thing I have to go by is the Location field as this should match up with the customers > address > city.
But I am unsure how to do this.
Then why dont you just create a new ID_Address in your Unit table and then use that as the Sub Summary field and you will get all units located at that address. You still have one Sub Summary part for your Customer at the top but only show the Customer Name there
Sub Summary part 1 = Customer Name
Sub Summary part 2 = Address ( could be several for the same customer)
Body = Units
Ok I understand that.
But how am I to get all of the appropriate Address ID's stored within ID_Address.
The conditions would need to be met where customers::company name = units::last known owner and units::archive_location = address_town to find out any of the ID's.
You can always to a Replace or else manually
I've managed to grab some of the Address_ID's by creating different TO based on the conditions.
Only problem is now I have 3 fields. Containing possible Address_ID's.
Is there a way I can condense them down? So that say if calc1 is empty, use the number in calc2 or if calc2 is empty use calc3?
Something on the lines of :-
- Show ID_Address
- If ID_Address is empty show ID_Address2
- If ID_Address2 is empty show ID_Address3
But in a calculation.
1 of 1 people found this helpful
In the Replace function you can do a Case() that does exact like you wish
I've haven't used the replace function before.
I understand how to put two of the calculations into a case but I'm not sure how I would get the third.
Case ( not IsEmpty ( ID_Address ) ; ID_Address ; ID_Address_2 )