Share via

One "order" or PO record, multiple items in one field with multiple quantities - is this possible and what is the ideal approach?

Anonymous
2024-09-16T00:27:41+00:00

Hello, new to access and having much fun learning. I am working on a project for someone starting a small business. I'm sure I will run into multiple brick walls like this where I need to ask around for solutions. If you would like to be my "go-to" for solutions, I will gladly pay. Let me know and we can discuss via email.

QUESTION:

I have a basic orders table to store orders submitted by customers. I haven't created an entry form yet. What I would like is for the customer to be able to select one OR multiple items to order, with unique quantities for each item.

EXAMPLE: customer wants to order 1 unit of product A, 2 units of product B, and 3 units of product C.

Is it possible to have this data stored in only one record for my orders table? If so, ideal approach? If not, best alternative?

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

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 40,181 Reputation points MVP Volunteer Moderator
    2024-09-16T00:42:40+00:00

    I would recommend studying the Northwind templates.
    You will see that one order can have many line items. The data is stored in two tables.

    The templates have lots of documentation accompanying them, and there are several presentations on YouTube as well.

    0 comments
  2. ScottGem 68,780 Reputation points Volunteer Moderator
    2024-09-16T01:22:03+00:00

    As Tom suggested review the Northwind templates.

    But you really need to spend some time learning relational database design. Your question is not a brick wall if you understand database design.

    One of the basis of relational design is the one to many relationship. When you have one item that can be related to many items. This is what you have; one order can have multiple items. This means at least FOUR tables. An orders table which lists data specific to an order. And an Order Details table which lists the items to be included in the order. Then you have a Customers table since Customers can have multiple orders and a Products table since a product can be ordered multiple times.

    0 comments
  3. George Hepworth 22,400 Reputation points Volunteer Moderator
    2024-09-16T03:02:24+00:00

    There are two versions of the Northwind Templates. One is for people just starting out using Access to create relational database applications, the other for more experienced Access developers who are looking for ways to improve their existing database skills.

    Both include the tables and relationships you need, although for someone first learning how to use Access, the Starter edition would make more sense to begin with.Investing time in learning about Normalization will help you create a usable relational database application in Access.

    0 comments
  4. Anonymous
    2024-09-16T06:07:52+00:00
    0 comments