Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 71
  1. #31
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

    I'm attaching a zip. It contains a database that is a revision of the last one you posted.

    I created a Form called Form1 --Purpose: To assign Customers as FacilityMgr or Room POC. For me, and I do not know the details of your set up, it is a form that allows the user to:

    -Select a Customer from a combo of Customers
    -Select a Building from a combo of Buildings
    -offers an Option Group to identify
    a) if this is to be a FacilityMgr, or
    b) if this is to be a Room POC

    It does not change anything at this time. It simply goes through a process and displays Message of what I think should be done. This is an iterative way to determine what the proper logic should be.

    Open the database --separate from anything you are doing.
    Open Form1 and try it.

    Comments are welcome, as are it should do x, y, ...z
    Attached Files Attached Files

  2. #32
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Thanks!

    I already have a form which adds customers to rooms. But this does help me make improvements on it. I like your comments too, that helps me learn.

    I'd say my biggest hurdle right now is figuring out my search form.

  3. #33
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have updated that zip, you should try it again (small changes).

    It is just a means of showing what should be done in different events. It is something like "stub processing" we used to do when prototyping. You simply display messages at first. Once you get the basics in place, you can add code (or as much as you can).

    If you display some simple messages for forms and controls, it will help you with logic and flow. You can always comment them out later.

    Tell me in 5 or 6 lines exactly what is FacMgr vs a POC; some examples please. That, to me, is confusing and seems to be a major thing in your environment.

    Also, I looked at your latest db for forum and have a few things
    ??What is Customer 1264 --no info but shows up in relationship??
    ??Customer to Rooms??? what is this exactly --Are these RoomPOC??

    I think you said some of these were not used and the database is not cleaned up, but it gets confusing to someone not real familiar with your set up.

    Then, let's look at the search form. Do you have a list of things you want to search for? Samples?

  4. #34
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I will clean up the database and change building / room names before I send you the most recent version.

    A facility manager is the POC for an entire building, vs rooms POC is a POC for a single room. These two are separate because a POC for a room is likely not a facility manager, and a facility is likely not a room poc. However, they can be both. I hope that makes sense.

    Customers to Rooms is my junction table between customers and rooms. Yes, RoomsPOC. I should change the name to that.

    Customer 1264, not sure how that got there. I think I accidentally created a new record in my customers table and didn't put a name or anything. This does however brings up yet another complication to my search form. That is, not all customers have names. Some are actual workcenters (shop/office symbol) and/or Organizations. I.E. if shop XYZ is the facility manager for building 1, I cannot search the LastName field for XYZ and find the correct record.

    So then, the requirements for my search form would be:

    To search by:
    Only building name (cbo)
    Building name and room name (cascading cbo, filtered by building name)
    Cabinets (cascading cbo, filtered by room name)

    Organization (txt)
    Shop Name (txt
    Office Symbol (txt)
    last name / first name (txt)

    Switch Name (txt)
    Switch IP (txt)
    Switch Serial Number (txt)

    I think there would be a AND operator between each search criteria, but I could be wrong. I would like to be able to search for one criteria, or multiple. For instance, I could search for last name "smith" and see 20 records. Or Last name "smith" AND first name "John" and see 3 records. Or last name "smith" AND first name "John" AND building "1" and see 2 record. Or same as before AND room name "100" and see 1 record.

    I think from a user's standpoint, when searching for a customer, it does not matter if that customer is a facility manager or a poc. I think it should search through both tables and then return all results. For example, if Bob Smith is a facility manager for building 1 but a room POC for building 2 room 100, I need to search "Bob Smith" and see both records. The form will separate facility manager / room POC into the two subforms, so I don't need to complicate the search form further by making users search separately.

    And, just so we're clear, my intention is using the search form to populate data in frmMainRooms. So whatever we search by (room, name, switch name, etc), it should go to that record in frmMainRooms which then displays all the information about that room.

  5. #35
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    My thinking is that I should create a form and then instead of subforms, I should have list boxes.

    So, I would have building ID and room ID. And then everything else in list boxes. In the header I would create a search form. All my list boxes would have to be populated by sql queries. My problem is I think it would have to be several different queries.

    I have successfully set this up for using only room ID. Basically SELECT * FROM Customers WHERE RoomsID = [RoomsID]. [RoomsID] being a text box on the form which contains the value of my combo box, cboSearchRoomName.

    However, to make this also work by last name... I have no idea. I have been researching this all day, but I will dig deeper.

  6. #36
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I think I figured it out. That is, what needs to be done, not how to do it.

    In my header, I need to create all the search boxes. Building Name, Room Name, Last Name, First Name, cabinets, switches, etc. Then in the OnClick of my search button, I need to create sql queries so that it will search using the criteria I imputed and use that to populate two text boxes in my form. That is, building ID, and room ID.

    For instance if the search was SELECT LastName FROM facilitymgr WHERE facilitymgr.BuildingID = Buildings.BuildingID and that returned building ID 1, "1" would be placed into txtBuildingName. Then the facilitymgr list box would get its values from a separate query. Something like "SELECT * FROM Customers WHERE facilitymgr.BuildingID = [BuildingID]."

  7. #37
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree that some changes to your current methods are likely. As mentioned before, you can't necessarily do everything in 1 form and multiple subforms. There are a few steps that you can take that will help.

    -make sure your test data is accurate (you shouldn't have records that you didn't know were there, or records that are incomplete)
    -you can have "bad data" records to check error situations but those records you know about
    -mock up some search screens with details of what should be returned --this does not have to be in Access ---can be pencil and paper

    In addition to search, you have to consider CRUD - create a record, read a record,Update a record and delete a record.
    Don't be offended but you are still in the analysis stage and getting a better handle on requirements.

    Again as mentioned previously, think of the "information and details" you would want if someone else had this project and it was being handed over to you. Or, if you were to hand over what you have to a consultant developer-- what do you think they would be asking?

    When you have some ideas and insights, as in the last few posts, mock them up (prototype) and test them. Lots of things are "discovered" this way.

    Post your latest database after your clean up.

    Keep at it, you're doing well.
    Last edited by orange; 09-28-2015 at 08:36 AM.

  8. #38
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Thanks.

    I have read, updated, and delete records. I've discovered problems with duplicate data and had to use composite keys to solve this.

    Don't worry, you can't offend me.

  9. #39
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where are you located?
    Do you have a revised, cleaned database?
    Why were there duplicates? Why composite index? Why not remove duplicates???

  10. #40
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    I'm in colorado springs, co.

    I'll attach a recent copy of the database when I get home from work.

    I was having duplicate data if I were to add a customer a second time, or add a building or room multiple times. Or when assigning customers to rooms. I setup composite indexes to avoid this.

  11. #41
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    OK.
    You should have primary keys in tables -- to uniquely identify each record in the table. With a PK you can not add a duplicate, you'll get a warning based on err 3022. If you use an autonumber PK Access will be happy, but some users will identify records by FirstName, Lastname etc -- so you put a unique composite index on the combination (that makes sense to you) --the unique index will also not let you store a duplicate.

    Looking forward to your revised database.
    Last edited by orange; 09-28-2015 at 04:52 PM.

  12. #42
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Ok, I finally cleaned up the database. The names of buildings, rooms, etc have been changed to make it more understandable. I've also created a detailed list of tests and I've ran those tests. Results are attached as an excel spreadsheet. I have found some design issues that I will need to fix. Mostly w/ duplicate data. Some of these issues, I could've sworn I've already fixed... perhaps they got lost in the numerous database copies I've made. Pretty sure I just need to create composite indexes, will do that when I get time.

    All unused queries and forms have been deleted. frmMainRooms, Navigation Form, and related forms / queries have not yet been deleted. I'm going to wait until I'm absolutely sure they're of no use to me. I.E. when I'm sure I'm working towards actually creating a search form which meets my requirements.

    I started building a search form, titled frmTestSearch. It is extremely rough. Of course, I'm open to suggestions on how to do this. My idea is to create a search form in the header and use that form to populate building ID and room ID below. These two fields will be hidden later and only building name and room name displayed. Then I will have several list boxes which will get their data from a query based on the values of building ID and room ID.

    Currently I've only created the list box for RoomsPOC. I haven't a clue how to setup my search form, if its even possible to do it the way I'm thinking about doing it. I've spent way way too much time researching how to do this. Most videos, articles, forums focus on searching through a form which already contains records vs. using a query to populate values in a text box.

    One question not related to the search form. In my customers table, and also in my equipment table, there are several fields which contain duplicate data. Organization, Shop Name, Office Symbol, Rank, Equipment Type, Switch type, IOS version. Should I break this apart further into more tables and thus reduce duplicate data? I'm not worried about the extra size of the database for me not having more tables. I think having to deal with extra queries so that the user would see "SrA" as rank instead of "2" might be more work than its worth. But this is something I've never done before, I'm likely wrong.

    CleanedupDatabase.zip
    Last edited by ittechguy; 09-29-2015 at 12:15 AM. Reason: Added question about duplicate data in customers and equipment table

  13. #43
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have not yet looked at details/forms etc. I saved a copy of the CleanedUpDatabase.
    I looked at the relationships, and reformatted slightly (moved boxes to remove crossed lines).
    Why are there 2 SecurityOptions ??

    Re your question on duplicated data--
    The idea with database and normalization is to remove as much duplication/redundancy as possible. You really shouldn't have duplication in Organization, Shop Name, Office Symbol, Rank, Equipment Type, Switch type, IOS version. This is a sign that there may be another entity(s), some attributes may not be with the proper entity.
    It isn't the number of tables, necessarily, that could be an issue. Getting all of the things (entities) identified, and getting all of their relevant attributes is a key step.

    Ideally users would see and work with the terms/values with which they are familiar and comfortable. Users shouldn't be seeing codes that are used for the database's software purposes. If the user is familiar with Switch, then they shouldn't be seeing things like EquipmentType 13.

    I'm going to look at the other materials now.
    Attached Thumbnails Attached Thumbnails CleanedUp_00.jpg  

  14. #44
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I understand you have 4 Bldgs and 4 FacilityMgrs
    CustomerID BuildingID BuildingName LastName FirstName
    1260 1 A Snuffy Joe
    1261 1 A DerpyDerp James
    1262 1 A Smith Matthew
    1263 1 A Jones Alex
    1260 2 B Snuffy Joe
    1261 2 B DerpyDerp James
    1262 2 B Smith Matthew
    1263 2 B Jones Alex
    1260 3 C Snuffy Joe
    1261 3 C DerpyDerp James
    1262 3 C Smith Matthew
    1263 3 C Jones Alex
    1260 4 D Snuffy Joe
    1261 4 D DerpyDerp James


    If a Facility Mgr is the POC for all rooms in a Bldg, how can Matthew Smith be Facility Mgr for bldg 1 (name [A])

    CustomerID BuildingID BuildingName LastName FirstName
    1262 1 A Smith Matthew
    1262 2 B Smith Matthew
    1262 3 C Smith Matthew

    and also be POC for Room 200 in Bldg 1?
    BuildingID BuildingName RoomsID RoomName SecOptionID LastName FirstName
    1 A 13 200 Pin Smith Matthew
    4 D 27 100 Pin Smith Matthew
    3 C 26 200 Key 1 Smith Matthew


    Facility Mgr of a Bldg is POC for entire building, that is every room in that building????

    Perhaps the concept should be to identify BldgFacilityMgrs, and to make them POC for every room in the Bldg?

    I'm not understanding FacMgr and POC and it seems basic to you. Need to clarify what these are and are not.

    From my view, if Bldg A has rooms
    BuildingID BuildingName RoomsID RoomName SecOptionID
    1 A 10 100 Pin
    1 A 13 200 Pin

    then, since Matthew Smith is a FacMgr of Bldg A, he should be POC for room 100 and room 200.

  15. #45
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    That helps a lot, tahnks!

    I didn't take much time to add customers to rooms (RoomsPOC) or to buildings (facilitymgr). Sorry if there's any confusion.

    Essentially, yes, a facility manager is the POC for every room in the building. But they're not. That is, a facility manager "owns" the entire building and everything in the building. However, they usually do not have access to some rooms within the building for which they're the facility manager for. My thinking was if I could search for "Building A, Room 200" and see a listing of customers who are the POC for that room, I should also see a list of facility managers for the entire building, in a separate subform or listbox. The main reason for this is unfortunately, people do move around. To other locations on base or to other bases nearby, or the opposite side of the earth. Phone numbers also, can change. So if I'm looking for "Building A, Room 200" and I call Matthew Smith but get no answer, I want to be able to easily see a separate list of building managers. At which point, I'll call the facility manager and ask "Who do you know that can let me into Room 200?"

    Facility Managers are similar to room POCs, but different. I need to separate the two. Another reason is if we are intending to do something to the building that requires coordination with the facility manager, we need to call the facility manager, not just the POC for a room inside the building. A facility manager is responsible for maintaining the entire building. They coordinate any work orders with Civil Engineering or with the communications squadron to make any necessary repairs within the building. They control who has access to parts of the building, keys, etc. etc.

    So as for Matthew Smith, he may be the facility manager for all of building A, but he also needs to be listed as a POC for room 200. This is because some rooms are actually owned by the facility manager. If Matthew smith wasn't listed as the POC for room 200, if I searched for that room and only saw facility managers, it would not help me much because I don't know if a facility manager can get into that room or not.

    As for sec options_1, I created a lookup so that it would show "Pin, X09, Key" instead of "1,2,3." I probably didn't need a separate relationship for that. But that brings me to the reason I didn't separate my customers and equipment table more. I'm not entirely sure how to deal with IDs instead of actual text. For instance, in my list box, it'd get the values from a query. Something like "SELECT * FROM Customers, Buildings WHERE BuildingID = [txtSearchBuildingName].

Page 3 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-07-2012, 05:31 PM
  2. Replies: 4
    Last Post: 03-30-2012, 01:47 AM
  3. Replies: 1
    Last Post: 11-29-2011, 01:37 AM
  4. Link between subform and mainform
    By crowegreg in forum Forms
    Replies: 2
    Last Post: 08-24-2011, 07:05 AM
  5. Replies: 0
    Last Post: 03-14-2009, 12:33 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