append query is squaring my records (x^2)

[ishtanbul]

this question originated in the table forums but has turned into a query problem.

basically I am trying to append query 13 fields (out of 48) of 19 records, because I get data sheets (lab results) that look the same every time but with unique data, and I only want to append 13 of those fields to build of a database of samples. That works fine, will post my sql below, but the problem is that rather than appending 19 records, it does 361 (19x19 btw, a perfect square). Why is this happening??

SQL

INSERT INTO [bio_Element Oxides] ( [Sample Name], SiO2, Fe2O3, Al2O3, CaO, MgO, Na2O, K2O, SO3, MnO, BaO, TiO2, P2O5, [Summe Asche Oxide] )
SELECT [temp_1 BIOMASS TEST].Suchfeld, [temp_1 BIOMASS TEST].Field36, [temp_1 BIOMASS TEST].Field37, [temp_1 BIOMASS TEST].Field38, [temp_1 BIOMASS TEST].Field39, [temp_1 BIOMASS TEST].Field40, [temp_1 BIOMASS TEST].Field41, [temp_1 BIOMASS TEST].Field42, [temp_1 BIOMASS TEST].Field43, [temp_1 BIOMASS TEST].Field44, [temp_1 BIOMASS TEST].Field45, [temp_1 BIOMASS TEST].Field46, [temp_1 BIOMASS TEST].Field47, [temp_1 BIOMASS TEST].[Summe AscheOxide]
FROM [temp_1 BIOMASS TEST], [temp_2 BIOMASS TEST mod 1];

Thanks!!

 

[ishtanbul]

well the problem appears to be fixed, but I don't get why... In the design view I had an additional table in the area that shows tables you can select for appening from, but only one was actually selected for all of the fields, the other one was a new dataset. Why would having another table simply sitting there but not selected cause the number of appended records to square?? that makes no sense. I removed the extra table and it worked great but thats a nonsensical solution

 

[MSAccessRookie]

this question originated in the table forums but has turned into a query problem.

basically I am trying to append query 13 fields (out of 48) of 19 records, because I get data sheets (lab results) that look the same every time but with unique data, and I only want to append 13 of those fields to build of a database of samples. That works fine, will post my sql below, but the problem is that rather than appending 19 records, it does 361 (19x19 btw, a perfect square). Why is this happening??

SQL

INSERT INTO [bio_Element Oxides] ( [Sample Name], SiO2, Fe2O3, Al2O3, CaO, MgO, Na2O, K2O, SO3, MnO, BaO, TiO2, P2O5, [Summe Asche Oxide] )
SELECT [temp_1 BIOMASS TEST].Suchfeld, [temp_1 BIOMASS TEST].Field36, [temp_1 BIOMASS TEST].Field37, [temp_1 BIOMASS TEST].Field38, [temp_1 BIOMASS TEST].Field39, [temp_1 BIOMASS TEST].Field40, [temp_1 BIOMASS TEST].Field41, [temp_1 BIOMASS TEST].Field42, [temp_1 BIOMASS TEST].Field43, [temp_1 BIOMASS TEST].Field44, [temp_1 BIOMASS TEST].Field45, [temp_1 BIOMASS TEST].Field46, [temp_1 BIOMASS TEST].Field47, [temp_1 BIOMASS TEST].[Summe AscheOxide]
FROM [temp_1 BIOMASS TEST], [temp_2 BIOMASS TEST mod 1];

Thanks!!

You have either left off part of your Query, or are using an invalid Query Format, depending on your preferences (see below). I believe that the problem with this Query is that it has a Cartesian Join. Since you have not defined what Field the Tables Join on, it will return results for every single Field in Table temp_1 compared to Every single Field in Table temp_2. If there are 19 Fields in each Table, that provides 361 results.

A correct solution to your problem can be found using one of the following two choices:

Standard SQL Method
 
SELECT {Fields to Select}
FROM Table1, Table2
WHERE Table1.JoinKey=Table2.JoinKey

MS Access Method
 
SELECT {Fields to Select}
FROM Table1 INNER JOIN Table2 ON Table1.JoinKey=Table2.JoinKey

In each Method, JoinKey represents a Unique Key in each Table that serves the same purpose, and is used to Limit the number of results.

------------------------------------------------------------------

As an unrelated note, I wanted to point out some additional things for you to consider.

1. Use of Special Characters such as Spaces and Underscores in the names of Tables or Fields should be avoided. In your example, [Sample Name] could be SampleName instead.
2. Use of default Field Names makes it more difficult to maintain the program in the Future. In your Example Field36 could be renamed to represent what it contains.