Unable to link two tables in access having identical fields

[Guest]

I want to join two tables A and B. Both have multiple columns. The field that
is exactly the same for two tables is "Phone Number". I want to put all
fields of both tables in one Make Table query, but because the phone numbers
are not idential, i.e. the same phone number could be called twice, so the
query result actually brings up four records for two phone calls. I don't
know how to eliminate the duplicated records. There is another criteria I was
trying to use "Call Time". But the "Call Time" for two tables are not the
same for the same phone call. So for example, "Call Time" in Table A might be
"200404022210" (stands for yy/mm/dd/hh/mm), but in Table B might be a few
minutes late like "200404022215". I'll attach a sample of the duplicated
query results so you know better what I mean.

Please reply me back At
Zhuang, Cindy, WCS [[email protected]]

 

[Guest]

If the value in the field are the same thn just Group by your query, that
should make two identical rows into one

 

[Rick B]

Why do you want to take this data and make a new table? Sounds like you
have a perfectly normal one-to-many relationship. You should be able to use
the tables just as they are and create forms, queries, and reports without
he need to duplicate the data in a new table.

What are you trying to accomplish by making a new table? Perhaps we can
tell you how to do it from a query.

 

[Guest]

Sorry I didn't make it very clear. There is only one column has DUPLICATE
values, but the rest of the columns have different values. So in other words,
I need all rows. Do you have an email address so I can send you a sample of
the query result vs the result that I want?

Thanks

If the value in the field are the same thn just Group by your query, that
should make two identical rows into one

I want to join two tables A and B. Both have multiple columns. The field that
is exactly the same for two tables is "Phone Number". I want to put all
fields of both tables in one Make Table query, but because the phone numbers
are not idential, i.e. the same phone number could be called twice, so the
query result actually brings up four records for two phone calls. I don't
know how to eliminate the duplicated records. There is another criteria I was
trying to use "Call Time". But the "Call Time" for two tables are not the
same for the same phone call. So for example, "Call Time" in Table A might be
"200404022210" (stands for yy/mm/dd/hh/mm), but in Table B might be a few
minutes late like "200404022215". I'll attach a sample of the duplicated
query results so you know better what I mean.

Please reply me back At
Zhuang, Cindy, WCS [[email protected]]

 

[Guest]

Actually I should be able to query fields values and also the value in the
fields are not same and it may vary is there a way out?

If the value in the field are the same thn just Group by your query, that
should make two identical rows into one

I want to join two tables A and B. Both have multiple columns. The field that
is exactly the same for two tables is "Phone Number". I want to put all
fields of both tables in one Make Table query, but because the phone numbers
are not idential, i.e. the same phone number could be called twice, so the
query result actually brings up four records for two phone calls. I don't
know how to eliminate the duplicated records. There is another criteria I was
trying to use "Call Time". But the "Call Time" for two tables are not the
same for the same phone call. So for example, "Call Time" in Table A might be
"200404022210" (stands for yy/mm/dd/hh/mm), but in Table B might be a few
minutes late like "200404022215". I'll attach a sample of the duplicated
query results so you know better what I mean.

Please reply me back At
Zhuang, Cindy, WCS [[email protected]]

 

[Guest]

Rick,

I asked someone else to post the question for me. (I can't post New message,
I can only reply). The subject line is confusing. My apology. It should be
"How to join two tables with similar value"

I have two tables from different companies. One company says at this time,
you made this call for this many minutes (3 minutes), another company says at
APPROXIMATELY the time you said, I made EXACTLY the call you said for that
many minutes (1 minute). There are other columns in the tables that will
shade some light on why there are the minutes difference, so I need to
combine these two tables together using the EXACT phone number, but the
person could call the same phone number twice, so I have DUPLICATE phone
numbers in both tables. What Access does is it matches the two phone numbers
twice, so I got four records for two phone calls. This is not what I want.
The "Call Time" is approximately the same, but not exactly the same, so I'm
not sure if Access can join two tables based on similar values as well.
Idealy, the "Call Time" should match, because I don't want to compare the
call you made at night "200404022210" with the call you made in the morning
"200404020506". Each table should have similar value for the "Call Time"

Thanks!

Cindy

Why do you want to take this data and make a new table? Sounds like you
have a perfectly normal one-to-many relationship. You should be able to use
the tables just as they are and create forms, queries, and reports without
he need to duplicate the data in a new table.

What are you trying to accomplish by making a new table? Perhaps we can
tell you how to do it from a query.

--
Rick B

