One Large Table Problem (1 Viewer)

Hello! I have one large table with about 250 fields and hundreds of records. Each field represents a computer and each record represents an app we have on that computer.

So my table looks like this:

Computer 1.....Computer 2.......Computer 250

Google............Java.................Microsoft
Windows.........Microsoft.............WinZip
Mediaplayer......Google.............Adobe

So as you can see, each computer can have the same apps as other computers. Some computers have apps that other computers do not have.

What I need is just a list of each app we have on all computers.

So my resulting table should have only one field for Apps and only show each app once. Ideally it would look like this:

Apps

Google
Java
Adobe
WinZip
Mediaplayer


I really don't know where to get started... Do a series of queries or macros? Any help is much appreciated! Thanks!

What I need is just a list of each app we have on all computers.

Click to expand...

The word "just" is throwing me. It sounds like you want to keep this horrible structure and extract the data you need for this one request. I would advise against this. Because once you have that list someone is going to "just" need the number of computers with WinZip on them. Then they are going to "just" need a list of computers with Adobe, but without MediaPlayer. Then they are "just" going to need...

You need to structure this data properly, in doing so you will get your list of unique applications. I suggest you read up on normalization (http://en.wikipedia.org/wiki/Data_normalization). That's the process of properly structuring your data. First you would set up the proper table structure, then you would move this table's data to that new structure.

That's the right way to do things. The band-aid way to get "just" what you want for this instance is to move this data to Excel and see what's online for generating a list of unique values in a range. I am sure code for that exists on the internet if its not inherent to Excel.

Thank you. I agree with everything you said. I am working on figuring out the normalization process. Could you give me an idea on the first few steps to normalizing the table?

To get started would I need to open the table in data sheet view or design view? etc.... etc....

cross posted
http://www.accessforums.net/access/finding-unique-records-table-51315.html and
http://www.accessforums.net/queries/identifying-unique-records-table-51314.html

jaebyrd240/jessica
I realize you only have a few posts. However, I would like to highlight to you that cross posting is fine--but please tell readers that you have done so. It's really a consideration/etiquette thing.

Here's why.

I am sorry. I am new to this and did not realize the problem of cross posting. But after reading that article I understand and will make sure to tell experts that I have done so moving forward.

I didn't realize that the same people were on different forums and so I thought I would reach a different group of experts trying different forums. But I see now that that could potentially frustrate those trying to help me.

Thanks for the insight.

All of the experts keep telling me to normalize my table, but I really need someone to give me an idea of the steps it takes to do such a thing.

I've read the articles on normalization and I get the concept. But I don't know how to apply it to my situation.

Work through this tutorial. It will take about 20-40 minutes, and you will learn table design.
If you still have doubts, then read through this one.

The tutorial is a very practical means of learning design concepts.

If you're keen, here is a list of videos in sequence to learn more about ( the why's behind)design:

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

You can go back and watch these as often as you want.

Good Luck

research many to many relationships - have a look at this link

http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

At the very least you will need three tables, one to describe the computer, one to describe the application and one to link the two

Ok since I am new at this.... What does adding to your reputation do?

Jaebyrd240 said:

Ok since I am new at this.... What does adding to your reputation do?

Click to expand...

You see the little green boxes on the left at the bottom of the block with the name, post count, thanks count, etc? As your reputation goes up, you get more blocks and a more impressive sounding description.

It sounds pretty minor, but it's nice to get the reputation points. Makes you feel appreciated, and to some users who notice the green blocks, it may give the impression that you know what you're talking about.

jdraw said:

If you're keen, here is a list of videos in sequence to learn more about ( the why's behind)design:

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Click to expand...

Jdraw,

