Results 1 to 6 of 6

Thread: lookup??

  1. #1


    I'm stuck and oh so frustrated! My question is, what ways are there to have a field in one table look up a value in another table when there are multiple fields to choose from?

    Specifically, I'm using Access to manage conference logistics. My program table contains 50 or so date calculations in separate fields. My Tasks table has a target date field that needs to be able to look up the right date calculation in the Program table.

    Currently I have a text field, DateFormula, in the tblTasks that matches the field label of the appropriate date calculation in the tblPrograms. I print out the tblProgram, find the right date calculation, and then rekey that in the TargetDate field of the Tasks table.

    There has got to be an easier way!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: lookup??

    Ordinarily, you wouldn't try to do this in a lookup in a table field, you would use a form and program the logic of the lookup into that. Could you explain what you mean by "...My program table contains 50 or so date calculations in separate fields..." so your answers will be more pertinent to the question? Is this the table you want to enter dates in? Why do you need 50 or so date fields?

  3. #3

    Re: lookup??

    I don't know whether this takes you any further than the previous answer, but....

    1. Do you have an ID field for your "Dates" table? If so, can you not refer to a calculation by this key?

    2. Personally, I would put a "Select Case" routine into the code for a form. This routine can then either do the calculation direct or, if you really need to store the calculation formulae for later, reference the table.

    Hope this helps...

  4. #4

    Re: lookup??

    I'll try to explain it a little better. Please bear with me.

    Each event timeline has several "anchor" dates. All the tasks' target dates can be figured + or - one of those dates. In effect, I have several "sub"timelines for each event. That way it doesn't matter whether I have 6 months or 2 years to plan an event; the related tasks stay together, it's just the relationship between the timelines that changes.

    When I add a new event, the frmPrograms does the date calculations I'll need for the tasks based on the anchor dates and returns those to the tblPrograms. It amounts to 50 or so date calculations.

    The tblTasks contains all the possible tasks for events. I choose the tasks pertinent to a particular event, make a separate table, assign resources and target dates and then add to a master table containing all the events.

    So, the 50 or so calculations ares to get all the possible target dates for a program based on the program's anchor dates.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    The Netherlands
    Thanked 0 Times in 0 Posts

    Re: lookup??

    I am not sure if I understand the problem completely, but I'll give it a shot.

    If you want to retrieve the data, I suggest you use a query and calculate the dates in the query.
    The results of the query can be used for reports and forms (read only!).

    If you want to store the data, just start writing some VBA that will do the job. This will give you full control of anything you want to do.

    Can you describe the problem you have in more detail, include coding, SQL statements etc.?
    I think we can help you better that way.

  6. #6

    Re: lookup??

    Bart, thank you thank you thank you. When I read your post the lights went on and I had an aha experience. I don't know why I couldn't see it before. Queries were the answer.

    I split my problematic field (suggested date field), into two fields. The first being a constant (the "anchor"), the second where I store the result of the date calculations from the query.

    The sequence is this:
    =I use a check box to select records
    =then use a make table query to separate them
    =then use update queries to assign resources and replace the constants with the specific value for that program
    =then the date calculation query returns the value to the target date field in the "made" table
    =then I use a append table query to add the new program to the "master".
    =then another update query to unselect the check boxes and I'm ready for the next one!!

    As you can see, a very very basic program. I'm probably taking the long way around. But, I'm just thrilled it works.

    I have much to learn and appreciate everyone's efforts to help. They sure paid off for me!

Posting Permissions

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