Entry order vs autonumber order?

[StargateFanFromWork]

I was speaking with my supervisor today. She knows much more about A2K than
I do as she's been sent on a few courses (we temps don't get sent on
courses, you see! <g>). She was explaining the reason why I ran into a
problem yesterday and today and I'd like to know if she's correct, pls?

I imported an Excel spreadsheet into Access. The autonumbering field worked
fine and everything was hunky-dorey. The fields were correctly labelled as
I'd initially sorted the Excel file into the order needed before the import.
My supervisor then gave me more data much later on and I entered this into
the new db. When the records were subsequently sorted according to the
important field, which I'll call the sorting field, the autonumbering was
off. No problem. At least, this has never been a problem before. Now my
supervisor needs both sets of data to be in order <sigh> (wish I'd known
that before that this was the one time where this was critical).

So, I took out the autonumbering to try to fix this and because subsequent
fiddling with trying to import a missing field, etc., etc., created gaps.
No problem, I found that by removing the autonumbering and putting it back
in reset the numbering sequence. I imagine this is a very rough workaround
by this ng's standards <g>, but it did the trick. But the autonumbering did
not follow my sort order, it seemed to revert the sort back to the entry
data sequence and the autonumbering reflects that. The two fields don't
ascend in accordance with one another. She said today that this is Access
behaviour and that this is inviolable. Is this the case? I just need to
make certain.

If this is, indeed, true, then I will have to try the dmax tip I was told
about yesterday. There should be no gaps to contend with, at least
initially, and the numbered field this creates hopefully will follow the
initial sorting order if I figure it out and write the code properly
<fingers crossed>.

If not, pls advise if there is a way to reset the autonumbering so that it
reflects a specific sort order vs an entry sequence order. I know I'm
probably hoping for the impossible here, but I don't want to take just her
word for it that this can't be done. The experts are in this ng here and I
must not leave any stone unturned until I know for certain whether or not
this behaviour can be modified or not.

Of course, once everything is entered and the db is ready to use, we'll be
up to date and new entries will be alright. It's just not to have to start
all over again with importing a complete and properly sorted sheet <phew>.

Thanks a million! D

 

[Mike Revis]

Well, as many people have pointed out in this ng, the autonumber feature of
Access is not to be used for sorting purposes.
It is a method that Access uses to *almost* guarantee a unique record in a
particular table.
If you need a field to sort on you have to create it.
If you need sequential numbering of records you have to create a field that
sequentially numbers records.

Mike

 

[Ed Warren]

You need to 'free' yourself from the spreadsheet thinking and think
database.
The database is just a 'heap' of data. The AutoIncrementing field is there
just to make sure you have an unique value to identify a specific row. It
should not serve any other purpose.
Like a good servant it should do its work in the backgound and remain
invisible.

With a properly designed database you can select/combine/sort data at your
pleasure to return the 'information' needed in the form and format required
by the user.

Some items that may require more information, so people can actually help
is:

You mention two sets of data, are these entered in the same table? different
tables? related tables?
If they went into the same table, then not statement "both sets of data need
to be in order" does not make sense, since you said the sort using 'the
sorting field' worked fine.

So a thouough discussion of the actual structure of the database might be in
order.

Ed Warren.

 

[StargateFan]

You need to 'free' yourself from the spreadsheet thinking and think
database.

True. And this will happen given time as I become more familiar with
A2K.

The database is just a 'heap' of data. The AutoIncrementing field is there
just to make sure you have an unique value to identify a specific row. It
should not serve any other purpose.
Like a good servant it should do its work in the backgound and remain
invisible.

Understood. It's just that anything to replace it is not as easy to
implement for the newbie. That's what I'm trying to do now.

With a properly designed database you can select/combine/sort data at your
pleasure to return the 'information' needed in the form and format required
by the user.

Right again and understood.

Some items that may require more information, so people can actually help
is:

You mention two sets of data, are these entered in the same table? different
tables? related tables?

True. Hard to know what will muddy the issue and what will not.

Unlike other times in similar situations, my supervisor wants the
records in alphanumeric order to be put in a binder but all in their
original order. For people to easily and quickly find these records,
despite the fact that in theory these forms will then all be in
alphanumeric order and easy to flip through, we'll need to create an
index of the forms and these will need to be separated by tabs with a
reference to the tab # in the index sheet. In the past, my supervisor
has never cared about the physical order of what we are indexing since
the index was in order, but this time that is not the case. She
doesn't know about using a different numbering system, btw, instead of
autonumbering so it will be good to implement this on many grounds.

What she is asking for is this:

Tab 1 Form AB1
Tab 2 Form AB2
Tab 3 Form BC1
Tab 4 Form BC2
Tab 5 Form BC3
Tab 6 Form CD1
etc.

But by not receiving all the forms at the same time for input, the tab
# (currently as an autonumber) and form names are not in order
together. Sorting by one throws the other out of sequence, and vice
versa.

So, we can't use the autonumbering and need to come up with something
that will generate that for us. It's all very well and good to say
not to use it, but wish it were as easy to generate a numbering field
that would work that was just as easy to set up and implement <g>.
That's the next step to figure out.

The problem with the dmax code that was talked about yesterday is that
it does not cover the problem of deleted records, at least, that's the
impression I got from the thread. We've had 2 cases at work where
records were chewed up. We've lost a small handful due to an import
problem, to a system crash and to not understanding at first that once
# is generated, if you leave record blank by accident, record is lost.

So hopefully deleted records can be compensated for with whatever
resolution for the above is recommended without messing up the
numbering.

If they went into the same table, then not statement "both sets of data need
to be in order" does not make sense, since you said the sort using 'the
sorting field' worked fine.

Again, hard to describe something, eh, using words ...

So a thouough discussion of the actual structure of the database might be in
order.

Hope the above helped re the order? Pls let me know if still unclear
and I'll try again.

Thank you. Cheers. D

 

[Ed Warren]

The state of affairs, as I understand it is:

You now have a data table with the following data in the table (showed row 4
missing) and you used the autoinc to generate what you thought would be the
correct tab number and it has failed.

Table1 Tab_AUTOINC Tab:REAL(not in data table now) Form importsession
missingdata
1 1 AB1 1

2 2 AB2 1


**missing** 3 missing BC1
4 4 BC2 2

5 7 CD2 2

6 6 CD1 2

7 5 BC3 2



If you know without a doubt that the proper Tab number is associated with
the Form Code (1:1), then you may be in luck. If not .... Post back with
more informaton.

First find and correct the missing Form Codes -- add them to the table and
don't worry about the auto-inc tab number.
You just need to make sure ALL of your forms are in the database. (add a row
for BC1)

Now you have a table will ALL the required forms, but the 'tab' number is
wrong.

Build a make table query, sorted on the FormField

----------Example SQL code------------
SELECT Table1.Form INTO temp
FROM Table1
ORDER BY Table1.Form;

Open the TEMP table in design mode.

add a key field: PhysicalTab (autoinc)

Save and open.

Now you should have a table with the correct tab numbers and forms related
to each other with the tab number in proper order for the form.

Now go back to your DataTable (in my case Table1). Add a field ::
PhysicalTab (number)

Save the table

build an update query based on the temp table and table1, joined on the
Form, updating the Table1 field PhysicalTab with the temp table value for
the physical tab.

-----query sql---
UPDATE temp INNER JOIN Table1 ON temp.Form = Table1.Form SET
Table1.PhysicalTab = [temp].[physicaltab];

Now you have the tabs and forms matched and can delete the autoincrement
field and use the physicaltab field as your primaryKey and use the dmax
methods discussed earlier to maintain the keys.

Ed Warren.

 

[Ed Warren]

Looks like my nice example table does not look so good in a text only form.

Read the Table1 stuff as
Table1
first column: Tab_autoincremented
second column: Tab_real
third column: Form
fourth column: ImportSession (1,2)
fith column: missing data

Sorry about the mess

