Autonumber

[Guest]

I am trying to create Autonumbers which begin with an R so you get R2000,
R2001, R2002.

I placed >R in the format for the field in the table and everything works
good until I us a Union Query and it drops the R. Why is this?

Is there a better way to do what I am trying to do.

I am actually bringing together 2 autonumber fields eventually from
exisiting databases and want to avoid duplicating numbers down the road.

 

[Rick B]

As discussed many many times (you shoudl try to search for an answer before
posting), autonumbers are only used to create a unique primary key. If the
number is to be meaningful or to be used in some way, you should not use an
autonumber.

Do some searches and read previous posts if you need more details.

Rick B

 

[Lynn Trapp]

Is your Formatting in both tables? If not, the UNION query will pick up the
formatting of the first table in the query and leave off the letter "R". Try
changing the order of the tables in your union query.

 

[Guest]

Lynn, it is actually the same query for both parts of the union. I pull out
records based on dates.

Any Ideas?
I have been searching past posts and find conflicting info on how to do this?

 

[Lynn Trapp]

I'm not sure. I can get it to work just fine. One thing you can try is to
concatenate the letter "R" to your Autonumber field as part of the Union
queryl

SELECT "R" & [YourAutoNumberField]
FROM YourTable
UNION
SELECT "R" & [YourAutoNumberField]
FROM YourTable;

 

[Guest]

Lynn, putting the "R" does produce a field called Expr1000 which does show
the R.

Is this going to cause a problem instead of the ID field?

Is there a way to still have the ID field instead of the Expr1000 if needed?

I'm not sure. I can get it to work just fine. One thing you can try is to
concatenate the letter "R" to your Autonumber field as part of the Union
queryl

SELECT "R" & [YourAutoNumberField]
FROM YourTable
UNION
SELECT "R" & [YourAutoNumberField]
FROM YourTable;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn, it is actually the same query for both parts of the union. I pull out
records based on dates.

Any Ideas?
I have been searching past posts and find conflicting info on how to do this?

 

[Joseph Meehan]

I am trying to create Autonumbers which begin with an R so you get
R2000, R2001, R2002.

I placed >R in the format for the field in the table and everything
works good until I us a Union Query and it drops the R. Why is this?

Is there a better way to do what I am trying to do.

I am actually bringing together 2 autonumber fields eventually from
exisiting databases and want to avoid duplicating numbers down the
road.

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Guest]

What I have done is combine 2 databases which both have tables with
autonumbers. There will come a point where these numbers which are brought
together by a union Query will duplicate. I know I could append new records
for one of tables to a much higher number that the other numbers would never
reach.

Would I would like to do is distinguish one set of numbers from a table for
both existing and new with an "R" so that I could have R3000 as well as 3000
for example is a query used down the road.

Does this make sense?

Can you help?

 

[Lynn Trapp]

Change the query like this

SELECT "R" & [YourNumberField] AS ID

You will only need to do that in the first select statement. It will carry
through for the entire result set.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn, putting the "R" does produce a field called Expr1000 which does show
the R.

Is this going to cause a problem instead of the ID field?

Is there a way to still have the ID field instead of the Expr1000 if needed?

I'm not sure. I can get it to work just fine. One thing you can try is to
concatenate the letter "R" to your Autonumber field as part of the Union
queryl

SELECT "R" & [YourAutoNumberField]
FROM YourTable
UNION
SELECT "R" & [YourAutoNumberField]
FROM YourTable;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn, it is actually the same query for both parts of the union. I

pull
out

records based on dates.

Any Ideas?
I have been searching past posts and find conflicting info on how to

do
this?

:

Is your Formatting in both tables? If not, the UNION query will pick

up
the

formatting of the first table in the query and leave off the letter

"R".
Try

changing the order of the tables in your union query.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


I am trying to create Autonumbers which begin with an R so you get R2000,
R2001, R2002.

I placed >R in the format for the field in the table and

everything
works

good until I us a Union Query and it drops the R. Why is this?

Is there a better way to do what I am trying to do.

I am actually bringing together 2 autonumber fields eventually from
exisiting databases and want to avoid duplicating numbers down the road.

 

[Joseph Meehan]

