By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Access 2003 Create report from two data sets

P: 6
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.
Apr 30 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.AccountID, t1.ClientName, t2.Field1, t2.Field2
  2. FROM Table1 As t1 LEFT JOIN Table2 As t2
  3. ON t1.AccountID = t2.AccountID
  4.  
Mary
Apr 30 '07 #2

P: 6
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?
Apr 30 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. Field; Type; IndexInfo
  3. StudentID; Autonumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Apr 30 '07 #4

P: 6
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
Apr 30 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try running this query
Expand|Select|Wrap|Line Numbers
  1. SELECT [MASTER EOG].ACCOUNTID, [MASTER EOG].PUBCODE, [MASTER EOG].NAME, 
  2. [MASTER EOG].PIATYPE, [MASTER EOG].FOD, [MASTER EOG].SUBSLNGTH, 
  3. [MASTER EOG].RATECODE, [MASTER EOG].CURRENTBAL, [MASTER EOG].EOGDATE, 
  4. [MASTER EOG].TELEPHONE, [MASTER EOG].ASSIGNDATE, [CLC REPORTS].AccountID, 
  5. [CLC REPORTS].Name, [CLC REPORTS].PubCode, [CLC REPORTS].Telephone, 
  6. [CLC REPORTS].RateCode, [CLC REPORTS].EOGDate, [CLC REPORTS].CallType, 
  7. [CLC REPORTS].Result1, [CLC REPORTS].Result2, [CLC REPORTS].Comment, 
  8. [CLC REPORTS].CCType, [CLC REPORTS].CCNbr, [CLC REPORTS].CCName, 
  9. [CLC REPORTS].CCExpire, [CLC REPORTS].CCAmount, [CLC REPORTS].BankName, 
  10. [CLC REPORTS].BankNumber, [CLC REPORTS].AccountNumber, [CLC REPORTS].CheckNumber, [CLC REPORTS].BankAmount, [CLC REPORTS].REPORTDATE
  11. FROM [MASTER EOG] LEFT JOIN [CLC REPORTS]
  12. ON [MASTER EOG].ACCOUNTID=[CLC REPORTS].ACCOUNTID;
  13.  
Mary
Apr 30 '07 #6

P: 6
It gives me the error: "Type mismatch in expression."
Apr 30 '07 #7

P: 6
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.
Apr 30 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Apr 30 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.