Ed Warren.

The state of affairs, as I understand it is:

You now have a data table with the following data in the table (showed row
4 missing) and you used the autoinc to generate what you thought would be
the correct tab number and it has failed.

Table1 Tab_AUTOINC Tab:REAL(not in data table now) Form importsession
missingdata
1 1 AB1 1

2 2 AB2 1


**missing** 3 missing BC1
4 4 BC2 2

5 7 CD2 2

6 6 CD1 2

7 5 BC3 2



If you know without a doubt that the proper Tab number is associated with
the Form Code (1:1), then you may be in luck. If not .... Post back with
more informaton.

First find and correct the missing Form Codes -- add them to the table and
don't worry about the auto-inc tab number.
You just need to make sure ALL of your forms are in the database. (add a
row for BC1)

Now you have a table will ALL the required forms, but the 'tab' number is
wrong.

Build a make table query, sorted on the FormField

----------Example SQL code------------
SELECT Table1.Form INTO temp
FROM Table1
ORDER BY Table1.Form;

Open the TEMP table in design mode.

add a key field: PhysicalTab (autoinc)

Save and open.

Now you should have a table with the correct tab numbers and forms related
to each other with the tab number in proper order for the form.

Now go back to your DataTable (in my case Table1). Add a field ::
PhysicalTab (number)

Save the table

build an update query based on the temp table and table1, joined on the
Form, updating the Table1 field PhysicalTab with the temp table value for
the physical tab.

-----query sql---
UPDATE temp INNER JOIN Table1 ON temp.Form = Table1.Form SET
Table1.PhysicalTab = [temp].[physicaltab];

Now you have the tabs and forms matched and can delete the autoincrement
field and use the physicaltab field as your primaryKey and use the dmax
methods discussed earlier to maintain the keys.

Ed Warren.

True. And this will happen given time as I become more familiar with
A2K.


Understood. It's just that anything to replace it is not as easy to
implement for the newbie. That's what I'm trying to do now.


Right again and understood.


True. Hard to know what will muddy the issue and what will not.

Unlike other times in similar situations, my supervisor wants the
records in alphanumeric order to be put in a binder but all in their
original order. For people to easily and quickly find these records,
despite the fact that in theory these forms will then all be in
alphanumeric order and easy to flip through, we'll need to create an
index of the forms and these will need to be separated by tabs with a
reference to the tab # in the index sheet. In the past, my supervisor
has never cared about the physical order of what we are indexing since
the index was in order, but this time that is not the case. She
doesn't know about using a different numbering system, btw, instead of
autonumbering so it will be good to implement this on many grounds.

What she is asking for is this:

Tab 1 Form AB1
Tab 2 Form AB2
Tab 3 Form BC1
Tab 4 Form BC2
Tab 5 Form BC3
Tab 6 Form CD1
etc.

But by not receiving all the forms at the same time for input, the tab
# (currently as an autonumber) and form names are not in order
together. Sorting by one throws the other out of sequence, and vice
versa.

So, we can't use the autonumbering and need to come up with something
that will generate that for us. It's all very well and good to say
not to use it, but wish it were as easy to generate a numbering field
that would work that was just as easy to set up and implement <g>.
That's the next step to figure out.

The problem with the dmax code that was talked about yesterday is that
it does not cover the problem of deleted records, at least, that's the
impression I got from the thread. We've had 2 cases at work where
records were chewed up. We've lost a small handful due to an import
problem, to a system crash and to not understanding at first that once
# is generated, if you leave record blank by accident, record is lost.

So hopefully deleted records can be compensated for with whatever
resolution for the above is recommended without messing up the
numbering.


Again, hard to describe something, eh, using words ...


Hope the above helped re the order? Pls let me know if still unclear
and I'll try again.

Thank you. Cheers. D

 

[StargateFan]

The state of affairs, as I understand it is:

You now have a data table with the following data in the table (showed row 4
missing) and you used the autoinc to generate what you thought would be the
correct tab number and it has failed.

