Results 1 to 9 of 9
  1. #1
    speer is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    3

    Question Is this possible to do?

    I've been using Excel for a while to track shipments and its become unmanageable / messy so im switching to Access.
    Heres a picture of what im going for: http://i.imgur.com/VV5IR4B.jpg


    I've been able to create a similar database in Access, but not exactly how I need it. Ill have several rows of book titles in each line with their costs but this creates a new id for each book creating a relationship issue. As seen here:
    http://imgur.com/a/UanJb


    Is it possible to make the cell have multiple lines so I can input multiple rows of information for a single ID?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by speer View Post
    ...
    Is it possible to make the cell have multiple lines so I can input multiple rows of information for a single ID?
    Unfortunately, anything is possible. The easiest answer would be, no.

    The issue is that Access is a desktop database. It is better described as a relational database. Bottom line, there are specific rules when creating a relational database and working with an RDBMS.

  3. #3
    speer is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    3
    Quote Originally Posted by ItsMe View Post
    Unfortunately, anything is possible. The easiest answer would be, no.

    The issue is that Access is a desktop database. It is better described as a relational database. Bottom line, there are specific rules when creating a relational database and working with an RDBMS.
    Ah, bummer.

    Do you know any methods or workaround to my issue, I basically just would like each horizontal cell to have an expandable option so it can open and give detailed comments about it including items in the shipment, prices, etc.

    Is there any way I could do this?

    Im doing this in excel now and each shipment takes 15 or so vertical cells and if any software can help me manage this better it would be a god send.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It is really hard to explain. You need to normalize your data structure. You need to identify your business rules and define your entities in a relational diagram. With an ERD in hand you can start to use your RDBMS to create tables and build queries.

    From there you can start to build your application that will provide your users a graphical interface to view, edit, the data.

    This is how you use Access. Right now your mind is focused on what the GUI might look like and how a user might interface with it.

    I am sorry there is not am easy explanation to provide you with. It takes time to learn RDBMS and it takes time to learn application development.

  5. #5
    speer is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    3
    Quote Originally Posted by ItsMe View Post
    It is really hard to explain. You need to normalize your data structure. You need to identify your business rules and define your entities in a relational diagram. With an ERD in hand you can start to use your RDBMS to create tables and build queries.

    From there you can start to build your application that will provide your users a graphical interface to view, edit, the data.

    This is how you use Access. Right now your mind is focused on what the GUI might look like and how a user might interface with it.

    I am sorry there is not am easy explanation to provide you with. It takes time to learn RDBMS and it takes time to learn application development.
    I checked out Northwind as an option, its packs a lot of stuff, a lot more then I would ever use and I dont think it would help me in the format I am seeking.
    I found a way to have a relational structure set with two tables, with one holding the main shipment details, and the other holding the contents (since the contents will span several cells vertically) and I had it set as a subform. The subform can create a complicated relation since I usually require 5-6 "books" per subform for each shipment cell and this creates hard reading and also increases chance to easily confuse / incorrectly type shipment id's which would create a huge headache in a real environment.
    I've also created a form, but as suspected it creates a new cell for each book duplicating each individual users information for every book. This too is complicated. Adding information from a custom form would require additional effort where I would have to input all the users information for each individual book they have, this is very impractical.
    Here are some pictures which I hope can shed some light: http://imgur.com/a/diGRv
    The first picture shows what it looks like for me right now in Excel for real work, which is very easy to enter (I just copy and paste everything when I receive new orders which already includes customer information).

    Not sure what I will do at this point.

  6. #6
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    My advice is to stop focusing on the form (in other words the interface) and design your database on paper first. LikeItsMe said. I really urge you to read a textbook on Access, if not the entire thing at least the introductory chapters that talk about general RDB design.

    But if you need a short answer, create 3 tables (at least), one that holds the customer information, one that holds the header data of the order (that includes shipping details, dates etc) and one that holds the item lines (specific books). Once you have done that you will need to be a bit familiar with subforms to create a nice interface but that's a later step.

  7. #7
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is a link on data base design that should be helpful

    http://r937.com/relational.html

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    There is such a thing as a single table database. I know there are instances where two tables can accomplish certain tasks. However, automation of the simplest tasks require more than three tables. Working with three tables is likely to help you understand why this is true.

    I have talked with people who try to explain to me how easy it is going to be to migrate their data from Excel to a relational database because their office has been using Excel for years and all of their data is digitized. Being digitized is not enough. I would probably prefer to automate a business process that is currently dependent on paper and file cabinets. In my experience, the hardest thing to do is migrate data from Excel to Access. Especially when the Excel workbooks use a lot of built in Excel features.

    If you do not need more than two tables, you do not need Access.

    Here is a little intro to an ERD
    https://www.youtube.com/watch?v=-fQ-bRllhXc

    Here is an overview of Keys. To get started, you need to understand What a Primary Key is and what a Foreign Key is. Brainstorm and discover your Candidate Keys. Use your Candidate keys to determine the Primary Key for each of your Entities.
    https://www.youtube.com/watch?v=_aN-8kszIdA

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think this free data model from Barry Williams' site covers most of what you are trying to move from Excel to Access.
    Customer Order OrderItems Invoice Shipment Delivery
    and there are other models on the site that may have more info on specific parts of your application.

    Here is an older video by Art Langer that shows using an Invoice/Order document and creating a data model. There are a number of related videos. It's older, quality isn't great, but the concept/message is important.

    This material is the general top down approach to data base design from the Williams' site.

    These are the Steps in a Top-Down Approach :-

    Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
    Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
    Analyze the Things of Interest and identify the corresponding Tables.
    Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
    For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities. If you are just starting out, I suggest that you postpone this level of analysis.
    At this point, you can produce a List of Things of Interest.
    Establish the relationships between the Tables.
    For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
    Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
    Identify the Static and Reference Data, such as Country Codes or Customer Types.
    Obtain a small set of Sample Data,
    e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
    "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
    Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
    For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
    Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
    You need to define a Primary Key for all Tables.
    For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.
    I recommend that names of Reference Data Tables all start with 'REF_'.
    For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
    This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
    However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
    It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be. Which means, of course, that it often never gets done.
    Confirm the first draft of the Database design against the Sample Data.
    Review the Business Rules with Users,(if you can find any Users).
    Obtain from the Users some representative enquiries for the Database,
    e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
    Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
    Development staff, etc. and repeat until the final Database design is reached.
    Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums