flat table or related tables? (1 Viewer)

[Libre]

I have a lot of data in spreadhsheets that looks like this:

ProductLine 1:
PartNumber 1| Supplier | SupplierPhoneNo | PartWeight | PartMaterial | ...
PartNumber 2| Supplier | SupplierPhoneNo | PartWeight | PartMaterial | ...
PartNumber 3| Supplier | SupplierPhoneNo | PartWeight | PartMaterial | ...
etc.

ProductLine 2:
PartNumber 1| Supplier | SupplierPhoneNo | PartWeight | PartMaterial | ...
PartNumber 2| Supplier | SupplierPhoneNo | PartWeight | PartMaterial | ...
PartNumber 3| Supplier | SupplierPhoneNo | PartWeight | PartMaterial | ...
etc.

and so on for 7 different ProductLines.

I have to create a "master spreadsheet" of all of the above.
I'm setting up a table in Access to hold the data before I export it to Excel - which is the final goal.
I have procedures in Access to find the needed data for each part number in each product line - comparing the key index (PartNumber) to related tables in Access.

The final result should be a single spreadsheet of all this data. Using sorting and filtering in the spreadsheet, one can see, for example, only those PartNumbers supplied by a certain supplier. Another wrinkle is that each PartNumber can be supplied by more than one supplier. Another wrinkle is that there are 3 plants that I have to collect and report this data.

If I try to create a flat table for the 7 product lines, 2 suppliers for each part number, 2 supplier addresses, 2 supplier phone numbers, for 3 plants, I end up with a massive table of 43 columns in all. I know that these one-to-many relationships (example, many suppliers for each part number, many plants for each part number, etc) deserve related tables, rather than trying to "columnize" it all in one single table. But the final output I want is a single spreadsheet without any lookups or macros - just a flat spreadsheet from this data.

So I'm at a bit of an impasse here, trying to decide how to do this. Would you just design a flat Access able of 51 columns or try to do it with multiple related tables in Access? I just don't know how I would get the result I'm looking for, using the (in my opinion, correct) approach in Access, of related tables. Of course a downside of the one flat table, is that if they add a plant or want 3 suppliers instead of 2, then that multiplies out and the number of columns in the flat table grows exponentially.

Sorry for so much verbiage. Not easy to clearly describe a technical problem with a few words. If your eyes glazed over after the first paragraph I understand - but if you read this through and can shed any light on this I'd be appreciative.

One last thing - this is a project for the President of my company (gulp), who called upon me, as I have a reputation for being pretty good at this stuff.

 

[plog]

If you are going to use Access, use it properly--structure your tables in a normalized fashion. I don't know where people get this idea that Access is Excel But Better. It's not.

Further, why must the end result be in Excel? Filters? Sorting? One huge spreadsheet you have to scroll right to see all the data? Why not incorporate all those functions into an Access form/report and make searching easy?

If you are going to structure your data for Excel and ultimately consume the data in Excel, why not just use Excel?

 

[Libre]

If you are going to use Access, use it properly--structure your tables in a normalized fashion. I don't know where people get this idea that Access is Excel But Better. It's not.

Further, why must the end result be in Excel?

because we're sending it to an outside contractor to do further work, who needs it in Excel.

Filters? Sorting? One huge spreadsheet you have to scroll right to see all the data? Why not incorporate all those functions into an Access form/report and make searching easy?

because we're sending it to an outside contractor to do further work, who needs it in Excel.

If you are going to structure your data for Excel and ultimately consume the data in Excel, why not just use Excel?

because I have to fill in the data from tables in Access. easiest way I can think to do that is bring in the data from Excel, run the procedures to get the needed data, store the data in an Access table, and then bring it back out to Excel.

I don't think that Access is Excel at all. But Access and Excel work very well as a team. Access has functions that Excel does not, and Excel has functions that Access doesn't, and the data is readily imported/exported from one to the other.

So I don't think it's correct to think you should work EITHER in Access OR Excel but not use both resources. I've solved many daunting problems using both programs in conjunction with each other.

Another thing is, when the President of YOUR company (or any user) tells you he needs a report in Excel, one response is to try to convince him that an Access report is the way to go. That wouldn't work well with this guy.

 

[jdraw]

because I have to fill in the data from tables in Access.

I'm not following the above.

