Share via

MS Access 2007, relationship between "Customer" and "Order" tables.

Anonymous
2014-02-04T21:05:15+00:00

Hello, I'm making a DB for  my small business in online sales. I have 3 most important goals to reach :

  1. making a customers' information DB
  2. making a stock available and products quantity.
  3. knowing the containe of each customer's order.

Well, I'll try to be short now:

I've made 2 tables, Cutomers (with their info fieleds), and Orders. I've made a relationship between the table Customers with the primery key CustomerID - which is AutoNumber data type, and Customer ID - (numbers) field in the Orders table.

Well it works yes, but when I'm chosing "a customer" in Order table, in Customer ID field, it show them as numbers :((( I want them to be names (from Customers name field).

As it is a number to number relationship, and as the field "customer name" is not a primary, I can't connect the "customer name" to "customer id", becouse Customer Name = text , but Customer ID = numbers.

I guess I need to do something with the Customer ID lookup - combo box in Orders table.

But I don't know WHAT exactly to let it show the NAMES instead of ID numbers.

Please help me in that.

I hope my  question was clear , thank you in advance,

Best regards

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,820 Reputation points Volunteer Moderator
    2014-02-05T15:37:48+00:00

    And, in addition to John and Ken's comments, using lookup fields on the table level is not recommended (see http:\www.mvps.org/access/lookup.htm). Lookups should be done on form as Ken recommended, not on tables.

    Another point here, regarding John's OrderDetails table. Since you want to track inventory (item 2), then you should be using a Transactions table. This table will record ALL movement of stock either in or out. So it would replace the OrderDetails table by showing the line items for each order. But it would also be used to ADD items to inventory by purchase or manufacture. Allen Browne has an excellent article on how to work with Quantity on Hand calculations:

    http://allenbrowne.com/appinventory.html

    One last point. Its not a good idea to use spaces in Object names. This can come back to haunt you.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-02-04T22:01:16+00:00

    In addition to Ken's excellent advice, you really need at least two more tables!

    If you want to track information about Products then you need a table of Products; and I presume that each Customer may order zero, one, or many Products in each order, and each Product may be a component of zero, one or many Orders. So you need a Products table, and an OrderDetails table with one row for each product in each order!

    It's not a perfect database by any means, but do look at Microsoft's Northwind sample database which gives an example of this structure.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-02-04T21:24:42+00:00

    Never enter data directly in a table's datasheet.  Create a form bound to the Orders table and for the CustomerID use a combo box set up as follows:

    ControlSource:    CustomerID

    RowSource:     SELECT CustomerID, [Customer Name] FROM Customer ORDER BY [Customer Name];

    BoundColumn:   1

    ColumnCount:    2

    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

    For an example see InvoicePDF.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The main invoice form in this little demo includes a combo box to select a customer in this way.  Note also how the NotInList event procedure of the combo box is used to enable you to enter a new customer by typing the name into the combo box, something not possible in a table's datasheet.

    Was this answer helpful?

    0 comments