Okay, this is just a scenario and you're saying that as an
representation, example "BC1" got deleted. Gotcha.

Table1 Tab_AUTOINC Tab:REAL(not in data table now) Form importsession
missingdata
1 1 AB1 1

2 2 AB2 1


**missing** 3 missing BC1
4 4 BC2 2

5 7 CD2 2

6 6 CD1 2

7 5 BC3 2



If you know without a doubt that the proper Tab number is associated with
the Form Code (1:1), then you may be in luck. If not .... Post back with
more informaton.

In the scenario so represented, that would be correct. We'd know when
A2K chewed up a number for whatever reason as there'd by a form

First find and correct the missing Form Codes -- add them to the table and
don't worry about the auto-inc tab number.
You just need to make sure ALL of your forms are in the database. (add a row
for BC1)

Now you have a table will ALL the required forms, but the 'tab' number is
wrong.

Build a make table query, sorted on the FormField

----------Example SQL code------------
SELECT Table1.Form INTO temp
FROM Table1
ORDER BY Table1.Form;

Open the TEMP table in design mode.

add a key field: PhysicalTab (autoinc)

Save and open.

Now you should have a table with the correct tab numbers and forms related
to each other with the tab number in proper order for the form.

Now go back to your DataTable (in my case Table1). Add a field ::
PhysicalTab (number)

Save the table

build an update query based on the temp table and table1, joined on the
Form, updating the Table1 field PhysicalTab with the temp table value for
the physical tab.

-----query sql---
UPDATE temp INNER JOIN Table1 ON temp.Form = Table1.Form SET
Table1.PhysicalTab = [temp].[physicaltab];

Now you have the tabs and forms matched and can delete the autoincrement
field and use the physicaltab field as your primaryKey and use the dmax
methods discussed earlier to maintain the keys.

[snip]

Okay, the solution above sounds extreme because that might take care
of things _this_ time, but what about future deletions? And it has
happened. The above steps would fix this _now_ perhaps, but as an A2K
newbie, I could just as easily and with a lot more ease at this stage
of the game simply export the table as it stands, remove the numbers
created by the A2K's autonumbering and re-import in to a whole new
table and add autonumbering for Access (and then I guess figure out
how to add this dmax code in to our particular situation). From an
expert here that probably sounds like a terrible way to go about this
but I haven't yet successfully carried out an append query let alone
done the sql thing, etc. <g>. I will one day, but doesn't look like
today.

However, despite all that, the above doesn't sound like it would
address future similar record losses, would it in a way that involves
little user intervention as I won't always be here?

The trouble is, if a record got chewed up, as happens to another db at
the office, it isn't as easy to physically account for it in this new
situation. In the old big db, if the above happens, we create a new
record, put the removable tab on a blank docket copy and stick that in
the binder with a notation re-directing users to new location of that
docket and its new number. But as mentioned, I'm restricted this time
around and I can't take that approach. We are required in this new db
to keep the tab order to always reflect the form order (what a drag!).
I don't know why, that's just what I was told. Talk about making life
difficult <g>. So there is no physical workaround this time.

I don't know. The more I think about this, the more it seems that
there isn't going to be a programmatical solution that users will
easily be able to understand and do. I might be a newbie, but I am a
power user with a lot of other experience under my belt and I could
handle it but others that come in will be true newbies <sigh>. That's
always the case.

Maybe the best solution is to take an approach that I would take in
Excel when confronted by something like this: we'll just have to
create the numbers manually by using a simple text box. I can't see
any other way to do this.

And why not? This could work. If I can't fix this, my supervisor has
already told me to go back to Excel. But I know from past experience
as this is my second time back in this dept., what the temps in
between have done to my Excel files and the consequences to the dept
have been quite tremendous! They messed up quite a few of our record
binders in 6 short months, so Excel is just _not_ the way to go.

Thanks for all the wonderful help. I think that I've hit on a
solution that will work in the real world for this particular
situation since we're so restricted.

Cheers! D

 

[StargateFan]

Looks like my nice example table does not look so good in a text only form.

