Results 1 to 10 of 10
  1. #1
    KirstyAmanda is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    7

    Too Many Tables!


    Hi there, I'm a new member of this site, and in dire need of some access help!

    I've been on a Basic level access course with my work a few months ago, so I have a rough idea of how to begin, but I'm having trouble with knowing what information to put in which table and how many to create.

    I've attached the excel sheet I'm practicing with - basically, it is a sheet to record all the guarantee claims of a ship and its equipment - there are 20 ships in total all with their own sheet - however I'm keeping it simple with starting with just one.

    We want to transfer the form to Access, and want to be able to create forms and reports based on criteria such as the ships name, identification number, date the claim was closed, manufacturer, area of equipment- all of which are found in the excel sheet, eg - I want to see all the claims for all the ships that are in the Fuel System area - that sort of thing.

    I know its a lot but could anyone help me with what info to put in tables - at least to give me a head start. Many thanks in advance!

    Access Claim Tracker.zipAccess Claim Tracker.zipAccess Claim Tracker.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    How many tables are you using?
    youd have tShips table, and tContents table.
    keep adding XL sheets to those.
    what else?

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    KirstyAmanda is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    7
    I have a table for the ship name and ID number (PK) and a table with the ID number, hull, where it was built and other details, I have a table with the 6 Main ship areas, then a table for the subsequent area titles within each of those areas, and then a table for each of the sub-subheadings found in the areas found within the 6 main areas, all of which equates to 62 tables. I would then be wanting to create a form that allows me to enter a new claim into the system by choosing from 3 drop down menus like I have in the excel sheet.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    All information about ship, which don't change, can be in one table.

    What are those sub-subheadings? Some places in ship? And they are grouped together to some bigger places (subheadings), and those again to some bigger places (areas), ad then biggest ones (main areas)? It remains me unclear why you need this grouping, but let's be so.

    You don't need so many tables for all those areas. One possible design:
    Lev1Areas: Lev1AreaID, ShipID, AreaNo (for ship), AreaName, ... (those are main areas for all ships you have now - and you can alvays add them when you get some new ship);
    Lev2Areas: Lev2AreaID, Lev1AreaID, AreaNo (for Lev1AreaID), AreaName, ... (all 2. level areas for all ships);
    Lev3Areas: Lev3AreaID, Lev2AreaID, AreaNo (for Lev2AreaID), AreaName, ... (all 3. level areas for all ships);
    Lev4Areas: Lev4AreaID, Lev3AreaID, AreaNo (for Lev3AreaID), AreaName, ... (the last level - all equipment or whatever you want take account off are linked to these areas)

    And then p.e. Equipment Inventory table
    EquipmentInventory: Lev4AreaID, InventoryDate, EquipmentID, ...

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, Welcome to the forum

    How stable are the values in Sheet1? For the heading "Eq For Cargo", will there/could there be more entries?


    there are 20 ships in total all with their own sheet - however I'm keeping it simple with starting with just one.
    Not the best idea. If you are going to have multiple ships with some or all different data, prepare for it now. Having to try and modify the dB for more ships will be a major task. Will/would have to change queries, forms and reports.....

    How much in common are the "Sheet1" headings/data for the 20 ships?


    I have a table for the ship name and ID number (PK) and a table with the ID number, hull, where it was built and other details, I have a table with the 6 Main ship areas, then a table for the subsequent area titles within each of those areas,
    Would you post your dB?

  7. #7
    KirstyAmanda is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    7
    Hi there!Database2 Copy.zip

    Sheet 1 is exactly the same for all ships, I want to be able to import all the ship's excel sheets, and then from there, create reports based on claims from all the ships (eg, how many ships have claims in the Eq for Cargo" are, or how many are there for "Radar Plants"

    I also want to be able to add new claims in for the existing ships, from a form that offers drop downs in the same was as the excel sheet does. (If that's possible, I don't even know!)


    My database is a bit of a shambles but I am happy to post it!

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have a clear statement/description of what you are trying to accomplish?
    I mean something clear enough that you could give it to someone else and they could build/design the database.
    It seems to me that you are plugging a way at something that isn't clearly defined.
    I'm not trying to be negative; just trying to point out that more analysis and design will help you to set up the database.
    Putting your requirements to paper clearly will identify/highlight situations that you have not yet considered.

    I did provide links to design info in post 3.

    You can also review some of the "knowledge nuggets" -short and humorous - available from BA-Experts
    Here are a few
    Process Analysis
    Data Analysis
    Getting Requirements

    Good luck.

  9. #9
    KirstyAmanda is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2017
    Posts
    7
    Access Claim Tracker.zipI want to take all the individual excel sheets (example sheet attached - all excel sheets have same formulas and format) and put them into a database, from which I can create reports based on certain criteria, such as the area of the claim (there are 3 drop down menus in the excel sheet- each reactive to the previous selection) so for example, I'd want to be able to create a report that shows all the claims from all the ships in the area of "Eq for Cargo" or "Diesel Engines for Propulsion" I'd then also want to be able to add new claims into the database for the ships, with a form that would deliver the same idea as the drop down menus in the excel sheet.

    We've has the brainstorming session, but our boss doesn't seem to realize that we've all only done the basic course and so has no idea how he wants it done, he just knows the end product.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Don't be too quick to jump into physical Access.
    Get a clear description of what your "business" is. This does not mean Excel spreadsheets or dropdowns or selections.

    Here's 2 samples of the level I'm talking about (from RogersAccessLibrary)
    1:
    ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
    An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job. ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.

    An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator).
    Employees can also earn certifications necessary for their job.


    2:
    We are Consolidated Widgets, the largest widget distributor in the Mid West. Our customers are small independent Widget Dealers who in turn sell the widgets to the consumer market. On the other end, we have our Manufacturers who make the widgets. Our function is to act as a middleman and warehouse for the independent dealers.

    In an effort to make our Dealers more aware of new trends in the widget market, we are hosting a series of seminars, which allow the Manufactures to explain and extol the virtues of their products. Therefore, we need a computerized system that will help us manage the event. We have selected Microsoft Access as the rapid application tool to accomplish this.

    The seminars work like this: The event takes place over a weekend, Saturday and Sunday. On each day, there will be seven seminars, four in the morning and three in the afternoon. Lunch will be provided for the attendees. Each seminar lasts 45 minutes, allowing 15 minutes time between seminars. In order to manage the seminars more effectively, we are asking each Attendee (there can be more than one Attendee from each Dealer) to sign up for the seminars they wish to attend.

    The Manufactures may have one or more seminars, which may be presented by one or more of their employees. We need to be able to track that information for the web site, if the information changes, we want to be able to make it instantly available. We also need to track other information about the Seminars, like its location (room number), the starting and ending times, the seminar description, etc.

    For the Dealers, we need to track the Attendees and their schedule, purchase order, sales tax rate, total cost to the Dealer, method of payment, etc.

    For our own purposes, we need to track the Attendees, their schedule, our employee who entered the registration information, the cost of each seminar, method of payment, whether it is paid in full, etc.

    We also want to have a simple survey where the Attendees rate each Seminar. We want to be able to give the Manufacturers feedback on how effective their seminars are. The rating will be a simple 1-5, Poor-Good style rating. The Manufactures will get a report giving them the percent for each level and the mean for the seminar. This will be on a seminar/hour basis, so that they can judge which of their people are the most effective presenters.



    If you work through this older tutorial (45-60 minutes) you will experience the process that will lead to a data base design to support your requirements. You have to work through the example to get the benefits of the tutorial- but what you learn can be used with any database.

    It seems you have had a basic course in MS Access. I'm suggesting a basic course in Database and Database design.

    Good luck.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  2. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  3. Replies: 5
    Last Post: 11-26-2013, 11:11 PM
  4. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  5. Replies: 10
    Last Post: 07-12-2011, 11:09 PM

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