Lookup field during data import

[BDibz]

Is it possible to have a field be automatically populated by a lookup table during data import?



Let's say I'm importing an Excel table with columns A, B, and C that are appended to a DB table, Table 1, with columns A, B, C, and D. A is the PK, C is an FK.

Table 2 is a DB table with columns C and D, where C is the pk and D is information it holds related to C. When I do the table import, I want Table 1 to automatically populate its "D" column based on the record related on "C".

I know I could do this if I were adding one record at a time via a form, but I don't know if its possible with an import. Any ideas?

[Beetle]

If Table 1 already holds the foreign key value ("C") from Table 2 then why do you want to also store "D" (from Table 2) in Table 1? This is unnecessary and redundant. If, at some point in the future, the value of "D" in table 2 changes, you would then need to go to the trouble to update the (redundantly) stored value of "D" in Table 1 as well. Can you provide more details about why you want to do this?

[BDibz]

You're right, I guess that wouldn't make sense. Basically, I have a form used for viewing/updating records. I want it to use information from Table 2 about objects in Table 1 to calculate dates to store in Table 1. However, I wanted those values to be modifiable after the initial commit, because there may be circumstances where the foreign value (a number of weeks) is different for specific instances of the same part.

If I were to drop the requirement for editing, how would I go about making this form pull data from another table to calculate a date based on information from both tables? The start date is entered in table 1, and the "lead time" is entered in table 2. I need to store the end date (start date + lead time) in table 1 for each part.

[Beetle]

....I need to store the end date (start date + lead time) in table 1 for each part.

In most cases you shouldn't store calculated values either. Again, if one of the underlying values that the original calculation was based on changes, there is no automatic way to update the previously stored calculated value. You should just calculate this on the fly as needed at either the query or form level. If you can upload a sanitized copy of your db I can help you with that. If you're unable to do that, then if you can provide a bit more detail on your structure we can try to work through it. Whether it would be best to do this in a query or form may depend on other factors like the type of form you're using, whether or not you need the record set to be updateable, etc.

[BDibz]

As I understand it, it would be best to have the table store the minimum possible amount of information, and then only calculate things based on this information at run-time in a query or form, but not actually save those calculations; since their values are implied in the expressions in the form or query, it is redundant to store them as values in the table.

What about a case where I want to keep a record of which jobs were completed/delivered on time? Currently, I'm doing this with in-table calculations. But I guess technically, it should be possible with a query or series of queries that does all the calculations at run-time. It seems like this is usually the case, as long as you plan far enough ahead to reduce your tables as much as possible.

I think I'm at the point where it may be best to actually start from scratch on this database; now that I know how all of my data needs to connect, and how my calculations can be done, I can re-build it in a cleaner way.

I would upload what I have now for you to take a look, but its become sort of Frankensteined throughout the process of being built, making changes, and trying to add new features and clean up new ones. It's functional, but seems very easily breakable.

[Beetle]

What about a case where I want to keep a record of which jobs were completed/delivered on time? Currently, I'm doing this with in-table calculations. But I guess technically, it should be possible with a query or series of queries that does all the calculations at run-time. It seems like this is usually the case, as long as you plan far enough ahead to reduce your tables as much as possible.

Assuming that you have fields that store a Promised Date and a Completed Date (for example), then a simple query would determine which jobs were completed on time just by comparing those dates.

There are some rare cases where it might make sense to store calculated value, but those are more the exception rather than the rule. You can find more info on calculated values here. Allen Browne's site is a good resource in general for Access (some of the info is dated, but still relevant).

If you are going to look at a redesign (I think we've all been there at some point), one of the moderators here, Orange, has some good resource links in post #3 of this thread.

Good luck with your redesign and let us know when you have other questions.