You're very kind. I understood it just fine <g>.

p.s., I just posted response a moment ago to the earlier post.

Read the Table1 stuff as
Table1
first column: Tab_autoincremented
second column: Tab_real
third column: Form
fourth column: ImportSession (1,2)
fith column: missing data

Sorry about the mess

<g> It was just fine. That happens all the time. Posts always come
out different from what we type, eh?

Thank you so much for your time. I really appreciate it. I've come
to a conclusion, as stated in the post I mention above, for this
current situation but I'm going to study this other approach on the
weekend. Must get a handle on the dmax thing and the append query and
SQL. I think I'll be in much better shape once I learn how to
successfully carry out these operations.

Thanks! D

Ed Warren.

The state of affairs, as I understand it is:

You now have a data table with the following data in the table (showed row
4 missing) and you used the autoinc to generate what you thought would be
the correct tab number and it has failed.

Table1 Tab_AUTOINC Tab:REAL(not in data table now) Form importsession
missingdata
1 1 AB1 1

2 2 AB2 1


**missing** 3 missing BC1
4 4 BC2 2

5 7 CD2 2

6 6 CD1 2

7 5 BC3 2



If you know without a doubt that the proper Tab number is associated with
the Form Code (1:1), then you may be in luck. If not .... Post back with
more informaton.

First find and correct the missing Form Codes -- add them to the table and
don't worry about the auto-inc tab number.
You just need to make sure ALL of your forms are in the database. (add a
row for BC1)

Now you have a table will ALL the required forms, but the 'tab' number is
wrong.

Build a make table query, sorted on the FormField

----------Example SQL code------------
SELECT Table1.Form INTO temp
FROM Table1
ORDER BY Table1.Form;

Open the TEMP table in design mode.

add a key field: PhysicalTab (autoinc)

Save and open.

Now you should have a table with the correct tab numbers and forms related
to each other with the tab number in proper order for the form.

Now go back to your DataTable (in my case Table1). Add a field ::
PhysicalTab (number)

Save the table

build an update query based on the temp table and table1, joined on the
Form, updating the Table1 field PhysicalTab with the temp table value for
the physical tab.

-----query sql---
UPDATE temp INNER JOIN Table1 ON temp.Form = Table1.Form SET
Table1.PhysicalTab = [temp].[physicaltab];

Now you have the tabs and forms matched and can delete the autoincrement
field and use the physicaltab field as your primaryKey and use the dmax
methods discussed earlier to maintain the keys.

[snip]

 

[StargateFan]

On Thu, 15 Dec 2005 06:14:00 -0600, "Ed Warren"
[snip]

I don't know. The more I think about this, the more it seems that
there isn't going to be a programmatical solution that users will
easily be able to understand and do. I might be a newbie, but I am a
power user with a lot of other experience under my belt and I could
handle it but others that come in will be true newbies <sigh>. That's
always the case.

Oops, talk about sounding like I'm bragging <sheesh>. I'm not,
actually <g>. It's just that this lowly clerical position doesn't
usu. bring in temps with more than rudimentary Excel knowledge never
mind Access! <g> Thanks and sorry 'bout that D.

 

[Ed Warren]

First things first

Get the database set up correctly in structure.

Then you can set forms up so the user's cannot edit, add, delete, or
whatever limits you want to put on them.

It sounds as if your real problem is you don't want to allow the user's to
delete rows -- so in the data entry form, change the allowed deletes
property to NO.

But first you gotta get the table and it's keys correct at least once.

Ed Warren

On Thu, 15 Dec 2005 06:14:00 -0600, "Ed Warren"
[snip]

I don't know. The more I think about this, the more it seems that
there isn't going to be a programmatical solution that users will
easily be able to understand and do. I might be a newbie, but I am a
power user with a lot of other experience under my belt and I could
handle it but others that come in will be true newbies <sigh>. That's
always the case.

Oops, talk about sounding like I'm bragging <sheesh>. I'm not,
actually <g>. It's just that this lowly clerical position doesn't
usu. bring in temps with more than rudimentary Excel knowledge never
mind Access! <g> Thanks and sorry 'bout that D.

 

