Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Contractors & subcontractors likely relate to a company even if they are independent. Therefore I'm seeing



    tblCompanies - with all company related data (this does not include personnel)

    tblPOCs - multiple or single poc per company; tblCompany ID as a foreign key, with all poc values (phone, name etc)

    tblTrades - PK with trade name and or trade type

    tblPOCtrades - relate trade (e.g. plumber) with POC. Therefore, any poc can hold multiple tickets or just one

    With that scenario, you should be able to search for any trade OR company in Tennessee, or even plumbers named Smith. Note that this is all off the top of my head. What you need to do in working this out is design on paper. IF you understand entity/attribute relationships you have a good chance of working it out. If not, that's where you need to start. The upside is that if you can envision a requirement it is 99% possible. The trick is having the underlying knowledge of db design principles.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Quote Originally Posted by orange View Post
    My understanding of this:

    Subcontractor "Electrician A" has 4 identified people (qualified electricians) that may be available for tasks/jobs.

    Referential integrity refers to the accuracy and consistency of data within a relationship. In relationships, data is linked between two or more tables. ... So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

    Can you lead us through a scenario indicating the process:
    - what triggers the need for subcontractor and workers,
    - who does what exactly to find resources,
    - what exactly happens when x resources are identified,
    - then what??

    How did/are you identifying Specialty? Are you working from some standard industry classification scheme?
    Who is expected to maintain the data?

    You have a table in your queries that is not defined NADVendorT

    Close, but not exactly. Subcontractor "Electrician A" has 4 points of contact (i.e sales, parts, accounting, human resources). Sometimes there are multiple points of contact just based on other things. The important part is there are multiple points of contact for each company.

    Scenario 1:
    1) We are awarded a job in Texas. We need an electrical company for the job.
    2) Someone in my company searches the vendor database. Chooses Texas from the dropdown list, picks electrician from specialty, and boom. A list of all electricians in Texas appears. He picks up the phone and calls each one. He will choose the point of contact that best fits (perhaps the sales contact in this case).

    Scenario 2:
    1) We are awarded a job in Texas. We need a very unique piece of equipment for the job. A manager says, oh try calling 'Company A'. They should have that.
    2) A person in my company searches "Company A" in the keyword. Calls the correct point of contact (perhaps, in this case it will be the contact for parts).

    Scenario 3:
    1) We are awarded a job in Texas. We need a unique piece of equipment A manager says, oh try calling 'John Smith'. I forget his company name, but I talked to him before and he has that stuff.
    2) A person in my company searches 'John Smith' and the company pops up. He picks John Smith from the associated contacts and calls him.

    Scenario 1 and 2 currently work fine in the database I attached earlier. Scenario 3 does not. That is where I am stuck.

    Identifying specialties: These are somewhat standard in the industry, but we have adapted and altered for our uses. I have created the table in my database.

    Table in queries NADVEndorT: Where did you see that? It should be deleted. That was from an initial version of this database that I have since changed. I thought I deleted all references.

    Maintaining the data: The data will be maintained by multiple users. Managers and Engineers will continuously add companies as we find new ones.

    I hope I have answered all your questions.

  3. #18
    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
    Qry DivisionQ
    SELECT DISTINCT NADVendorT.Division AS Expr1
    FROM VendorT, NADVendorT
    ORDER BY NADVendorT.Division;

    How do you know which PointOfContact is associated with which area/division?

    Qry StateQ
    SELECT DISTINCT NewVendorT.State AS Expr1
    FROM VendorT, NewVendorT
    ORDER BY NewVendorT.State;

    Specialty is associated with Vendor, correct? Are you anticipating that a Vendor may have multiple Specialty(s)?
    If so , I don't think your relationships allow that.

    In scenario 1, how would your "someone" know which PointOfContact, at each identified Vendor, to call? How do you relate PointOfContact and "area of expertise" (sales, parts, accounting, human resources...)?

    In scenario2, "Calls the correct point of contact" ---which he/she would identify How?

    In scenario3, where manager remembers contact name,but not the company-" but I talked to him before and he has that stuff". John Smith may be a name related to multiple companies --so you'd call them all? What if that manager hadn't remembered the name - John Smith? Then what? Do you plan on maintaining multiple Notes/comments with each interaction with each PointOfContact at each Vendor? If so, need to allow for that in your set up. It could be useful if trying to recall the Contact name and/or vendor name from previous activities.

    Anyway, it seems to me -and I admit to not knowing the details nor scope of your proposed business/database - that your tables and relationships do not meet your requirements.
    Good luck with your project.

  4. #19
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Took a look at db. What you say doesn't work could be as simple as
    Code:
    SELECT VendorT.VendorID, VendorT.[Company Name], UniqStateT.State, POCT.FirstName, POCT.LastName
    FROM (VendorT INNER JOIN UniqStateT ON VendorT.State = UniqStateT.State) 
    INNER JOIN POCT ON VendorT.VendorID = POCT.VendorID
    WHERE (((UniqStateT.State)="ga") AND ((POCT.LastName) Like "*miller*"));
    and having an unbound control for searching on either first or last name. However I see that the focus is on modifying filter in vba rather than record source of form or using queries as filter objects. Regardless, you have no controls for a name search.

    but you do have some design issues, I think:

    -some fields contain mixed data (numbers and text)
    -tables that look like lookup tables are indexed but allow dupes (e.g why would you allow duplicate state abbreviations?)
    -there are few PK fields but what's odd is that in relationships, POCT.POCID shows it's a PK but not in design view of table. Can't recall ever seeing that before
    -no referential integrity is set anywhere, so if you change a value for Specialty it won't propagate. If you used a different PK rather than using values as PK's (e.g. autonumber like 1 instead of "Canopy") then you wouldn't need to, but because you don't, you need to.
    -you cannot search on company??
    -if I enter 4 search parameters while drilling down and 1 of them needs to change, you have no way to negate just one and the entire search needs to start over. That's not a design flaw but it is a user-friendly type of thing. A blank as 1st combo value could help.

    I was going to point you at AB search form but see that I already did that. A thorough study of it should solve your problems - if you have table designs that can support it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #20
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Thank you for your posts. I will have to read them carefully to try to understand everything. In areas where you are confused why I did something, it is likely because I am learning and it's probably quite simply, wrong. I literally never used Access or knew a thing about databases, until a few months ago when I googled "What is a database" and then watched a bunch of youtube videos. Hah! This was my first database I have ever made, so it's a big learning curve. You've been very helpful. Once I play around and think about everything you wrote, I'll let you know if I have further questions. It might take me a few days of playing around to understand everything.

    Thanks!

  6. #21
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Quote Originally Posted by orange View Post
    Qry DivisionQ
    SELECT DISTINCT NADVendorT.Division AS Expr1
    FROM VendorT, NADVendorT
    ORDER BY NADVendorT.Division;

    How do you know which PointOfContact is associated with which area/division?

    Qry StateQ
    SELECT DISTINCT NewVendorT.State AS Expr1
    FROM VendorT, NewVendorT
    ORDER BY NewVendorT.State;

    Specialty is associated with Vendor, correct? Are you anticipating that a Vendor may have multiple Specialty(s)?
    If so , I don't think your relationships allow that.

    In scenario 1, how would your "someone" know which PointOfContact, at each identified Vendor, to call? How do you relate PointOfContact and "area of expertise" (sales, parts, accounting, human resources...)?

    In scenario2, "Calls the correct point of contact" ---which he/she would identify How?

    In scenario3, where manager remembers contact name,but not the company-" but I talked to him before and he has that stuff". John Smith may be a name related to multiple companies --so you'd call them all? What if that manager hadn't remembered the name - John Smith? Then what? Do you plan on maintaining multiple Notes/comments with each interaction with each PointOfContact at each Vendor? If so, need to allow for that in your set up. It could be useful if trying to recall the Contact name and/or vendor name from previous activities.

    Anyway, it seems to me -and I admit to not knowing the details nor scope of your proposed business/database - that your tables and relationships do not meet your requirements.
    Good luck with your project.

    Thank you for your reply! To answer your questions:

    Points of Contacts are only associated with companies (vendors). They are not associated with divisions. Divisions and specialties are also only associated with vendors, and for now, I do not anticipate vendors having multiple specialties, though I contemplated that and realize I would need another table and more relationships. I avoided that because it is my first database and was trying to not over complicate.

    In scenario 1, the person would know what point of contact to call based off experience and some other factors. For example, some vendors have multiple points of contacts because they might have a california contact, and an arizona contact. So the person would call whomever is the contact in the correct state. Other times the contacts vary by department. It can be a bit all over the place. Whomever does the search, will have the experience level to determine. In fact, sometimes we send emails to all points of contact. It really just depends. This database will only be used by certain people in our company with a high enough level of expertise and understanding.

    In scenario 2, same answer as above. Anyone with access to this database will have enough experience to know who to call. If not, they can rely on the notes by the contacts name. I might note 'call this person for parts' or 'call for jobs on west coast'.

    In scenario 3, yes if they said john smith and 15 came up, that would be fine. I could search and say to my manager, hey you mean the john in texas with ace environmental?! That's enough to jog his memory. Truthfully, if I search a keyword and a first name, it's unlikely ill get more than a couple results. If the manager doesn't remember the name, then i search by keyword or state. For our purposes, it will get us there. The database functions in the way I want it to, currently (though admittedly it may be designed in a poor manner due to this being my first), I just cannot search by first or last name right now.

  7. #22
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Quote Originally Posted by Micron View Post
    Took a look at db. What you say doesn't work could be as simple as
    Code:
    SELECT VendorT.VendorID, VendorT.[Company Name], UniqStateT.State, POCT.FirstName, POCT.LastName
    FROM (VendorT INNER JOIN UniqStateT ON VendorT.State = UniqStateT.State) 
    INNER JOIN POCT ON VendorT.VendorID = POCT.VendorID
    WHERE (((UniqStateT.State)="ga") AND ((POCT.LastName) Like "*miller*"));
    and having an unbound control for searching on either first or last name. However I see that the focus is on modifying filter in vba rather than record source of form or using queries as filter objects. Regardless, you have no controls for a name search.

    but you do have some design issues, I think:

    -some fields contain mixed data (numbers and text)
    -tables that look like lookup tables are indexed but allow dupes (e.g why would you allow duplicate state abbreviations?)
    -there are few PK fields but what's odd is that in relationships, POCT.POCID shows it's a PK but not in design view of table. Can't recall ever seeing that before
    -no referential integrity is set anywhere, so if you change a value for Specialty it won't propagate. If you used a different PK rather than using values as PK's (e.g. autonumber like 1 instead of "Canopy") then you wouldn't need to, but because you don't, you need to.
    -you cannot search on company??
    -if I enter 4 search parameters while drilling down and 1 of them needs to change, you have no way to negate just one and the entire search needs to start over. That's not a design flaw but it is a user-friendly type of thing. A blank as 1st combo value could help.

    I was going to point you at AB search form but see that I already did that. A thorough study of it should solve your problems - if you have table designs that can support it.

    Sorry, maybe a dumb question but what is PK...?

    Looking through these issues, but quick responses:
    -fields contain mixed data: possibly due to necessity, or more likely due to my inexperience. I'll investigate.
    -lookup tables allow dupes: I'll change this. Due to inexperience.
    -PK fields, not sure what that is. Hah, let me know and I'll investigate this design flaw.
    -cannot search company - the company is searched in the keyword field. Keyword searches either company or the notes field.
    -4 search parameters flaw - yes i realized this but was too inexperienced to fix, so i was picking my battles.

    I'm fairly certain my approach from the beginning (split table using apply filter for searching) is probably not the best way, but it was what I started with being a total beginner just watching youtube videos.

    Thanks again for all your help.

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    PK = primary key. You could get instant answers by searching rather than waiting for someone to respond. Not only that, it would present other related subjects of equal importance, like FK.

  9. #24
    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

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

Similar Threads

  1. Searching a subform field from the main form
    By mindbender in forum Queries
    Replies: 2
    Last Post: 11-01-2018, 12:16 PM
  2. Keyword Search from Subform
    By jdowning in forum Forms
    Replies: 5
    Last Post: 05-20-2016, 07:47 AM
  3. Replies: 4
    Last Post: 02-05-2016, 02:32 PM
  4. Searching by field on subform on main form
    By helen21112010 in forum Forms
    Replies: 1
    Last Post: 10-16-2012, 07:55 AM
  5. Replies: 3
    Last Post: 08-22-2012, 03:28 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