Lookup field from table

[Co9ug9ar]

Right now I have a table with field for a case #, case name, year, month, due
date (1st, 2nd...), and Calendar date(1/1, 2/15,ect) and I have to manually
go through each case and enter in the desired calendar date for about 11,000
records based on the month, year, and due date. Is there a way to set up
another table(s) that contain the Calender date as input and hte rest as
column headers so that my original table can look up the Calendar date from
the other table so I only have to enter the Calendar date into the new table
and it will update in all the case?

The only way I could think of was to give every month a new table and have
just the Due date as column headers with the desired Calendar. However, with
this plan I wouldn't know how to call access to these different tables from
the original table.

Any suggestions would be great.
Thanks,
Keith

 

[Phillip Windell]

I doubt you need another table.

You should be able to do some kind of Loop with the Date() or Now() function
in combinations with an UPDATE SQL statement. The Loop would end when it
hits the EoF.

But I really don't know what you are really doing specifically so I can't be
any more detailed than that,... not to imply I could be more detailed
anyway,... but you never know.

Are you saying that these records have a blank date entry for both date
fields?

 

[John Vinson]

Right now I have a table with field for a case #, case name, year, month, due
date (1st, 2nd...), and Calendar date(1/1, 2/15,ect) and I have to manually
go through each case and enter in the desired calendar date for about 11,000
records based on the month, year, and due date.

How is the Calendar Date calculated (or related?) to the due date?
What's the logic, if any? You may be able to use the DateSerial()
function to convert a year, a month number, and a day to a Date/Time
value: run an Update query updating CalendarDate to

DateSerial([Year], [Month], [DueDate])

if I'm understanding your logic correctly.

You can certainly create a Table with these four fields (the year,
month, duedate, and calendar date) and create a Query joining on the
fields that you do have to update the calendar date - or, better,
remove the calendar date from your table altogether and just use this
join to look up the date as needed.

John W. Vinson[MVP]