index | search | no replies | posting guidelines | help login | register
Index » Products » ASP Report Wizard v2
search this forum:
:: Linked value Tables :: (8)Post a New Message | Post a Reply
Aug 11 2005, 10:06 AM
 Linked value TablesPost a Reply
 lirving@coffs.com.au

View this author's profile Send this author a private message Visit author's homepage

since: Feb 22, 2005
from: somewhere

I have a number of tables that get info out of a values table

one value is Disability _type another is sex
I want to display the value name from the values table
i tried a text replacement
{{[values].[value]}} but it just comes up blank

If i had lots of sub table I can see how to do it but i only have one values table and each database row might have several numerical values that i wish to replace in the report with the text from [values].[value_Name]


Aug 11 2005, 1:37 PM
 Re: Linked value TablesPost a Reply
 ghost

View this author's profile Send this author a private message Visit author's homepage

from: Washington, DC

Are you trying to join two or more table?


-------------------------
Ghost
Aug 11 2005, 8:06 PM
 Re: Linked value TablesPost a Reply
 lirving@coffs.com.au

View this author's profile Send this author a private message Visit author's homepage

since: Feb 22, 2005
from: somewhere

Yes but not in the noraml master child relationship ie user table linked to invoice table with a link or key field user_ID

what I have is a number of tables which get their text names from a values table
for instance in the user table there is a field for [sex] - it contains a numeric value  5 or 6 in the same record there is a value for [disability_type] there are several from 56 to 64

The values table contains about 150 records which have the names ie. disability_type 56 is "Intellectual", 5 is "Female"

So I need like a Dlookup function to replace the number in the field with the text value from the values table, this should happen after the lookup query for the row in the before show event of the field or something similar




Aug 11 2005, 8:17 PM
 Re: Linked value TablesPost a Reply
 lirving@coffs.com.au

View this author's profile Send this author a private message Visit author's homepage

since: Feb 22, 2005
from: somewhere

Or Can i create a dynamic/ calcutated field say sexvalue and do a lookup like

sexvalue.value= select value from values where values.value_ID=users.sex

if so where would create the variable and where would i put the select staement

Aug 12 2005, 9:47 AM
 Re: Linked value TablesPost a Reply
 ghost

View this author's profile Send this author a private message Visit author's homepage

from: Washington, DC

A basic report with multiple tables will archive this result if you input the relationships correctly e.g.
if you have four tables -

1. User (Name,Address,City,StateCode,EducationLevelID,GenderID)
2. State (StateCode, StateName)
3. Gender (ID,Name)
3. EducationLevel (ID,Description)


To generate a descriptive report on the user with the following fields:
User's name, gender description, state name and education level i.e.
(User.Name,Gender.Name,State.StateName,EducationLevel.Description)

Their relationships will be as follow:

User.StateCode = State.StateCode AND
User.GenderID = Gender.ID AND
User.EducationLevelID = EducationLevel.ID







-------------------------
Ghost
Aug 13 2005, 10:26 AM
 Re: Linked value TablesPost a Reply
 lirving@coffs.com.au

View this author's profile Send this author a private message Visit author's homepage

since: Feb 22, 2005
from: somewhere

User.StateCode = State.StateCode AND
User.GenderID = Gender.ID AND
User.EducationLevelID = EducationLevel.ID

Idon't get it.

The above would work for three different tables. selecting values that you display from each child table, but only once for each row from each table.

In my data base the vales for gender, disability and education and other things are in one table organised in groups. I can substitute the first one (gender) but I can't see how  to go back to the same table to query the second (disability).

Aug 14 2005, 9:26 AM
 Re: Linked value TablesPost a Reply
 ghost

View this author's profile Send this author a private message Visit author's homepage

from: Washington, DC

gender, disability and education and other things are in one table organised in groups

Tables are supposed to be the lowest level of organization NOT groups. What exactly do you mean by groups?
Gender, disability and education are supposed to be in different tables.


-------------------------
Ghost
Sep 17 2005, 7:52 PM
 Re: Linked value TablesPost a Reply
 lirving@coffs.com.au

View this author's profile Send this author a private message Visit author's homepage

since: Feb 22, 2005
from: somewhere

Here is some working SQL from my DB, you can see the repeated trip to the revalues table using Alias names fro the table and field. Can I do this in report Wizard

SELECT sex.Value_Name AS refvalues_Value_Name, disabilitytype.Value_Name AS disabilitytype_Value_Name, Referral.Next_Contact_type AS Referral_Next_Contact_type,
NextContactType.Value_Name AS NextContactType_Value_Name, ReferralOrigin.Value_Name AS ReferralOrigin_Value_Name, CurrentAccPlacement.Value_Name AS CurrentAccPlacement_Value_Name,
Referral_Received_Date, Name, Address, Town, Postcode, Date_of_Birth, NESB, Language_Spoken, ATSI, Referral_Other, [Primary_Carer_ATSI/NESB],
AGE_of_Primary_Carer, Primary_Carer_Heath_Issues, Primary_Carer_Heath_Issues_Description, Referral.Next_Contact AS Referral_Next_Contact,
Exit_Date
FROM Plans INNER JOIN ((((((Referral INNER JOIN refvalues sex ON
Referral.Sex = sex.Value_ID) INNER JOIN refvalues disabilitytype ON
Referral.Disability_Type = disabilitytype.Value_ID) INNER JOIN refvalues NextContactType ON
Referral.Next_Contact_type = NextContactType.Value_ID) INNER JOIN refvalues ReferralOrigin ON
Referral.Referral_Origin = ReferralOrigin.Value_ID) INNER JOIN refvalues CurrentAccPlacement ON
Referral.Current_Accomodation_Placement = CurrentAccPlacement.Value_ID) INNER JOIN refvalues Criticalissues ON
Referral.Critical_Issues = Criticalissues.Value_ID) ON
Plans.Ref_ID = Referral.Ref_ID
WHERE Referral.Postcode LIKE '%{s_Postcode}%'
AND Referral.Date_of_Birth >= #{s_Date_of_Birth}#
AND Referral.NESB = {s_NESB}
AND Referral.Referral_Received_Date >= #{s_Referral_Received_Date}#
AND Referral.Language_Spoken LIKE '%{s_Language_Spoken}%'
AND Referral.ATSI = {s_ATSI}
AND [Primary_Carer_ATSI/NESB] = {s_Primary_Carer_ATSI_NESB}
AND Referral.AGE_of_Primary_Carer >= {s_AGE_of_Primary_Carer}
AND Referral.Next_Contact >= #{s_Next_Contact}#
AND Referral.Next_Contact_type = {s_Next_Contact_type}
AND ReferralOrigin.Value_Name LIKE '%{s_ReferralOrigin_Value_Name}%'
AND Plans.Exit_Date >= #{Exit_Date}#
ORDER BY Referral.Name desc

Pages: (1)   [1]

search this forum: