unlinked tables

[malka]

I am trying to write a simple report that gets data from 2 unlinked tables. Actually one field in one table is embedded in a field in the other table.
From what I read - I will need to have a subreport done. But is there a way to still link with extracting part of the field with a formula.
Ex:
Table 1 - agt_no (04, 05, PR etc.) (Main report)
Table 2 - Pseudo_Agt (A9JT04, R4W605, A9JT06, A9JT0PR).
The linked data is the last 2 characters. The first 4 Char. are branch no. And I need this table just to get the Agnet name.

Thanks,

Malka

 

[Ngolem]

does Table 1 have the agent and branch numbers as separate fields?...or just the agent no.? Is this agent 2 digit number unique...or could 2 branches have the same agent number?

If all you want is the Agent Name form the Table 2 then passing it back as a shared variable could be done.

make table 2 a subreport and link it on {Table1.agt_no} to {Table2.Pseudo_Agt}

NOW go to the subreport Record Select modify the formula

to the following:

right({Table2.Pseudo_Agt},2) = {the linking parameter}


this will not be pushed to the server so will be slower but cannot be helped I think.

If both Branch and Agent numbers are in the Table 1 you could use an SQL expression to concatonate them together and then link up the Table 2 to the SQL experession....that is another possiblity

JimBroadbent@Hotmail.com

 

[malka]

Thanks for the quick reply.

Answer to your questions:
Agent no. is Unique regardless of the branch.
Branch no. does not appear in table1.

I will try your solution which according to what I read is the right one.

Thanks again.

 

[malka]

I was wondering if I could get the Agent name in the Primary report. The agent name is in the subreport that I have created.
How can I integrated all data from both tables eventhough the tables are not linked. They can be linked as I mentioned by extracting the 2 rigth char. or witht the formula:
right({AGENT_Q.PSEUDO_AGT},2) = {?Pm-passnger.AGT_NO}

thanks for any reply.

 

[Ngolem]

ok...so you have established that we now can link using the record select formula

right({AGENT_Q.PSEUDO_AGT},2) = {?Pm-passnger.AGT_NO}

now All you have to do is assign the Agent name to a shared variable and you can pass this information back to the Main Report

Is the Agent name the complete reason for the subreport or are you gathering/displaying other information in the subreport?

JimBroadbent@Hotmail.com

 

[malka]

Ngolem,

Thanks again for your reply.
I still have some more questions.
Yes, the Agent name is the complete reason for the subreport. I do not need any other info from the 2nd table. I believe we already established that creating a subreport is the only way to link between these 2 unlinked tables.
Is there another?
How do I assign the Agent name to a shared variable?
From the primary report I can only see the fields from the primary table.

 

[Ngolem]

well...if the agent name is all that is required then there is no problem...we do not need shared variables...unless you want to use the Agent's name in more than one location in the main report.

If it is only required in one location then place the subreport exactly where you want it and size it accordingly.

In the subreport...suppress all sections except for the one displaying the Agent Name field or formula containing the Agent name

and you are done....just make certain the subreport lies within the group header and footer for Table 1 - agt_no JimBroadbent@Hotmail.com