Joins (sql) 1

[jmandiaz]

Hi Guys,
can anyone tell me why i can not pull all the data from two tables please see below:

SELECT Bsa.Nameline1, Bsa.Accountnumber, dbo_Accounts.AccountNumber
FROM
Bsa RIGHT JOIN dbo_Accounts ON Bsa.Accountnumber = dbo_Accounts.AccountNumber;


SELECT Bsa.Nameline1, Bsa.Accountnumber, dbo_Accounts.AccountNumber
FROM
Bsa LEFT JOIN dbo_Accounts ON Bsa.Accountnumber = dbo_Accounts.AccountNumber;

i'm querying a sql server and a table in access how ever when i join them by account number i can only pull data from one of the tables. Please advice

-jaime

 

[dhookom]

I expect the data types of the AccountNumber fields aren't the same.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

Hi Duane,
My data types for both fields in each table are set to "text". Do you have any other suggestions?

Thanks in advance

-Jaime

 

[dhookom]

I would find an AccountNumber that you know exists in both tables. Query each table separately on that accountnumber. If AccountNumber in dbo_Accounts is the SQL data type of CHAR, you might have trailing spaces which you would not have in the Access table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

Hi Duane,
I did what you suggested meaning I queried both tables separately on an account number that exists on both tables.

These were my results on bsa table my data type on accountnumber is set to “text” my field size is 16.

The account number in the datasheet view is displayed as 12345678

My sql statement look like:

Select bsa.accountnumber
From bsa
Where
Accountnumber = ‘12345678’

On my dbo.accounts table my data type on accountnumber is also set to “text” my field size is 16

The account number in the datasheet view is displayed as 0000000012345678

When I ran the sql statement below:

Select dbo_accounts.accountnumber
From
Dbo_accounts
Where
Accountnumber = ‘12345678’

It returned no data

When I ran this sql statement

Select dbo_accounts.accountnumber
From
Dbo_accounts
Where
Accountnumber = ‘0000000012345678’

The query return the record.

Do you know why this is happening? On how to correct the issue? Does it matters that I do I table links via the ODBC on some tables and not others?

Thanks in advance

-Jaime

 

[dhookom]

Why is what happening? It seems clear that the data doesn't match. I can't explain why your values in the two tables are different. You can create expressions in queries that would allow the values to match.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

Duane,
"when i ask "why is this happening"? why is it that in dbo_account table my account number has leading zeros where in my bsa table it does not? does it have to do with the way i import the data? can you give me an example of an expression in a query that would all the values to match?

p.s. thanks for the link referencing the sql books very much appreciated.

-Jaime

 

[dhookom]

There was no earlier mention of "import the data" or anything that suggested the values should be the same. It's kinda like saying why isn't my pear and apple. Now, if they had both come from the same tree then I would have a clue that the values should be the same.

To convert one value to match the other, you can match on expressions that use Right() or "00000000" & AccountNumber.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

Duane,
I apologize for not being specific with the question and being vague on it. Let me try to make myself a bit clearer. This is what I’m trying to accomplish.

I have two systems one of the systems is using a mainframe and the other system is using SQL Server 2000. Both systems produce “canned” reports however the systems do not give you to much flexibility on what type of reports you get.

What I want to do is combine two reports into one report on my SQL Server system I can connect via access (odbc) and pretty much “dice and slice” the data I’m after. On the mainframe system; the system has a tool in which I can create some reports and export the reports via a csv file. I then take the csv file and import it into a access table. Once the csv file has been imported I create a primary key to have some kind of relationship to my sql tables.

I hope you understand what I’m trying to do. Again thank you for your help on this.

-Jaime

 

[dhookom]

You may need to add (or remvove) "00000000" to the field following import from one of your systems.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmandiaz]

Duane,
Thank you very much for your help on this. What i ended up doing was exporting the table into excel and then converting the column to a number and re-importing it back into access. I'm sure like you mentioned i can also do this via an expression at this time however i want to see if can pull the data that i'm looking for before spending more time on this.

 

[dhookom]

You can just run an update query in Access to modify the value of a field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]