Uploading Loop Function

[tl mike]

I am trying to create a form that will allow me to upload data from Excel.

The data that is in the excel spread sheet needs to go into a table the is linked to other tables. The excel spread sheet has the data as such

- Product Name
- Model
- Manufacturer
- Type

The database tables are setup as such:

Tables
Catalog Product Table
- Catalog Model PK
- Model FK
- Product FK

Product Table
- Product PK
- Product Name

Model Table
- Model PK
- Manufacturer FK
- Type FK

I was thinking if I uploaded the excel spread sheet data into a blank table then ran a loop function that will look for the model, manufacturer, and type informaiton and save the information Model PK and joining Product PK in the Catalog table.

I only know a little VBA and am unable to write a function like this.

Any help whether this will work and or some coding is greatly appreciated.

Attached a sample DB

 

[gemma-the-husky]

you should be able to upload the table, but then use various append/update queries to insert/amend items

 

[jzwp22]

Are you going to be doing this upload on a regular basis or is it just a one time thing? You can probably do this with a series of queries run in a specific order. It might be good to create the queries and see how they work out. Once you get the queries to work, you can create a VBA procedure that runs the queries in the correct sequence.

I would import the Excel speadsheet into Access as a new table as you suggested. You will probably have to use a series of append queries to get the data into your primary Access tables. The reason I said a series of append queries is that say for example that you have a manufacturer listed in your Excel data that is not in your manufacturer table. You would have to run this append query before you could append the model information for that manufacturer. You will also have to worry about appending duplicate information for manufacturers and models.

Once finished, I would delete the table with the Excel data in it.

Regarding the database you attached to your post, I noticed that you had lookup fields at the table level. Although Access has this functionality, it is generally not recommended. Lookups are best done at the form level.

 

[tl mike]

Thanks for the quick replies!!


I have tried doing an append query etc. but I start to have problems with trying to get the data to look at all the variables.

I have attached a new db which has a table called Excel. You will see how the excel data is structured.

From that table I need to add that information to the catalog table. In order to do so the model information needs to pull up the model PK and that is where I get stuck.

With my limited Access knowledge I dont know how to create some type of criteria that it looks for the Model, Manufacturer, and Type to get the model pk to input into the Catalog table.

 

[jzwp22]

I had a quick question about your table structure. Let me illustrate with an example. You have a Ford Explorer SE, does it take the same bumper as a Ford Explorer Limited ? In your current structure, either type can only take the same bumper. I don't know if this is true. The part may vary by both model and type; this is what your Excel table shows as well. To tie a part to both model and type, you would need to modify your structure to the following.

tblModel
-ModelPK primary key, autonumber
-ModelName
-ManufacturerFK

tblModelTypes
-ModelTypePK primary key, autonumber
-ModelFK foreign key to tblModel
-TypeFK foreign key to tblType

tblType
-TypePK
-Type

tblProduct
-ProductPK
-ProductName

Then you would tie the model-type and product in your catalog

tblCatalog
-CatalogPK
-ProductFK
-ModelTypeFK (foreign key to tblModelType)



Once the table structure is cleaned up, we can proceed with the queries need to move the data out of the Excel table.

 

[tl mike]

I wish it was that easy but as the db shows same models can have different types but take the same products. So the data has to be shown as:
Model
Manufacturer
Type

taking the PK from that comidination in the models table to link to the products.

 

[tl mike]

Actually I think I finally have it somewhat figured out with the use of an update query I set it up to where all the related fields are linked

Model Table Excel Table
Model = Model
Type = Type
Manufacturer = Manufacturer

And run the query to update a new field in the excel table ModelPK
Once i do that I guess I will have to go into the Excel table and delete the additional information since I now have the actual modelPK and then append the data to the catalogs table.

I wish there was a less labor intensive way of doing this but that is what I have figured out so far (then again it would take years to input the data 1 by 1) so this will help out a lot.

 

[tl mike]

Attached is db that I have added the update and append queries that will update the PK's and then Append the data to the Catalog table

 

[jzwp22]

Since a model can have multiple types, you have a one-to-many relationship. Normalization rules would warrant the structure I proposed earlier. With that structure you have the ability to tie whatever product is necessary for that model & type. In the attached database, I have modified the structure accordingly, and I also removed the lookups at the table levels as I suggested earlier. I then created a query that joins all of the appropriate fields in your primary tables (qryManufModelTypeList). The SQL text of that query is as follows:

SELECT tblManufacturer.ManufacturerPK, tblManufacturer.Manufacturer, tblModel.ModelPK, tblModel.ModelName, tblType.Type, tblModelTypes.fkTypeID, tblModelTypes.pkModelTypeID
FROM tblType INNER JOIN ((tblManufacturer INNER JOIN tblModel ON tblManufacturer.ManufacturerPK=tblModel.ManufacturerFK) INNER JOIN tblModelTypes ON tblModel.ModelPK=tblModelTypes.fkModelID) ON tblType.TypePK=tblModelTypes.fkTypeID;


I use the above query in the append query (qryAppendToCatalog) to match to your Excel table and then append the corresponding productID and modelTypeID. You would just need to run qryAppendToCatalog to do the append--there is no need to run an append query followed by an update query. This is the text of the append query:

INSERT INTO tblCatalog ( ProductFK, ModelTypeFK )
SELECT tblProduct.ProductPK, qryManufModelTypeList.pkModelTypeID
FROM (qryManufModelTypeList INNER JOIN Excel ON (qryManufModelTypeList.Manufacturer = Excel.Manufacturer) AND (qryManufModelTypeList.ModelName = Excel.Model) AND (qryManufModelTypeList.Type = Excel.Type)) INNER JOIN tblProduct ON Excel.Product = tblProduct.ProductName;


The query qryFinalListing shows all of the details:

SELECT qryManufModelTypeList.Manufacturer, qryManufModelTypeList.ModelName, qryManufModelTypeList.Type, tblProduct.ProductName
FROM tblProduct INNER JOIN (qryManufModelTypeList INNER JOIN tblCatalog ON qryManufModelTypeList.pkModelTypeID = tblCatalog.ModelTypeFK) ON tblProduct.ProductPK = tblCatalog.ProductFK
ORDER BY qryManufModelTypeList.Manufacturer, qryManufModelTypeList.Type DESC;

 

[tl mike]

Thanks!!! for the bit of information I would have never thought of it that way. Setting up the db the way you suggested will also alleviate some problems I have with data entry. Some individuals enter a specific model name differently then the actual model name made by the manufacturer.

 

[jzwp22]

Glad I could help out. With respect to data entry, I would recommend that you load up the manufacturers, models and types as much as you can beforehand, and then have your users choose from combo boxes for data entry. This will reduce the variation in the data entry by your users, and it will make any appends/updates easier.