Importing data from Excel in Access, can data in column be used to populate another?

[dbreda88]

So I have this database that tracks purchases made that we process on behalf of several clients we represent. They submit requests to us, and we look over their paperwork and make sure that they've crossed their T's and dotted their I's and then approve them to be sent to one of our representatives for processing. We have to approve huge numbers of these each day, and up to now, we've been entering each one as a record into our database by hand, one at a time. These records are stored and used and updated to track the history and status of a purchase as it goes through the process, as well as be able to find information about that purchase when its completed should the need arise down the line.

What we were hopping to do, is have an Excel sheet import large batches of data into our database each day once we've finished approving these requests. The idea is to get all the information necessary entered into a dummy Excel sheet, then instead of entering them all one by one into access, we import the data in one single motion. We can already do this with mild success and I think we can handle that part. Here is the problem though.

One of the fields we record is the vendor whom the purchase was made from. The vendors are tracked by a 10 digit vendor ID assigned to each one. Our form that we use to enter these by hand has two fields for this, a Vendor ID field, and a Vendor Name field. By entering the ten digit vendor ID into the proper field, the Vendor Name field automatically populates the name of the vendor that is associated with that ID. (This information is taken from a different table in the same Access save file.)

As you might have guessed, attempting to import an Excel sheets information into Access will yield the vendor ID, but it will not auto-populate the vendors name for us. Is there any way we can get this to work automatically upon import?

[Rabbit]

It looks like your data is not normalized. You don't need to store the vendor name again as it sounds like you already have it in a master vendor table. Therefore, there's no need to duplicate the data. When you need to get the vendor name, you can retrieve it in a query where you join to the vendor table.

For more info about normalization, you can read our article here: http://bytes.com/topic/access/insigh...ble-structures

[dbreda88]

Not sure exactly what you're implying what I do and don't need but the master vendor table is meant to eliminate the need to manually enter vendor names when entering records. This is alleviated by simply entering the 10 digit vendor id which automatically makes the vendor name field populate, by cross referencing the master vendor list. The main table (separate from the vendor list) has all the purchase records and each record has the vendor ID and vendor name column for each purchase. As I said previously, only the vendor ID field needs to be entered, due to the cross reference (though this shortcut doesn't work if entered directly into the table. The form I mentioned in my original post, the one we use to manually enter one record at a time, must be used for this function to work. If entering directly on the table, you must also manually enter the vendor's name.) that I mentioned before. However, when importing records into the purchasing table, it will only enter the vendor ID number and won't auto-populate the name like it would using the form. Is there any way I can get it to do that?

[Rabbit]

The article I linked explains in more detail what I am suggesting.

I am saying that you don't need vendor name in your "main table". It is storing extra information that is not needed. The article explains how to normalize the data and has a link at the bottom that explains why it is important to normalize the data.

One of the reasons normalization is important is exemplified by the very issue you are having right now. If the vendor name is looked up when needed from the vendor table instead of stored in the main table, then it will always be there and it will always be correct.

[dbreda88]

We actually kind of do want the vendor name in the main table though... Its sort of necessary.

[Rabbit]

Can you explain why it is necessary? Because while you think it's necessary, that's rarely the case. Since it was designed incorrectly in the first place, you are now looking for a workaround to fix the design issue. What I'm trying to help you do is fix the structural issue so that you don't need a workaround.

Proper design says it is unnecessary and whatever you think you need it for, you can do without storing duplicate data. So unless you have a compelling reason to store the vendor name in the main table, it's better in the long run for you to follow proper design.

You said you want to display the vendor name associated to whichever vendor id is stored in the main table. And I'm betting you also have reports where you would like to display the vendor name. All of this is doable with proper design and without storing the vendor name in the main table.

[dbreda88]

The vendor name in the main table helps us to identify quickly which vendor each purchase was made out to. This way we can simply use the vendors name instead of its ID when speaking by word of mouth to verify that we're talking about a specific purchase. Its an additional way to make sure that we have discussions regarding the correct purchase and everyone is on the same page.

I don't have any reports, its mainly another piece of information we'd like to be displayed and available at a moments notice just by glancing at it. Even if we didn't have the form cross reference the ID number with the name for us, we'd still have the vendor's name in our database for each purchase, but we'd be doing their names by hand instead. This was a time saving measure.

[zmbd]

dbreda88,
What you are talking about is accomplished with the query.

My main PC is down for the count last night or I would build you a test database to give you the ideas; however, I have a very (very) old upload here FaithFormationT racking.ZIP


