Giberish in View Totals Query

J

John Dumay

Hi Folks,

My data gives funny results when I "View totals" in a query.

My data looks like this first

Column_Category Row_Category Score
35 to 44 Years Operations 1
35 to 44 Years Operations 1
45 to 54 Years Operations 1
35 to 44 Years Operations 1
45 to 54 Years Operations 1
45 to 54 Years Operations 1
25 to 34 Years Operations 1
35 to 44 Years Operations 1
45 to 54 Years Operations 1
45 to 54 Years Operations 1
45 to 54 Years Operations 1
25 to 34 Years Operations 1

And when I use "View Totals" I get this gibberish


Column_Category Row_Category CountOfScore
ê°€! Administration 1
ê°! Sales and Marketing 1
ê°‚! Other 1
ê°ƒ! Administration 1
ê°„! Sales and Marketing 1
ê°…! Administration 1
ê°†! Sales and Marketing 1
ê°‡! Supply Chain 1
ê°ˆ! Technical 1
ê°‰! Administration 1
ê°Š! Information Technology 1
ê°‹! Payroll 1
갌! Operations 1
ê°! Finance 1
ê°Ž! Operations 1
ê°! Technical 1
ê°! Finance 1
ê°‘! Operations 1
ê°’! Supply Chain 1
ê°“! Operations 1

(LH Side is actually Chinese type characters)



I have removed any foreign language settings but no matter what I do it is
still the same.

Any help is apprciated.

Regards,

john Dumay
 
J

John W. Vinson

Hi Folks,

My data gives funny results when I "View totals" in a query.

My data looks like this first

Column_Category Row_Category Score
35 to 44 Years Operations 1
35 to 44 Years Operations 1
45 to 54 Years Operations 1
35 to 44 Years Operations 1
45 to 54 Years Operations 1
45 to 54 Years Operations 1
25 to 34 Years Operations 1
35 to 44 Years Operations 1
45 to 54 Years Operations 1
45 to 54 Years Operations 1
45 to 54 Years Operations 1
25 to 34 Years Operations 1

And when I use "View Totals" I get this gibberish
Click to expand...

My guess is that you have a Lookup Field in the table - so what you see in the
table grid is NOT what's actually in the table.

Perhaps you could open either or both query in SQL view and post the SQL text
here, along with the names and datatypes of each table field.

John W. Vinson [MVP]
 
J

John Dumay

Hi John,

Please find following th SQL which to me looks OK.

SELECT tblBO_CrossTab_Column_Items.Column_Category,
tblBO_CrossTab_Row_Items.Row_Category, Count(tblBO_CrossTab_Row_Items.Score)
AS CountOfScore
FROM tblBO_CrossTab_Column_Items INNER JOIN tblBO_CrossTab_Row_Items ON
tblBO_CrossTab_Column_Items.ResponseID = tblBO_CrossTab_Row_Items.ResponseID
GROUP BY tblBO_CrossTab_Column_Items.Column_Category,
tblBO_CrossTab_Row_Items.Row_Category;

The tables are created by a "Make Table Query" so there are no "lookups".

I used an Inner join as I only wanted data that was common to both tables.

You reply and assistance is appreciated.

Regards,


John Dumay
 
J

John W. Vinson

And when I use "View Totals" I get this gibberish
Click to expand...

The gibberish may be either attempting to display a number field as text, or
it could be some sort of database corruption. Have you compacted and repaired
the database?

Try copying the SQL of the query out to notepad (or use the SQL you posted
here, just need the text). Delete the query; compact the database; create a
new query and copy the SQL back in.

John W. Vinson [MVP]
 
J

John Dumay

Hi John,

Yes, I always compact and repair the database. I have done this manually
when I had the DB open and it is set to happen every time I close the DB as
well. I also reloaded the data from the source. I have even deleted and redid
the query as suggested. The odd thing is that I can group on the field and it
displays as it should if I use only either column. I have used the ungrouped
data in code and manually build the cross tab matrix in code by cycling
through the records and storing in a string matrix.

Your assistance is appreciated.

Regards,

John Dumay
 
J

John Dumay

Hi,

thanks everyone for your assistance but i seem to have resolved the issue.

