Select from 2 tables, data from 2nd table missing 
Author Message
 Select from 2 tables, data from 2nd table missing

I'm using the following select statement
  Stg = _
 "SELECT * from Main, Data where Data.Pin =Main.Pin " & _
   "order by Main.Pin"

When I step through the records, the some info from the Data table
is missing.
The 1st record contains the data but the 2nd record is missing the
1st line.

Example:
Main.pin        1 Data.txt1 = "A"
Main.pin        1 Data.txt1 = "B"
Main.pin        1 Data.txt1 = "C"

Main.pin  2 Data.txt1 = "B"
Main.pin        2 Data.txt1 = "C"

The data
 Main.pin       2 Data.txt1 = "A" is missing!

Is there a cure or workaround?

loudelon...



Wed, 18 Dec 2002 03:00:00 GMT  
 Select from 2 tables, data from 2nd table missing
Louis,

yes, you have to use out-joins to copy with this.

In Oracle, this would be coded as:
 SELECT * from Main, Data where Data.Pin =Main.Pin(+) " & _
    "order by Main.Pin"

You then need to check for NULLs in the data.* field of the recordset.

Liam
http://www.vbexpress.com.


Quote:
> I'm using the following select statement
>   Stg = _
>  "SELECT * from Main, Data where Data.Pin =Main.Pin " & _
>    "order by Main.Pin"

> When I step through the records, the some info from the Data table
> is missing.
> The 1st record contains the data but the 2nd record is missing the
> 1st line.

> Example:
> Main.pin 1 Data.txt1 = "A"
> Main.pin 1 Data.txt1 = "B"
> Main.pin 1 Data.txt1 = "C"

> Main.pin  2 Data.txt1 = "B"
> Main.pin 2 Data.txt1 = "C"

> The data
>  Main.pin 2 Data.txt1 = "A" is missing!

> Is there a cure or workaround?

> loudelon...



Thu, 19 Dec 2002 03:00:00 GMT  
 Select from 2 tables, data from 2nd table missing
I'm using the following:
  VB6 and ACCESS 2000.
 Public DbC As New ADODB.Connection
 Public Cmd As New ADODB.Command
 DbC.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
   "Dbq=C:\My Documents\myData.mdb;Uid=Admin;Pwd=;"
  Set Cmd.ActiveConnection = DbC

I tried this but it still leaves out the 1st subrecord:

  S = "SELECT * from Main inner join Data " & _
   "where Data.Pin = Main.Pin order by Main.Pin"

I tried this, the data provider returned and E_FAIL status

  S = "SELECT * from Main left join Data " & _
   "on Main.Pin = Data.Pin order by Main.Pin"



Quote:

>yes, you have to use out-joins to copy with this.

>In Oracle, this would be coded as:
> SELECT * from Main, Data where Data.Pin =Main.Pin(+) " & _
>    "order by Main.Pin"

>You then need to check for NULLs in the data.* field of the recordset.

>Liam
>http://www.vbexpress.com.



>> I'm using the following select statement
>>   Stg = _
>>  "SELECT * from Main, Data where Data.Pin =Main.Pin " & _
>>    "order by Main.Pin"

>> When I step through the records, the some info from the Data table
>> is missing.
>> The 1st record contains the data but the 2nd record is missing the
>> 1st line.

>> Example:
>> Main.pin 1 Data.txt1 = "A"
>> Main.pin 1 Data.txt1 = "B"
>> Main.pin 1 Data.txt1 = "C"

>> Main.pin  2 Data.txt1 = "B"
>> Main.pin 2 Data.txt1 = "C"

>> The data
>>  Main.pin 2 Data.txt1 = "A" is missing!

>> Is there a cure or workaround?

>> loudelon...

loudelon...


Thu, 19 Dec 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Findfirst from 2nd table selected from combobox

2. Copying Data from a Table to a 2nd

3. Using SQL to fill table - missing data

4. Importing select Excel data into Access 97 tables

5. Data Reporting using a multi-table select as datasource

6. selecting data from a table with dbcombo

7. Analyzing table structure w/o selecting data

8. slq guru help needed: how to select data not in another table using sql

9. Extract table data to Access table

10. Update SQL table from data in another table

11. Create Tables using Table Data

12. Update table based on data in another table

 

 
Powered by phpBB® Forum Software