Access 2003 Create report from two data sets

[sesmap]

I have a table which is a list of clients that we are sending to another company to contact for updating their account. I have a second table that is the other company's report back to me telling which of those clients they reached, what the outcome of that contact is, whether additional product was sold, etc. I would like to create a report which merges the records so I can see how many leads I outsources, how many contacts and sales were made and any other information they send me such as whether the client has a complaint that needs to be referred to my CSRs. I also want to know how many leads I gave on a certain date. Can anyone help? This is a project that was due last week but all my study and attempts have led me only to frustration. There are common pieces of information in both tables ie clients names, accountID ( I set this as the primary key in both tables), sales group and phone numbers. Each of the tables has a few fields not in the other table.

[MMcCarthy]

You need to get the query right. Consider your first table as the master table and link the two tables based on AccountID as follows. You can include fields from both tables.

SELECT t1.AccountID, t1.ClientName, t2.Field1, t2.Field2
FROM Table1 As t1 LEFT JOIN Table2 As t2
ON t1.AccountID = t2.AccountID
 

Mary

[sesmap]

Thanks for your help Mary but I am still stuck. I guess I didn't give you enough information. Each of these reports comes to me daily. They are Excel documents. I have linked each one into access but I am unable to pull all of each set of reports into one table. Do I have to append each one into a single table for each group?

[MMcCarthy]

Forget the report for the moment. You have to look at this as a query first. If the query is right the report will be easy. If you provide the metadata of the two tables (It might be a good idea to import rather than link to excel) and details of exactly what you want in the output I will try to help further.

Here is an example of how to post table MetaData :
Table Name=tblBookings

Field; Type; IndexInfo
 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[sesmap]

Here is a query that has just the Master Table

SELECT [MASTER EOG].ACCOUNTID, [MASTER EOG].PUBCODE, [MASTER EOG].NAME, [MASTER EOG].PIATYPE, [MASTER EOG].FOD, [MASTER EOG].SUBSLNGTH, [MASTER EOG].RATECODE, [MASTER EOG].CURRENTBAL, [MASTER EOG].EOGDATE, [MASTER EOG].TELEPHONE, [MASTER EOG].ASSIGNDATE
FROM [MASTER EOG];

Here is the query that has the other table - CLC Reports

SELECT [CLC REPORTS].AccountID, [CLC REPORTS].Name, [CLC REPORTS].PubCode, [CLC REPORTS].Telephone, [CLC REPORTS].RateCode, [CLC REPORTS].EOGDate, [CLC REPORTS].CallType, [CLC REPORTS].Result1, [CLC REPORTS].Result2, [CLC REPORTS].Comment, [CLC REPORTS].CCType, [CLC REPORTS].CCNbr, [CLC REPORTS].CCName, [CLC REPORTS].CCExpire, [CLC REPORTS].CCAmount, [CLC REPORTS].BankName, [CLC REPORTS].BankNumber, [CLC REPORTS].AccountNumber, [CLC REPORTS].CheckNumber, [CLC REPORTS].BankAmount, [CLC REPORTS].REPORTDATE
FROM [CLC REPORTS];

I appended all the excel reports into two separate tables Master EOG and CLC Reports. Then, just to make sure they would work, I pulled each table into a query. I am not certain how I put the code into a new query. This is all very new to me and I appreciate your patience. Pat

[MMcCarthy]

Try running this query

SELECT [MASTER EOG].ACCOUNTID, [MASTER EOG].PUBCODE, [MASTER EOG].NAME, 
[MASTER EOG].PIATYPE, [MASTER EOG].FOD, [MASTER EOG].SUBSLNGTH, 
[MASTER EOG].RATECODE, [MASTER EOG].CURRENTBAL, [MASTER EOG].EOGDATE, 
[MASTER EOG].TELEPHONE, [MASTER EOG].ASSIGNDATE, [CLC REPORTS].AccountID, 
[CLC REPORTS].Name, [CLC REPORTS].PubCode, [CLC REPORTS].Telephone, 
[CLC REPORTS].RateCode, [CLC REPORTS].EOGDate, [CLC REPORTS].CallType, 
[CLC REPORTS].Result1, [CLC REPORTS].Result2, [CLC REPORTS].Comment, 
[CLC REPORTS].CCType, [CLC REPORTS].CCNbr, [CLC REPORTS].CCName, 
[CLC REPORTS].CCExpire, [CLC REPORTS].CCAmount, [CLC REPORTS].BankName, 
[CLC REPORTS].BankNumber, [CLC REPORTS].AccountNumber, [CLC REPORTS].CheckNumber, [CLC REPORTS].BankAmount, [CLC REPORTS].REPORTDATE
FROM [MASTER EOG] LEFT JOIN [CLC REPORTS]
ON [MASTER EOG].ACCOUNTID=[CLC REPORTS].ACCOUNTID;
 

Mary

[sesmap]

It gives me the error: "Type mismatch in expression."

[sesmap]

I figured out the error, one Account ID was text, the other number. I changed them both to text and it worked! Thank you, thank you, thank you.

[MMcCarthy]

I figured out the error, one Account ID was text, the other number. I changed them both to text and it worked! Thank you, thank you, thank you.

You're welcome.