Crosstab Query without duplicates?

[danielmurray]

I need to create a crosstab query to count client ages by male and female.&nbsp;&nbsp;This is part of a much larger report with lots of selection criteria based on date assisted, type of assistance, etc.&nbsp;&nbsp;I have a working select query to remove any clients with repeat assists.<br><br>I generally create an unduplicated select query by setting the Unique Values or Unique Records in the Query's Properties to Yes.&nbsp;&nbsp;However, those options are not available in a crosstab query.<br><br>I tried to &quot;feed&quot; the crosstab query with a select query that de-dupes based on the user's selection criteria, but then the crosstab query complains about undefined parameters.&nbsp;&nbsp;I tried to follow the message's advice to setup a parameter list, but things just got out of hand, since I am selecting on a ton of criteria.<br><br>I am stuck between the necessity for a crosstab query but without the ability to dedupe it.&nbsp;&nbsp;Any help would be greatly appreciated.

 

[Geekette]

if you are getting duplicates in your crosstab query, there are indeed ambiguities in your data: i.e. somewhere there are people with both genders checked [heh heh - i'm from new york city, so that's not unusual ] or perhaps someother field like that.<br><br>for example you should have one field for gender, with a code to indicate whatever gender the person wishes to be. make sure that all of the descriptive fields you have are single instances with choices within those instances, with supporting descriptive tables:<br><u>PersonTable</u><br>lname, genderID, assistanceType, etc.<br><br><u>genderIDTable</u><br>genderIDcode, gender<br><br>assistanceTypeTable<br>assistanceTypecode, assistanceName<br><br>and so on<br><br><br>

 

[danielmurray]

Thanks for your response.



I guess what I didn't make clear was that in this database there indeed is a separate field for gender, but there isn't a single AssistanceType field as you documented.   I have a one-to-many relationship where one client record can have many assistances, each with an assistance type.



Since the assistance record contains the date, I must query based on the relationship of Clients--->Assistances and therefore potentially get many duplicates since the client will be listed once for each selected assistance record.



If I run a select query that filters this relationship based on a form with criteria and returns all correct assistance records, but I only display the ClientID and tell the query "Unique Values", it dedupes nicely.  Crosstab won't do this, and if I use the dedupe select query linked to the Client table to generate unique clients with genders, the crosstab starts complaining about parameters (the form-based criteria from the select query).



Sorry for the long post. ;-)

 

[JimConrad]

You could change the &quot;dedup&quot; query to a &quot;make table&quot; query.&nbsp;&nbsp;Then take that table into your cross-tab query. <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[danielmurray]

Thanks for your response.

Great idea.&nbsp;&nbsp;I considered that, but wanted to avoid messy database operations that might impact the database integrity in the long run.&nbsp;&nbsp;If I need a temporary table, I have tried to use a permanent table that simply gets cleared upon each use.

If I use the Make Table query and then delete the object, will this impact the database over a period of time?&nbsp;&nbsp;This is in a fairly active multi-user environment, and I want to avoid techniques that require more maintenance and &quot;watchdogging&quot; if possible.&nbsp;&nbsp;I anticipate this report will be run in &quot;clusters&quot; where monthly it could be run a dozen times in a row as the user does some &quot;data mining&quot;.&nbsp;&nbsp;While the report is important, I can't decide if it's important enough to warrant a permanent work table just for that...

 

[DougP]

In Dewsign view of your query.<br>Right click on the little black line between the Tables in your query and look at the 3 options.<br>Choose a different one and re-run your query. If it does not work try the other 2 and this MIGHT solve your problem.