Using variables in Crystal Reports

[shortstuff2212]

I have the following tables in an Access db.

Table - ElectoralArea
ElectionID ElectorLevel Polldistrow EACode
1 Ward DistrictWard CAD

Table - PollingDistrict
PDCode DistrictWard County Parish
AA CAD HU ST
AB CAD HU ST
AC CAD HU ST
AD CAD HU ST
BA CAD HI SB
BB EAB HI SB

Table - PollingStationRange
PSCode PDCode PSNumber Electorate
2212 AA 1 1-1999
2215 AB 2 1-1999
2218 BA 3 1-1999

I need to find out what data is in the Polldistrow field and then take the EACode and find the same code in the relevant field in the PollingDistrict table and store the PDCode then find all the pollingstations with the same PDCodes and display them on the report.

Can anyone help with this as I have never used variables in crystal reports and I am really stuck on how to do this.

Any help would be greatly appreciated.

 

[Reebo99]

You don't need to use variables, you just need to join the tables in the correct way.

open all 3 tables and when you are faced with the join options :

PollingDistrict.DistrictWard To ElectoralArea.EACode
PollingDistrict.PDCode To PollingStationRange.PDCode

Then simply put the fields you wish to see, from the 3 different tables, in the report. Tell me how you get on, or if you need better procedures.




Reebo
Scotland (Going mad in the mist!)

 

[shortstuff2212]

The reason I can't do that is the data in the ElectionArea table changes.

Table - ElectoralArea
ElectionID ElectorLevel Polldistrow EACode
1 Ward DistrictWard CAD
2 Parish Parish GED
3 County County HUN

Table - PollingDistrict
PDCode DistrictWard County Parish
AA CAD HU ST
AB CAD HU ST
AC CAD HU ST
AD CAD HU ST
BA CAD HI SB
BB EAB HI SB

Table - PollingStationRange
PSCode PDCode PSNumber Electorate
2212 AA 1 1-1999
2215 AB 2 1-1999
2218 BA 3 1-1999

The first thing I need to do is find out what is in the Polldistrow field, then look in the pollingdistrict table for the relevant field and then find all the pollingdistricts attached to that area.

Sorry if I am not explaining this very well but any help would be appreciated.

 

[Reebo99]

Oh, sorry about that.

Firstly, is this correct:

get value of Polldistrow (e.g. DistrictWard)
goto pollingdistrict table
if value of Polldistrow = DistrictWard then match the value of EACode to the corresponding field value?

If this is correct, then when joining, open up 3 instances of ElectoralArea and link one via EACode to DistrictWard, one via EACode to County and one Via EACode to Parish.

You then have a lookup which you can evaluate in a number of ways. Reebo
Scotland (Going mad in the mist!)

 

[shortstuff2212]

It is correct but I am not sure what you mean when you say open up 3 instances of ElectoralArea and link one via EACode to DistrictWard, one via EACode to County and one Via EACode to Parish.

Do you mean in the sql statement?

Kath
(Going mental in Manchester)

 

[Reebo99]

No,

Either
1. When you first creat the report and you have a list of tables to select from, simply select the same table 3 times. They will automatically be called something different like Table, Table_1, Table_2 etc. Then when the visual linking comes up you will have 3 instances of the same table to link to.

2. Goto Database > Visual Linking Expert > Tables and choose the same table a couple more times then see 1.

Let me know how you get on. Reebo
Scotland (Going mad in the mist!)

 

[shortstuff2212]

Hi

Thanks for the help, I can't get this to work so have decided to go a different route.

Cheers

Kath