Linking Problem

[Cort]

Hello all, here is my problem and I'm sure there is an easy answer so I'll try not to ask the same question twice.

I have 2 fields I want to link labeled Zip code. The problem I have is that one zip field is 5 digit and another is 5+4. The first field is used to identify the county so 5+4 is not necessary or practical. How can I link these two fields to get the county information I need.

thanks.

 

[dgillz]

These fields are different, so you cannot link them, or you may struggle with inconsistent results, in my opinion.

In the table with 5+4, write a formula to take the first 5 characters:

left({Zip+4},5)

Then use this formual field in a linked subreport to pull out the county information. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Cort]

Thank you for your response. I'm unfortunatly unable to modify the database in any way so I cannot create a new field in the table to display only the first 5 of the zip.

I created an access query to display the zip+4 field with only 5 digits and it works sometimes by linking in that query but the results are quite erratic.

So I'm supposing there is no link type where you can only compare the first 5 letters in the linked fields to determine a match?

Thank You for any help you may be able to provide.

 

[dgillz]

I am not talking about modifying the database, I am talking about making a formula field within crystal reports, then using that formula field to a linked subreport.

If you need to know how to do either of those, let me know. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Cort]

OK yeah I'm going to need some help here. I've used subreports many times and Linked ones also but I'm clearly doing something wrong here.

1. I create a report and create a formuls using Left to return the 5 digit zip code. Save report

2. Use Insert Subreport, name it and start the wizard. I select the tables I need and Correct links. I also add the 5 Digit Zip code table at this time and leave it unlinked as there is no common field.

3. On the link tab for the linked subreport I select the formula, add it and in the drop down menu where you select the field to link the formula to I can only select the actual zip table. I cannot select the Zip field within that table. Only the table is listed the individual fields are not listed. I think it has something to do with the table being unlinked to begin with.

4. I go have a beer in frustration.

Again thank you for your help.

 

[dgillz]

Write one crystal reports, from the table with the 5 digit zip code. Do not add any tables to this report, linked or unlinked.

Write another, separate, crystal report from the table with the 5+4 zip code. make one of the fields in this report a formula field of Left({Zip+4},5). This field will be used to link the 2 reports later. Do not add any extra tables to this report.

Insert the first report into the second one, linking on the formula field from the second report to the 5 digit zip field from the first report.

You should now have all the data you want in one report, it just needs to be subtotaled and grandtotaled however you want it to be.

Remember to use shared variables when trying to add numbers from both the main report and subreport.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com