Importing excel data in access table

[plog]

Does it mean that we cannot use Foreign Key in a table which doesn't have a direct path from Primary Key to Foreign Key??

Correct. At that point its redundant data and a source of data errors. If you need access to data that is indirectly related to a table, you create a query linking them by whatever table(s) are between them.

My question about one-time/multiple imports had to do with autonumber primary keys, not your table structure. When you import data multiple times, from a flat file (non-normalized) to a database (normalized tables) it can be tricky making sure the autonumber primary keys get assigned correctly as foreign keys. It requires you're import process has a specific order so that the keys get assigned in the correct order so everything can link up.

Your names still could use some work. 'Type' (tblCableSchedule_CableType) is a reserved word, I see a slash in tblCableSchedule_Index.Description/TagNumber and parenthesis in tblCableSchedule_SUM_ManHours.Scope(Quantity)

 

[khurram7x]

Thank you so much for your support. Now I'm one step ahead of where I was before your advices.

I've presented my Manager yesterday after the final design, and he said it's too complicated for you at this stage and it'll take a very long time to develop a system if you first want to learn everything and then try present a perfect database design at first step. He said we need to import tables from Excel sheets time to time in our database with every revision for client, and it'll be a big problem for you to import each and every column and later match foreign keys. He knows bit about databases, and is very supportive.

He added, you're learning so start with baby steps. He said just produce a flat table first, don't worry about repeating values and later as you'll start understanding database concepts better, you'll improve the design and we'll use the new version for next project. So, now i'm starting all over again with a flat design ((

 

[plog]

I can see his point--he needs something that works, even if its not the most efficient. My advice and what I do when I start a new job--use the existing official process, but work towards the proper one.

Use his method for the "official" system, but develop your "proper" one on the side in any free time you have. This way, you have something to judge your proper version against. Hopefully at some point you've refined the proper one to the point that it becomes the official one.

Good luck and we're here when you run into issues.

 

[khurram7x]

Thanks plog, really appreciate your support. I'll work through the proper system in free time. You helped me to know the basic design things, so I'll apply )