Importing Customer Data from DataEase

  • Thread starter Thread starter mthornblad
  • Start date Start date
M

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
 
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.
 
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.
Click to expand...
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.
Click to expand...
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"
Click to expand...
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.
Click to expand...
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.
Click to expand...
I would appreciate any suggestions on the best way to proceed.
Click to expand...
Thanks in advance
Mark Thornblad
Click to expand...
Click to expand...

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
 
Back
Top