I want to join two tables A and B. Both have multiple columns. The field that
is exactly the same for two tables is "Phone Number". I want to put all
fields of both tables in one Make Table query, but because the phone numbers
are not idential, i.e. the same phone number could be called twice, so the
query result actually brings up four records for two phone calls. I don't
know how to eliminate the duplicated records. There is another criteria I was
trying to use "Call Time". But the "Call Time" for two tables are not the
same for the same phone call. So for example, "Call Time" in Table A might be
"200404022210" (stands for yy/mm/dd/hh/mm), but in Table B might be a few
minutes late like "200404022215". I'll attach a sample of the duplicated
query results so you know better what I mean.

Please reply me back At
Zhuang, Cindy, WCS [[email protected]]

 

[Guest]

I cant see a way , except joining two fields together the number and the date
mybe format the date to ddmmyyyyhh in both tables and then join the fields
and hope that there were no two calls in the same hour.

sorry I cant help.

Actually I should be able to query fields values and also the value in the
fields are not same and it may vary is there a way out?

If the value in the field are the same thn just Group by your query, that
should make two identical rows into one

I want to join two tables A and B. Both have multiple columns. The field that
is exactly the same for two tables is "Phone Number". I want to put all
fields of both tables in one Make Table query, but because the phone numbers
are not idential, i.e. the same phone number could be called twice, so the
query result actually brings up four records for two phone calls. I don't
know how to eliminate the duplicated records. There is another criteria I was
trying to use "Call Time". But the "Call Time" for two tables are not the
same for the same phone call. So for example, "Call Time" in Table A might be
"200404022210" (stands for yy/mm/dd/hh/mm), but in Table B might be a few
minutes late like "200404022215". I'll attach a sample of the duplicated
query results so you know better what I mean.

Please reply me back At
Zhuang, Cindy, WCS [[email protected]]

 

[John Vinson]

Rick,

I asked someone else to post the question for me. (I can't post New message,
I can only reply). The subject line is confusing. My apology. It should be
"How to join two tables with similar value"

I have two tables from different companies. One company says at this time,
you made this call for this many minutes (3 minutes), another company says at
APPROXIMATELY the time you said, I made EXACTLY the call you said for that
many minutes (1 minute). There are other columns in the tables that will
shade some light on why there are the minutes difference, so I need to
combine these two tables together using the EXACT phone number, but the
person could call the same phone number twice, so I have DUPLICATE phone
numbers in both tables. What Access does is it matches the two phone numbers
twice, so I got four records for two phone calls. This is not what I want.
The "Call Time" is approximately the same, but not exactly the same, so I'm
not sure if Access can join two tables based on similar values as well.
Idealy, the "Call Time" should match, because I don't want to compare the
call you made at night "200404022210" with the call you made in the morning
"200404020506". Each table should have similar value for the "Call Time"

I'd suggest joining the two tables by phone; use an expression in a
vacant Field cell:

TimeDiff: Abs(DateDiff("n", [Table1].[Call Time], [Table2].[Call
Time]))

This will be the absolute (positive, doesn't matter which call came
first) difference in the call times in minutes. If you want to match
calls that fall within ten minutes of each other, say, you could put a
criterion of <10 on this field.

John W. Vinson[MVP]

 

[Guest]

John,

I got "Data type mismatch in criteria expression". Should I type "DateDiff"
as below or is there a column name in my table that I should use? I entered
the below in the vacant cell: TimeDiff: Abs(DateDiff("n",[company A
20040402]![Call Time],[Company B 20040402]![Call Time]))

Thanks,

Rick,

I asked someone else to post the question for me. (I can't post New message,
I can only reply). The subject line is confusing. My apology. It should be
"How to join two tables with similar value"

I have two tables from different companies. One company says at this time,
you made this call for this many minutes (3 minutes), another company says at
APPROXIMATELY the time you said, I made EXACTLY the call you said for that
many minutes (1 minute). There are other columns in the tables that will
shade some light on why there are the minutes difference, so I need to
combine these two tables together using the EXACT phone number, but the
person could call the same phone number twice, so I have DUPLICATE phone
numbers in both tables. What Access does is it matches the two phone numbers
twice, so I got four records for two phone calls. This is not what I want.
The "Call Time" is approximately the same, but not exactly the same, so I'm
not sure if Access can join two tables based on similar values as well.
Idealy, the "Call Time" should match, because I don't want to compare the
call you made at night "200404022210" with the call you made in the morning
"200404020506". Each table should have similar value for the "Call Time"

I'd suggest joining the two tables by phone; use an expression in a
vacant Field cell:

TimeDiff: Abs(DateDiff("n", [Table1].[Call Time], [Table2].[Call
Time]))

