1

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];
  • 2
    You're probably looking for a UNION, not JOIN. – mustaccio Aug 14 '17 at 20:07
  • Welcome to the forum! Mustaccio's comment is the key to solving this, but since you're new to posting here, a hint for future posts: You took care to post an extended example of data (which can be very good) but then abbreviated the SQL query. However, code can often reveal much not described in words and is often the source of the problem and any potential answer, so go ahead and just post the full code. Definitely format it to make it easier to read (as you have done) but things like <condition> can actually make it harder to interpret even though your intent is to simplify. – C Perkins Aug 14 '17 at 22:22
  • Unless you sufficiently limit a query by specifying unique join conditions, Access will always return a Cartesian product of the joined data rows. With no JOIN keywords and no WHERE conditions, this is also called a CROSS JOIN. A "pure" cross join in Access SQL is specified with no join keywords, just a list of table names separated by commas. Although you did indeed specify a join with conditions, it's apparent that <condition> was not on unique key values, so Access still returned all possible combinations of rows where your <condition> applied. – C Perkins Aug 14 '17 at 22:32
  • Thanks for all the comments so far. I've tried using UNION before and I couldn't figure out where to place it. I used the INNER JOIN statement so that I could make sure that my query would only fetch rows that were equal in 'Precursor m/z' values. – Blair Aug 15 '17 at 15:45
2

If there are duplicate values do you want two(+) results or just one?

If you want ALL results:

SELECT Time FROM a
UNION ALL
SELECT Time FROM b
UNION ALL
SELECT Time FROM c

If you want just UNIQUE results:

SELECT Time FROM a
UNION
SELECT Time FROM b
UNION
SELECT Time FROM c

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.