Importing Customer Data from DataEase

[mthornblad]

Hi

I am converting an inventory system from a dos-based database called
DataEase to Access. In the DataEase system a 4 digit customer number
(called a numeric string in DataEase) uniquely identifies each
customer in the Customers table. This number is used as the basis for
a relationship between the Customers table and the Invoices table.

The Customers table has approx 1000 records and the Invoices table has
approx 9000 records. I need to import the data from the Dataease
system to Access but I need help on the best way to proceed.

The customer number field in the Customers table (in Dataease) is an
"auto increment" field type. This field type adds 1 to the highest
customer number. If the last customer is "2004" and you enter a new
customer, the customer number of the new record is "2005"

If I was starting from scratch I could use the AutoNumber field type
in Access and everything would be fine. But I need to be able to keep
the existing relationship between the existing Customers table and
Invoices table.

If I import the existing data into Access using a 4 digit text field
as the destination field type that would work but when I enter new
records into the Customers table, I would have to assign the next
customer number myself.

I would appreciate any suggestions on the best way to proceed.

Thanks in advance
Mark Thornblad

 

[Allen Browne]

Mark, I'm not clear about the system DataEase uses, but a 4-digit "numeric
string" might imply that they store the value in a Text field that has the
literal leading zeros as part of the string if needed.

If you import the data into a temporary table, you could create a query that
converts that into a true numeric value. For example, if the field is named
CustID and there are no Nulls in the field, you could type this into the
Field row in query design:
CLng(Val([CustID]))

You could then change the query into an Append query (Append on Query menu),
and append that numeric value to the AutoNumber field in your new table.

 

[mthornblad]

Mark, I'm not clear about the system DataEase uses, but a 4-digit "numeric
string" might imply that they store the value in a Text field that has the
literal leading zeros as part of the string if needed.

If you import the data into a temporary table, you could create a query that
converts that into a true numeric value. For example, if the field is named
CustID and there are no Nulls in the field, you could type this into the
Field row in query design:
CLng(Val([CustID]))

You could then change the query into an Append query (Append on Query menu),
and append that numeric value to the AutoNumber field in your new table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I am converting an inventory system from a dos-based database called
DataEase to Access. In the DataEase system a 4 digit customer number
(called a numeric string in DataEase) uniquely identifies each
customer in the Customers table. This number is used as the basis for
a relationship between the Customers table and the Invoices table.

The Customers table has approx 1000 records and the Invoices table has
approx 9000 records. I need to import the data from the Dataease
system to Access but I need help on the best way to proceed.

The customer number field in the Customers table (in Dataease) is an
"auto increment" field type. This field type adds 1 to the highest
customer number. If the last customer is "2004" and you enter a new
customer, the customer number of the new record is "2005"

If I was starting from scratch I could use the AutoNumber field type
in Access and everything would be fine. But I need to be able to keep
the existing relationship between the existing Customers table and
Invoices table.

If I import the existing data into Access using a 4 digit text field
as the destination field type that would work but when I enter new
records into the Customers table, I would have to assign the next
customer number myself.

I would appreciate any suggestions on the best way to proceed.

Thanks in advance
Mark Thornblad

Allen

Thanks. I ran a test of what you suggested and it worked perfect. I
could have never figured
out how to do that without your help.

I still have a couple of issues as far as the conversion from DataEase
to Access but I'm
almost there. I'm on the downhill side of my learning curve with
Access.

Thanks again... I appreciate your help greatly.

Mark Thornblad
Centre, Alabama