Results 1 to 4 of 4
  1. #1
    zaboomafoo
    Guest

    Report Data Selction (Access 2000 SR-1)

    Does anyone have an idea on how to do the following:
    We have a database with 7 tables. The tables contain essentially identical fields. The purpose is to keep a chronological history of property owners. Within each table, there are one record per property. This has worked well with one exception.
    When we want to generate a report which shows only the most current property owner, I do not know how to make the report print only the information for each property from the most current table (i.e. - for property 1, the original owner, in table I may be the most current, but for property 2, there may have been several sales, and I want the information from table 5 to display). I am not real strong a programming, so a VB function is tough, but not impossible. PLEASE if anyone has a great idea, please let me know

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Data Selction (Access 2000 SR-1)

    It seems to be you have a very poor database design. From what you've described, it seems to me there should only be 2 tables: a property table and an owner's table.

    The only way I can see creating the report you want is to do a union of all the tables, which essentially combines all the tables into one huge recordset.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    zaboomafoo
    Guest

    Re: Report Data Selction (Access 2000 SR-1)

    I inherited the database design - does not mean that it can't be changed. Once the design is changed, how then would I go about chosing the newest owner for each property? Thanks

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report Data Selction (Access 2000 SR-1)

    All you need is a simple query joining the owner and property tables. You would need a selection criteria for the purchase date (I assume that is how you determine the most recent owner), which would be a subquery like this:
    Select Max(purchasedate) from owners where owners.propertyid=property.propertyID
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •