Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82

    In over my head trying to create database for tracking cable numbers

    I have been working on creating a database to keep track of where wires/cables have been ran. I have information in an excel document that has all data that I need to transfer into the new database. At this point I am not as concerned with importing that data as I am about getting a good database set up first.

    I have managed to create a working database but the problem is that the database has 17 tables, quires, forms and reports for all the wire categories. I believe that I can trim the number of tables down to one or two.

    The Excel spreadsheet is set up with 17 sheets for each wire category (ASI, SDV, HDV...etc). The wire categories are pretty much static and the only time that a category would change is if we added one. The information that we track for each category is:

    Wire Gauge
    Wire Number
    Source Rack
    Source Description
    Destination Rack


    Destination Description
    Drawing Number

    A physical copy of the excel spreadsheet is printed out as wire are added or removed and the spreadsheet is organized by wire numbers. The wire numbers are three letters followed by four to six numbers (ASI1234). I have to be able to reports and queries that just pull the information for category of wires and organize them by wire number.

    I think I can get away with just two tables. A table for my wire categories and the wire information but I am not sure how the two tables would be linked. I attached a picture of how I think the category table should be linked to the wire info table but I don't think it is correct, because I get a list of all wire in all categories when I try to generate a report. Click image for larger version. 

Name:	Tabel Relationship.jpg 
Views:	52 
Size:	17.3 KB 
ID:	48008

    Any help is greatly appcriated

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


    Without seeing the Excel sheet, I would start with
    Click image for larger version. 

Name:	Design1.png 
Views:	51 
Size:	15.2 KB 
ID:	48009
    Notice there are NO spaces in the field names....

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    tblWireCategories: WireCategoryID, CategoryName;
    tblRacks: RackID, RackDescription;
    tblWires: WireID, WireCategoryID, WireGauge, WireNumber, SourceRackID, [SourceConnectionNumber], DestinationRackID, [DestinationConnectionNumber].

    (Obviously there will be several wires connected to same rack, so you have somehow to identify, to where in rack the particular wire is connected!)

  4. #4
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by ssanfu View Post
    Welcome to the forum...


    Without seeing the Excel sheet, I would start with
    Click image for larger version. 

Name:	Design1.png 
Views:	51 
Size:	15.2 KB 
ID:	48009
    Notice there are NO spaces in the field names....
    Sorry, I should have attached some screen shots of the excel file.

    Each column on the excel worksheets contains the various information about the number assigned to the wire, the source, destination and a description of where the wire is connected.

    At the bottom of the worksheet are all of the different categories that we use so we don't have to look through all of our audio cable before we find the network cable that we are trying to find.
    That is why I was thinking I would only need two tables.

    Click image for larger version. 

Name:	Excell Worksheet Top.jpg 
Views:	43 
Size:	45.4 KB 
ID:	48010



    Click image for larger version. 

Name:	Excell Worksheet Bottom.jpg 
Views:	43 
Size:	76.2 KB 
ID:	48011
    A concern that I have is how would I create reports that only pull up a certain category? When I am using the reports wizard I have to select a field from a table. So if I use the CategoryName field from the tblWireCategories table, would that just give me a report with every wire from every category?

  5. #5
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    The reason that we have the source description and destination description is to identify where in the rack a wire is connected.

    Just a question about your suggestions. You are saying that my tblWireCategoies table should have two fields or would the CategoryName be the information that is in the WireCategoryID?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @drunyan0824

    To assist you with a design to support your business, readers need to understand the "business processes" involved. We don't know the details as well as you, and we don't need to. But we do have to know the key points/steps in the business. Instead of describing excel to us, please just run through a day at the office, or, if it's more relevant, a job to run cable X from point A to Point B. What exactly do you do? What do you need to know about the wire involved, any spool/roll involved, the specifics of the "job" where the wire/cable was used?

    Whatever is important to you and your business should be included in the description somewhere.
    The forum is about Access and database not excel, and although some are proficient in Excel, others are not. To get maximum focused responses, be specific and concise.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @drunyan0824

    Quote Originally Posted by drunyan0824 View Post
    Each column on the excel worksheets contains the various information about the number assigned to the wire, the source, destination and a description of where the wire is connected.

    At the bottom of the worksheet are all of the different categories that we use so we don't have to look through all of our audio cable before we find the network cable that we are trying to find.
    That is why I was thinking I would only need two tables.
    That is a good starting point. You are not starting out "committing spreadsheet" - a good thing. "Committing Spreadsheet" means designing tables/relationships like the Excel spreadsheet.

    Arvil suggested a 3rd table. I would modify the design to be
    tblRacks: RackID_PK, RackType, RackNumber, RackDescription;
    "RackType" would be Source or Destination.
    Click image for larger version. 