You have several spreadsheets in Excel, and
your boss tells you the report must be in Excel, and
an outside contractor to do further work, who needs it in Excel - WHY do you need Access?

My view is, if you are going to use Access, or any tool, use it as it is designed to be used.
But I'm not getting info from your post that says you need Access.

 

[Libre]

User: Here's some Excel spreadsheets of Part Numbers and some other blank columns. We need you to fill in the data. When you're finished, give us an Excel spreadsheet with the missing data filled in - and we'll send it to an outside contractor who will do further work.

Me: Hmmmmmmm. I have an Access database of Part Numbers that I created, with the required information. If I bring the Excel data into an Access table(s) that I'll create just for this project, I can then loop through the table and collect the required data from the related tables, writing it to this new Access table as I go. When the loop is done, I will export (or copy/paste) the data from Access back into a spreadsheet and give it to the user.

It's fine to say that a tool must be used only in the manner it is designed, but that attitude is far less useful in the real world than the ingenuity to adapt the tools and resources at your disposal to the problem at hand.

 

[jdraw]

Yes, regarding adapting a tool --It's surprising how many things look like nails, when the only tool you have is a hammer.

Adapting is fine, but planning automated support for a recurring, viable business requires more than adapting.

I see what you're doing and have been there. Use the tools you know to accomplish the immediate goals/objectives.

Good luck with your project.

 

[Libre]

Yes, regarding adapting a tool --It's surprising how many things look like nails, when the only tool you have is a hammer.

These are also quite common:
-it's surprising how many nails are left hanging (hammer in hand) because they were not recognized as nails;
-it's surprising how many nails are left hanging, because you didn't realize the hammer also has a claw end;
-it's surprising how many nails are left hanging, because you didn't have a hammer. You did have a money wrench (or a stone, a brick, a bust of Aristotle, etc), perfectly capable of driving a nail, but sorry, no hammer.

All this is to say, flexibility will go a lot further than strict adherence to a general principle.

Finally, there is nothing about this project that Access and Excel were not designed to deal with - it's just up to me to figure out the best way to use them.

And thanks for your responses!

 

[plog]

Me next. I love this internet game of beat the analogy into the ground. So, here's mine:

You have a nail, you have a hammer and you have a nail gun. Your boss wants you to use the nail gun because hey, its a nail gun which is like a hammer, but better for some reason (he doesn't fully know why its better, its just accepted the nail gun is better). However...

He doesn't want you to plug the nail gun in, or use the pneumatics, you must grab the nail gun by the shaft and beat the nail into the wall with the handle.

 

[Libre]

The result?
Nail is driven, boss is happy, job is kept.

But besides - nobody is telling me HOW to do it, or what tools to use. They are only telling me the result they want - an Excel spreadsheet.

 

[JHB]

...
Me: Hmmmmmmm. I have an Access database of Part Numbers that I created, with the required information. If I bring the Excel data into an Access table(s) that I'll create just for this project, I can then loop through the table and collect the required data from the related tables, writing it to this new Access table as I go. When the loop is done, I will export (or copy/paste) the data from Access back into a spreadsheet and give it to the user.
...

And what exactly is the problem with the above?
I would do it in the way it would be easiest for me - in one big table or in several tables would not be the matter - only how can I produce the result easiest would matter.
But without seen the "input" data you get and the result you want it isn't easy to say which solution to choose.

 

[AccessBlaster]

Who the heck cares what happens to the data, export it into an Excel spreadsheet or PDF why is that even an issue? Have you at least gotten this far?

[B][I]tblproductline[/I][/B]
PartNumberID
PartNumber
PartWeight
PartMateral
........

[B][I]tblSupplier
[/I][/B]SupplierID
Supplier
SupplierPhone
.....

 

[DennisOJensen]

Looks at Nail in left hand... looks at hammer in right hand... looks at computer on desk. Thinks. Puts nail and hammer down and sits done to work on the problem with the computer.

Getting back to the original question. Create the relational tables that you should have to store the data properly. Populate those tables with the data. Then create a query that gives you the results you want then copy and paste into an excel spreadsheet.

Oh and I concur with the -- use the tools that you have to the best of their ability but get the problem solved in the most expedient method possible if that is what is being asked. Sometimes the down and dirty solution is sadly the better solution. I so much prefer the utopian world of my dreams