count certain records?

[bionicman]

Hello everyone, i got a quick question. Seems difficult to me, but it probably is not.

I want to have the count of certain records be returned to me on a report. Basically, i have a unique number that i input for each record (ABC15723652, CLG17893215). each one is 3 alpha, then 8 numeric characters. I eventually want a report that will tell me the number of ABC records, and the number of CLG records.

like this:
ABC = 7
CLG = 5

I don't care about what the number behind the alpha character is, so i know i will have to use a wildcard, but i need to find the count of the records.

Any help would be appreciated, i hope i explained simple enough.

Thanks
BionicMan

 

[FoFa]

I would base your report off a query, and as part of that query use a calculated column of AlphaPart: LEFT(MyCol,3) if using the QBE or in SQL LEFT(MyCol,3) AS AlphaPart

Then you can use this in your report as another field, setup a group on it.

 

[bionicman]

Could you explain this a little more? I have not used access in a while, and don't remember a whole lot (that goes for coding too).

Thanks in advance.

 

[FoFa]

Create a query, pull all the fields you want in your report into that query. After you do that, create a new calculated column (just go to a blank column in the query) and put in AlphaPart: LEFT([MyCol],3) and you will have a new column of just the 3 first characters of MyCol (I don't know what your column name is that contains that ALC8347383 value). Basically it says, give me the left most 3 characters of MyCol. So if MyCol contains AGB12345678, it would return AGB. Now you can use report wizard to create a report off of this, and when the report wizard asks for a group, group on this new column (AlphaPart) and it will give you a count in the group footer for each unique value of AlphaPart.

 

[KKilfoil]

Another way to go, particularly if you need to separately use the alpha and numeric parts of your field...

Your 'aaa########' format field could also be replaced with two separate fields, one for the 'aaa' part, and one for the 8 digits.

Then searching or counting the 'prefix' part is simple using standard access functionality.

The two fields can be 'merged' in a query by creating a calculated field like

CompoundField : [PrefixPart] & [NumbericPart]

and use this query value wherever you presently need your exisitng field.

If you wish to guarantee uniqueness, define an index using both of these fields in your table.

 

[bionicman]

I'm almost there

Fofa,
first off, thanks for the help so far, i have just about what i want. my only problem is when i run the report it comes up as follows:

ABC
ABC11111111 7/22
ABC11111112 7/22

GLH
GLH111111111 7/22
GLH111111112 7/22

(yes, i made it return the date it was entered and show me the number also). But i am not getting the count of them, which is what i need. any ideas?

 

[FoFa]

There is a count records function (do not remember what it is off the top of my head) in reporting. I don't use reports too much except for really simple stuff, so I may not be the best person to ask on a report.

 

[bionicman]

I figured it out, thanks again for all the help!