Getting the Right Data

[Matt_]

Hey, Please bear with me... I'm not sure if this is the right to post this but I'm hoping so. If not, please feel free to move it wherever it fits in best.

Here's my situation.

I've got a contact address table within a solution, that contains different types of address for a student. These could be Mailing Addresses, Home Addresses, Temporary Address, etc.

They are all start and end dated, so it is noted whether the address is current; and any old addresses can be kept on file for future reference.

Also, not everyone will have a Mailing address. In most cases, the home address will suffice. So I need to assume that the mailing address is the home address.

I'm running into a problem now, that if I need to display an address on another report, (say a letter to the student) I want to display only the address that is type: Mailing (or Home), and is current.

Any suggestions on how to go about obtaining that address to use on the layout?? I have attached a PDF copy of the data entry screen, as well as the list of fields.

I would appreciate any help :

Thank you!

fmForums3-tbl.ContactAddr.pdf

fmForums4-AddressDataEntry.pdf

[Matt_]

[color:blue]Anyone? Anyone?

[LaRetta]

Hi Matt,

Your request should be fairly easy to produce if your structure is sound. But we can't see your structure. And even if we saw your structure, it would be difficult to explain successfully.

If you can attach your file as a zip, it will be much easier for us to assist you.

LaRetta :wink2:

[Fenton]

A couple of things. First, I don't think the "current" calculation is of much use there, because it must be unstored, to pick up the current date. I think I would do that out in the table which was looking for the address; have an unstored current date field there, and use it in a relationship back to addresses. Much like your current calculation field, but via a relationship, which will produce all current addresses for the contact.

The question then is which one? I would create a calc in the viewing table to produce the address. If the count of the relationship is 1, then that's the address. If the count is > 1, then I'd have a 2nd relationship, which included a criteria to match Type = "Mailing". You'd need a "dummy" unstored calc field (result Text), = "Mailing" to use on the originating side. I'd sort the relationship descending by date.

Yes, I know, 2 relationships seems overkill. But that way you can have a "Home" type and a "Mailing" type, and still be able to match each, when required. It would not require that anyone change a previous address entry's type when adding a newer one.

You'd have to recreate this in any table that needed the address.

[Matt_]

Thanks Fenton,

I'll give it a crack and see how it goes. I'll report back if there's any problems or I can't figure it out :)