Autonumber: adding dummy zeroes? starting the #'s at a given #? 2

[sarajini]

Hi,
I have a huge stack of records that I’d like to give ID’s by autonumbers.
Is there any way I can tell Access to make the autonumber have six digits? I think the best way to do this would be through adding dummy zeroes. (The first record should have the autonumber 000001; the 3600th record should have the autonumber 003600.)

I have a second stack of records that also needs autonumber ID’s.
Can I tell Access that the autonumbering should start at a specific number? (I want Access to start autonumbering these at 015000.)

Thanks!
Sarah

 

[psemianonymous]

1. Beware of autonumbers if you wish to have them be sequential, because they won't necessarily be as sequential as you like. Autonumbers were not designed to be meaningful, it's too bad they're as useful as they are, otherwise no one would attempt to make them meaningful.

2. Yes, set your Format property of the field to "000000" for six-digits.

3. There is no way to start at 15000 except to create EXACTLY 14999 records, delete them, and add your real records. See #1 for the reason why.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[sarajini]

Hi Foolio,
Thanks for the 6-digit tip. Whew, that was easy. The rest of what I have to do isn’t so easy : (

I work in a (very nice public-interest) law firm. We have a huge bunch of clients. Among themselves the clients have 9000 cases and 13,000 brief services. All the client records are in excel databases.

I want to give each client a unique ClientID number. The client’s autonumbers should be in rough (not perfect) date order. I’m hoping Autonumber can help me out.

The problem is that a client can have anywhere between 0-3 cases and 0-10 brief services. This is a pain because I want to make sure each client has only one ClientID. And I want to order the client’s ID’s based on the client’s date of first contact with us. The date of first contact will be the “opendate” for EITHER a case OR a brief service, whichever was earliest. Each case record or brief service record lists the client’s first/last name.

If I gave each record a unique ID, then a single client could potentially have many clientID’s. Bad. Here is my plan to solve that problem, so far:

[COLOR=003388]
1. Import cases and briefservices databases from Excel.
2. Make a query that list all records (whether cases or brief services) by OpenDate
3. Give every record an autonumber
4. Make sure this is all a table
5. Write query: return all records with same first/last name (must be a modifiable query).
6. Change autonumber to plain “number”
7. Go through query manually and replace the number for every client with the ex-autonumber given to the client's earliest service.
8. When entering records directly into access, we would have to enter the client ID ourselves.
[/color]

That looks pretty crude. Is there any earthly way to make Access give the same autonumber to people with the same first/last name? Can I fool around with queries to do this?

Maybe I could write a query after I change all the records from autonumber to number? Could a query select out all records where clients have the same first/last name? Let’s say Joe Schmoe has 3 records, and they have ClientID numbers of 5, 340, and 1000. Could the program automatically give each of his 3 records his earliest ClientID number (5)?


Whew…that was long.
Thanks in advance for any advice you might be able to give!!!

--sarajini, banging head against desk

 

[psemianonymous]

OK, here's where you start to see how Access is idealogically different from Excel--you don't have to store everything in a flat-file format. Forget the numbering system for now; it could work with some modification, but I think I have a better way. Here we go:

ASSUMPTIONS:
1. You can identify a client by their name or some other easy identifier. This identifier is entered into the Excel sheet EXACTLY the same every time. This means NO NAME MISSPELLINGS.
2. That was all.


WHAT TO DO:
1. Just import everything into a temporary Access table. This is the start point. I will call it "imported".
2. Go to the query part. Open a new query in design view.
3. Get your "imported" table.
4. Drag your client identifier (i.e. name) as one of the fields.
5. Drag your "date of contact" field as one of the query fields.
6. Make this a totals query by clicking on the sigma/summation/don't-know-what-else-to-call-it icon.
7. Leave your client identifier as "Group By". That's fine
8. Set your "Date of Contact" field to "Min"
9. On the line below "Min", select it to sort by "Ascending".
10. Now. Change this query to a Make-Table query.
11. Choose a table name (like "Clients&quot
12. Save the query. Run the query. You should have your table.
13. Add an autonumber field and it should sort your clients in order of earliest arrival date.


There is much more to do, like getting ALL the client info into this table and eliminating possible duplicate clients, and then linking the visits to the "client ID", but that should get you started.

 

[sarajini]

Hi Foolio,
Wow—those steps work pretty well. (I made a mock “import” table to try them out on.) After step 13 (add autonumber), I made a query out of a) the import table and b) the autonumber field I created. I linked the “firstname” and “lastname” fields.
Each client had the same unique autonumber. Thanks!

There’s just two hitches--when it comes to date-ordering the clients. I know that Access’s autonumbers aren’t perfectly sequential. But these seem like they should be pretty easy to work out.