What I have done is combine 2 databases which both have tables with
autonumbers. There will come a point where these numbers which are
brought together by a union Query will duplicate. I know I could
append new records for one of tables to a much higher number that the
other numbers would never reach.

Would I would like to do is distinguish one set of numbers from a
table for both existing and new with an "R" so that I could have
R3000 as well as 3000 for example is a query used down the road.

Does this make sense?

Can you help?

How about a binary filed (yes no) to ID the original source. Then if
you don't need the existing numbers for anything else, either eliminate the
number totally or add a new autonumber filed to create a new set up numbers,
then delete the original field.

If they are still in two tables, then Access will not have a problem
with numbers duplicating as they only need to be unique in their own table.

 

[John Vinson]

What I have done is combine 2 databases which both have tables with
autonumbers. There will come a point where these numbers which are brought
together by a union Query will duplicate. I know I could append new records
for one of tables to a much higher number that the other numbers would never
reach.

Would I would like to do is distinguish one set of numbers from a table for
both existing and new with an "R" so that I could have R3000 as well as 3000
for example is a query used down the road.

Does this make sense?

No. An Autonumber field - or a Long Integer field, which it becomes if
you turn off the autonumbering - *IS A NUMBER*. The character R is not
allowed in any numeric field. The suggestions elsewhere in this thread
will let you *DISPLAY* the R in front of the numeric value, but it
will not be something you can store in an autonumber or numeric field
in the table.


John W. Vinson[MVP]

 

[Guest]

Now I need more info? Not sure what you mean by binary field yes no tto ID
original source?

Yes the autonumbers are in 2 different tables but I union them together into
one field for report purposes. That is why I need the identifier. I need
the original numbers as users identify items by the existing numbers. Since
I have both sets of numbers on one report I also need a way to distinguish
which numbers are which!

I need more explanation on how to do this?

 

[Guest]

John, I don't have to store the "R". It only has to appear on Forms and
Reports. How do I get around the Union Query Issue?

Is expr1000 ok to use as a field? I only need to identify one set of
numbers from the other set.

I read allot about Custom Counters but we have many simultaneous users and
some of what I read said duplicate records could occur!

 

[John Vinson]

John, I don't have to store the "R". It only has to appear on Forms and
Reports. How do I get around the Union Query Issue?

Is expr1000 ok to use as a field? I only need to identify one set of
numbers from the other set.

I read allot about Custom Counters but we have many simultaneous users and
some of what I read said duplicate records could occur!

The field name is COMPLETELY IRRELEVANT in a UNION query anyway. You
can replace expr1000: with any name you want, but the fieldname in the
UNION will be the fieldname in the first SELECT statement.

TO distinguish which table is which, I'd simply add a calculated field
in both selects; just put

Tablename: NameOfTheTable

and NameOfTheTable will appear in every record.

John W. Vinson[MVP]

 

[Guest]

John, my issue in the Union Query is that the Same query is being used in
both Select and Union to change and merge fields on just one set of the
numbers. I have not even got to trying this with numbers from both tables as
the union query drops the letter. The fieldname in both is ID and like I
said the query works fine until I union the same query and then fails.

Thoughts on this or am I missing your point?

 

[Joseph Meehan]

Now I need more info? Not sure what you mean by binary field yes no
tto ID original source?

Yes the autonumbers are in 2 different tables but I union them
together into one field for report purposes. That is why I need the
identifier. I need the original numbers as users identify items by
the existing numbers. Since I have both sets of numbers on one
report I also need a way to distinguish which numbers are which!

I need more explanation on how to do this?

I can't help but wonder if you need two tables. Could the data in both
tables be placed into a single table? The two groups could still be
identified by a single binary filed (a binary filed is just a yes no, on
off, check box type filed that has just two states, it is yes or no. If yes
it may be "R" and if no then it is not "R". In reality Access stores the
data as 0 or -1. It might simplify things.

 

[Guest]

Using the >R format for the ID field did do the trick. In the end my
expr1000 reverted back to the Service Call Number Field due to the number of
union queries I used. The report did reflect the R in front of all numbers
from the one table which is what I wanted.

The nice thing is as folks have pointed out is that the database only uses
the R for display purposes and it is not not required that it be included
when entering the number to bring up a form to complete.

Thanks to all on this subject who hung with me. At least this gives me some
options without having to try and combine the two tables which at this point
would be painful.