THe probem is in the "inner join" of the two tables. Apparently SQL as
implemented in Access2003 doesn't like this when doing a Totals or a CrossTab
Query.

The solution I found was to revert to a normal Select Query and then use
this as the basis of a Make Table Query. The reultant table can the be used
to make a Totals or even a Crosstab query with out the gibberish. A bug in MS
Access 2003 (haven't attempted in 2007)???

But anyway problem solved, just have to write the code to make sure the
tables and queries are executed in the right sequence so i continully get the
right results. A bit of a pain but on a few more milliseconds of processing
time, so what the heckif it works.

Thanks for yur input.

Regards,

John Dumay
 
J

John Dumay

Hi John,

Thanks for your assistance but i seem to have resolved the issue.

THe probem is in the "inner join" of the two tables. Apparently SQL as
implemented in Access2003 doesn't like this when doing a Totals or a CrossTab
Query.

The solution I found was to revert to a normal Select Query and then use
this as the basis of a Make Table Query. The resultant table can the be used
to make a Totals or even a Crosstab Query with out the gibberish. A bug in MS
Access 2003 (haven't attempted in 2007)???

But anyway problem solved, just have to write the code to make sure the
tables and queries are executed in the right sequence so I continully get the
right results. A bit of a pain but only a few more milliseconds of processing
time, so what the heck if it works.

Thanks for yur input.

Regards,

John Dumay
 
J

Jeanette Cunningham

Hi John,
glad you solved it. However, using a make table query is a difficulty in the
future for your database.
I am interested to know more details so I can help others who may have the
same problem later.
Do you have a table with Department names like Administration, Marketing etc
and a field for the years of marketing for the departments?
Your query with Column_Category , Row_Category and Score comes from a table,
which table is joined to which table for the inner join?
I would like to be able to reproduce the gibberish on my computer if you
will help with the details.

Jeanette Cunningham
 
J

John Dumay

Jeanette,

If you send me your e-mail address i will send a DB with the two tables and
the query.

Actually I like using the make table queries as I run these in code in order
so that i do not have too many cascading queries whic while they can run take
for ever. i founf you can run these in a specific sequence in code it can
dramitically improve the speed of the database.

Regards,

John Dumay
 
J

Jeanette Cunningham

Thanks John,
Please be sure to zip up the database before you email it.
The e mail is below, I'm sure you can remove the spaces to create the real
one.
jj cc 14 @ gmail .com

Jeanette Cunningham
 
J

John Dumay

Hi Jeannette,

I have sent the email as requested. Please check and let me know.

i have imported the tables and provided you with two queries which display
the erroneous results.

Regards,

John Dumay
 
J

Jeanette Cunningham

Thanks John,
I downloaded the database. As soon as I looked at the tables and saw 4 memo
fields I had a pretty good idea why your queries were giving some nonsense
characters. Now memo fields are well known for causing problems with some
queries, so I changed all the memo fields to text (as they only had about 30
characters).
Both queries including the totals query now give the results you expect
without any strange characters.

I don't know if you have a need for those 4 fields to be memo fields, in the
example you sent this wasn't the case, so hopefully this has fixed this
problem.
Thanks for sending the tables and queries.

Jeanette Cunningham
 
J

Jeanette Cunningham

Thanks John,
I downloaded the database. As soon as I looked at the tables and saw 4 memo
fields I had a pretty good idea why your queries were giving some nonsense
characters. Now memo fields are well known for causing problems with some
queries, so I changed all the memo fields to text (as they only had about 30
characters).
Both queries including the totals query now give the results you expect
without any strange characters.

I don't know if you have a need for those 4 fields to be memo fields, in the
example you sent this wasn't the case, so hopefully this has fixed this
problem.
Thanks for sending the tables and queries.

Jeanette Cunningham
 
J

John Dumay

Hi Jeanette,

Thanks for your suggestion. Since I create these tables dynamically, how do
it set the field properties to "text" instead of "memo' when using a "Make
Table Query"?


Regards,


John Dumay
 
J

John W. Vinson

Hi John,

Please find following th SQL which to me looks OK.

SELECT tblBO_CrossTab_Column_Items.Column_Category,
tblBO_CrossTab_Row_Items.Row_Category, Count(tblBO_CrossTab_Row_Items.Score)
AS CountOfScore
FROM tblBO_CrossTab_Column_Items INNER JOIN tblBO_CrossTab_Row_Items ON
tblBO_CrossTab_Column_Items.ResponseID = tblBO_CrossTab_Row_Items.ResponseID
GROUP BY tblBO_CrossTab_Column_Items.Column_Category,
tblBO_CrossTab_Row_Items.Row_Category;
Click to expand...

If (as per your discussion downthread) Category is a Memo field, that's almost
surely the cause of the problem. Grouping by, joining on, or sorting memo
fields requires creating an Index on the field - and Access has to truncate it
to do so. All sorts of peculiar problems can result.

MakeTable queries are VERY rarely actually needed. If changing the Category
and other memo fields to Text, compacting the database, and putting
appropriate Indexes on these fields doesn't improve your query performance
enough (it *WILL* improve it!), and you still need temp tables, I'd create
tables with appropriate field definitions and indexes; empty the table and run
Append queries, rather than running MakeTable queries.

John W. Vinson [MVP]
 
J

John Dumay

Jeannette,

The problem I have, as stated earlier in the thread, was that these tables
are created dynmaically from an SQL data source. I suspect in the conversion
the SQL field converts to a 'Memo' rather than a "text" due to the data type
used in the native SQL Table (which I have no control of). I thought about
changing the mapping for the SQL datatype to "Text" instead of "Memo", but
there are many fields where I don't wan this to happen, so it is the
exception rather than the rule.

So my question is whether the "Memo" fields in the table can be changed to
"Text" fields using VBA. If that is the case I can get my code to do this,
then run the CrossTab or Totals queries? hving to change it manually after
the tables has been creatted is not an option for the end user of tis DB
appplication. I have done a search but can't really find anything on the
topic.

Thanking you in anticipation of your assistance.

John Dumay
 
J

Jeanette Cunningham

John,
Let's start with the process that creates your tables. Not quite sure what
happens in the process that creates your tables dynamically. Are you
importing from the native SQL table? Maybe you link directly instead? Can
you outline the steps the database goes through in creating the tables from
the sql source.

Jeanette Cunningham
 
J

John Dumay

Hi Jeanette,

The orignal source of my data is an SQL Database hosted on an external
server. I utilise MS SQL Sever Management Studio to export the Tables (140 of
them) to my MS SQL server on my Local PC so I have a duplicate database of
the source. I then utilise a Utilisty from EMS
(http://www.sqlmanager.net/products/mssql/dataexport) to export my data to an
Access database called results.mdb. My database (Reporting.mdb) links to ALL
of the tables in Results.mdb so that I now have full access to the tables and
contained data.

The data is a collection of survey results that I host for my clients and I
build customised reports based on answers to questions (business objects)
from the surveys.

The problem i have is that the original SQL datatype for this particular
field appears to be incorrect and will map as a "Memo" field and not a "Text"
field.

I create some standard tables in Access using "Make Table" queries because
many of the queries I require are highly complex and cannot be done using
simple queries and require several levels of queries. If I don't use "Make
Table" queries these take a LONG time to execute where as deleting and
recreating the individual tables in a stepped process in code really speeds
up the performance of the database the only drawback being the increased DB
size, but with todays capacity of PC's this is not really an issue
considering the performance improvement.

So if I had a bit of code to convert a Memo field to a text field it would
be fantastic, otherwise it is just another step in code (about 20 lines with
error checking) to build the tables with the Inner Join as individual records
and utilise that data for the CrossTab query.

I guess the real issue is how Access crashes whe you try to "Total" data
from Memo fields.

Also the real solution is the creator of the SQL DataBase (a third party) to
change the field to mimic a Text dtatype, but I am not to sure if they would
(or could) do this, so I am in some way stuck with what I have.

Regards,

John Dumay
 
J

Jeanette Cunningham

John,
thanks for replying. Glad we could find the reason for the gibberish and I
can see why you use make table queries. Sounds quite complicated.
Good luck with your reports.

Jeanette Cunningham
 
Top