Query to count records in both number and text format

[smlcfc]

Hi All,

I have an XL link to a table in Access. The table has a field called CLAIMREF that I need to count. I need to split the count depending on the length and also have a total volume aswell. So, I need a query that returns 3 fields...1 showing total records where the lenght of CLAIMREF is 7 or less, 1 which shows total records where the length of CLAIMREF is more than 7, and 1 which shows total.

First I tried the below expressions:

Sum(IIf(Len([CLAIMREFNUMBER])<=7,1,0))


Sum(IIf(Len([CLAIMREFNUMBER])>7,1,0))
CLAIMREF (a simple count)

The numbers didn't add up - I realised the CLAIMREF column contains numbers and text in XL. So I tried this:

Sum(IIf(Len(Format([CLAIMREFNUMBER],"Text"))<=7,1,0))
Sum(IIf(Len(Format([CLAIMREFNUMBER],"Text"))>7,1,0))
CLAIMREF (a simple count)

I still can't get my expressions to count all records and therefore the totals do not add up.

You may have gathered I am fairly inexperienced with Access so any advise on how I could achieve this would be very much appreciated!

Thanks

[smithse]

Hi smlclc,

We were all new at Access once.

Have you got a few rows of sample data? That might be easier to figure out how to help you. Sometimes, more work works better than a complex formula to achieve what you are trying to do. In your case, an example could be creating a column in an Access query which flags whether or not the CLAIMREF field is longer than 7 characters, and one column which flags whether or not it is less than 7 characters, and summing those columns. Assuming your formula is correct here, your 'sums' will add up.

[smlcfc]

Sample data below. I need to be able to count how many rows have more than 7 digits and how many do not (the two different lengths signify different categories).

20120919000538
20120919000546
20120919000949
20120919000957
20120919000967
5723224
5721436
5720596
5720540
5722408

[smithse]

Hi smlclc,

You could have a single query, but for ease of understanding, I have broken it into two queries. The second relies on the first.

Query1, counts the records based on the length of the value: (note it assumes the name of your table as 'Table1')

SELECT Table1.CLAIMREF, Len([CLAIMREF]) AS CRLEN, IIf(Len([CLAIMREF])=7,1,0) AS ShortLen, IIf(Len([CLAIMREF])>7,1,0) AS LongLen
FROM Table1;

The second query returns the sum of the first, and this one is just names Query2 though it won't change anything you're doing:

SELECT Sum(Query1.ShortLen) AS SumOfShortLen, Sum(Query1.LongLen) AS SumOfLongLen, Count(*) AS AllClaims
FROM Query1;

As I say, it could be combined into one query, but I hope this helps you see for now.

[smlcfc]

Thanks smithse,

I'm a little confused - this looks like a SQL query. I am looking to build my query using the Access wizard.

The above statement is declaring variables etc which I am not familiar with in Access.

Sorry if I'm not quite catching on! Appreciate the help.

Thanks,
smlcfc

[smlcfc]

Also - my point is that even though I am able to successfully calculate the len of all records and sum accordingly, this is not returning the correct result as some of the records are in different formats.

I think I need to reformat the fields I am counting which I have tried to do as text however this still doesn't seem to be working.

Thanks

[smithse]

Hi,

Can you explain the different 'formats' a bit more. The Len() function works regardless of letter or number, so have you got some more examples of the format that these values are stored in? It seems odd that the Sum() if not correctly counting.

Are you able to post your SQL statement here?

[smlcfc]

SELECT Format([DATE OF PRINT],"YYYYMM") AS [Month], Sum(IIf(Len([CLAIMREFNUMBER])<=7,1,0)) AS UoU, Sum(IIf(Len([CLAIMREFNUMBER])>7,1,0)) AS ToU, Count(Tbl_Disputes_New_Answers.CLAIMREFNUMBER) AS Total
FROM Tbl_Disputes_New_Answers
GROUP BY Format([DATE OF PRINT],"YYYYMM")
HAVING (((Format([DATE OF PRINT],"YYYYMM"))>="201201"));

Not sure why this isn't working. But it isn't including some of the records in the sum.

The column in XL does seem to be all formatted as text, however some of the cells show the 'number stored as text' warning.

[smithse]

Hi smlcfc,

It appears you might have an issue with the format of the date fields. MS Access always uses US formatting for dates, and can cause issues there for a start.

Also, using reserved names like Month as a field name, is a no no... Try something like PrintMonth. Avoid column names like Month, Day, Date, Time etc. Access always get upset! LOL

Also, your format statement returns a value that can be either a string or a number. If is returns a number and your are querying it for a "string" value of > "201201" it will get it's knickers in a knot.

As painful as Access makes dates at times, convert your [DATE OF PRINT] to a valid Date type, and query it accordingly. This way your [DATE OF PRINT] field can be queried a bit easier too. For example, if you wanted to query it, (as you have in your example) for anything later than January 2012, you would used the criteria '>#31 JAN 2012#'

I've done a lot of swearing and cursing for things like you are trying to achieve!

[smlcfc]

I use this formatting for dates in all of my queries and never have any issues.

I think the issue is with the formatting of [CLAIMREFNUMBER]. I think I need to force Access to change all values in this field to equal format before summing the 1,0's. However I am just unaware of which function I should use to do this. I have tried formatting all as text however this is not working.

Thanks again for your help smithse

Regards,
smlcfc

[smithse]

No probs,

Do you have any examples of the fields which are formatted differently, apart from the samples listed above? Are there any trailing or leading spaces, or spaces in the middle? I'm just grasping at straws at the moment...