Hi
I have a table in which lies a list of tasks. i need to produce a layout that will list found records, and show a "quantity", a number of times it occurs in the found set. I additionally only need the item to be shown once, and not for each occurrence of it.
Please can you advise. If I haven't worded it very well I apologise, and please ask any questions that would help.
Thanks,
Mike
Hi Brian,
I have a relationship set up as you described, please could you supply an example of the Count command you mentioned?
Mike
See this thread for suggestions.
ExecuteSQL() does not work on "found set" unless you recreate the "find" in the WHERE clause. ExecuteSQL() does not use any relationships, either.
A Summary Report showing only the sub-summary part (no Body part) and sorting correctly may get you what you need.
Beverly
Assuming the setup and you want to use the Count() function
TableA::pk_TaskID----< TableB::fk_TaskID
In TableA create a new calc field with the calculation
Count(TableB::fk_TaskID)
The caveat is that if in Table2 there is a QTY identifier you have to factor that into the equation by using a SUM() function instead of Count()
Sum ( TableB::QTY )
**** this is based on my interpretation of your original post. If i misunderstood the goal please let me know
wow what a delay beverly , your post just came through on my end. I read the OP as in found set being a list of tasks not as in found related records. i.e. how many times did we perform this task and not as how many times did Bob performed this task.
Anywho, bev as well as others are much MUCH more knowledgeable and can offer better advice
Have you tried creating a sub summary section on the layout when sorted by the tasks field that you are counting?
Put the field and its Count() Summary field in that section of the layout then remove the body section of the layout.
Sort the found set and you should get a list of tasks with a count of how many of each.
Unless I have misunderstood your question I would have thought that is all you need to do.
Just spotted you gave the same answer as I just posted higher up the thread re-Sub Summary parts - missed it because initially it was a response to ExecuteSQL() which did not seem what was needed.
Did not mean to duplicate the same answer!
You are doing just fine, brianb! It takes a village and all that!
I found that answering questions on the forum is a great learning experience for you and the OP!
I've flubbed more than once, we should not judge each other.
Keep on keeping on!
Beverly
Sent from miPhone
Blame it on the 'net. LOL. Good to have same answers, don't you think?
Beverly
Sent from miPhone
Hi,
If were to upload a very small sample file I have created to test offline, would you be able to have a very quick look at it for me. I believe I have followed the COUNT instruction by using the sub-summary, but it is possible I'm doing it wrong.
Thanks,
Mike
If you use Advanced Editor on this forum, you can attach files.
Beverly
Thank you :-)
You don't seem yo have used a count() summary field. If you want to count the number of instances of each type of task you simply need a summary field Count(Task_ID) You can then count the instances as well as showing the total quantity of all instances in the Sub Summary line. The layout should be based on the tasks table to ensure it is accurate.
I have made a quick amend for you to look at attached.
This would depend on how you have your tables and relationships set up. If there is an "item Summary" table where you have a single record for each entry you could relate it to the table of records where it appears more than once.
then in the item summary table use count() to or a summary field to give the total times that item is listed
If there isnt a relationship and no other way to make it work based on the data model and relationships an executeSQL() may be your easiest, but not best method. You can add this calculation field to any layout based on any table
An example calculation
ExecuteSQL(" SELECT DISTINCT ItemField, COUNT(ItemField) FROM TableName" ; " - " ; "" ; "")