Crystal Reports Linking zip code tables

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Linking zip code tables

Page of 2 Next >>

Author	Message
db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	Topic: Linking zip code tables Posted: 15 May 2012 at 10:05am
	I'm curious if anyone has an idea on how to link two different tables within Crystal by zip code? The problem is that one table contains zip codes that may contain 5 character zips along with the -**** but not always. The other table only contains 5 characters for all zips. Thanks.
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 15 May 2012 at 10:10am
	you can use a command object with a case statement or left(field,5) on the longer zipcode field to shorten it. Edited by DBlank - 15 May 2012 at 10:17am
	IP Logged

kostya1122 Senior Member Joined: 13 Jun 2011 Online Status: Offline Posts: 475	Posted: 15 May 2012 at 10:15am
	you could also try to link them in select expert, but the performance might suffer table(2).field = left(table(1).field,5)
	IP Logged

db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	Posted: 15 May 2012 at 10:42am
	I'm a newby on this stuff. If you could elaborate, I'd really appreciate it.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 15 May 2012 at 10:50am
	when creating a report the data source option there is an Add command fucntion. for an existing report it is in the Database, Database Expert (Data tab) screen. double clcik on add command and you can write a sql query to be your data source. In the query you can join the two tables together using either a case statement or a left(field) for example: select table1.fielda, table1.fieldb, table2.fieldy from table1 inner join table2 on table1.zipcode = left(table2.zipcodelong,5)
	IP Logged

db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	Posted: 15 May 2012 at 11:12am
	You are awesome. Thank you. Appreciate your time.
	IP Logged

db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	Posted: 15 May 2012 at 11:35am
	Any idea based on my query below why I'm getting an error? select address.zip, January_2012_z5ma.zip from January_2012_z5ma inner join address.zip on January_2012_z5ma.zip= left(address.zip,5) I receive an invalid object name 'January_2012_z5ma" error.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 15 May 2012 at 11:41am
	not seeing anything obvious. *January_2012_z5ma* is a table in your DB?
	IP Logged

db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	Posted: 15 May 2012 at 11:43am
	Yes. A zip code db we pay for anually. That's the db that has consistent 5 character zip codes.
	IP Logged

db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	Posted: 15 May 2012 at 11:51am
	I should probably note that that table is a an Access db. Not sure if that matters or not. Thanks again for your time.
	IP Logged

Page of 2 Next >>

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum