Access- problem with tables

keithdc

Hi,


I have inherited an access database. The database contains 6 (or so) tables. There are approximately 2000 records, with data spread across the 6 tables. Each record has a unique number that is common across the tables.


For various reasons, a huge amount of the data and records are irrelevant. I would like to merge all the tables in to one table (I can easily delete the irrelevant records).


I believe I could do a query, export the data, and then create a new database by re-importing the data but this seems overly complex.


How can I re-create one table with all the other tables?


Many thanks for any help.

gonzo127

do a simple make table query, use the unique number on the records to link the records and pull across all the fields from each of the tables that you want

paddyrg

Do all the tables contain the same shape of data? Are they like 6 different regions each with name, phone number etc? If so, you can do what you want in one slightly involved SQL statement using UNION and some filtering.

Or alternatively the other tables may be acting as lookup tables for filtering and economy of space (look up 'third normal form' if you want some background). It's good form, although these days space and processor power are so cheap it's not a big deal any more.

As a rule though, 2000 records is tiny, so should not be causing you speed problems. More likely is not understanding the fundamentals of database systems which means you're looking to delete some rows in order to work/find records easily, and remove history. My personal preference is to never delete data unless it's absolutely essential - the value of many data base systems is in that historic data, being able to call former clients and make them current again for instance.

Do you want to learn something new and quite different? Access can be the start to a life of professional IT, getting to understand data, normalisation, SQL etc. This might be the excuse you need for work to send you on a training course to get you started - it's not a short path but can be very lucrative if you're interested

Jivesinger

paddyrg wrote: »

Do all the tables contain the same shape of data? etc.

I think understanding the data is key.

It's still possible to combine the data in the various scenarios, but the Access code would depend on the exact arrangement of the data.

In addition to the things paddyrg mentioned, you also need to watch out for the cases where the identifier is common to all tables, but in some tables there are multiple records for that ID.

For instance there may be a customer identifier on every record, but each customer might have multiple orders, so the Orders table could have any number of records for each customer. In this case you wouldn't really want to combine them into one table.

It will be hard to give an answer on here without knowing more about the data.

Mr_Toad

Jivesinger wrote: »

I think understanding the data is key.

It's still possible to combine the data in the various scenarios, but the Access code would depend on the exact arrangement of the data.

In addition to the things paddyrg mentioned, you also need to watch out for the cases where the identifier is common to all tables, but in some tables there are multiple records for that ID.

For instance there may be a customer identifier on every record, but each customer might have multiple orders, so the Orders table could have any number of records for each customer. In this case you wouldn't really want to combine them into one table.

It will be hard to give an answer on here without knowing more about the data.

This and what paddyrg said.

As someone who worked with large database for decades the above is good advice.

I can't tell you the problems I've had over the years with databases designed by people who don't understand the data and the normalisation process.

keithdc

Hi folks,


Thank you for the advice so far.


To pick up the queries on the data. The data was collected for a research study. Each record refers to one study event- the unique identifier that is used across tables describes the date and time that the event occurred.


Some people will have been involved in the study twice, but is the number of events that I am interested in.

To answer the query about why I wish to delete records.... the original database will stay in existence. I am using the data for a different analysis, so have made a copy of the original. To make it easier for me to get my head around how many events there are, I want to delete the irrelevant ones. Changing the format will also make it easier to add new records, as the input format has changed.


Long-term developing a high-level understanding of databases would be useful, but I don't really have time to do that at the moment so am after a relatively simple and quick method.


If I have not covered everything, please let me know.

WaywardDriver

As previous reply says you need to understand your data first as this will dictate the solution.
If identifier unique across tables and fields (columns) in each table are the same then can simply copy and paste to append from Tables 2-6 into Table1 say. Or use SQL query INSERT INTO Table1 SELECT * FROM Table2 and repeat for Table3 etc.
If identifier unique and fields different then need to create a master table with all fields and modify SQL query above. e.g. INSERT INTO MasterTable (ID, Field1, Field2) SELECT ID, Field1, Field2 FROM Table2
If identifier not unique then need to use a CROSS JOIN.
Latter 2 require some SQL knowledge but I use MS SQL Server and not sure if same syntax as Access.

keithdc

Thanks- the identifier is unique and consistent across all tables, so will see if I can get that to work.


Many thanks