1) I have two separate “contact date” fields, one for Cases and one for Brief Services. I want Access to work with the Min of either cases or brief services, whichever is earliest. I think my table is sorting once by the min of cases, and then sorting again by the min of brief services. How to fix this? I tried writing an sql query, but since I’m a novice at this (as shown below), it got syntax errors.

SELECT [mock import table].FirstName, [mock import table].LastName,
Min([mock import table].CaseDateOpen UNION [mock import table].BriefDateOpen)
AS MinOfAllServices
FROM [mock import table]
GROUP BY [mock import table].FirstName, [mock import table].LastName
ORDER BY Min([mock import table].CaseDateOpen), Min([mock import table].BriefDateOpen);

2) The nulls are a problem. Access seems to consider them as the earliest date possible. I want the opposite—for Access to treat nulls as latest possible date. This way, I hope, Access will just ignore nulls, getting the earliest date of all the existing dates. (This is important because client records may have nulls in either cases or brief services.)

Foolio—thanks for all your help. You rock.

sarajini

 

[psemianonymous]

Excellent.

Okay, you have two separate things, briefs and cases. You want to combine these two sets of dates and find the *combined* earliest date. Some dates are NULL, but that probably means that the date has not yet occurred, so these clients should get the last IDs possible. Here we go.

Know that in Access you can use queries as data sources for other queries, and this is a situation where you will want to use this feature.

1. Create a query which pulls the client name and the brief date. We will edit this query later to solve the NULL problem, but that will be later.

2. Create a query which pulls the client name and the case open date.

3. Create a UNION query which will combine the two queries you have just made. It will look something like "SELECT * FROM [qryQUERYNUMBERONEABOVE] UNION ALL SELECT * FROM [qryQUERYNUMBERTWOABOVE]".

4. You now have a query which pulls all of the dates. Now we'll tackle the null problem.

5. For each of the first two queries, you will need to use the Nz() function - this basically replaces any Null value with any other pre-set value. Use it like so:

CalculatedField: Nz([qryNUMBERONE]![CaseOpenDate],#1/1/3000#)

That will replace any empty/Null date with the year 3000.


Anyway, I think this is what you're looking for. Once you have your *properly sorted* list of clients and (more accurate) earliest contact date, you can now re-do the clients table with better results.



It's been fun helping you out--your questions are very well...I can't seem to find the words....very well thought out, I suppose.

 

[sarajini]

Hi Foolio,
Thanks again for helping out!!!
I'm glad that my posts aren't too unintelligible

The union statement hasn’t given me any trouble so far.
I am having problems with the SQL though; I don't know if I used the Nz() function correctly. I ended up with a new field with the nulls replaced, but the new field doesn’t treat its contents as dates.

I wrote this code into the query (“numberone”) that pulls the case date from the original table (“mock_import_table.”)

SELECT mock_import_table.FirstName, mock_import_table.LastName, mock_import_table.CaseDateOpen, nz([numberone]![CaseDateOpen],#1/1/3000#) AS NewCaseDateOpen
FROM mock_import_table;

When I ran this, Access asked me for a parameter for [numberone]![CaseDateOpen]. I left that blank…Access gave me a “NewCaseDateOpen” field of 1/1/3000 for EVERY entry.

So I changed

 nz([numberone]!CaseDateOpen], #1/1/3000#)

to

 nz([mock_import_table]!CaseDateOpen], #1/1/3000#)

In this code’s NewCaseDateOpen field, the actual open dates are stated again, and each null is replaced with 1/1/3000. (yay!)

At first, it looks perfect. The problem is that the NewCaseDateOpen field doesn’t appear to treat its entries as dates. When I run the query, the #’s don’t come up in the resulting datasheet. But when I tell Access to sort by NewCaseDateOpen ascending, the first dates are 1/1/3000 and 11/1/99, and the last date is 9/3/96.

Is there some quick code that would put the NewCaseDateOpen field in date format?

Again, thanks. I'd be completely disheartened about completing this thing without folks like you!!!

 

[psemianonymous]

Unfortunately, the union query is definitely causing problems. The date conversion to a "text" field is bad. Here's a way that will definitely (hopefully) fix the problem:

SELECT mock_import_table.FirstName, mock_import_table.LastName, mock_import_table.CaseDateOpen, Format(nz([mock_import_table]!CaseDateOpen], #1/1/3000#),"yyyy-mm-dd") AS NewCaseDateOpen
FROM mock_import_table;

Basically you take any date, and then format it so that as text it looks like "2003-07-15" which sorts properly. Do this for each of your 3 queries. Hopefully this will fix the problem.






----

If the above does not work, create a fourth query that pulls all information from the 3rd query (the UNION query) and AGAIN formats the date to "yyyy-mm-dd" format. Then you can use THAT query to sort on. So long as it works, it doesn't matter how ugly it is, right?

 

[sarajini]

No problem with the above code...
IT WORKS!!!! Woohoo!

Foolio is a god!

gratefully,
sarajini