Interconnecting data help

[grewpar]

Hey guys!



I have multiple tables, and they all have different data except for one column of "Report Keys". I'm trying to connect simply the report keys from each each table so that I can have all of the data connected and organized based on the report key. I could manage this on excel somehow, but I'm new to Microsoft Access and despite much personal inquiry, I cannot figure it out.

Any help is appreciated!

Thanks

[Bob Fitz]

Is "Report Keys" the Primary Key in one of the tables.

[grewpar]

I actually didn't know what a primary key is until I just looked it up. I don't have anything set as a primary key. Is this how I would interconnect the data?

Thanks for the help so far!

[June7]

Do you want to provide db for analysis? Follow instructions at bottom of my post.

Yes, primary and foreign keys is how tables are related (hence the term 'relational database').

[Bob Fitz]

I suggest you search google for pages on database design/structure. Here's one I found: http://office.microsoft.com/en-gb/ac...#_Toc270678227

[grewpar]

Okay so I have been doing some research so that I don't waste everyone's time. So I have set report keys as the primary key. I have also set up a relationship between the report keys between all six tables....Now that the relationship has been established, how do I use this to view all of the info for one report key at once? Or what features have I unlocked by creating a relationship?

[Bob Fitz]

Can you post a screen shot of the Relationships

[grewpar]

Can you post a screen shot of the Relationships

Here's a screen shot.



For each report key there is data, however the data is separated into tables, I'm trying to see all of the data for each row/report key consecutively. Are relationships the wrong way to approach this?

And by the way, I really appreciate all of your guys' help so far.

[June7]

What is the data type of each table's Report_Key field? Cannot link on autonumber fields.

Why do you have manufacturer address info in two tables?

[grewpar]

The data type of each Report_Key field is "Long Integer" I believe. Also the manufacturer address info is in two tables for reasons unknown, so I'm keeping the data just in case, it may be slightly different for some report keys, however I can't look through 1.2 million lines to verify.

[June7]

These tables have 1-to-1 relationship? Will there always be related records in both tables. If yes and yes, why not one table?

Why do tables have year in their name? What is the third table with Field2 and Field6 for?

[grewpar]

Yes these tables have a 1-to-1 relationship, however one may have more report keys than another. All the data isn't in one table because I didn't create it, and perhaps because all of the data would exceed 2 GB of space. I most definitely would have put it in one table! The years indicate the span of time that each report key belongs to.

Summary of tables:

Leftmost: This table contains every single report key ever created, I just deleted all of the keys besides the 2012-2013 because that's the only data relevant to what I'm doing.

Middle: This table has every device name and manufacturer associated with every report key, which the leftmost table does not have.

Rightmost: This table contains the report key, and a description of a malfunction for the corresponding devices....

All tables contain unique things, which is why I need all three. The first will help me verify that I'm not missing any report keys in the others....which I probably am, hence its importance.

[June7]

With 1-to-1 relationship, data all in one table doesn't make any difference about file size if the empty field is Null and not empty string. I never allow empty strings in tables.

[grewpar]

Well there were three separate .txt files, and I'm not sure on how to combine them while keeping all the report keys/rows in sync.

[June7]

Options:

1. Create the necessary fields in the destination table. Use query builder to join the tables then switch to UPDATE query and set the UpdateTo criteria. Run the query.

2. Create a query that joins the tables then switch to MAKE TABLE and run. If you are happy with the new table, delete the originals.