Taking Data From One DBF file and Matching up to Another DBF

[techgamma]

Basically, I have one DBF with just serial numbers, and text describing each item. I have another database with the Serial Numbers and the Actual Name of item. I would like to somehow merge these so that the report would show The actual name of them item, the serial number, and the description of the item.

Would I use subreporting for this or is there a way to link the two DBFs ???

Thanks ...

 

[kenhamady]

If there is an index on one of the dbfs you can link them together using visual linking. That will make one virtual table out of them both. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[techgamma]

That is the problem, I am not sure how to index one DBF to another, I am used to working on everything within one DBF.

Any hints ?

 

[kenhamady]

You can't create the indexes with Crystal, you would have to do that in the program that creates the dbf. Where did the dbfs come from?

If you can't create the indexes, you can try the ODBC-Dbase option, and select your dbfs from there. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[mmeadmmm]

I'm having a simular issue, I want to compare the 2 data bases and pull out the records that do not match. They are foxpro databases that are both indexed. I tried to do an join but can not. how can you compare 2 dbases and pull out the records that do not match?

 

[IdoMillet]

Use a subreport approach.

You may need to suppress the matched records by having the subreport return a shared variable. Be sure to place the subreport in detail section A and the main report info in detail section B to allow the subreport to process first for each potential match.

Cheers,
- Ido ixm7@psu.edu

 

[kenhamady]

mmeadmmm,

When you joing FoxPro tables using indexes (not via ODBC) then all joins behave as left outer joins. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[pjrlion]

I need more clarification with how to solve an issue similar to the "Use a subreport approach" issue Idomillet addressed.

I have been asked to produce a report, working within a dbase database (goldmine contact mgmt dbase), that shows only records of accounts that have not been called on by way of any activity (email, appt, call etc) for greater than 1 year.

Logically I would split the database into 2 sets of records (contact records whose lastdatecalled is greater than 1 year and records whose records are less than 1 year). And then ask for only records whose account number exists in the record set greater than 1 year but not in the new records record set. That way one does not pull in old account records that in fact are being called on.

I need to do it in crystal and have access to 7.0 sql designer and report designer. Could you explain the steps programmically and in more detail?

 

[IdoMillet]

You should be able to do all of this with a single SQL statement. Please describe the relevant tables and
columns.

Cheers,
- Ido ixm7@psu.edu

 

[pjrlion]

Contact1 tables pertinent fields are companyname, companycontact, contactphone, accountnum. This table links to a Contacthistory table by way of accountnum field. Contacthistory tables pertinent fields are accountnum, SalesRep (known as TsgUser), ContactActivitydate, Contactactivitytype (Call, Email, PhoneCall etc).

I need to know what accounts that no contact (from that specific account)has been called, visited, or emailed by a Salesrep. The query below gives me all old account contact records but does not exclude accounts that have 1 account contact visited, called, or emailed within the year and another contact (the same account) with activity older than 1 year.

This almost-right query is being written in Crystal sql designer:

SELECT DISTINCT
ContactHistory.`ACCNTNUM`,
ContactHistory.`LASTDATE`,
ContactHistory.`RECTYPE`,
ContactHistory.`TSGUSER`,
Contact1.`COMPANYNAME`,
Contact1.`LASTNAME`,
Contact1.`WORKPHONE`,
MONTH (ContactHistory.`LASTDATE`),
YEAR (ContactHistory.`LASTDATE`)
FROM
`ContactHistory` ContactHistory INNER JOIN `ContactHistory` Contact1 ON
ContactHistory.`ACCNTNUM` = Contact1.`ACCNTNUM`
WHERE
ContactHistory.`RecType` IN ( 'Call','Appt','Email' ) AND
ContactHistory.`TsgUSER` IN ( 'LARRY','WILLY','JOE','BOB','MARY','JOHN','HARRY','BRIAN' ) AND
ContactHistory.`ACCNTNUM` IS NOT NULL
GROUP BY ContactHistory.`Accntnum`, ContactHistory.`LastDate`, ContactHistory.`RecType`, ContactHistory.`TsgUser`, ContactHistory.`CompanyName`, ContactHistory.`LastName`, ContactHistory.`WorkPhone`, MONTH(ContactHistory.`Lastdate`), YEAR(ContactHistory.`LastDate`)
HAVING MAX(ContactHistory.`LastDate`) < {ts '2001-01-01 00:00:00.000'}

 

[kenhamady]

Might it be because your GroupBy is for AccountNo so it is looking at the Max of the Account, not the Max of the contact? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[pjrlion]

To try to say the issue another way, I want all accounts that have no (account) contacts called on. The contact records that are greater than 1 year should not be returned if another contact in the same company (account) is being visited-called-or emailed by another sales rep. Conceivably our sales company may have lost communication with a few contacts from a specific account but have been doing business with other contacts from the same account.

Thus, I think some grouping (solution 1 shown earlier)is okay. What is unfortunately being returned are all contact records that are greater than 1 year.

On a small scale I have split sample contact records up into 1 record set that comprised records with dates older than 1 year and record set #2 having records within 1 year and only accepted records in record set #1 (old record set) when the records account numbers were not in any of the records in record set#2 (records generated during 2001)
(See this coding below). A Problem arises when the record set within the IN( )is too large. The in(set operation) doesn't work. Joins and/or groups have to be employed and I haven't found the right join formula. The &quot;solution&quot; below uses the NOT IN( ) logic and only works for me when there are few records within the sub-select.

SELECT
ContactHistory.`ContactID` ,
ContactHistory.`AccntNum`,
ContactHistory.`LastDate` ,
ContactHistory.`RecType`,
ContactHistory.`CompanyName`,
ContactHistory.`LastName`,
ContactHistory.`WorkPhone`,
ContactHistory.`TsgUser`,
ContactHistory.`Notes`
FROM
`ContactHistory` ContactHistory
WHERE
ContactHistory.`LastDate` < {ts '2001-01-01 00:00:00.000'} AND
ContactHistory.`RecType` IN ( 'Call','Appt','Email' ) AND NOT
ContactHistory.`AccntNum` IN
(SELECT DISTINCT
ContactHistory.`AccntNum`
FROM
`ContactHistory` ContactHistory INNER JOIN `ContactHistory` ContactHistory2 ON
ContactHistory.`AccntNum` = ContactHistory2.`AccntNum`
WHERE
ContactHistory.`LastDate` > {ts '2001-01-01 00:00:00.000'} AND
ContactHistory.`RecType` IN ( 'Call','Appt','Email' ) )

 

[kenhamady]

You do realize that the date you are using is not the date of the action, but the date that the record was last modified? This might not correspond with the date of the event and might add to the confusion.

Try using the OnDate from ContHist and see if you get better results. I assume that you are using Goldmine. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[pjrlion]

You are correct that I was working from the Goldmine database at work. This is a consulting job and I will not be back in the job until after the new years. However, I am testing the query at home with access as my test database.
Using Access data I created dummy records.Two records were designated as having the same account number but different contacts and contact dates (a 2000 and a 2001 date). I was hoping that neither record would show in my result query because even though one contact record is old enough to be trapped the other contact provides an account record and date within the year to free it from the result query.

Your suggestion to use ondate at work will be taken but it does not solve the core query issue. Hoe to hear from you soon. Thanks for the attention.
Pete

 

[kenhamady]

I am afraid that I am not able to troubleshoot your SQL statement further. Maybe Ido has some ideas. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com