AutoNumbers in Access

[ddoblank]

I have created two tables that each generate a different PO number for us.
One starts with a letter followed by number, ie, A001 the other starts with a
56-A001. After these numbers are generated, I try to append them into another
table so I can have all the PO's in one table to create a report. Each time I
do this, I loose my prefix, ie the "A" or the "56-A" and all I get are just
the numbers. If I run a query, everything is there and if I create a report
from the Autonumbering tables, everything is there, but I can not get them to
show up in the appended table. And no, I can't create a query for each and
append them into a joint table, tried that and it didn't work either. Please
help.
Thanks, Darren

 

[Tom van Stiphout]

On Wed, 24 Dec 2008 13:48:00 -0800, ddoblank

There is no need for this third table. Possibly not even for the
second table but we'll let that one go for now.
Rather you create a union query for your report:
Create a new query, don't select any table, and switch to SQL view.
Then write:
Select myField1, myField2, myField3 from myTable1
union all
Select myField1, myField2, myField3 from myTable2
(of course you substitute your object names for my placeholders)

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

I have created two tables that each generate a different PO number for us.
One starts with a letter followed by number, ie, A001 the other starts with a
56-A001. After these numbers are generated, I try to append them into another
table so I can have all the PO's in one table to create a report. Each time I
do this, I loose my prefix, ie the "A" or the "56-A" and all I get are just
the numbers. If I run a query, everything is there and if I create a report
from the Autonumbering tables, everything is there, but I can not get them to
show up in the appended table. And no, I can't create a query for each and
append them into a joint table, tried that and it didn't work either. Please
help.
Thanks, Darren

Are these actually Access Autonumber fields? The shouldn't be, for several
reasons:

- Autonumbers cannot contain text such as A or 56-A.
- Autonumbers cannot be controlled between two tables (there will be
duplicates).
- Autonumbers will always get gaps - just hitting <Esc> after a new record has
been started will permanently use up an autonumber.
- Autonumbers can become random (if you Replicate the database, for example).
This would lead to oddities like PO Number 331228412 followed by PO Number
-1189325617.

Secndly, your table structure is suspect. What is the difference between these
two tables (other than the prefix)? It would seem they contain the same type
of data since you want to combine them for a report; if so they should be all
in the SAME table, probably with a new Prefix field (containing "A" or "56-A"
or maybe other values), and a manually or programmatically assigned Long
Integer (not Autonumber) field for your PO number.

 

[a a r o n _ k e m p f]

wait a second.. so your AutoNumbers aren't reliable, and you blame the
AutoNumbers?

when I see these same symptoms 'Access AutoNumbers aren't reliable' I
say 'Yet another reason to move to SQL Server'

things just work when you use SQL Server.
Jet isn't reliable enough for a half dozen users and a mere 25mb of
data.

-Aaron