Table field to duplicate value of Autonumber (1 Viewer)

boethos

Registered User.
Local time
Today, 11:28
Joined
Jul 5, 2017
Messages
21
I have a table that generates a new autonumber for each new record. I need to use that number in other queries, etc, but in those queries (and odbc connections) it's a Number field and when I try to join it to the autonumber field, it's a type mismatch (no surprise). I want to create a second field in that same table that has the value of the autonumber field and keeps it up to date for all new records (auto refresh after update or something) with the type of Number to be matched to other tables, etc.

What's the best way to do this? Can this be setup as the Default Value of the field in the table to be something like =Value("[autonumberField]") ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:28
Joined
May 7, 2009
Messages
19,169
can you convert the Number field to Long (Clng()), then join them.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
Arnel, doesn't using a function of a foreign key defeat any indexing that might otherwise have happened with the dependent table?

I am not understanding something here. When you say the field on which you wish to perform this JOIN is a NUMBER, what KIND of a number is it? Because if it is a LONG (i.e. 32-bit integer) then it should not have a type-match problem. If it is a number of lesser (or greater) size, I could see the mismatch. Please explain the data type of this field.

Since you are using ODBC, what is the database or database engine on the other end of the connection? That will help us to clarify the problem.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:28
Joined
Feb 19, 2002
Messages
42,970
There is no reason to duplicate the autonumber field. Autonumber is a long integer. and will join to other long integer fields. In the related records, they have their own Autonumber Primary keys but the foreign keys are defined as long integer.

Please post the two tables with some data and we will tell you what is wrong.

PS autonumbers do not change. That is the point. Perhaps you have your relationship defined backwards.
 

boethos

Registered User.
Local time
Today, 11:28
Joined
Jul 5, 2017
Messages
21
Thanks fellas. I didn't know autonumber was a long integer. I'll look into that. We have property (land, house, etc) for each record. We're generating our own internal 'index' number to identify each property. We're wanting to match it up to the Peachtree database using that index number to match it with payouts for taxes, etc. Trying to get Peachtree to work with odbc is a challenge, but that's another thread.
The index number in peachtree isn't a long integer, so I have to have the same datatype in Access to do the match without a 'type mismatch' error.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:28
Joined
Oct 29, 2018
Messages
21,358
Thanks fellas. I didn't know autonumber was a long integer. I'll look into that. We have property (land, house, etc) for each record. We're generating our own internal 'index' number to identify each property. We're wanting to match it up to the Peachtree database using that index number to match it with payouts for taxes, etc. Trying to get Peachtree to work with odbc is a challenge, but that's another thread.
The index number in peachtree isn't a long integer, so I have to have the same datatype in Access to do the match without a 'type mismatch' error.
If you can't modify Peachtree, then I guess you'll have to modify Access. Find out what data type Peachtree is using and create your own pseudo autonumber field in Access using the same data type as Peachtree.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:28
Joined
Oct 29, 2018
Messages
21,358
I had imported a table of data from Peachtree, and set the index number to long integer. I just tried a query to match the index numbers (Access-autonumber to Peachtree-Long integer) and got "Cannot join on Memo, OLE, or Hyperlink Object (prop.index=[Peachtree Job list].index)

Now what?
Well, that is true. Can you post the SQL statement for your query? How many fields are you joining?
 

boethos

Registered User.
Local time
Today, 11:28
Joined
Jul 5, 2017
Messages
21
Well, that is true. Can you post the SQL statement for your query? How many fields are you joining?
For now, just the one. I don't know what type of field is IN Peachtree. At some point I want to link the Acess db to Peachtree for live data sampling by the acct dept. For now, I just imported the data into Access so I can do prep and match the records we have indexes for in PT, and I can set the datatype in the import field so it will match (learning what it'll take to get the two to communicate).
Then later do a live linkup.
 

boethos

Registered User.
Local time
Today, 11:28
Joined
Jul 5, 2017
Messages
21
I got the query to pull from both the DB and the local import from Peachtree. I had to set the index field in Peachtree to Large Number to match up with the Access db.
 

GPGeorge

Grover Park George
Local time
Today, 08:28
Joined
Nov 25, 2004
Messages
1,776
Thanks fellas. I didn't know autonumber was a long integer. I'll look into that. We have property (land, house, etc) for each record. We're generating our own internal 'index' number to identify each property. We're wanting to match it up to the Peachtree database using that index number to match it with payouts for taxes, etc. Trying to get Peachtree to work with odbc is a challenge, but that's another thread.
The index number in peachtree isn't a long integer, so I have to have the same datatype in Access to do the match without a 'type mismatch' error.
I would entirely avoid futzing with the AutoNumber itself. I would add a second field to the table for the PeachTree Index and join the PT data on that field when required.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:28
Joined
Feb 19, 2002
Messages
42,970
You CANNOT control the autonumbers. Therefore, your PeachtreeID will be a separate field. As long as it is the same data type as the linked Peachtree table, you will be able to join them.

If the Peachtree table is not updateable, the query that joins Access to the Peachtree table will also be NOT updateable.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:28
Joined
Sep 12, 2006
Messages
15,613
I have a table that generates a new autonumber for each new record. I need to use that number in other queries, etc, but in those queries (and odbc connections) it's a Number field and when I try to join it to the autonumber field, it's a type mismatch (no surprise). I want to create a second field in that same table that has the value of the autonumber field and keeps it up to date for all new records (auto refresh after update or something) with the type of Number to be matched to other tables, etc.

What's the best way to do this? Can this be setup as the Default Value of the field in the table to be something like =Value("[autonumberField]") ?

No - it IS a surprise, and very much so. An autonumber is a long number. That autonumber PK will generally just be repeated as a number (long) representing the foreign key in a related table, and of course you can join between the autonumber, and the number, otherwise nothing would work in databases. So you must have something else causing an issue.

Now if you have 64bit access, maybe you have a long in one table, and a long long (is that the right description) in the other - and maybe you would not be able to join those 2 fields.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:28
Joined
Feb 19, 2002
Messages
42,970
I don't know what type of field is IN Peachtree.
Just link the table and open it in design view to see what Access thinks it is.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:28
Joined
Sep 12, 2006
Messages
15,613
peachtree might use GUIDs or might be strings. They would not be compatible with long numbers

you should be able to see if you can link to a peachtree table, just open it in design mode, and you will be able to see the design.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:28
Joined
Jan 23, 2006
Messages
15,364
Have you talked to Peachtree/Sage or read any related documentation?

From quick google searches
1) Peachtree Software is most likely the earliest entry-level accounting systems in the world tracing its roots to 1976 as part of The Computer SystemCenter, an early Altair 8800 microcomputer dealer. The company acquired Layered in 1990.

2) Peachtree Is Now Sage 50 Accounting| Sage US. Native-cloud accounting software for small business. ... Advanced financial management platform for professionals with a growing business.

More searching indicates the takeover/acquisition occurred ~2012.
 
Last edited:

boethos

Registered User.
Local time
Today, 11:28
Joined
Jul 5, 2017
Messages
21
Sorry for the sparse postings. Been busy. You know how I.T. goes.
Yes, been through boku docs. I figured this forum would know more by experience the best way to do it.
I'll be tied up next week, will attack it again the following week.
Thanks everyone for your input!.
Within the last couple of weeks we upgraded from Premium to Quantum, now a few more hiccups.
 

Users who are viewing this thread

Top Bottom