I have a table that has a field called Customer Number, this field is what connects this table to my main database the Customer Number has a relationship with the field Customer Number in my main database.

Within this table Each customer number can have up to 4 records displaying various data. I'm trying to create a new field within that table that automatically creates a number 1-4 based on the record number.

So record 1 for customer number X, the field would have an auto calc value of 1 (Aircraft 1), for record 2 for customer x the value would be 2 (Aircraft 2), etc all the way to 4. 

Customer number Y, record 1 the value again would be 1, record 2, value would be 2.

Is there a way to do this? Sequencing doesn't stop at 4 and doesn't limit this to each customer number.

 

I'm trying to have each customer number able to have up to 4 aircraft listed and then have another table that allows up to 4 pilots to be attached (with a relationship) based on either Aircraft 1, 2, 3 or 4.

 

Thoughts?

Not positive I understand the rules based on your post, but I would do something like:

Tables: Customers, Aircraft, Pilots, Aircraft Assignments, Pilot Assignments

1 record per customer in Customers table

1 record per aircraft in Aircraft table

1 record per pilot in Pilots table

When you want an aircraft assigned to a customer, add a record in the Aircraft Assignment table, which would include the customer ID and the aircraft ID. When you want to associate a Pilot to an aircraft, add a record to the aircraft assignments table, which includes pilot ID and aircraft ID.

If you want to limit the relations, you can do that via scripts by getting counts of related records. 

I'm trying to create a new field within that table that automatically creates a number 1-4 based on the record number.

​What would be the purpose of this field? In general, it's not a good idea for data to depend on sibling records.  Records do get deleted and added, and it's very easy to get duplicates going the way you describe. If you want a field to serve as a unique identifier, just use an auto-entered serial number in the Aircraft table.

Edited May 20, 201510 yr by comment

Tables: Customers, Aircraft, Pilots, Aircraft Assignments, Pilot Assignments

​The way I understand this, Customers and Aircraft are related one-to-many, so there would be no need for an Aircraft Assignments table.

Yes, I think we need better understanding of the 'rules' as I call em.