Some Help & Advises

[desert emperor]

I hope I'm posting in the right place, if no let me know where to put this post.

I'm trying to make a database for my customers to enter information related to them and what they bought from me to be able after that to make some statistics and analyze..
So the first Table I made is:

PART I:
"Customers" : contains the fields:
-CustomerID : Number (PK)
-CompanyName : Text
-FirstName : Text
-LastName : Text
-Gender : Text
-Nationality: Text
-CustomerAddress: Text
-CprNO: Text (it's the identity nb)
-DateOfBirth: Date/Time
-Race: Text
-Email: Text
-CompanyWebsite: Hyperlink
-PhoneNumber: Text
-MobileNumber: Text
-FaxNumber: Text
-Notes: Memo


1- I need all these info is it considered so many fields for 1 table?
2-For the "CustomerAddress" field is it better to divide it into "streetNb", "City" , "country"...? i saw some threads that suggest that why?
3-Are all my field types correct? Especially for the PK (is it better to make it auto number and "increment"? for the phone,mobile and fax is it ok as "text"? or better "number" ? show i give it a mask so all the entered phones have the same format?

Part 2:

Table : "Products"
Fields:
-ProductID: AutoNumber - Increment (PK)
-ProductName: Text
-UnitPrice: Number
-InStock: Yes/No

1- For the UnitPrice if i want to use a currency other than the US $ or Euro can i configure my own? or i should leave it like now as a number without any sign?

Part 3 :

Table : "Employees"
Fields"
-EmployeeID : AutoNumber - Increment (PK)
-FirstName : Text
-LastName : Text
-Title : Text
-WorkPhone : Text

1-Same question as before for the workphone data type.Also any fields in mind that i should add?

Part 4 :

Table: "Orders"
Fields:
-OrderID : Number
-CustomerID : Number
-ProductID : Number
-EmployeeID : Number
-WarrantyNumber : Text
-InvoiceNumber : Text
-ProductNumber : Text
-SerialNumber : Text
-DateOfPurchase : Text
-PlaceOfPurchace : Text
-SalesmanName : Text
-CustomerName : Text

1-Well for this table it should handles the orders so i can enter for each customers the product ( or products he bought) at the same or differents dates.
2-I'm not sure that the fields that i entered in this table are correct in "data type" or "location" ( i mean they should be in other table? for example "SerialNumber", "WarrantyNumber" should be in here or in "product table" or it doesn't matter??
3-I added the fields "CustomerID", "ProductID" and "EmployeeID" so i can make relations with the other tables later! is it correct or no?

WELL i will stop in here for now, i'm not sure that you are interested in helping me maybe you'll find it long for reading but it's all explanations to make answers easier and accurate, please try to answer me on my questions first and than give me your suggestions and later i have in mind other questions but i want first to finish with the tables, structures and relations in my database.
Thanks so much for all of you that take a look, and would like to help.

 

[GaryPanic]

PART I:
"Customers" : contains the fields:
-CustomerID : Number (PK)
-CompanyName : Text
-FirstName : Text
-LastName : Text
-Gender : Text
-Nationality: Text
-CustomerAddress: Text
-CprNO: Text (it's the identity nb)
-DateOfBirth: Date/Time
-Race: Text
-Email: Text
-CompanyWebsite: Hyperlink
-PhoneNumber: Text
-MobileNumber: Text
-FaxNumber: Text
-Notes: Memo

this part would be 1 table with a couple of drop down boxes
gender /nationality
customer address would be better suited to
add1
add2
add3
add4
zip/postcode

you can group them together later if you require

 

[desert emperor]

ok but

OK i'll divide the address into many fields.
BUt about the fields "gender" and "nationality" how i can make them drop down menues?
I thought about that but i don't know how to make it inside a table! i mean i have a basic idea about making that in a "form" not a table can u please tell me your idea in details and the steps i should make?
It sounds nice since i can directly choose MALE/FEMALE....

ok i'm waiting your reply

PS:what about the other tables?do you have time to take all the look on my post?

THX

 

[anthonys]

PART I:
"Customers" : contains the fields:
-CustomerID : Number (PK)
-CompanyName : Text
-FirstName : Text
-LastName : Text
-Gender : Text
-Nationality: Text
-CustomerAddress: Text
-CprNO: Text (it's the identity nb)
-DateOfBirth: Date/Time
-Race: Text
-Email: Text
-CompanyWebsite: Hyperlink
-PhoneNumber: Text
-MobileNumber: Text
-FaxNumber: Text
-Notes: Memo


1- I need all these info is it considered so many fields for 1 table?
2-For the "CustomerAddress" field is it better to divide it into "streetNb", "City" , "country"...? i saw some threads that suggest that why? Separate fields as it ensures that the data is entered in the same order and makes searching by town and postcode possible
3-Are all my field types correct? Especially for the PK (is it better to make it auto number and "increment"? for the phone,mobile and fax is it ok as "text"? or better "number" ? show i give it a mask so all the entered phones have the same format? Autonumber is easier as it saves you working out how to keep it unique, i like to set most fields as text unless i am doing calculations on them. For example if you change it to a number you wouldn't be able to have international numbers like this +44 20 123 4567

Part 2:

Table : "Products"
Fields:
-ProductID: AutoNumber - Increment (PK)
-ProductName: Text
-UnitPrice: Number
-InStock: Yes/No

1- For the UnitPrice if i want to use a currency other than the US $ or Euro can i configure my own? or i should leave it like now as a number without any sign?Store as number and then add the currency symbol in reports etc. if you deal in international currencies you might want to add 2 new fields. Firtsly 1 called currency (text) in which you store the relevant sysmbol ($,£,€....). you should also store the exchange rate (Number) so that you can get the correct conversion value in years to come

Part 3 :

Table : "Employees"
Fields"
-EmployeeID : AutoNumber - Increment (PK)
-FirstName : Text
-LastName : Text
-Title : Text
-WorkPhone : Text

1-Same question as before for the workphone data type.Also any fields in mind that i should add?as above

Part 4 :

Table: "Orders"
Fields:
-OrderID : Number
-CustomerID : Number
-ProductID : Number
-EmployeeID : Number
-WarrantyNumber : Text
-InvoiceNumber : Text
-ProductNumber : Text
-SerialNumber : Text
-DateOfPurchase : Text
-PlaceOfPurchace : Text
-SalesmanName : Text
-CustomerName : Text

1-Well for this table it should handles the orders so i can enter for each customers the product ( or products he bought) at the same or differents dates.
2-I'm not sure that the fields that i entered in this table are correct in "data type" or "location" ( i mean they should be in other table? for example "SerialNumber", "WarrantyNumber" should be in here or in "product table" or it doesn't matter??
data types look ok and the location is also correct as the "SerialNumber", "WarrantyNumber" will be unique for each order. I would question the need to store ProductNumber, salesman and customer name as these can be worked out by linkingto the employee, customer and employee table. If you store the details in this table you will have a lot of issues if someone changes name etc
3-I added the fields "CustomerID", "ProductID" and "EmployeeID" so i can make relations with the other tables later! is it correct or no? Yes this is correct, it means you don't have to store the customer, product and employee details in multiple places

WELL i will stop in here for now, i'm not sure that you are interested in helping me maybe you'll find it long for reading but it's all explanations to make answers easier and accurate, please try to answer me on my questions first and than give me your suggestions and later i have in mind other questions but i want first to finish with the tables, structures and relations in my database.
Thanks so much for all of you that take a look, and would like to help.

Hope this helps!!

 

[anthonys]

OK i'll divide the address into many fields.
BUt about the fields "gender" and "nationality" how i can make them drop down menues?
I thought about that but i don't know how to make it inside a table! i mean i have a basic idea about making that in a "form" not a table can u please tell me your idea in details and the steps i should make?
It sounds nice since i can directly choose MALE/FEMALE....

ok i'm waiting your reply

PS:what about the other tables?do you have time to take all the look on my post?

THX

You will have to make the drop down menus when you create your data entry forms

 

[desert emperor]

THX Anthonys

Your answers were so helpful and organized and that's why i added your reputation!!
OK now i understand better the first part of my project which is the table, i'll make the changes on the suggested things that you said (address, currencies..) and delete the fields from the order that they are already entered in other table just let me take a look on what i have now to continue with you on what i should do more and give you what i'm expecting to get as a result.

THX so much

 

[anthonys]

No Worries, best of luck!!

 

[desert emperor]

OK here i'm Again

After taking a look to the whole database I want to say:

1- I think i have no problem concerning the tables "customers", "products" and "employees" just i'll ask about some question to make clear for myself:

a-the 2 tables "employees" and "products" are almost fix for me. i mean in that that for example i have 10 employees to enter and after that it's done, well i can add or delete later but let's say now i entered my employees so it's not gonna change frequently right?so i have no major works in here. IS it prefered/more professional to make forms for them? is a query a need between a table and a form?

b-For the Table "customers" let's make a scenario that 5 new customers came today and bought from my products ok? so i can easily enter all the related info in the customer table (sure after making a form "add new customer") so all what is related to the customer is entered in the customer table right?

C-What about what is related to what they bought?it should be entered in the "orders" table right? So only i'm not understanding how it'll work exactly!! can u plz explain to me a little more
example a customer bought a product from me, i entered his info in the customer table now i want to place his order right?
-OrderID: is unique and is a primary key
-CustomerID : i should get it from the "customers table right? the question is i should go to the customers table and see what is the ID then write it manually in the orders table or form? or it can be done automatically by using a drop down menu (combo box i think)?
-Same question for ProductID EmployeeID as they are PK in the other tables and they will be the link between the Orders table and the other 3 tables right? SO WHAT U SUGGEST automatic or manual entry of these fields??
-For the fields WarrantyNumber InvoiceNumber ProductNumber SerialNumber DateOfPurchase PlaceOfPurchace they are not present in other tables and you said that their places are correct in this table because they are unique for each sale so i think there is no problem right? any other opinion in this?
-Finally for the SalesmanName and CustomerName in the "orders" table, i know they are present in "employees" and "customers" tables respectively just i putted them in here to know which employees served which customer! i understand a little your idea so please explain more?how to link to the tables without putting these fields again in the orders tables? is it because i already entered the CustomerID and EmployeeID???explain this point plz cuz i don't have a clear idea.

Now i have 4 tables i'm using access 2007 again: i clicked on Database tools--->relationships and i see my 4 tables so i dragged and dropped CustomerID from the order table to the customer table and i see a line with "1" from the side of "customer" table and "infinity" from the side of "orders" table!! i did the same for the other 2 tables "emplyees" and "products" is it correct??? one to many relation is the one i want?and is it in the right direction?and also i checked the 3 boxes when the relation popup appeared!right?

So i think the part 2 of this project is till here after that i'll ask you about how to illustrate what i want from multiple tables to see what i want in 1 place!

THANKS AGAIN FOR YOUR HELP if you like i can attach you what i have in a zip file so u can take a better idea just what i want is understand what i'm doing and not having someone do the job without me knowing how!
But if u see that you can help more in that with having the database plz tell me.
BEST REGARDS

 

[Dennisk]

I work in the Health service and one attribute we have to collect is a person's sex.

Gender is not the same as the biological sex of a person. So a transvestite could have a biological sex of male but have a female gender.

 

[anthonys]

After taking a look to the whole database I want to say:

1- I think i have no problem concerning the tables "customers", "products" and "employees" just i'll ask about some question to make clear for myself:

a-the 2 tables "employees" and "products" are almost fix for me. i mean in that that for example i have 10 employees to enter and after that it's done, well i can add or delete later but let's say now i entered my employees so it's not gonna change frequently right?so i have no major works in here. IS it prefered/more professional to make forms for them? is a query a need between a table and a form?You should make a form for them as you do not want users doing direct table data entry. You can also control what the user enters easier in a form. You do not need to have a query to create a form, you can just link the form to the table

b-For the Table "customers" let's make a scenario that 5 new customers came today and bought from my products ok? so i can easily enter all the related info in the customer table (sure after making a form "add new customer") so all what is related to the customer is entered in the customer table right?All the static info that rarely changes is stored in the customer table.

C-What about what is related to what they bought?it should be entered in the "orders" table right? So only i'm not understanding how it'll work exactly!! can u plz explain to me a little more
example a customer bought a product from me, i entered his info in the customer table now i want to place his order right?
-OrderID: is unique and is a primary key
-CustomerID : i should get it from the "customers table right? the question is i should go to the customers table and see what is the ID then write it manually in the orders table or form? or it can be done automatically by using a drop down menu (combo box i think)? Combo box means you can select the name of the customer but actually store the ID in the table
-Same question for ProductID EmployeeID as they are PK in the other tables and they will be the link between the Orders table and the other 3 tables right? SO WHAT U SUGGEST automatic or manual entry of these fields??again combo box
-For the fields WarrantyNumber InvoiceNumber ProductNumber SerialNumber DateOfPurchase PlaceOfPurchace they are not present in other tables and you said that their places are correct in this table because they are unique for each sale so i think there is no problem right? any other opinion in this?
For each order created you will manually enter these fields, although the date field could be set to default that date (=Now())
-Finally for the SalesmanName and CustomerName in the "orders" table, i know they are present in "employees" and "customers" tables respectively just i putted them in here to know which employees served which customer! i understand a little your idea so please explain more?how to link to the tables without putting these fields again in the orders tables? is it because i already entered the CustomerID and EmployeeID???explain this point plz cuz i don't have a clear idea.
Because you have stored the id's you can link to name when you create reports using a query. When you use a combo box you can display the name from the number

Now i have 4 tables i'm using access 2007 again: i clicked on Database tools--->relationships and i see my 4 tables so i dragged and dropped CustomerID from the order table to the customer table and i see a line with "1" from the side of "customer" table and "infinity" from the side of "orders" table!! i did the same for the other 2 tables "emplyees" and "products" is it correct??? one to many relation is the one i want?and is it in the right direction?and also i checked the 3 boxes when the relation popup appeared!right? This is correct if you want Access to control your data integrity. A lot of developers like to control it themselves but for your first database and a learning excercise this is correct

So i think the part 2 of this project is till here after that i'll ask you about how to illustrate what i want from multiple tables to see what i want in 1 place!

THANKS AGAIN FOR YOUR HELP if you like i can attach you what i have in a zip file so u can take a better idea just what i want is understand what i'm doing and not having someone do the job without me knowing how!
But if u see that you can help more in that with having the database plz tell me.
BEST REGARDS

I don't mind answering your questions and respect that you want ot learn as you go along. I would recommend that you research the use of sub forms to create you customer->orders form. I would also recommend that you look at the sample databases installed with MS Access (Northwind and Orders). These would give you a great understanding of the final product and would answer a lot of your initial questions

Ant