[StargateFanFromWork]

First things first

Get the database set up correctly in structure.

Then you can set forms up so the user's cannot edit, add, delete, or
whatever limits you want to put on them.

It sounds as if your real problem is you don't want to allow the user's to
delete rows -- so in the data entry form, change the allowed deletes
property to NO.

Sounds like a good idea. Perhaps that will work in conjunction with the
text box. I never thought of doing that as this hasn't come up before.

(But still the problem when A2K chews up numbers and it has! But I'm not
beating that horse to death anymore. I'll cross that bridge when I get to
it if we lose numbers due to a crash. And by using the text box for
numbering for the tabs, that should hopefully take care of that!)

But first you gotta get the table and it's keys correct at least once.

True!! Hopefully no more other XL2K sheets with new info to add trickle
in!!!!! D But if they do, I'm thinking that the text box will work
there, too. This is still so much better than using XL2K and since our goal
is to get all new binders indexed into one db so we can find the info our
boss wants/needs in a moment, this is going in the right direction.

Thanks so much! Appreciate the help. D

Ed Warren

On Thu, 15 Dec 2005 06:14:00 -0600, "Ed Warren"
[snip]

I don't know. The more I think about this, the more it seems that
there isn't going to be a programmatical solution that users will
easily be able to understand and do. I might be a newbie, but I am a
power user with a lot of other experience under my belt and I could
handle it but others that come in will be true newbies <sigh>. That's
always the case.

Oops, talk about sounding like I'm bragging <sheesh>. I'm not,
actually <g>. It's just that this lowly clerical position doesn't
usu. bring in temps with more than rudimentary Excel knowledge never
mind Access! <g> Thanks and sorry 'bout that D.

 

[Ed Warren]

Again, you are trying to solve three problems at once.

First data structure, get that right.

Second "Access chews up numbers" -- this is very infrequent event, if at all
in a properly structured database, it is solved by a proper backup plan,
and/or a query to check to make sure all the numbers are still there, and to
report any missing numbers.

Third User access to data, and editing limits (edit, add, delete privileges)

No one approach is going to solve all of these at the same time.

Lots of luck.

Ed Warren.

First things first

Get the database set up correctly in structure.

Then you can set forms up so the user's cannot edit, add, delete, or
whatever limits you want to put on them.

It sounds as if your real problem is you don't want to allow the user's
to
delete rows -- so in the data entry form, change the allowed deletes
property to NO.

Sounds like a good idea. Perhaps that will work in conjunction with the
text box. I never thought of doing that as this hasn't come up before.

(But still the problem when A2K chews up numbers and it has! But I'm not
beating that horse to death anymore. I'll cross that bridge when I get to
it if we lose numbers due to a crash. And by using the text box for
numbering for the tabs, that should hopefully take care of that!)

But first you gotta get the table and it's keys correct at least once.

True!! Hopefully no more other XL2K sheets with new info to add trickle
in!!!!! D But if they do, I'm thinking that the text box will work
there, too. This is still so much better than using XL2K and since our
goal
is to get all new binders indexed into one db so we can find the info our
boss wants/needs in a moment, this is going in the right direction.

Thanks so much! Appreciate the help. D

Ed Warren

On Thu, 15 Dec 2005 07:59:47 -0500, StargateFan

On Thu, 15 Dec 2005 06:14:00 -0600, "Ed Warren"

[snip]

I don't know. The more I think about this, the more it seems that
there isn't going to be a programmatical solution that users will
easily be able to understand and do. I might be a newbie, but I am a
power user with a lot of other experience under my belt and I could
handle it but others that come in will be true newbies <sigh>. That's
always the case.

Oops, talk about sounding like I'm bragging <sheesh>. I'm not,
actually <g>. It's just that this lowly clerical position doesn't
usu. bring in temps with more than rudimentary Excel knowledge never
mind Access! <g> Thanks and sorry 'bout that D.