Name:	Design2.png 
Views:	37 
Size:	16.6 KB 
ID:	48017




    Have you drawn/designed the dB on paper/cardboard/the window/...??
    "Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.



    Quote Originally Posted by drunyan0824 View Post
    A concern that I have is how would I create reports that only pull up a certain category? When I am using the reports wizard I have to select a field from a table. So if I use the CategoryName field from the tblWireCategories table, would that just give me a report with every wire from every category?
    You have ONE report for any selected category. But you could have other reports with different formats to display the data in different formats.
    You would set the criteria (the WHERE clause) to the category in a control on a form for the report record source (a query)


    The query MIGHT look like:
    Code:
    SELECT tblWireCategories.CategoryName, tblWireInformation.WireGauge, tblWireInformation.WireNumber, tblRacks.RackNumber, tblRacks.RackDescription AS SourceRack, tblRacks.RackNumber, tblRacks.RackDescription AS DestinationRack, tblWireInformation.DrawingNumber
    FROM tblWireCategories INNER JOIN (tblRacks INNER JOIN tblWireInformation ON (tblRacks.RackID_PK = tblWireInformation.DestinationRackID_FK) AND (tblRacks.RackID_PK = tblWireInformation.SourceRackID_FK)) ON tblWireCategories.WireCategoryID_PK = tblWireInformation.WireCategoryID_FK
    WHERE (((tblWireCategories.CategoryName)="RF"));
    On a form, you could have a combo box to select the "Category" (any one of the 18 categories) that would limit the report to the selected category.

  8. #8
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    @ ssanfu

    Quote Originally Posted by ssanfu View Post
    Have you drawn/designed the dB on paper/cardboard/the window/...??
    "Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
    I can draw out the tables and I know how I want the tables to change as I add new information. I managed to figure out how to set up a query to get information for only one of my categories of wires and generated a report from that query.

    The big problem that I am finding is that I can't make heads or tails of the macro builder, expression builder or VBA code.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can/would you post the dB and the Excel workbook (if there is no sensitive data in it)?

  10. #10
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    I can 't post the excel document, but I could post the database I have been trying to build with fake data for testing purposes. Would that be helpful?

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes. It would really help.

  12. #12
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by ssanfu View Post
    Yes. It would really help.
    Tables and Re;ationships Test for Wire Database.zip


    Here is the file.

    Your old programmer trick really helped. I really started to think about what I wanted the code to do instead of the code itself and I have made some decent head way on making the database.

    I was playing around with a form that I based off of the wireCategory query. I found that I suddenly had a new entry on my wireCatergory table when I updated the wireCatergory_PK field on the wireCategory Query. Then I tried updating the wireCategoryID field on the form and the wireCategory_PK field updated to the correct data. Then I was able to create a combo box based on the wireCategory and when you make a selection in the combo box the wireCategory_PK field is updated. Unfortunately, I used the wizard to build the combo box so I didn't really learn how to write the code.

    I was able to make a query that only returns records of a single wire category, but I don't understand the syntax. When I look at the query in design view there is a column that reads [wireCatergory]![wireCategoryID] and the criteria is set 1. I understand what is happening, the query is only pulling the records that correspond with the first entry of the wireCatergory table.

    What do I gain by having three tables instead of two?

  13. #13
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    The attached might help
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  14. #14
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    @orange

    I am sorry. I thought my original post contained the required information. In the future I will try to be more descriptive of what I am trying to accomplish with access.



  15. #15
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Thank you,

    I am starting to see the need for multiple tables. The rackID in my wireInformation table is depended on the what rack a wire is going to, rackID get its own table that is linked to the wireInformation table. You keep the number of fields in a table to one or two so you have less of a chance of corrupting the data and it make revising information easier.

    I noticed that in the relationships between the wireInformation Table, tblRack and tblRack_1 you selected the enforce referential integrity but you didn't select the cascade options. What was the reason for that? I would think that I would want have those checked so that if there was change to the tblRack that change would take place on all the records.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. soccer table head-to-head query
    By sgramesh75 in forum Queries
    Replies: 2
    Last Post: 03-29-2017, 11:09 PM
  2. Replies: 2
    Last Post: 10-22-2014, 08:03 AM
  3. How to Create Database for tracking KM Per Liter
    By Narinderpal in forum Database Design
    Replies: 1
    Last Post: 09-30-2013, 06:16 AM
  4. Replies: 1
    Last Post: 02-02-2012, 06:43 PM
  5. In over my head with a database class
    By fixittech in forum Database Design
    Replies: 3
    Last Post: 01-22-2010, 07:45 AM

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