Query on filed with numbers and letters

[n4467r]

I have a database for a direct mail project that contains a field to identify the specific letter for each recipient. This field contains both numbers and numbers with letters. Example: 21353231 and 33233021BOA.

I need a query to identify the total number of records processed for each letter type. When I set this up it returns the record counts for the letter codes containing only numbers values.

Thanks for your help!

 

[dhookom]

First, tables contain fields, not databases. It would help if you identified the exact field names and provided several records. Then show us the resulting display that you would expect/like returned.

Can you show us the SQL view of the query that didn't work?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[n4467r]

Sorry, I am new at this. My table contains a database of records pertaining to a direct mail project. There are over 800,000 names (or records) included and each record is assigned a “letter Code”. This letter code field contains both numbers and letters such as:

Letter Code
21353231
21323241
21323251
33233021BOA
3323405NORD
33233011DOW


A simple query (using wizard) to retrieve counts by “Letter Code” returned counts for the first 3 items only. In query design view I tried to assign the mixed items as “criteria” and no values were returned. Perhaps there is formatting or something that I need to use so the query will recognize the items with numbers and letters?? They are currently assigned as text in the Table Design.

Thanks…
David

 

[PHV]

Something like this (SQL code) ?
SELECT [Letter Code], Count(*) As CountOfLetter
FROM yourTable
GROUP BY [Letter Code]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

David,
You still didn't answer my question

Then show us the resulting display that you would expect/like returned.

PHV took a guess but this seems to rudimentary. I could be missing something.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I think so too Duane.

 

[n4467r]

Sorry for the vague question. It seems that the root of the problem comes from the format on the imported field "Letter Code". With a general format in excel, it viewed some as numbers and some as text so importing that file to Access seemed to confuse the query. When I converted the excel file field to text an did the import, it seems to work fine.

Thanks for hanging in there with me!

David

 

[lespaul]

if you still need help (and the next time you do need help), be sure to include in your post a section that states:

Using the data above, I want to know the number of records where the first 6 numbers are the same. In this case I want to return:

Letter Code     Count
213532          1 
213232          2           
332330          2
332340          1

It's much easier with a visual result to figure out exactly what help you need.

Leslie