Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    9

    Question Unanswered: Trouble With Particular Query

    Hi All, I hope someone can help me with this, I've always had trouble with queries. Anyway, here's what I'm trying to do:

    1) I have one table that contains information on a company (name, address, etc.). Contained in this table is a field, let's call it "Decision".

    2) Linked to this company table is another table with a whole bunch of information including a field called "Date" and another called "Decision".

    Based upon the premise that a decision is reached multiple times in the table 2 but at different times, there are multiple records in table 2 for one record in table 1. I want to keep the decision field in table 1 up to date. So, I'm looking to create a query that will examine all dates in table 2 (that relate to a given record in table 1) and then choose the corresponding decision with the latest date, and then populate table 1's decision field with that information.

    I hope that my problem is clear and that someone can help me, I have a feeling that it can be done simply with the built in Access Query features, but am not sure how to accomplish it. Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    WHY have that column in the 1st table in the first place? This breaks normalisation ... Querying for the lastmost decision by date will give you the answer every time ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2006
    Posts
    9
    Well, they're not exactly the same field, the names are different, I just used the names above as an example. If one field is let's say "Local_Decision" and another is "Latest_Decision" what does that change? Also, I'll need to pull the date field in table 2 into table 1 as well, for example "Local_Date" and "Latest_Date". The reason I have the tables set up this way is because I plan to do quite a bit with reporting as well and having all the information in table 1 would be much easier from my perspective.

    I'm going to toy with what you suggested and see how it plays out...any additional thoughts? Thanks.

  4. #4
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    One of the rules of Normalisation is to avoid duplication. You do not need all of the data in one table for ease of reporting- your queries easily combine data from multiple tables.

Posting Permissions

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