So my query in Microsoft Access 2007 is taking data from three different tables: a, b, c. In these tables are double values all named as "Time" that were run to record how long an event occurred.
Here are the values I currently have for each table:
a.Time: 7.776, 117.7144, 3.0921, 6.8509
b.Time: 11.0392, 11.8620, 111.1510, 14.2148, 14.2899, 7.8790
c.Time: 2.3672, 115.9478, 117.3253
What I'm attempting to do is to put all these doubles into one column called 'Time'. Here's what I'm hoping to get (having them sorted is a plus):
Time
------------
7.776
117.7144
3.0921
6.8509
11.0392
11.8620
111.1510
14.2148
14.2899
7.8790
2.3672
115.9478
117.3253
What I've got so far is putting each of doubles into their own column, hoping that it would at least show a maximum of 6 rows displaying all the times from their respective tables. However, this is what I ended up with:
a Time b Time c Time
---------------------------
7.776 11.0392 2.3672
7.776 11.8620 2.3672
7.776 111.1510 2.3672
7.776 14.2148 2.3672
7.776 14.2899 2.3672
7.776 7.8790 2.3672
117.7144 11.0392 2.3672
117.7144 11.8620 2.3672
117.7144 111.1510 2.3672
117.7144 14.2148 2.3672
117.7144 14.2899 2.3672
117.7144 7.8790 2.3672
3.0921 11.0392 2.3672
3.0921 11.8620 2.3672
3.0921 111.1510 2.3672
3.0921 14.2148 2.3672
3.0921 14.2899 2.3672
3.0921 7.8790 2.3672
6.8509 11.0392 2.3672
6.8509 11.8620 2.3672
6.8509 111.1510 2.3672
6.8509 14.2148 2.3672
6.8509 14.2899 2.3672
6.8509 7.8790 2.3672
7.776 11.0392 115.9478
7.776 11.8620 115.9478
7.776 111.1510 115.9478
7.776 14.2148 115.9478
7.776 14.2899 115.9478
7.776 7.8790 115.9478
117.7144 11.0392 115.9478
117.7144 11.8620 115.9478
117.7144 111.1510 115.9478
117.7144 14.2148 115.9478
117.7144 14.2899 115.9478
117.7144 7.8790 115.9478
3.0921 11.0392 115.9478
3.0921 11.8620 115.9478
3.0921 111.1510 115.9478
3.0921 14.2148 115.9478
3.0921 14.2899 115.9478
3.0921 7.8790 115.9478
6.8509 11.0392 115.9478
6.8509 11.8620 115.9478
6.8509 111.1510 115.9478
6.8509 14.2148 115.9478
6.8509 14.2899 115.9478
6.8509 7.8790 115.9478
7.776 11.0392 117.3253
7.776 11.8620 117.3253
7.776 111.1510 117.3253
7.776 14.2148 117.3253
7.776 14.2899 117.3253
7.776 7.8790 117.3253
117.7144 11.0392 117.3253
117.7144 11.8620 117.3253
117.7144 111.1510 117.3253
117.7144 14.2148 117.3253
117.7144 14.2899 117.3253
117.7144 7.8790 117.3253
3.0921 11.0392 117.3253
3.0921 11.8620 117.3253
3.0921 111.1510 117.3253
3.0921 14.2148 117.3253
3.0921 14.2899 117.3253
3.0921 7.8790 117.3253
6.8509 11.0392 117.3253
6.8509 11.8620 117.3253
6.8509 111.1510 117.3253
6.8509 14.2148 117.3253
6.8509 14.2899 117.3253
6.8509 7.8790 117.3253
As you can see, I get 72 rows (4 x 6 x 3) of the combinations of all the times in each row. Anybody got any ideas on how to fix this?
EDIT: Based on an earlier comment, I'm posting the entire code that I have here.
SELECT N.[Precursor m/z], N.[a Time], N.[b Time], c.[Time] AS [c Time], N.
[Glycan ID], N.[Glycan Sequence]
FROM (
SELECT a.[Precursor m/z], a.[Time] AS [a Time], b.[Time] AS [b Time],
a.[Glycan ID], a.[Glycan Sequence] FROM a
INNER JOIN b ON a.[Precursor m/z]=b.[Precursor m/z]) AS N
INNER JOIN c ON N.[Precursor m/z]=c.[Precursor m/z];
Best Answer
If there are duplicate values do you want two(+) results or just one?
If you want ALL results:
If you want just UNIQUE results: