Counting

[Chrisopia]

Hi there, I am not very good at the count query and have tried many tutorial websites and forums to figure this out.

I have a list of company names, and a list of last names, I have created a query that filters through, taking the first 3 letters of the company name, and if it doesnt exsist it will take them from the first name.

I have asked this forum before, but I am trying a slightly different method now, if I could only understand the Count query a bit more... even if VB will help?

Example below: (where Customer ID is the column I am trying to fill in, I already have entried in the other columns for 1300 contacts)

------------------------------------------------------------------
| ID   |  Company Name   |  Last Name   |  Name ID   | CustomerID |
====================================================
|    1 |  Builders R Us       | Smith          |  BUI          |  BUI001      |
------------------------------------------------------------------
|    2 |                          | Smith          | SMI          |  SMI001      |
------------------------------------------------------------------
|    3 | Building Comp       | Walker         | BUI          |  BUI002       |
------------------------------------------------------------------
|    4 | Smith and Co       | Smith           | SMI         |  SMI002      |
------------------------------------------------------------------

I'm sure you get the idea...

any help please???

(Sorry if table looks weird, hope you can understand it?)

 

[khawar]

What do you want to count

 

[Chrisopia]

the NameID, counting upwards uniquely

the example there showed:
BUI001
SMI001
BUI002
SMI002 and so on...

 

[DCrake]

To fully grasp what you are trying to achieve you need to understand the following possibilities.

If you cound the number of customers whose first 3 letters of their name are the same you may not get an answer beacuse there are none.

Therefore your customer Id would be ABC001

If it counts 5 you would expect the next customer id to be ABC006, however you may have has a company whose customer ID was ABC003 but they went out of business and you have deleted them from your database. You already have ABC006 so you would get a duplicate ID. Therefore you need to use the DMax() instead of DCount() that way you can safely assume that the next default number is unique.

Also what happens if your new company is the AA or o2 they only have 2 digits or characters.

Also what happens if an existing company changes their name? Unlikely but it can happen, I had to change my company name recently. This would mean that you would have to build this senario into your admin tools and the issues surrounding this functionality can be large.

If you are planning on using the compound id as a primary key I would shy away from that and use an auto number instead.

David

 

[Chrisopia]

Ah, I see.

I would consider using DMax, but was unsure how to work it, and I have tried really hard to figure it out. It seemed to retreive the highest value, but then the numbers weren't being saved, and it just kept updated them all:

BUI001 or

BUI002
BUI002 or

BUI003
BUI003
BUI003n

which is very annoying?

2 Digit scenario is rare in which case I would prefereably use two digits instead of three.

A change in name wouldn't affect the database. Whatever customerID is given it must stick unless the customer also changed address too, then it would be considered a completely different company.

We are not going to use it as a primary key, it is for filing purposes as the ID will link to a code used in the filing system, were we keep samples of customer jobs etc.

An alternative would be to Enter them as the customer arrives, but use some sort of check to tell you what number you're upto for that particular combination of letters

 

[DCrake]

Try This:

Public Function GetNextCustomerID(Prefix As String) As String

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset(Select * From Customers Where Left(CustomerID,3) = '" & Prefix & "' Order By CustomerID Desc;")

If Not Rs.EOF And Not Rs.BOF Then
    GetNextCustomerID = Prefix & Format(Val(Right(Rs("CustomerID")),3),"000")
    Rs.Close
Else
   GetNextCustomerID = Prefix & "001"
End If

Set Rs = Nothing

Save this to a module (not the same name) then open the immediate window and enter:

?GetNextCustomerID("BUI")

To use in you app

Me.CustomerID = GetNextCustomerID(Left(Me.CustomerName,3))

Remember this is aircode and as such it is untested. Field and table names are for brevity, make sure you use your own names.

David