Microsoft Access Relationship/Query Question

15 pts.
Tags:
Access 2007
I have a database with around 3,000,000 records organized by account number, and another database with 100,000 or so organized by the same account number. I import both these databases into Access, and link up the account numbers… I want to pull data from both databases in order to create a new table.

The problem is, there are some files that are in the table of 100,000 that are not in the table of 3,000,000 and when I create the new table, it simply omits those items. Is there a setting somewhere that will make it list every file from the list of 100,000, and if the data is not present in the list of 3,000,000 it will say “NA” or “Null” or something? I appreciate any advice anyone can provide! Thanks!



Software/Hardware used:
Microsoft Access

Answer Wiki

Thanks. We'll let you know when a new response is added.

Are you creating your new table this way ?
<pre>
SELECT …
INTO [newTable]
FROM [table_of_100000] t1 JOIN [table_of_3000000] t2
ON t1.account_no = t2.account_no</pre>

If so, you need to use an <a href=”http://msdn.microsoft.com/en-us/library/bb208894.aspx”>Outer Join</a>.

Something like this:

<pre>SELECT …
INTO [newTable]
FROM [table_of_100000] t1 <b>LEFT </b>JOIN [table_of_3000000] t2
ON t1.account_no = t2.account_no</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following