Data Being Split In Access

Question

Another Access question. I have a situation where data is imported for individuals that is being split into two different sections. These individuals have the same ID but the information is not being grouped together. This is due to some of the information coming directly from a site known as ASQ and the rest of it is coming from ASQ via other sites. Is there a way to combine the information, since it comes from different areas? Or does it need to remain separate due to the different data sources?

Answer 1

The database relational model is based on the premise that each entity type in the real world is modelled by a single table, whose columns represent attributes of the entity type which are determined solely by the table's key.  Consequently you'd start with a table which models the persons entity type, and its columns would represent attributes like FirstName, LastName, DateOfBirth etc.  Other entity types would be tests types, classrooms and programmes.  The many-to-many relationship type between them would be the test results per person per classroom, and would be modelled by another table, something like this:

TestResults

….PersonID

….ClassroomID

….ProgrammeID

….TestTypeID

….TestDate

….Result

However, there might be other relationship types between this and the four referenced tables.  For instance, if a particular test type relates to only one programme, then ProgammeID is redundant in the above table and can be removed.  The TestTypes table would include a ProgrammeID foreign key column, so once the test type is known, the programme is also known, i.e. it is functionally determined by TestTypeID.

So, when importing your data from the two sources you can probably do so initially into two separate tables.  It might be possible to decompose the two tables independently, or you might need to first join them in a query, and then decompose the result table of the query into the set of normalized tables.

For an illustration of how a non-normalized table is decomposed take a look at DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file imports data from Excel and then decomposes it into a set of normalized tables by means of a series of 'append' queries which are executed in a specific order.

Before looking at the demo, however, I'd recommend that you look at two other files in the same OneDrive folder.  Firstly, DatabaseBasics.zip is a general introduction to developing relational databases in Access, and includes descriptions, with examples, of how the various relationship types are implemented.  Secondly, Relationships.zip takes this a little further and illustrates how relationships are built up across a database to achieve the final model.

Answer 2

Hi Ken,

Again, thanks for your reply to this. To the best of my knowledge, they are not determined solely by the key. I'm dealing with data on literacy and it seems to be grouped by test types that come from different classrooms in different programs. Thus, they appear to be separated out by the program more than the ID. So what I gather from your response is that they can go into related tables, but can never be joined together? So I would have to have two separate entries in the database for the individuals, regardless of having the same ID. Is that correct? Once again, I'm new to all of this (spent most of my life in finance and got thrown into this) and am just trying to get it all straight in my head. Thank you for taking the time to answer.

Answer 3

If the data being imported represent legitimate attributes of the same entity type (persons in your case by the sound of it), each of which is functionally determined solely by the key (ID in your case), then they should be inserted into rows in a single table.  The data from the two sources would each be a subset of the columns in the table, with the common ID value as the primary key.

If, on the other hand, each attribute (datum) is not determined solely by the key, then the imported data would need to be decomposed into a set of correctly normalized related tables.  Which tables the data are inserted into would depend not on the source of the data, but on what attributes of what entity type each datum represents.  Consequently some data from both sources might be inserted into a single table, or into separate related tables.  Without knowing the nature of the data being imported more than that cannot be said.