I know this is bad but how to fix it?

[delaikhi]

I have tables: tblOrder and tblCustomers. I have a field called [Customer] and it looks up to the field [Customername] in Customer. Than I have a query: Order Query, which includes both [Customer] and [Customername].
Problem is when I start to input Orders, the Customername keep coming up with new customers in tblCustomer. I know there is something bad but not sure how I can fix this. Please tell me how?

 

[RuralGuy]

Are you using Lookup Fields? http://access.mvps.org/access/lookupfields.htm

 

[delaikhi]

Yes, I do. Part of it because I want not to look up customer every time I put in an order. Another part is because the users keep messing up with customers' names. However this drives me crazy because it is difficult to sort the customers into several types (ie loyal, oneoff or walk-in)

 

[jdraw]

I agree with Allan -- don't use table field lookups (see the link he gave).
I think you have a database structure issue.
Here are some video tutorials to lead you through Customer, Orders, OrderDetails and Items.
http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Good luck

 

[Cronk]

Don't use lookup fields, nor allow users to enter data directly into a table.

In your tblOrders, have a field CustomerID which links to the primary key field in tblCustomers.

Have your users select customers from a combo box with its row source set to tblCustomers and bound to CustomerID in tblOrders.

You can set the combo to Limit to List to stop users entering new customers and changing customer names.

 

[delaikhi]

Don't use lookup fields, nor allow users to enter data directly into a table.

In your tblOrders, have a field CustomerID which links to the primary key field in tblCustomers.

Have your users select customers from a combo box with its row source set to tblCustomers and bound to CustomerID in tblOrders.

You can set the combo to Limit to List to stop users entering new customers and changing customer names.

Cronk,
Actually I have that kind of field in tblOrders and I also want users to input the Customers' names in a Combo Box created from a queryOrders. In fact, I want the users to do the job for me (put in customers' names including new ones but not to mess up with the ones that already there).
After a while, I found out that every customer with a new order regardless what product they buy would come up with a new value in tblCustomer, as a result, I ave several customers with the same names and in some cases the same products :banghead: and this been driving me crazy!

 

[Cronk]

I set the LimitToList as true and then use the NotInList event to query whether a new customer is to be added. Where necessary, I open a New Customer form and also collect any other info for the new customer such as address/phone number.

Search for a suitable example of the code.

 

[delaikhi]

I set the LimitToList as true and then use the NotInList event to query whether a new customer is to be added. Where necessary, I open a New Customer form and also collect any other info for the new customer such as address/phone number.

Search for a suitable example of the code.

Thanks Cronk, It seems resolved my issue but I still have another one, sorry
I have other 2 tables: product_types and product_details; tblproducttypes contains things like stationary, household, bath etc... the tblroductdetails contains subcategories of product_types like pen, soap etc. in the tblproductdetails I put fields like product_price, sales_tax etc. In tblproducttypes I put a field look_up_to_productdetails which would look up to product_details_id.
Now, with 4 tables as I am having (tblOrders, tblCustomers, tblproductypes and tblproductdetails), I created a query OrderQuery, it seems ok with two tables tblcustomer, tblorder and tblproductprice, but I am not so sure where the tblproductdetails would fit in? Please tell me what and where to put this last one in to the cheme? Thanks

 

[jdraw]

Did you watch the videos I suggested?

 

[delaikhi]

Jdraw, thanks for the videos. Actually I watched them several time, but still cannot comprehend it . Is there any simpler way to go with my particular database? Say what to do with my OrderQuery? Thanks

 

[Cronk]

What are the fields in your tblOrders?

 

[jdraw]

Here's a tutorial that leads you through the process step by step.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

It includes answers.

 

[delaikhi]

Guys, here is my tables and the fields included:
1. tblOrder: OrderID (Autonumber, PK); OrderCode (unique numer and letter, indexed (no duplicate)); OrderDate; OrderQuantity; OrderAmount; Customer (lookup to CustomerID in tblCustomer)
2. tblCustomer: CustomerID (Autonumber, PK); CustomerName; CustomerPhone; CustomerAddress...
3. tblProductType: ProductTypeID (Autonumber, PK); ProductType; ProductName (look up to ProductDetailID)
4. tblPoductDetail: ProductDetailID (Autonumber, PK); ProductCode (unique numer and letter, indexed (no duplicate)); ProductName; ProductPrice...
Now it seem ok with tbl 1 and 2 but I got stacked with relationship between tblOrder, tblProduct and tblProductDetail.
Please tell me what to do about this? Thanks

 

[Cronk]

Now I know your data structure is bad!

tblCustomer is OK. The missing bit is that the order table does not provide for what is being ordered, and tblProductType if it is to contain product groups should not contain any reference to Product.

tblProduct
ProductID - PK
ProductTypeID - FK to tblProductType
ProductName
etc

tblProductType
ProductTypeID - PK
ProductTypeName

tblOrder
OrderID
ProductID - FK to tblProduct
CustomerID - FK to tblCustomer
Quantity
Price
etc

The above provides a system for an order for one product. If you want an order to be more than one product you would create another table, tblOrderDetails and move the ordered product info to it, ie

tblOrderDetail
OrderDetail - PK
OrderID - FK to tblOrder
ProductID
Quantity
Price
etc

Cronk's first two steps of database design
Step 1 Turn your computer off
Step 2 Get your data structure right