Query gets corrupted when a criteria is specified?

[tangcla]

This is really bizarre... basically I've got a table which I've pulled a few columns out of, and grouped.
Without any specifying criteria, the results are fine... but if a criteria is specified, one of the columns will return garbage values, with Wingdings and Chinese (literally).

I've tried setting the query criteria both in an Access query and also as part of Excel's database query conditions, but both do the same thing...

 

[spaddhu]

Please specify the query and your table structure from which the query pulls data out.

 

[tangcla]

This is going to get interesting, short of me posting a copy of the table and query (is that possible here?)

Basically the table is constructed by a CSV, which is appended daily.

The field which gets corrupted, and also the field which I am specifying criteria to, is a text field.

 

[tangcla]

I've included a copy of the output, a filtered and unfiltered tab in a spreadsheet.

The criteria I have specified is on the Claim Status Cd column to show "APPR" and "PEND" results only.

 

[tangcla]

I've attached an extract of the table and the query.

 

[spaddhu]

I work with Access 97. I suppose, the file you have sent if of 03 or 07. So am not able to open it. Anyways I saw the excel sheet. I presume that Claim Rate Type is the text column that is troubling you.
To start off with, first check the table structure in your Access table. See if you have specified any Field Size, Format Mask for the Text fields.

 

[spaddhu]

Next in the query, how have you specified the criteria?

In ("APPR","PEND")

Like this?

 

[tangcla]

Next in the query, how have you specified the criteria?

In ("APPR","PEND")

Like this?

That's how it was in Excel; in Access the SQL is HAVING (((CLMS004.[Claim Status Cd])="APPR" Or (CLMS004.[Claim Status Cd])="PEND")).

 

[spaddhu]

I see that you have grouped columns haven't you??
That being said, just try overwriting the SQL generated in Access from Having(((CLMS004.[Claim ...)) to Having (CLMS004.[Claim Status Cd] in ("APPR","PEND"))

Also if possible try converting the database to prior version or something, and reattach so that I could see it.

 

[spaddhu]

Also if possible, just copy paste your SQL query generated by Access. You will be able to see this if you select SQL view from the View menu.

 

[tangcla]

I tried changing the SQL code but same result.

Here's the SQL code. The strange thing is, it was working fine on Friday, but today it no longer works after I appended today's CSV - I've checked the table contents and nothing looks corrupt?

SELECT CLMS004.[Site Cd], CLMS004.[Claim Rate Type Cd], CLMS004.[Claim Rate Type], CLMS004.[Claim Status Cd], CLMS004.[Claim Status], [Date no timestamp].PdWk, Sum(CLMS004.[Claim Amount]) AS [SumOfClaim Amount], Count(CLMS004.[Claim ID]) AS [CountOfClaim ID]
FROM CLMS004 INNER JOIN [Date no timestamp] ON CLMS004.[Creation Date] = [Date no timestamp].Date
GROUP BY CLMS004.[Site Cd], CLMS004.[Claim Rate Type Cd], CLMS004.[Claim Rate Type], CLMS004.[Claim Status Cd], CLMS004.[Claim Status], [Date no timestamp].PdWk
HAVING (((CLMS004.[Claim Status Cd])="APPR" Or (CLMS004.[Claim Status Cd])="PEND"))
ORDER BY CLMS004.[Site Cd], CLMS004.[Claim Rate Type Cd];

 

[tangcla]

btw, Claim Rate Type field type Memo.

 

[spaddhu]

Huh huh... Memo field has that peculiar behavior. Sometimes, when the number of rows in your csv increases beyond a threshold (I think it is something around 65000 records), it starts behaving odd. Just try deleting your latest data in the source and recheck if the query works fine.

 

[spaddhu]

This seems to be a standard bug with Access. Refer to this site. It provides the solution too.

http://allenbrowne.com/bug-18.html

 

[Galaxiom]

The immediate problem is you have used a memo field instead of text. Why it makes Chinese is indeed mysterious as I know nothing about memo field type. (Another reason to continue avoiding memo fields I think.)

However your database has other major structural issues.
[Claim Rate Type] should be stored as a code rather than text and looked up as required.
[Claim Status Cd] should be a Y/N filed unless there are more statuses to come in which case it should also use a numeric code and a lookup on forms and reports.

[Date no timestamp] table should not even exist.
Instead the other field values should be drived from the date as required using a expression or function.
See DatePart function.
http://office.microsoft.com/en-au/access/HA012288121033.aspx

 

[Galaxiom]

I can now see there are other statuses. However there is absolutely no point storing both a code and the full name in the main table. Indeed this a normalization failure.

Ditto storing [Claim Rate Type] and its code.

It is marginal with a four character code but I would consider putting all these code and name combinations in lookups and using a number ID. Access is really quick relating with numbers and it saves hassles with having to use quote marks around the strings in queries and code.

And do drop the spaces from your names.
[ClaimRateType] rather than [Claim Rate Type]
You don't have to use the [] then.

 

[tangcla]

Thanks guys for your input - indeed it was because of the memo field. Changed it to text and all is well.

Galaxiom: the reason the table is there is because the CSV that I use to append includes this field. If I were constructing the table from scratch, I would definitely have the field linked to an off table. Same reason why the field names have spaces in them - it's so that if ever I need to use the original field, then I can use the standardised names.
Also, with [Date no timestamp], is because I link the date through to period and week references. There's yet another anomaly which involves the CSV having a date format of d/mm/yyyy hh:mm AM/PM, which Access does not like, so I (stupidly) have several date tables to link to - the CSV source files are generated from a system which is quite stupid, and I sadly have no control of the output.

 

[Galaxiom]

Also, with [Date no timestamp], is because I link the date through to period and week references. There's yet another anomaly which involves the CSV having a date format of d/mm/yyyy hh:mm AM/PM, which Access does not like, so I (stupidly) have several date tables to link to - the CSV source files are generated from a system which is quite stupid, and I sadly have no control of the output.

Usually the first step when importing poorly constructed data is to convert it to standard formats and normal forms. I would not even import the extraneous fields except to maintain a translation table of the Descriptions and Code as new ones appear.

As you have the codes supplied from another source I would keep these alphanumeric codes but get rid of the full Description stored in every record.

Any date format can easily be converted to a standard one or any other (including the period-week format you require ) provided there is some kind of underlying pattern. It is simply a case of working out the translation expression. Your period-week format does not look particularly difficult. Access has a plethora of date and string manipulation functions.

The problem with looking up a table to return the period from a date is that it requires considerable maintenance or the table runs out of records and the system falls over. It can also store an incorrect entry. Once you have created the conversion function no further maintenance is required and the output is absolutely reliable.

 

[tangcla]

Thanks for that. Sometimes I use the actual date and other queries use periods or weeks though.

I guess the reason i've left the dates as it is, is because a lot f the other reports are just linked as CSV files and not imported into Access. I know if I were to import then i can change the format easily enough, but if they remained just linked text files, what can I do about the date formats? The reports are generated every morning.

 

[Galaxiom]

Access can only append records to linked text files so updating the date format on the original is not achievable. Also when importing data it is best to leave the original file untouched for the sake of maintaining an audit trail.

I would import the file and rewite it to a compliant structure as you already realised could be done.