InfoPath Dev

I really hope someone can help me with a solution to the following.

I need to run a query on an access data base that will return the number of dulplicate entries. 

The data base is set up like this

Occupation Title

Teacher

Teacher

Teacher

Coach

Coach

Mentor

"Occupation Title" is the label and "Teacher, Coach and Mentor" are entries.  I need to run a query that will return all of the Titles only and a count of occurances,

Like this:

Teacher 3

Coach 2

Mentor 1

I was able to exlude the duplicates in a repeating table but the number of entries is always 1.  I am using expression boxes with filters and rules but I cant figure out how to make infopath count the number of times the entries appear in the respective column.

Any help would be greatly appreciated..  Thank you all.

Hi:

Sounds like you are returing all the data to InfoPath and then need to aggregate it there? This can be done with conditional formatting and expression boxes, but it would probably be better to create a view in Access that aggregates that data and return the view. Fewer rows is going to make your form faster....

Wow Thank you for the quick response Hilary.... your responses have helped me a great deal in the past. 

 I was only wondering how I would condition an expression box to count duplicates in an access data base. I have the following ......count(@OccupationsOne[. = .])...... in the "counting" expression box but when I filter for uniques values in the repeating table the count come back 1 because only one title is being displayed.  I was wondering if there was a way to get infopath to look at the "@OccuaptionsOne" column only and return the corresponding numbers for the duplicate entries found there. 

This is the last piece of my puzzle so any help from you would be appreciated. thank you

Any chance you can post your template in a reply? Under the options tab.... I can get the syntax right for your form then....

Generally, though, the problem with your expression is probably that you are using the field selector to get "@OccupationsOne" - so InfoPath will be using a relative XPath... and there will be only one that matches within that repeating group. Use the full XPath to the @OccupationsOne, or something like ../../dfs:NameofRepeatingGroup/@OccupationsOne so that you are going back up the tree to look at all instances of your repeating group (note that the XPath above is a complete guess, and there is probably 0% chance your repeating group is called "NameofRepeatingGroup")

Hilary,

 I was trying to solve my problem before bothering you or anyone else.  I'm at a loss for a solution.  I was able to filter for unique values but I still get blank fields for the duplicate records and i was not evern close to getting it to count.. I really hope you can help me with this.  I need to be run a query that will return entries titles once but a count of thier occurance. 

Example of my data structure in access data base.

OccupationsOne <<<this is the column title and the data field.  My tree looks like this>>>myFields/my:dataFields/d:VET1/@OccupationsOne

teacher  <<<<<<These are entries in the access rows and the informations I need to display.

teacher

coach

coach

counselor (whick is what I'm going to need after this project LOL)

When I run a query in need the following display.

teacher 2

coach 2

counselor 1.

 thank you very much for your time.

Adolfo

Is it the main data source that will have all the records?

I'm guessing a bit here, since I can't run your query. But if my guesses are wrong in the attached sample, I'll put together a sample form that I do have a DB for, and see where I went wrong, okay?

At any rate, give this a try, right click the link and save the xsn locally. Then right click the xsn to open in design mode. I'll check this thread tomorrow and see if I've guessed right, okay?

Hilary,

Thank you for taking your time to help me.  I can't tell you how much I appreciate it. 

Yes, the data will be stored in the main data source.

Your example filtered out duplicates perfectly but the count was 5 all the way down when there were 2, 2 and 1. 

I tried to adjust it but didn't get far.  If there is any time in your schedule to get me over this final hurdle I will be forever obliged. 

 Thank you

Sounds like I need to adjust the XPath I'm using for the count expression - I'll get that fixed later today and post a new version.

Oh my. I made a mess of that expression. I think I over-thought it. Try this one.

It's working! Thank you

I was wondering about your expression in the count field.  "count(VET1[@OccupationsOne = current()/@OccupationsOne])".  Is there a way to filter for a second criteria?  I have another column in the main data source that stores "1 - 2 Years", "2 - 5 Years" or "5 + Years" and I was hoping to display your count solution filtered by one of the three choices.  

any ideas?

Thank you Hilary.

Hi:

Sure, you can add additional filters. I'm not sure which field it would be in your data source, so I'll bold the piece you'll need to change.

count(VET1[@OccupationsOne = current()/@OccupationsOne and @SomeOtherField = "1 - 2 Years"])

Some things to note - that comparison is going to be case sensitive. So if your field actually stores "1 - 2 yEars" for example, you won't have a match. Also, the comparison is going to care about spaces - "1-2" is not the same as "1 - 2".

Hilary your awsome!! I just have one more question.....(I'm starting for feel like Lot from the old testament...LOL)

I have three fields that collect titles. 

OccupationsOne, Occupation Two, OccuaptionThree

if all fields held diffrent titles how could I use an expression box to diplay to contents of all locations in one expression box.

for example

OccupationsOne  OccupationTwo   OccupationThree

Teacher                Plumber             Coach

                           Plumber             Coach

I query for all locations in one expression box to get ( There would be two expression boxes, one for the title and one for the count) I need to get all the titles from the three fields so I can then count them.

Teacher  1

Plumber 2

Coach 2

In a repeating table......Im so lost ......

I've attached an example in an effort to explain.  Please let me know if you need clarification.

Your suggestions are always usefull and much appreciated.

Thank you

Before we go too far down that path, may I ask - are any other applications using your database? It seems to me that the best thing you could do if not is to normalize your database - this whole Occupations 1 -3, Ref 1 - 5, etc is going to be pretty nasty to maintain over the long run. It would make so much more sense to create proper tables and relationships.

I hope you know, I intend no snark in that comment - and I realize you may just be stuck here with some messy legacy database. But if there is any chance you can change it - I'd sure take it if I were you.

I see at least 4 tables here - one with the main Client information (basing that on the field called "ClientName"). Another table for Occupations, a table for Refs, and a table for Jdc. I think it would surprise you how much easier some of this would get with a proper, normalized database.