Query with two tables

[Guest]

Hello.
Question from a novice.

I'm trying to create a query with 2 different tables.
One table is from my database, and the other one is a linked table from
another database. Both tables have a common number, Social Security Number,
so I set the relationship to them.
When I created the query, only partial data shows up.
(There are over 800 datas in the tables but only 50 of them show up.)
What am I doing wrong?

 

[Tom Ellison]

Dear Marco:

Although each table may have 800 rows of data, it may be the case that they
only have 50 Social Security Numbers in common. If you have an INNER JOIN
in the query you would see only the 50.

With the other 750 rows in each table, a complete listing would have 1550
rows. Is that the way you see it too?

If you post the SQL view of the query you have and tell me if you want all
1550 rows, I can show you how to do this.

Tom Ellison

 

[Guest]

Hi Tom,

Thank you for your reply.
I've double checked the tables.
For the most part, the tables have the Social Security Numbers in common.
So there are much more than 50.
Any suggestions?

 

[Tom Ellison]

Dear Marco:

Please post the SQL of the query that returns only 50 rows.

It is not unlikely that the numbers do not match except for these 50 rows.
First, what datatypes are these? If either or both are text, do they
contain spaces before or within the numbers? If they contain anything other
than 9 digits then the other portions (spaces, hyphens, etc.) then these
details must match exactly as well.

If it is not this, the only other possibility with which I'm familiar is a
corrupted database. Create a new table and paste your 200 rows in and try
again. That may show where the problem is.

Tom Ellison

 

[Guest]

Thanks Tom,

I found out what the problem was.
My table's Social Security Numbers 's input mask is set up with
000-00-0000;;_ input mask.
The other table's is currently set up with the same input mask, but I'm
assuming it was changed resently. So, the 50 datas that came up are recent
inputs.
For the rest of the data, when they entered the numbers, I think they typed
in the hyphens, which was the difference.
Is there a way to match these two ss#, even with the dashes so I don't have
to go and fix 750 of them?

 

[Tom Ellison]

Dear Marco:

Good work! Use the Replace() function to remove hyphens. Join on the
values after the hyphens are removed. This can be done in the JOIN
statement, or you could run an update query to change the rows.

Do the experimentation on a copy of the database. Don't screw up the only
copy you have with an experimental error!

Tom Ellison

 

[Guest]

Thank you.

I hate to bother you again, but I'm not familiar with the Replace() function.
How is this done?

 

[Tom Ellison]

Dear Marco:

The best advice I can give is that you learn to use the Help system that
comes with Access. There are considerable details there on Replace() and
many other functions.

Here's how you can do this for your column:

Replace([SSN], "-", "")

Replace the hyphens with nothing.

Works whether there is no hyphen or many.

Tom Ellison

 

[Guest]

Tom,

Thanks.
I've been trying to find it in the Help, without success.
I think I need to study the basics more.

Thank you for your time and help!

Dear Marco:

The best advice I can give is that you learn to use the Help system that
comes with Access. There are considerable details there on Replace() and
many other functions.

Here's how you can do this for your column:

Replace([SSN], "-", "")

Replace the hyphens with nothing.

Works whether there is no hyphen or many.

Tom Ellison

Thank you.

I hate to bother you again, but I'm not familiar with the Replace()
function.
How is this done?