This will be the absolute (positive, doesn't matter which call came
first) difference in the call times in minutes. If you want to match
calls that fall within ten minutes of each other, say, you could put a
criterion of <10 on this field.

John W. Vinson[MVP]

 

[John Vinson]

John,

I got "Data type mismatch in criteria expression". Should I type "DateDiff"
as below or is there a column name in my table that I should use? I entered
the below in the vacant cell: TimeDiff: Abs(DateDiff("n",[company A
20040402]![Call Time],[Company B 20040402]![Call Time]))

What did you use as a criterion? Are your two tables *named* Company A
20040402 and Company B 20040402 (eeep!!!! BAD design storing data in
tablenames...)?

Please post the complete SQL view of the query.

John W. Vinson[MVP]

 

[Guest]

John,

Here it is: SELECT DISTINCT [Company A 20040402].*, [Company B 20040402].*,
Abs(DateDiff("n",[Company A 20040402]![Call Time],[Company B 20040402]![Call
Time])) AS TimeDiff
FROM [Company A 20040402] LEFT JOIN [Company B 20040402] ON [Company A
20040402].Phone No= [Teleglobe 20040402].Phone No;

Sorry about the table name and etc.

Thanks!

John,

I got "Data type mismatch in criteria expression". Should I type "DateDiff"
as below or is there a column name in my table that I should use? I entered
the below in the vacant cell: TimeDiff: Abs(DateDiff("n",[company A
20040402]![Call Time],[Company B 20040402]![Call Time]))

What did you use as a criterion? Are your two tables *named* Company A
20040402 and Company B 20040402 (eeep!!!! BAD design storing data in
tablenames...)?

Please post the complete SQL view of the query.

John W. Vinson[MVP]

 

[John Vinson]

John,

Here it is: SELECT DISTINCT [Company A 20040402].*, [Company B 20040402].*,
Abs(DateDiff("n",[Company A 20040402]![Call Time],[Company B 20040402]![Call
Time])) AS TimeDiff
FROM [Company A 20040402] LEFT JOIN [Company B 20040402] ON [Company A
20040402].Phone No= [Teleglobe 20040402].Phone No;

And you're getting an error about *criteria*? That's very very odd:
you have no criteria in this query at all.

Try copying the SQL (with the real company names, not Company A, I'm
guessing) to Notepad; delete the query; compact the database; create a
new query; open it in SQL view and paste the SQL back in. It should be
working, all I can guess is that it has somehow become corrupted or
that you have a forgotten filter or order-by clause.

John W. Vinson[MVP]

 

[Guest]

John,

I changed the "Call Time" from Text format to Number format for both tables,
and I'm able to run the query, but I still got duplicate rows just like
before? Is there any other way? I'm using Access 2000.

Or is there a way to do it in Excel since I only 700 rows? How do I use
vlookup? I only know how to do a simple vlookup. Don't know how to combine
both Exact match and similar match?

Thanks,

Cindy

John,

Here it is: SELECT DISTINCT [Company A 20040402].*, [Company B 20040402].*,
Abs(DateDiff("n",[Company A 20040402]![Call Time],[Company B 20040402]![Call
Time])) AS TimeDiff
FROM [Company A 20040402] LEFT JOIN [Company B 20040402] ON [Company A
20040402].Phone No= [Teleglobe 20040402].Phone No;

And you're getting an error about *criteria*? That's very very odd:
you have no criteria in this query at all.

Try copying the SQL (with the real company names, not Company A, I'm
guessing) to Notepad; delete the query; compact the database; create a
new query; open it in SQL view and paste the SQL back in. It should be
working, all I can guess is that it has somehow become corrupted or
that you have a forgotten filter or order-by clause.

John W. Vinson[MVP]

 

[John Vinson]

John,

I changed the "Call Time" from Text format to Number format for both tables,
and I'm able to run the query, but I still got duplicate rows just like
before? Is there any other way? I'm using Access 2000.

OH!

I was assuming that CallTime was a Date/Time field, and my query was
based on that assumption. It will certainly not work as written with
either Text or Number.

What's actually stored in CallTime? How does it look? Do you have a
good reason not to actually store the call date and time in a
Date/Time field?

John W. Vinson[MVP]

 

[Guest]

John,

It's like "20040501002127". That's "yyyy/mm/dd/hh/mm/ss" for both tables.
And I can't change it to "Date/Time" format.

Cindy

 

[John Vinson]

John,

It's like "20040501002127". That's "yyyy/mm/dd/hh/mm/ss" for both tables.
And I can't change it to "Date/Time" format.

