How to join tables with no matching, unique fields (1 Viewer)

[KirRoyale]

I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.
I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.
My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).
My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.
I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what I’m looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
Could somebody please advise how I can solve this?

 

[recyan]

Till someone comes along, do not know if this is what you need, all the same :

Table1 - This is the main table containing all the Phone Numbers
TheID - Autonumber
PhoneNumber - Text

Table2
PhoneDetailsID - AutoNumber
PhoneNumber - Text
Details - Text

A Make-Table query

SELECT 
Table1.PhoneNumber, Table2.PhoneNumber, Table1.TheID, Table2.Details 
INTO 
Table2_New
FROM 
Table1 LEFT JOIN Table2 
ON 
Table1.PhoneNumber = Table2.PhoneNumber;

Thanks

 

[KirRoyale]

Recyan, That works a treat. Thank you!

 

[recyan]

Glad you got things working.
In case you have not already done so, just check the Data Type of TheID in the new table Table2_New. If it is AutoNumber, you should be able to change it to Number, if required.

Thanks

 

[KirRoyale]

Thank you for pointing that out. I have just changed the data type from Autonumber to Number.
By the way, although the query worked, I am not able to open it in Design view. I get the message:
“Microsoft Access can’t represent the join expression….in design view”
Would you have any idea why I get this message? I have posted the query below:
SELECT [PhoneNumbers].[電話番号（MSN）], [Call0CumulativeTbl].[電話番号（ＭＳＮ）], [PhoneNumbers].[ID], [Call0CumulativeTbl].[通話開始日], [Call0CumulativeTbl].[通話開始時刻], [Call0CumulativeTbl].[通話時間], [Call0CumulativeTbl].[相手先電話番号], [Call0CumulativeTbl].[付加使用種別], [Call0CumulativeTbl].[ローミング], [Call0CumulativeTbl].[通話料金], [Call0CumulativeTbl].[請求年月] INTO Call0YTDTbl
FROM PhoneNumbers LEFT JOIN Call0CumulativeTbl ON PhoneNumbers.電話番号（MSN） = Call0CumulativeTbl.電話番号（MSN）;

 

[recyan]

By the way, although the query worked, I am not able to open it in Design view. I get the message:
“Microsoft Access can’t represent the join expression….in design view”
Would you have any idea why I get this message? I have posted the query below:
SELECT [PhoneNumbers].[電話番号（MSN）], [Call0CumulativeTbl].[電話番号（ＭＳＮ）], [PhoneNumbers].[ID], [Call0CumulativeTbl].[通話開始日], [Call0CumulativeTbl].[通話開始時刻], [Call0CumulativeTbl].[通話時間], [Call0CumulativeTbl].[相手先電話番号], [Call0CumulativeTbl].[付加使用種別], [Call0CumulativeTbl].[ローミング], [Call0CumulativeTbl].[通話料金], [Call0CumulativeTbl].[請求年月] INTO Call0YTDTbl
FROM PhoneNumbers LEFT JOIN Call0CumulativeTbl ON PhoneNumbers.電話番号（MSN） = Call0CumulativeTbl.電話番号（MSN）;

Do not know why that is happening ? Ideally, you should be able to see it in Design mode.
Am unable to understand your field names, as they are not appearing in English.
Do you have a ( & ) in your field names ?
eg :
PhoneNumbers.電話番号（MSN）
Call0CumulativeTbl.電話番号（MSN）
If yes, think you should avoid them.
All the same till some one comes along, just try

ON PhoneNumbers.[電話番号（MSN)] = Call0CumulativeTbl.[電話番号（MSN)]

in SQL mode & then try opening in Design Mode.

Thanks

 

[KirRoyale]

There are no '&' signs in the [電話番号（MSN）] field but there is a space.
I was able to get the query to open in design view by renaming the [電話番号（MSN）] field ‘PhoneNumber’ on the ‘Call0CumulativeTbl’ table - and without changing the name in the ‘PhoneNumbers’ table! Also, I don’t appear to need the square brackets ‘[]’.
So, problem solved! Thank you.
SELECT PhoneNumbers.電話番号（MSN）, Call0CumulativeTbl.PhoneNumber, PhoneNumbers.ID, Call0CumulativeTbl.通話開始日, Call0CumulativeTbl.通話開始時刻, Call0CumulativeTbl.通話時間, Call0CumulativeTbl.相手先電話番号, Call0CumulativeTbl.付加使用種別, Call0CumulativeTbl.ローミング, Call0CumulativeTbl.通話料金, Call0CumulativeTbl.請求年月 INTO Call0YTDTbl
FROM PhoneNumbers LEFT JOIN Call0CumulativeTbl ON PhoneNumbers.電話番号（MSN） = Call0CumulativeTbl.PhoneNumber;

 

[Pat Hartman]

I don't see how generating an autonumber is helping. You STILL need to join on phone number to populate the foreign keys in the related tables.

Why didn't joining on phone number work for you.

 

[KirRoyale]

Thank you for the post.
Because the phone number (電話番号) field is not numeric and appears multiple times in the transaction files, Access just hung when I tried to create queries that joined on it.
My idea was to have the ID field in every table, each ID number relating to a particular phone number). I thought this would enable me to join the tables and create queries.
I have now joined all tables (the ‘PhoneNumbers’ table with just 1 row for each phone number and the ID – and all the transaction tables ‘Call0YTDTbl’ to ‘Call8YTDTbl’, now with the foreign key (ID) from the phone numbers table) and attempted to run a select query to show the total monetary amounts (料金) by phone number from each transaction file.
This has not worked as I would have liked. Even including only 2 of the transaction tables (albeit large ones, almost 10,000 and 9,000 records respectively) results in a message that the 2gb limit has been reached and the query cannot be completed.
I think I must be doing something fundamentally wrong and would appreciate further guidance. The current query is posted below:
SELECT PhoneNumbers.ID, PhoneNumbers.電話番号, Sum(Call0YTDTbl.通話料金) AS Call0, Sum(Call1YTDTbl.料金) AS Call1
FROM (((((((PhoneNumbers LEFT JOIN Call0YTDTbl ON PhoneNumbers.ID = Call0YTDTbl.ID) LEFT JOIN Call1YTDTbl ON PhoneNumbers.ID = Call1YTDTbl.ID) LEFT JOIN Call2YTDTbl ON PhoneNumbers.ID = Call2YTDTbl.ID) LEFT JOIN Call3YTDTbl ON PhoneNumbers.ID = Call3YTDTbl.ID) LEFT JOIN Call5YTDTbl ON PhoneNumbers.ID = Call5YTDTbl.ID) LEFT JOIN Call6YTDTbl ON PhoneNumbers.ID = Call6YTDTbl.ID) LEFT JOIN Call7YTDTbl ON PhoneNumbers.ID = Call7YTDTbl.ID) LEFT JOIN Call8YTDTbl ON PhoneNumbers.ID = Call8YTDTbl.ID
GROUP BY PhoneNumbers.ID, PhoneNumbers.電話番号;