How to merge separate tables

[RogueIII]

I've just started using Access. I've got a database with 11 different tables. They all have a 'regno' number column. I'm trying to merge the separate tables into one table.


There's a few problems. Some of the tables are missing a 'regno' number while other tables mention a 'regno' number more than once. I've tried using 'Simply Query Wizard' but that usually hits the 2GB limit. I think that's because it duplicates the entries where regno numbers are shown more than once. What should I do? Is there a better tool to merge the tables?




If it helps, here's the Access file: Access Table



Ultimately, I want to export the merged table to excel. Here's what I'm aiming for the final table to look like: Final Table

[ranman256]

Build a keyed table then append recs from all the tables.
duplicates will be ignored.

[RogueIII]

Build a keyed table then append recs from all the tables.
duplicates will be ignored.

The problem is that I would like the duplicate entries to remain. I might be easier with a simplified example of the database.

Here's the Income table:

Regno Number	Income
200053	122402
200053	213423

And here's the Trustee table:

Regno Number	Trustee
200053	Mr Smith
200053	Mr John

The merged file is similar to the following:

Regno Number	Income	Trustee
200053	122402	Mr Smith
200053	122402	Mr John
200053	213423	Mr Smith
200053	213423	Mr John

I'm trying to get the merged file to look like this:

Regno Number	Income	Trustee
200053	122402	Mr Smith
200053	213423	Mr John

So, it ignores the other columns. Almost, if it looks for a 'regno' number and copies and pastes columns next to it.

For a better example of I'm trying to achieve, you can have a look at the 'Final Table' file. So, would that be possible to do something like that in Access?

[orange]

Keeping duplicates is not the purpose or intent of relational database.
You might get some insight on database concepts from Rogers Access Library and
Normalization

Good luck.

[RogueIII]

Keeping duplicates is not the purpose or intent of relational database.
You might get some insight on database concepts from Rogers Access Library and
Normalization

Good luck.

Thank you for those links! You're right in that the current database is a relationship/normalised one. But, how can I export the database to a table similar to Figue 1 in the second link? Would that be possible in Access?

[June7]

How should the query know to reject Mr John for Income 122402 and Mr Smith for Income 213423? What rule should be applied?

GIGO - Garbage In Garbage Out. Bad data structure means bad data output.

[RogueIII]

How should the query know to reject Mr John for Income 122402 and Mr Smith for Income 213423? What rule should be applied?

GIGO - Garbage In Garbage Out. Bad data structure means bad data output.

But, would it be possible to have the query look for a regno number in a table and copy the columns into a separate table? So, search Income Table for all instances of regno number 200053. If it finds a match, copy the Income column into a separate table. Then, it goes to the Trustee Table and searches for the regno number 200053. It finds Mr John and Mr Smith. It pastes this data in the final table, next to the Income column.

So, instead of matching data, it does a basic find and copy/paste. Would that work?

[June7]

Still not going to get the output in your "I'm trying to get the merged file to look like this" example. Same question. What rule should be applied to eliminate Mr Smith or Mr John?

[RogueIII]

Still not going to get the output in your "I'm trying to get the merged file to look like this" example. Same question. What rule should be applied to eliminate Mr Smith or Mr John?

Oh, ok. I don't think there's a rule to eliminate Mr Smith or Mr John. It's more that the Income and Trustee fields relate to the regno number rather than to each other. So, that's why the data shouldn't be 'duplicated'. It's why I thought the copy/paste method would work. Or should I be using relationships? I want Access to understand that information from each table should be taken for the regno number. The information from Income Table isn't related to the information from Trustee Table, at least as far as Access is concerned

[June7]

Can do a query that joins the tables and shows that Mr Smith and Mr John are both associated with the same Regno, and this will also show them as associated with the same Income. If that is not desirable, what purpose is served in joining these tables if the result is nonsense?

Maybe you need to build a report based on the query that does grouping of data by Regno and Income. Then in the detail section would be listed the Trustees who are associated with the Regno/Income combination.

[orange]

Tables and relationships are designed based on your business requirements and rules. Relationships are not arbitrary; they are based on the rules of your business.

If you spend 30 to 45 minutes on this tutorial, you will learn the process of taking your business description to a data model that you can test. Once your data model matches your requirements, you can build/develop your data base.

Good luck.

[RogueIII]

Can do a query that joins the tables and shows that Mr Smith and Mr John are both associated with the same Regno, unfortunately this will also show them as associated with the same Income.

So what purpose is served in joining these tables if the result is nonsense?

I'm exporting the results to Excel and it would work better there, especially with filters on the regno column. I'm going to be running it through a data visualisation tool as well as sending the finalised spreadsheet via email. It would make it much easier if everything was in one table instead of needing to continually refer to 11 different tables and a list of regno numbers and names.

Would it work if I got a list of regno numbers (with no duplicates) and used an append query to add each table individually?

[June7]

What 'each table'? You only describe two tables - are there more?

I still don't see how writing data to another table will accomplish more than can be done in query.

[RogueIII]

What 'each table'? You only describe two tables - are there more?

I still don't see how writing data to another table will accomplish more than can be done in query.

Ah, I thought you might have seen the actual database file. But, there's 11 tables in total. I only used two as an example of what I wanted to do.

The query would be fine. But, the table is being sent to people who don't have Access and are only familiar with Excel. So, it needs to be easy for them to use. And putting everything in one table seems like the best way to achieve that.

[June7]

Oh, I did try your downloads but I am blocked here. I can download files attached to thread.

If relationships are 1-to-many or many-to-many then everything in one dataset will have repetitious data. Period.