+1] This isn't the ideal database for a simple example, I will apologize in advance for this; however, my working install of Office is toast for the moment :-(

+2] One you unzip the file, Open, Allow the VBA to run, Make trusted if you would like - but not needed for operation.

++You will have to press [F11] to open the Access Object Navigation Pane, from there take a look at the main attendance table... you will see there that there are no actual names or events recorded; however, if you will look at the queries. reports, and the main form you will see that the names are used.

If I remember correctly, in the queries I setup the fields thru the properties to act as lookup fields. (I NEVER EVER do this at the table level unless setting up a sharepoint database). There is a six month report, it's not going to show anything as the data is for 2012 :)

The database could (and should be) normalized further to pull locations to their own table as well as other information. I have a better one used for seating arrangements that I use for events to make sure certain people don't sit next to or at the same table as other people etc... that would be a good database to show you - but it's on the toasted PC :(

The thing is, your users ideally should NEVER use the data tables directly. They should be accessing the data preferably via forms and then via queries. You provide the nice friendly user names via query and other methods... only in a very specific set of circumstances does one violate the normalization concept.

[Rabbit]

dbreda88, to summarize, everything you said you want to do can be done without storing the name in the main table. And it is better not to store it in the main table.

[dbreda88]

Well we really do WANT the names to be shown on the main table for every purchase. I'm not opposed to moving all the vendor data from the vendor table itself into a query or something to that effect but the vendor names must be displayed for every single purchase. The separate vendor list is simply a place to retrieve those names, where the list has each vendor name and ID that currently exists, recorded in said list, one record per vendor.

I'm aware that forms and tables should not be placed in the same location, and I know how to fix that issue already. That said, I haven't gotten around to it but I will be making a front end and back end soon enough.

We deal with tens of thousands of purchases every year, and deal with hundreds upon hundreds of different vendors and the list grows every year. We simply must have the longhand name of that vendor displayed for every purchase.

[Rabbit]

I feel like we keep repeating the same point over and over but you're having trouble understanding what we are trying to tell you. Let me try one more time as clearly as I can make it.

You said:

... the vendor names must be displayed for every single purchase ...

We are saying:

1. We know that's what you want. We understand your requirements. We deal with this requirement every day.
2. You can accomplish this without storing it in the main table. This is standard operating procedure that is done in all our databases.
3. It's better design if you don't store it in the main table.
4. If you do it our way, i.e. the correct way, you will never have to worry about the vendor name not being there. That is, no matter how you add the vendor id to the main table, the vendor name will always be populated with the most recent up to date name.
5. If you insist on doing it the way you are doing it now, you will always have to worry about the vendor name being wrong or not being populated. We are trying to show you how to do it so you never have to worry about this.

[zmbd]

dbreda88;
We very well know and understand what you are attempting.

1) Select Queries do not move the data. Your data stays right there in the tables where you put it.

2)Did you take a look at the database I referred you to in {Post#8 - below}?

Please take a look at the report named [rpt_retreatsatt ended]
This is based on the query [qry_families_an d_attended_retr eats] which in return is based on the [tbl_attendance] with relationships to [tbl_family] and [tbl_retreat]

I do not store the names of the family nor the names of the retreats over and over again in the attendance table, just the reference to them. Then when this information is required, I pull it together in the query for the report.
In the attendance table all you see are entries like:

[pk][fk_retreat][fk_family]
[1] [1]         [1]
[2] [2]         [1]
...
[30] [15]       [6]

however in the report you will see something like:

Family PK - Family Name - - Retreat Name
1           Alpha           Annunciation
                            Dummy Data Rulles

2           Beta            Annunciation
                            Getting to know your spouse
...
15          November        Nativity
                            (etc...)

(forgive the reference to church activities, this database was in response to a specific question; however, it should serve to illustrate the point Rabbit and I are trying to explain.)
The reason this does this is because the query pulls from the tables based on the table relationships and I selected to return the fields containing the human-friendly text. at no time is the data moved by the query

You can certainly base your reports on a table; however, the queries are where the real power of the database resides, especially in the workflow that you are describing.

3} Tables and forms can be stored in the same place. It is preferable for a multi-user system to split the database into a front-end and back-end so that more than one person can be working with the data files. However, as you noted, we need to get this other concept of normalization down first.


So we really know nothing about your database design; however, we can tell from what you've given us that you are on the path to something that will become a monster very quickly with the amount of data you are talking about.

PLEASE, take a look at what we've given you... we're only try to help you.