Relationships and Building new Tables

[jduran2]

I have been working with a software that delivers data using ".mdb" files or Access as seen below. These are large files with several primary keys that build relationships between the tables. I am not very familiar with Access, but these seem to be how the data is built together.



My current goal is to filter the data in order to have a single table with adjusted columns from different tables. I have played around with the queries and been able to filter out information from individual columns, but is there a way to take columns and place them into a new table?

For Example: Looking at my image, the first primary is analysis set where a primary key "AnalysisIndex" is located. This is a value that I am interested in having on my new table. However, Analysis set has a large set of "sub data". When I add this column to the new table, I want it to update its size so that it is not just 1,2,3,4,5,6,etc. but rather 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,etc. with the sub data in neighboring columns.

Thanks

[CJ_London]

My current goal is to filter the data in order to have a single table with adjusted columns from different tables

sounds like you are wanting an excel/spreadsheet view of the data which is not how databases are intended to work. They work using queries, just selecting whatever data is required for the purpose in hand

For Example: Looking at my image, the first primary is analysis set where a primary key "AnalysisIndex" is located. This is a value that I am interested in having on my new table. However, Analysis set has a large set of "sub data". When I add this column to the new table, I want it to update its size so that it is not just 1,2,3,4,5,6,etc. but rather 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,etc. with the sub data in neighboring columns.

which means what? Perhaps provide some example data from all of the relevant tables and the expected outcome. Don't try to simplify it, just explain what it is you are trying to do, with examples

[jduran2]

Okay, I will give an specific example that I would like to repeat across all data.

My first relevant data sets come from the AnalysisSet table. Let's say that we are interested in Analysis Index 100.



I would next go to the AnalyteSet Table and find the corresponding Analysis Index of 100 which then has related fields of Analyte Index and Analyte Name that I am interested in keeping as well.



This is where all of my "filtering" will take place. I have played around with this and managed to get it to work through using the Queries like you mentioned. I specifically used the criteria for Analyte Name and listed the strings that I am interested in. However, the other data that I will get to in a moment is not present when simply filtering through one column which is why I would like all of the associated columns to be side by side so that all columns are adjusted for the desired criteria.

Next from the obtained Analyte Set, I need to open the Sample Set table and again find the appropriate Analysis Index value (100 in this example). Note in the image below that the Sample Index numbers share the Analysis Index number 100 for Sample Index (1658-1708)



Now, with these values, I can go to the Analyte Data table and gather all data for matching sample index numbers (1658-1708). This table also has the Analyte Index values for each Sample Index along with the values that I will ultimately be interested in (Mean Response and RSD).



Now That I have the location of all data, I would like to sort of combine so that the new table or query has specific fields from each table.
Fields:
AnalysisIndex
AnalyteIndex (For each AnalysisIndex)
AnalyteName (For each AnalysisIndex)
SampleIndex (For each AnalysisIndex)
Label (For each AnalysisIndex)
MeanResponse (for each AnalyteIndex and each SampleIndex)
RSD (for each AnalyteIndex and each SampleIndex)

Then, I with this setup and hierarchical organization, I would setup a criteria to filter on.

This is at least the goal and vision that I have for the final output.

[CJ_London]

OK, so what is the problem with using a query based on just the tables you have mentioned?

You've explained your data which is helpful, thank you.

1. join AnalysisSet to AnalyteSet on Analysisindex as indicated in your relationships
2. join AnalysisSet to Sampleset on Analysisindex as indicated in your relationships
3. join Sampleset to AnalyteData on SampleIndex as indicated in your relationships
4. if relevant, you can also create a join between AnalyteData and AnalyteSet on AnalyteIndex

Then in the query grid, drag down the fields you want to appear and/or apply a criteria

Don't confuse relationships with joins, they may be represented the same way (like a table or query looks the same). Query joins are not restricted to what has been mapped as relationships

Edit: (number added to joins for ease of identification)
One other thought is you don't necessarily joins 2 and 3 since you don't appear to need any data from the Sampleset table, just joins 1 and 4

[jduran2]

There is no issue using the query as you suggest. I was having issues because I was missing join 4. My solution was as you suggested; however, what are the differences in the different types of joins? I went with option 1 when creating the relationship and it simply made a direct connection as seen in the first image below. My final query ( as seen in the second image) appears to be correct, but I will dig through the data a bit more to confirm.

image 1



image 2

[CJ_London]

however, what are the differences in the different types of joins?

As I said before, don't confuse joins with relationships - per your query these are all 'normal' inner joins. The 1 and infinity signs just means there is a one to many relationship involved but does not affect the join. If you had outer joins (i.e. include all records from tableX and those records from tableY where there is equality) then you would see a arrow head at one end of the join line