Ok... my apologies. If it's a text string (which you did say in your
first post, I just spaced on it) you won't be able to use DateDiff.
It's also too big a number to convert to Long Integer or even Double
(14 decimals, you'll lose seconds precision).

I guess I'd still suggest converting it to date/time, since neither
string nor numeric comparison will be very easy. Try using

Abs(DateDiff("n", CDate(Format([company A 20040402]![Call Time],
"@@@@/@@/@@ @@@@"))), CDate(Format([Company B 20040402]![Call
Time],"@@@@/@@/@@ @@@@"))))


John W. Vinson[MVP]

 

[Guest]

John,

How do I change "20040501002127" to date/time format? Can I just select
"Date/Time" from the drop down box in Table's design view and in the "field
property/format" line choose "general date" and that's it? or should I also
fill in some other lines in the "field property" section?

I got a blank column after I did it for company A table. And company B table
didn't have any change in the same "Call Time" column.

Thanks,
Cindy

John,

It's like "20040501002127". That's "yyyy/mm/dd/hh/mm/ss" for both tables.
And I can't change it to "Date/Time" format.

Ok... my apologies. If it's a text string (which you did say in your
first post, I just spaced on it) you won't be able to use DateDiff.
It's also too big a number to convert to Long Integer or even Double
(14 decimals, you'll lose seconds precision).

I guess I'd still suggest converting it to date/time, since neither
string nor numeric comparison will be very easy. Try using

Abs(DateDiff("n", CDate(Format([company A 20040402]![Call Time],
"@@@@/@@/@@ @@@@"))), CDate(Format([Company B 20040402]![Call
Time],"@@@@/@@/@@ @@@@"))))


John W. Vinson[MVP]

 

[John Vinson]

How do I change "20040501002127" to date/time format? Can I just select
"Date/Time" from the drop down box in Table's design view and in the "field
property/format" line choose "general date" and that's it? or should I also
fill in some other lines in the "field property" section?

I got a blank column after I did it for company A table. And company B table
didn't have any change in the same "Call Time" column.

You would need a two step process, since this long numeric string
can't be perceived by Access as a date/time. I'd suggest *adding* a
new Date/Time field to the table (the format is pretty much
irrelevant, since all it does is control how the stored Double Float
number gets displayed). You could then run an Update query updating
this new field to

CDate(Format([olddate], "@@@@/@@/@@\ @@@@"))

or some variant on the format.

John W. Vinson[MVP]

 

[Guest]

Hi, John,

I add a new field and updated the field with your update query which is
successful. But after I click "run" in the joint query, it asks about
"TimeDiff" parameter, whatever I put in there "<10", "<2", "10" or "2", it
still gives duplicates like before. Seems the query is not working. Do you
know what I did wrong? Here's the SQL:

SELECT DISTINCT [Company A].*, [Company B].*,
TimeDiff=Abs(DateDiff("n",[Company A]![New Date Time],[Company B]![New Date
Time])) AS Expr1
FROM [Company A] LEFT JOIN [Company B] ON [Company A].Phone No = [Company
B].Phone No;

Thanks,

Cindy

How do I change "20040501002127" to date/time format? Can I just select
"Date/Time" from the drop down box in Table's design view and in the "field
property/format" line choose "general date" and that's it? or should I also
fill in some other lines in the "field property" section?

I got a blank column after I did it for company A table. And company B table
didn't have any change in the same "Call Time" column.

You would need a two step process, since this long numeric string
can't be perceived by Access as a date/time. I'd suggest *adding* a
new Date/Time field to the table (the format is pretty much
irrelevant, since all it does is control how the stored Double Float
number gets displayed). You could then run an Update query updating
this new field to

CDate(Format([olddate], "@@@@/@@/@@\ @@@@"))

or some variant on the format.

John W. Vinson[MVP]

 

[John Vinson]

I add a new field and updated the field with your update query which is
successful. But after I click "run" in the joint query, it asks about
"TimeDiff" parameter, whatever I put in there "<10", "<2", "10" or "2", it
still gives duplicates like before. Seems the query is not working. Do you
know what I did wrong? Here's the SQL:

The syntax of defining an alias is incorrect in your query. Also, use
.. not ! for field delimiters. Try

SELECT DISTINCT [Company A].*, [Company B].*,
Abs(DateDiff("n",[Company A].[New Date Time],[Company B].[New Date
Time])) AS TimeDiff
FROM [Company A] LEFT JOIN [Company B] ON [Company A].Phone No =
[Company B].Phone No;


John W. Vinson[MVP]