I've got a fairly simple text table, where for each sample, I want to compare the logged ASTM Group Name developed by field inspection to the more accurate ASTM Group Name determined by Grain Size and Atterberg. I have the following fields in my text table:
Borehole <<SAMPLE.PointID>>Inspector <<Lookup(<<DRILLING INFORMATION.Inspector>>,<<DRILLING INFORMATION.PointID>>=<<SAMPLE.PointID>>)>>Sample Number <<SAMPLE.Sample_Number>>Sample Depth <<SAMPLE.Depth>>Logged Group Name <<sql(Select [Group_Name] From [Sample_Desc] Where (([Sample.PointID]=[Sample_Desc.PointID]) and ([Sample.Depth]=[Sample_Desc.Sample_Depth])))>>Lab Group Name <<ASTM_Name(<<SV READINGS.Reading>><<#>>,<<SV READINGS.Percent_Finer>>,_ <<ATTERBERG.Plastic_Limit>>,<<ATTERBERG.Liquid_Limit>>,_ <<ATTERBERG.Organic>>)>>
Everything is working fine except the "Logged Group Name" I've tried many different permutations but I cannot get it to select the correct logged description, which is in the field <<SAMPLE_DESC.Group_Name>>. I've tried lookups and SQLs and am not having luck.
The keyset for the table is POINTID,Depth.
Thank you for your help.
Hi Monique,
A couple things might need to be addressed...
In the Where clauses, the order of the arguments need to be reversed.
Also, the Where clause search value should be in gINT double-angle brackets instead of square brackets.
And since the PointID is a text value, it should be in single quotes.
So, I think this might get it working:
<<sql(Select [Group_Name] From [Sample_Desc] Where (([Sample_Desc.PointID]='<<Sample.PointID>>') and ([Sample_Desc.Sample_Depth]=<<Sample.Depth>>)))>>
(I'm assuming Sample_Desc is a 1:1 child of Sample. If that's not the case, it might require more thought.)
It would be difficult to assess how to write the functions without having your custom database structure.
Since you are calling in Multiple tables into the report, how they are related is important to the Query.
The Table keys and how the tables are related determine how you would write the SQL Query for example.
I will take a guess that Sample Desc is a child of Sample, but if there are extended keys or if it is one to many then you would need to include that in the Query.
For Example The Select statement would call in the field in the child and where clause would compare it to each one of the Keys of the parent for as many key fields as you have.
SELECT [Sample_Desc].[Group_Name], _
FROM [Sample_Desc], _
WHERE [Sample_Desc].[PointID] =’<<SAMPLE.PointID>>’, _
AND [Sample_Desc].[Depth] = <<SAMPLE.Depth>>,_
AND [Sample_Desc].[Additional Key Field 1] = <<SAMPLE.Additional Key Field 1>>
If you want to send me the gINT files I can give you a more detailed answer.