index | search | no replies | posting guidelines | help | login | register |
Index » Products » ASP Report Wizard v2 |
| |||||||
Aug 11 2005, 10:06 AM |
| ||||||
lirving@coffs.com.au 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 |
| ||||||
ghost from: Washington, DC | Are you trying to join two or more table? ------------------------- Ghost | ||||||
Aug 11 2005, 8:06 PM |
| ||||||
lirving@coffs.com.au 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 |
| ||||||
lirving@coffs.com.au 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 |
| ||||||
ghost 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) 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 ------------------------- Ghost | ||||||
Aug 13 2005, 10:26 AM |
| ||||||
lirving@coffs.com.au 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 |
| ||||||
ghost 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 |
| ||||||
lirving@coffs.com.au 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] |