I'm computer literate, but a big novice at DB creation. I've been reading lots of pages and the manual and can see there are many interesting ways to go about the same task.

I've converted a database from Access via an xlsx and got it working fine. It is a one table DB that stores to one unique record, workshop repairs done in a bicycle shop. What I'd like to do is have the customers in their own table so they can autocomplete in the main table and obviously now be linked to multiple repairs.

There are around 10 thousand entries in the one table and I'm not quite sure how I would add this old data of first and last name into a new solution (would require looking for duplicates), or even if it's worth doing.

I'm thinking trying to do the new customer table from now on would be a reasonably easy task, but trying to add the old data as we'll would probably make the first task much more difficult and way beyond my ability?

Is that all you have about the customer: a first name and a last name?

Sorry a phone number as well. I was thinking of adding an email address but it's not super important. There is a Point of Sale system in the shop that records much more data. This is just for the workshop and I'm hoping to run a few reports on it to check time spent to profit made, but mostly just because I find databases quite fascinating now!

Uhm, I meant in the existing records. It's not quite clear, esp. with:

 

There is a Point of Sale system in the shop that records much more data.

 

In the 10k imported records, all you have is first name, last name and a phone number? But can you get more from the POS system?

Yes only first, last and phone from workshop Access DB. I haven't looked if I can export from the POS system yet. Might check that tomorrow. I'm assuming I could because it allows you to import.

Well, the more data you have, the more unique customers you'll be able to create. Although with a concatenation of  FirstName & LastName & PhoneNumber there probably won't be a lot of false duplicates.

 

Anyway, the procedure is something like this:

 

0. Back up your data;

1. Create a calculation field (result is text) with the concatenation above;

2. Sort by this field and export records grouped by the same field (this will export unique records only); export only the three fields: FirstName, LastName and PhoneNumber;

3. Import the result into a new table where you also have a CustomerID field (Number, auto-enter serial number); perform auto-enters during the import;

4. Define a temporary relationship between the two tables, matching on the three fields;

5. Define a CustomerID field in the original table and populate it with the matching customer's ID;

6. Switch the relationship to match on the two CustomerID fields;

7. Delete the calculation field created in step 1.

Thanks Comment for the great procedure. I've realised from the export that the mechanics over 10+ years of entering service records, aren't well known for their spelling! Their are so many versions of the same names with one letter different or missing. I end up with 6800 unique entries! Which of course will mean that many customers with have multilple CustomerID's. I'm reluctant to want to go through and fix them all, so I may just let them have thier multiple ID's and just pick one at a time when they come back in and fix all their entries manually then. No point in fixing entires from 10 years ago that have no relevance now.

Saying all that, I'm hoping you can tell me how to do
"5. Define a CustomerID field in the original table and populate it with the matching customer's ID;"

 

Just need a hand on how to actually make it populate the new CustomerID field in the original table. Thanks again for you help.

… the mechanics over 10+ years of entering service records, aren't well known for their spelling! Their are so many versions …

 

Don't be so hard on those poor mechanics …

Haha they're the coolest guys most of the time

Their are so many versions of the same names with one letter different or missing. I end up with 6800 unique entries!

 

Well, if you wanted to play with it, you could find duplicates, sort them by phone number field and see how different they are. My guess would be that the phone numbers are between 99% to 100% unique.

 

 

Saying all that, I'm hoping you can tell me how to do

"5. Define a CustomerID field in the original table and populate it with the matching customer's ID;"

 

Just need a hand on how to actually make it populate the new CustomerID field in the original table.

 

Show all records (in the original table);

Click into the field (on any record);

Select: Records > Replace Field Contents… > Replace with calculated result =

Customers::CustomerID