R
Robert_L_Ross
Hopefully this won't be too complicated to explain...
I had a DB split on our network (both files .mdb).
In a form representing 'setups' I had a tab with one subform each (Schools,
Assignments and Notes). For each record on the parent form, the subforms
displayed results from other tables assicated by the parent record's Primary
key...for this problem it's the Assignments tab (pulling the number of
assignments from the SetupAssignments table and also the number of follow-ups
from the AssignmentFollowUps table).
I was able to count the PrimaryKeys to get the total number of assignments,
then do a sum of a Yes/No field to get the number of follow ups that had been
checked and a count of a text field to get the total number of follow-ups
with notes.
No problems, db ran fine.
I recently had our DBA's move the back-end db to SQL2k5. SQL has of course
changed the field types...I now can't count or sum an ntext field (for some
reason my yes/no field in access converts to an ntext field).
My question is...I've tried a pass-through query to get my two field
results, then brought those into the source query for my subform, but
received an error stating I can't use a pass-through query as a data source
for a subform. How do I get these numbers? Access could 'convert' a text
field at the query level to realize I wanted the number of records with text
in the field. I can get SQL to do that, but I can't use the result as a data
source for the form (doesn't do me much good to know the value but not be
able to display it).
Would it work to maybe make an unbound field and have it reference the
pass-through query? If so, I need to somehow have the pass-through query
"pass" the record number I am on so the field will update correctly - not
sure how to do that.
As you may have guessed, I'm relatively new to SQL Pass-Through queries.
Any ideas would be greatly appreciated...thanks!
Rob
I had a DB split on our network (both files .mdb).
In a form representing 'setups' I had a tab with one subform each (Schools,
Assignments and Notes). For each record on the parent form, the subforms
displayed results from other tables assicated by the parent record's Primary
key...for this problem it's the Assignments tab (pulling the number of
assignments from the SetupAssignments table and also the number of follow-ups
from the AssignmentFollowUps table).
I was able to count the PrimaryKeys to get the total number of assignments,
then do a sum of a Yes/No field to get the number of follow ups that had been
checked and a count of a text field to get the total number of follow-ups
with notes.
No problems, db ran fine.
I recently had our DBA's move the back-end db to SQL2k5. SQL has of course
changed the field types...I now can't count or sum an ntext field (for some
reason my yes/no field in access converts to an ntext field).
My question is...I've tried a pass-through query to get my two field
results, then brought those into the source query for my subform, but
received an error stating I can't use a pass-through query as a data source
for a subform. How do I get these numbers? Access could 'convert' a text
field at the query level to realize I wanted the number of records with text
in the field. I can get SQL to do that, but I can't use the result as a data
source for the form (doesn't do me much good to know the value but not be
able to display it).
Would it work to maybe make an unbound field and have it reference the
pass-through query? If so, I need to somehow have the pass-through query
"pass" the record number I am on so the field will update correctly - not
sure how to do that.
As you may have guessed, I'm relatively new to SQL Pass-Through queries.
Any ideas would be greatly appreciated...thanks!
Rob