Help Designing Tables

[sakthivels]

Hello everyone,

I need some help tp design a table. The scenario is as follows. I am to create a table to hold the details about the customer which includes fields like Name, Address, Contact No., Email ID. I use the autonumber to generate the CustomerID. This customer has enrolled his brother also with the company. Now my problem is to link the main customer and his brother using the same ClientID.



Analogy to the issue is Customer having different accounts with the same bank. For ex. Mr. Adam is customer of XYZ bank. The customer id is as follows
Savings Bank A/c 001-712641-100
Current A/c 001-712641-200
Fixed Deposits: 001-712641-300

The client id is 712641 and the last 3 digits (ie, 100, 200, 300) denote the account type.

I need to create a similar kind of number for the above scenario (ie Customer family members also get the same customerid and some sufix to distinguish them). Kindly someone advise me on this.

[RuralGuy]

Is this a school project? With what part do you need help?

[sakthivels]

Respected RuralGuy,

This is a not a school project. I am working for a company. My boss has asked me to create a table for him. I am not too good in Access, so I am seeking some help from the forum.

The scenario is to create a table to collect the customer details. I explained him to use the autonumber feature to create an unique customer id, but my boss requires that the relatives of the customer should also have the same first part of the customer id and the last sufix should be selected from a combo, so that would look like an unique id.

He says the same analogy that I've mentioned earlier, like the customer of a bank having multiple types of account under the same account number plus the account type as the suffix.

I am very confused with the design of table. Kindly advice me on this.
Regards,
Sakthivel

[RuralGuy]

Break the problem down into smaller tasks and start implementing the tasks. When you run into trouble then ask a specific question and we will try to answer it.

[sakthivels]

Hello everyone,

Can someone please help me on this. Sorry I was not much clear in the previous posts, hence I am reposting.

The scenario is to collect customer informaiton like address, city, state, zipcode to a table. Customer ID is something line XX-YYYYYY-ZZ where XX is a category to be selected from a combo box, YYYYYY is autogenerated number and ZZ is if the customer id already not exist 01 else it will be incremented by 1, ie the new value should become 02.

The reason I need this type of table design is there might be a family where the parent will be my main customer, and when their son or daughter also enrolls, I'll add them to the same family group.

If the parent is given the Customer ID as
A1-000001-01 then the son will be enrolled as A1-000001-02 and the daughter will be enrolled as A1-000001-03.

If a new customer comes and enrolls whom doesn't belong to the above family, then his Customer ID should be generated as A1-000002-01 and his family members will have the ID as A1-000002-02.

I am very much confused when I was asked to do this hence I am requesting assistance.

Regards,
Sakthivel.

[RuralGuy]

I'm going to suggest you keep the three values in three separate fields. They can always be put together but it will be easier to increment the values with DMax() if they are separate.

[sakthivels]

Respected RuralGuy,
I have an idea, let's say I create a command button, when the user clicks on it, I open a form, where I give the option "New Client" and "New Family member", So if I select the New Client Option I again open another form to select the category. Once selected I assign the Customer ID as "XX-YYYYYY-ZZ", where XX is category selected, YYYYYY is autogenerated number, ZZ an I default it to 01, since its going to be a new client.

If I select "New Family Member" Option, I present a form to type in the full Customer ID "XX-YYYYYY-ZZ", and I do a search in my database for the customer ID and if exists, I assign the Customer ID as XX-YYYYYY-(ZZ+1), ie I increment the value of ZZ by 1.

Now all I need to know is how do I search for the table for the criteria and how to assign a value to a variable that is to be collected from a form.

Kindly help me on this.
Regards,
Sakthivel

[RuralGuy]

Here's a link to the syntax for all of the Domain finctions. I would suggest using DMax().