Not sure where to go with Access

[mylifeasbrad]

Hello Everyone,
I'm not really familiar with Access and I remember how my teacher said Access is pretty great with data manipulations and showed me a bit. I was wondering if Access can help me with some inventory. I used pictures to help show what I have, how it relates, and what I want to do as an end result.


I have a column titled B (these are actual SKU numbers and there are multiple copies of them) you can see in the first picture. Column C has all different data that I need to relate to that number in Column B when pulled for a report.

I tried to draw out the relation in the second picture how I need the same SKU to relate to all the data in the the next opposing cell.

From there I would like to create a table or report or something that lays out like I made up in the third picture. I do need to be able to make that last report or table to format into excel so I can upload it to a retail POS inventory. I have roughly 30,000 cells so if anyone could help guide me what to do, this process would save me hours of labor doing it by hand!

Picture 4 shows how the goal is with color from picture 2.

Thank you very much!

 

[MarkK]

Hey there and welcome to the forum.

To me it doesn't seem specific enough what you are trying to do, like, you say, "from there I would like to create a table or report or something," but a table and a report are so completely different.

Your best starting place if you have no experience with relational databases is read up on what they call "database normalization," which describes a set of principles that govern how to represent related data in tables. There are some basic rules that if you break them, you might as well not bother with a database.

hth

 

[plog]

You didn't ask a specific question, so here's my general answer: Start by structuring your table(s). Google 'normalization' and read a few tutorials on the subject.

Then, once you have that set up correctly, you will need to move your existing data to that structure. Most likely this will involve the importation of it into a temporary table and then a few append queries to make sure the data goes to the right fields/tables in the new structure.

 

[mylifeasbrad]

Hey there and welcome to the forum.

To me it doesn't seem specific enough what you are trying to do, like, you say, "from there I would like to create a table or report or something," but a table and a report are so completely different.

Your best starting place if you have no experience with relational databases is read up on what they call "database normalization," which describes a set of principles that govern how to represent related data in tables. There are some basic rules that if you break them, you might as well not bother with a database.

hth

Sorry I wasn't sure what to call things in Access. Thank you for your input and I will spend some time looking up database normalization!

What I want is to have is an excel sheet that looks like my final picture. I need to move the data into that format instead of like I do in the first picture.

 

[Foe]

What I want is to have is an excel sheet that looks like my final picture. I need to move the data into that format instead of like I do in the first picture.

The best way to accomplish that would be to use Excel.

But, in all seriousness, the first thing you must train your mind to do when learning Access is to stop thinking in terms of Excel. They are different programs that do different things. Based on what I've read here, I think what you really want is a Report that presents the information in your final picture. Access can do that for you. It can create this Report for you and then export it to Excel if you're hard over on using a spreadsheet, but you can also stay entirely within Access and achieve what you've described.

Figuring out the base units of your data (normalization) is the first step.
To get you headed in the right direction, from what I can see you need a table with SKU numbers and a table for each of the attributes you have assigned to those SKU numbers.

Check out the attached db for an example. The SKU table attributes are just pointers to the list of attributes in the other tables. Once you've got the base parts separated, you'd create some forms to interact with that data. You don't really want to toy with the tables directly once their setup.

 

[jdraw]

Here's a tutorial that leads you from a description of an issue/opportunity to a normalized set of tables and relationships.
Spend an hour or so and work through this, and you'll quickly see whether Access is suitable or not.

Good luck, let us know what you decide.