setvalue based on date of last entry

[krowe]

Hi All

I would like a bit of functionailty within my database, but i'm not sure where to start.

I have main table called tblPerson. Within this (amongst other fields) is ID (unique), CurrentAddressLine1, CurrentAddressLine2, CurrentAddressLine3.

I have another table called tblPropertyHistory with fields PropID (unique), ID(links to tblPerson) AddressLine1, AddressLine2, AddressLine3, MoveInDate, MoveOutDate,

I would like the Current Address fields in tblPerson to always be the address in tblPropertyHistory which has the most recent MoveInDate.

I have a bit of a feeling I need to create a query based on tblPerson, then have some kind of lookup to tblPropertyHistory, but what kind of formula do i need in the query field to return the correct address info, and then can i get this into the tblPerson, or should i just re-direct my forms and reports etc to the qry???

Or should the query have both tables, in which case how can i make the address info the most recent?

As you can probably guess, im at a bit of loss with this so far

Thanks in advance for your help.

Kev

 

[Trevor G]

Kev

It sounds as though you are working to hard on this, the Person table should hold data against the person and the property table should hold all the property details including Move In Date etc, then you have a list which you can query the 2 tables, who is in what property and place an Sort by date if required.

 

[krowe]

Hi

Thanks for your reply.

I agree, I shouldn't have current address in tblPerson, so I can get rid of the property fields in tblPerson.

The problem still exists though, that most people have previous addresses (therefore there is a one-to-many rel for tblPerson-tblPropertyHistory, and for most of my exports I just need the most recent.

Is there a way that the query will just return the address which is most recent, is there perhaps a MAX function i can put in the criteria for MoveInDate.

Thanks again

I am already a bit clearer about how to do this.

Kev

 

[Trevor G]

Hi Kev,

In the properties Table add an extra field which can give you a drop down to state current or past or something along those lines, then you can query this field for all current addresses.

That would work.

 

[krowe]

That sounds like a plan, in that case can I validate that only one property for each person can have the current value?

Thanks

 

[Trevor G]

Well that seems as though you now have a solution in hand. Well done Kev.

If you need further assistance then let me know.

 

[krowe]

Well there is one last bit....

How would i do the validation so that only one property per person contained a "current" in that field.

It's important as the database is supposed to create standard letters and the query will be the data-source for a mail merge, I only want to end up with one letter each time, and if there are multiple addresses with "current" for any person I will end up doubling up on letters and have different addresses on each.

Sorry to be a pain

Kev

 

[Trevor G]

OK Kev,

What I suggest is create a query that will show the person name, the property address (Only one line should be necessary) and the option regarding validation, then look to add Totals so you can Group by Person and Group by address, but then Use Count for the Validation, this way you can see if there are more than 1 record showing 2 Validations, so you can then resolve the records before doing your mail merge.

You could even add criteria below the Count to show any record Greater than 1 (>1).

Then all you should need to do is run the query before doing the mail merge.

Hopefully this will help you conclude this part of the database.