importing excel to access keeping relationships in access tables (1 Viewer)

[emshim]

I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.

I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.

I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.

I hope this makes sense, Thanks in advance for any help you can give!

Emily

 

[neileg]

I would normally do the data reorganisation in Excel and upload the dat into Access and establish the relationships. Quick and dirty fixes are much easier in Excel.

 

[emshim]

The problem is, i need it to be updated every week, and it cannot take too long to do it, is there any other way around this that can be incorporated into the click of a button?

Emily

 

[neileg]

Ah, OK. It really depends on what format your spreadsheet is in and whether id=t has IDs and Partnumbers that you can use to establish PK/FK relationships.

 

[David Eagar]

Maybe a silly question, but if you have gone to all the trouble to build the database & figure out how to migrate your XL data, once that is done, why not add any subsequent data straight into the database?

 

[emshim]

The EL spreadsheet is updated by a different person for pricing, and is used in a number of other databases and XL spreadsheets.

Neileg, the spreadsheet has the correct columns, but nothing else. What would need to be added?

emily

 

[David Eagar]

I might be out of line here, but I will continue anyway..

As a business strategy, your goal must be to have 1 central location for the data, which all authorised users can access. Having multiple spreadsheets, held by multiple users, updating other multiple spreadsheets to me is surefire way of creating business chaos

Construct the database so that ALL applications source their data from the one, true source of current, up to date, accurate data

 

[emshim]

I have imported the whole excel spreadsheet into the database. I believe i need a number of queries, which will find each company, and put each product into the product table related to that specific manufacturer. Could anyone give me an idea of the kind of query i will need to do this? Or is there a better way you know of?

Thanks for your time,

Emily

 

[David Eagar]

It is probably a series of update queries, but without seeing the data, it is hard to be more specific

 

[emshim]

I will upload a copy of part of the excel spreadsheet which i need to import. Is this the data you ment?

Emily

 

[emshim]

I only need the manufacturer, product, sub-system and part Number. From this excel spreadsheet, you can probably see what the relationships are.

Any help would be greatly appreciated

Emily

 

[David Eagar]

Hooley dooley, you don't do things by halves do you?

If your database is is set up using normalisation, you will need to check your database table structure and do a lot of find & replace in the spreadsheet.

For example for Manufacturer, VCON would be pk 1 in the Manufacturers lookup table, POLYCOM would be 2 etc etc

Same for Categories and any others you can think of

 

[neileg]

Mmm...

If this must be the source of your data, then the existence of the blank lines and headings, plus the multiple sheets is going to make a regular import of this data a real pain. You are going to have to read in the file one line at a time, in code, nad test each line to see if it matches the right spec to be data rather than a heading or formatting. This type of coding is outside my skillset so I can't help.

I supect that it could be easier to read the data into another spreadsheet and strip out the lines you don't need.

I agree with David's observations about having a single central data repository. It would be much easier to produce your spreadsheet price list from data held in Access that it is to read the spreadsheet into Access from Excel.

There are some anomolies in the data, too, such as some items having a supplier of 'In-Stock' unless that really is the supplier name!

 

[emshim]

hmmm, seems like ive got to a point where im stuck then, as, to be honest, my programming skills are virtually non existent with exception of a few basic bits and bobs! I did think that these headings were going to cause a bit of trouble...i'm going to try and take your advice by producing another pricing list from the original, stripping it up. The problem is, because the pricing list is written by someone else, it is not my choice of how it is formatted! Ahhh! Thanks for the input anyway guys, it is much appreciated!

Emily

 

[David Eagar]

If this was my project (and praise the lord it is not) I would start with populating lookup tables (Category, Manufcturer, System & Subsystem)

THEN begin the process of building the Main table to store prices etc, but as Neil has pointed out, it MUST be consistent - one and only one rule for all!

 

[David Eagar]

hmmm, seems like ive got to a point where im stuck then, as, to be honest, my programming skills are virtually non existent with exception of a few basic bits and bobs! I did think that these headings were going to cause a bit of trouble...i'm going to try and take your advice by producing another pricing list from the original, stripping it up. The problem is, because the pricing list is written by someone else, it is not my choice of how it is formatted! Ahhh! Thanks for the input anyway guys, it is much appreciated!

Emily

Do not despair, what you are trying to acheive IS doable, but needs a lot of thinking through. Spend some time searching this forum (it is a goldmine) one of the common themes hammered by the experts is that the data drives the database

This is in many cases is contrary to how users want things to happen - I prepare my data this way, design your database to suit! (This will generally be a built in recipe for failure)

Get the data right, that is in the most efficient, flexible manner possible, build the database and then instruct users - From now on THIS is how you will enter your data

 

[emshim]

i could not open that test.zip file, my computer will not allow me to at work for some reason. Thanks for the info though, and if i find some amazing way of doing it, i will let you know. Problem is, i have about 3 days until i go back to university, so i'm gonna be in trouble if its not finished (which is unlikely!!!) But at least i'm learning a lot of ways, NOT to do things in the future, thanks to all you guys on here!!!

Thanks for the help,

Emily

 

[David Eagar]

Just as a bye the bye, how many users need access to this data?

 

[emshim]

Im not sure exactly, but there are two or three other databases i believe which use information from this XL spreadsheet, and then maybe 10 people who use each database?

Emily

 

[David Eagar]

For what it is worth, I think your structure should start with (attached)