The content of these videos is really extremely poor. I mean just plain wrong and thoroughly misleading to students and beginners. I know I have said the same before and I guess you disagree because you must have posted links to them a few times. The trouble is they are so bad that I feel I just have to repeat myself. My advice to the OP is to read a good book on database theory and design rather than waste time watching these videos about something called "Logic Data Modeling" (sic - it's a phrase I had never heard until I watched these videos).

People laugh at this one but not out loud.

Buy a book on Dummies or Idiots.

They have simple walk through tutorials. You can scan the whole book in a Day reading only what concerns you. By the end you will have the basic knowledge of what you want. With No Logic Data Modeling.

I have about 4 of the dummies books. I still refer to them from time to time.

I have not gone through the videos that Button Moon spoke about. But if they are like the title of Logic Data Modeling implies then run a mile. You can if you wish come back to them at a later date.

In post #8 it list the tables that you need. I believe CJ London is correct.

Jaebyrd240 said:

All of the experts keep telling me to normalize my table, but I really need someone to give me an idea of the steps it takes to do such a thing.

I've read the articles on normalization and I get the concept. But I don't know how to apply it to my situation.

Click to expand...

Yes, by all means you should reorganize you data. Here is a general approach. I see the need for three tables.

You should have one table which would have all the base info that you have on all computers. i,e,

Computer ID,
Vendor
Model
Operating System
Processor
RAM
Local Disk Storage.....etc,


Another table would by Applications with all the pertinent info about the all the applications you use
Application ID
Vendor
Name
Version....etc.

A third table would tell you what apps you have on what computer, ie.
AppsInstalledinComputer or SoftwareInstalls/Licenses or sth.like that

SoftwareInstallation ID. PK
Computer ID FK
Application ID FK
License Number
Named User....etc

All the info that you want about the placements of Apps on the Computers you can get from the third table. You should not add fields to a table with new arriving application to do this. When a new application arrives you make a record of it in the Applications table and then add records as needed into the Installs table. This gives you a complete picture and you can make queries efficiently across these three tables.

Best,
Jiri

Yes I understand how to organize and link tables.

My problem is that I did not design, create, or have anything to do with the formation of this table.

It was emailed to me as-is. Its really beyond my scope of my job - It was sent to me by a coworker.

This is not something I will need for future reference for myself. Its more like a one-time favor for a friend type of thing.

So I was really hoping someone would send me a few quick steps. Like for example:

1. Build and Run Query ABC
2. Build and Run Delete Query DEF
3. Append Query GHI to Query ABC
4. Create Table JKL
5. Run Macro Delete Dup Record MNO
6....etc...etc....

Jaebyrd240 said:

Yes I understand how to organize and link tables.

My problem is that I did not design, create, or have anything to do with the formation of this table.

It was emailed to me as-is. Its really beyond my scope of my job - It was sent to me by a coworker.

This is not something I will need for future reference for myself. Its more like a one-time favor for a friend type of thing.

So I was really hoping someone would send me a few quick steps. Like for example:

1. Build and Run Query ABC
2. Build and Run Delete Query DEF
3. Append Query GHI to Query ABC
4. Create Table JKL
5. Run Macro Delete Dup Record MNO
6....etc...etc....

Click to expand...

Take a quick peek at the post above, Jaebyrd. This should get you started.

Jiri

It was sent to me by a coworker.

Click to expand...

I don't suppose your co-worker created this table from smaller ones like the ones proposed by Solo?

But if you want a suggestion as to how you could get what you want as a one time exercise

1. Create a single column table (we'll call it tblSmall) with a field called apps
2. Create a query to copy the first column to the new table
3. Repeat 249 times for all the other columns to append to the same table
4. run the query to give you the list
5. pray there aren't any typo's creating potential duplicates

query for 2 and 3
query for 4 change table and field names to suit

Buttonmoon,

We have definitely crossed paths on this before. I agree these are not great, and definitely not theory oriented videos. In fact, even the quality of the video - from a shoot and capture the scene - is poor.
But most of the people on these forums have no formal training in logic, maths, database... Many have found themselves in a job which in some way, shape or form involves database. They are turning to this and other free forums to get some basic guidance and advice.
Many questions lack context. In fact on many forums, the posters feel that Access = database. M$oft has done a remarkable marketing effort that ignores much of the theory and sells the software. That the poor souls who have to use these tools without proper training is obvious.
Now we could tell them all to go to university or college and get formal training. Certainly a great thought, but totally impractical.
I have watched the Chris Date videos you have recommended in the past. Very good and very theoretical --certainly all the background material.
But I contend we are mainly (but not always) helping those who do not have formal training and are up to their waists in alligators while trying to earn a living. (Lately we seem to be getting an abundance of college freshmen who don't work well independently).
So my bottom line is that these videos were a set that started with an issue and progressed through some concepts to identify an ERD. Are they the be all and end all --NO WAY. Is there another set of videos with a common issue that lead the watcher to understand basic concepts --probably. I haven't found one or have not seen one referenced in the forums I frequent.
Don't get me wrong, I recognize the Date videos (and Chen and Codd papers) as a part of the training and concepts. I just don't think that they are the set of videos to which you first send the most common poster.
I'd like to see a database series of videos with better quality (content and recording techniques) that were addressed at the beginner/casual user level. If you or anyone reading this, finds such a series, please post the link for all to see.
Until then, I am going to agree to disagree with you. Agree that these videos are not of great quality, and disagree in that they are helpful to some; do discuss some concepts and are probably at a level to inform many and may even inspire (or incite) others to investigate further for additional information.

I don't know who came up with the word Normalisation. It is not very descriptive.

The object is not to repeat Data. This is called redundant Data.

If you had a business at a certain address and you sent thousands of invoices every month. Why would you repeat the Company's address thousands of times. (Redundant Data) Put the address in a table especially used for addresses. Put it there ONCE.

Every time you need the address you go and get it from the address table. This table could be further broken down by putting the City and Postal Code in yet another table.

This way you save a lot of typing and space. Note also that the Postal Code is the same every time and the City is spelt the same way. So there should be no mistakes.

Now go and look at the Northwind Database and see how is done in a real example. Take a special look at the Relationships.

Hope this helps.