Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    37

    Comparing the results of a calculated field within a table

    I'm sorry to say, I'm not even sure how to ask this question appropriately, so please bear with me.



    I'm working on a database comprised of three tables, and a joining table.

    The first of the three tables (Households) contains contact information about family units - names, addresses, email and so on. In this table, there is a "CompleteName" calculated field, which takes the data entered in various other name fields, and creates a complete name. For example, enter the following in the form:

    "Mickey" "M" "Mouse"
    and
    "Minnie" "Q" "Mouse"

    and the calculated field displays
    "Mouse, Mickey M and Minnie Q" as the CompleteName

    At the bottom of the form is a button that leads to the next tab in the form, where a subform for another table ("SkillSets") appears. What I'm hoping to do is to cause the clicking of the button to compare the CompleteName field with other such fields in the table and alert the user if the CompleteName data already exists in the table, as that would indicate the household already exists within the database. Ideally, it would offer to edit the record instead. And, of course, if the record does not exist, then the SkillSet tab would open and allow skills to be assigned to the household.

    But I'm still quite new to all of this, so am a little lost. Any guidance would be appreciated.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    Calculated fields have limited benefits and a number of disadvantages particularly when trying to use in a comparison. Just because you can, doesn't mean you should.

    Also sounds like your data is not normalised which can lead to unnecessarily complex or impossible to write queries.

    For example - from your example data, what if the two family members have different surnames? Or are recorded differently - e.g.

    "Mouse, Minnie Q and Mickey M" as the CompleteName

    but perhaps you have resolved these issues so if that is the way you want to go, you could use the dlookup domain function but it will be slow for any significant amount of data because calculated fields cannot be indexed. something like


    Code:
    dim ExistsID as long
    
    ExistsID=nz(dlookup("ID","myTable","ID<>" & me.ID & " AND CompletedName='" & me.completedName & "'"),0)
    
    If ExistsID<>0 then 'this name already exists
    
        'do something here
    
    end if

  3. #3
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    37
    You raise a good point, though I'm not sure I understand what you mean by not normalized. There are three tables - Households, SkillSets and Resources, and a joining table that links them all together for a many-to-many relationship. The fields themselves are pretty standard - there's a HoHFirstName, HoHMiddleInitial, HoHLastName, and the same three for Spouses. (HoH in this case is "Head of Household"). I readily admit that I'm a neophyte, but I *think* they and the other fields are correctly configured. My odd man out is the one single calculated field. It isn't strictly speaking necessary, but I had hoped it would at least alert someone to the possibility of duplication, without halting the process every time another "John Smith" was entered. As we have several families in the community with successive generations of the same name, I was hoping that it would be at least raise a red flag. But I'm certainly willing to try another approach, if you have a suggestion?

    In the case of differing surnames, the calculated field addresses that by adding the surname for the spouse, if it differs. For example, if Mickey M Mouse and Daisy D Duck are an item, the field calculates this as "Mouse, Mickey M and Daisy D Duck" (or the other way around, depending on how the couple self-identifies the head of house). The database is expected to be about 5000 records altogether.

    Would it be helpful (or even permissible) to post images of the forms, or copies of the database? I sometimes feel that I haven't the proper vocabulary to explain things correctly.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    I'm suggesting that your data is not normalised because you have two 'name structures' in one record. If there is a change in the definition of a household you need to change the table plus relevant queries forms and reports and no doubt code or macros.

    It may be how you have it structured works for you in your situation but what if you have a Mormon family with multiple wives? Or a family splits up? Or the wife of one John Smith divorces and becomes the wife of another John Smith (unlikely I know but...). Or you have two couples with the same names and middle initial but live at different addresses (which is probably why you are requiring this routine in the first place). Or one person who is HOH of two properties? You talk about skillsets of the household - what about the skillsets of children, parents, aunts and uncles who may be living at the same address?

    You appear to be creating a field to be used in a report rather than one suitable for checking duplicates.

    You can upload images and files if you wish, but before you do, google 'normalisation' and find some templates customer/contact databases where one customer (household in your case) can have many contacts (HOH and partner in your case) - Access has a few. Until you understand the concepts properly it will be difficult to help.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    On top of what ajax already pointed out, any time you rely on free form text fields to provide you with an accurate link between one table and another you are going to be sorely disappointed. Your tables should have a primary key field that is NOT subject to change. For instance if you update the last name to correct a spelling error on your household table you have just orphaned all the skills on the household skill table.

    What you should really have is something like

    Code:
    tblHousehold
    H_ID  H_Address  H_City  H_Zip ----> other household related fields (building type, etc.)
    
    tblPeople
    P_ID  P_FirstName  P_LastName ----> other person related fields (income, gender, etc.)
    
    tblHouseHoldPeople
    HP_ID  H_ID  P_ID ----> other people in the household fields (date joined, date left, etc..)
    tblHouseholdPeople would be a junction table between people and households, this way if someone moves or marries into another household you're tracking you have the ability to move them between households.

    Next you'd want your skills, and the skills should be related to the PEOPLE rather than to the household, that way when you move a person their skills 'leave' the household they were in and 'join' the household they move to

    Code:
    tblSkill
    S_ID  S_SkillName ---> other skill specific fields (how frequently certification needs to be refreshed etc)
    
    tblPeopleSkill
    PS_ID  P_ID  S_ID ---> other personal skill related fields (certification dates, etc if applicable)
    you may also want to consider setting person relationships i.e. X is the Father of Y and so on.

    all the ID fields in this example would be autonumbers. Autonumbers can't be changed unless you change the structure of your table which would ruin your data anyway and they are NOT subject to change over time which is what you are after.

  6. #6
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    37
    Thank you for your reply! I'm quite certain I lack the wit to describe this properly, but here goes:

    The database is designed to replace a printed book that, in printed form, is difficult to maintain and expensive to keep current. It essentially is nothing more than a list of people who have skills and/or resources available in the event of an emergency. Who has a backhoe? Who has ATV's? Who has medical expertise, or plumbing skills, or can cook for a large group? etc, And which of these people are nearest to the need?


    At no time is a free form text field being used as a link between tables. the database looks something like this:
    tbl_Household
    Primary Key: HouseholdID
    HoHLastName (Head of Household Last Name)
    HoHFirstName
    HoHMI (Head of Household Middle Initial)
    SpouseLastName
    SpouseFirstName
    SpouseMI
    Address
    Address2
    City
    State
    ZipCode
    Phone
    Email
    CompleteName (this is the calculated field. It used primarily for display purposes at the top of the form, but I'd also like it to be used as a simple warning if "Mouse, Mickey M and Minnie" are already in the database. The user will be able to either edit the existing listing, or add it again - but at least they will be warned, in plain English, that they might be inputting redundant data.

    The household form does not concern itself any other household members, so no information about relationships, or dependents or the like is required. The only other fields are a yes/no field asking if they can help with foreign (non-English) languages and if so, which language, and a field that asks how many people would they be willing to house in the event of an emergency.

    The second table is tbl_SkillSet.
    Primary Key: SkillSetID
    HouseholdID
    Various yes/no fields related to potential skillsets, such as:
    AutoRepair
    EmergencyMedicalTechnician
    Counselor
    Plumber
    Also, the table has an occasional text field for more specific information about a skillset.

    The third is tbl_Resources
    Primary Key: ResourceID
    HouseholdID
    Various yes/no fields related to potential resources, such as:
    Generator
    FarmTractor
    CuttingTorchWelder
    Backhoe
    Also, the table has an occasional text field for more specific information about a resource.

    There is a joining table
    tbl_HouseholdSkillSetResourceJoin
    Primary Key: HouseholdSkillsetResourceJoinID
    HouseholdID
    SkillSetID
    ResourceID

    The data entry form is a tabbed form. The first tab is tab_Household, and includes most of the fields listed on that table above. The complete name appears at the top, and is automatically created as the name data is filled in below. This "CompleteName" is displayed at the top of all form, regardless of which tab you're on. When the form is complete, there is a button at the bottom that moves the user to the second tab.

    The second tab is tab_SkillSet. It contains a subform (sbfrm_tbl_SkillSet) and has checkboxes for all of the yes/no fields listed on that table above, and where additional information is required, text boxes to enter specifics. At the bottom is another button that moves the user to the third tab.

    The third tab is tab_Resource. It contains a subform (sbfrm_tbl_Resource) and has checkboxes for all of the yes/no fields listed on that table above, and where additional information is required, text boxes to enter specifics.

    That's pretty much it, so far. In all my testing, the data entered ends up in the right places in the right tables (yay!) and while I have much further to go (like figuring out how to make an easy-to-use query form that let's the user look up plumbers who have backhoes and live in a particular city, and figuring out how to import the existing records from excel), what I'm hoping for at the moment is a way to remind the user that the household he is creating may already exist, and an offer to edit that record instead of creating a new one. My idea was to use the CompleteName field as the comparison, as there are many John Smiths, but not many "Smith, John J & Jenny J" Other suggestions are welcome, but so far, all others I've received have revolved around things that change fairly often (like phone numbers, or addresses).

    But if there IS more than one, the user can freely add the second one. Each will show up in the database with it's own contact information and it will be up to the person using the database to determine which "Smith, John J & Jenny J" they're looking for. And if the spouse changes, all that need happen is the household record is edited to reflect or the new spouse or remove the old one completely. Or am I missing something?

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    Or am I missing something?
    It looks like you have employed a 'semi excel' method in that you are combining data storage and presentation into the design (the excel way) rather than keeping data storage and presentation separate (the database way). I say semi because you have separated out some data into different tables but havent gone far enough. But at the end of the day, it's what works for you, just don't be surprised if you find the same comments coming up if you have other questions in the future.

    Good luck with your project!

  8. #8
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    37
    Thanks! You're probably correct - I'm certainly much more acquainted with Excel than Access, so I may have created some weird hybrid of the two. Also, I'm trying to keep an eye on the need to import the spreadsheet data into the access tables, which is already proving to be tricky.

    If I understand you correctly, I should have created a separate "household" table, a separate table for the head of the household and a separate table for the spouses. I assume the address field and other contact information would go in the Household table? It's counter-intuitive to me. I think of Household as "family" rather than "location".

    What else should be separated out?

    A HUGE part of my issue is that I'm not starting from scratch here. The data I have to work with is about 3000 records in a spreadsheet. There are no separations of names (i.e, the spreadsheet contains a single name field, "Doe, John and Jane" along with the various skillsets and resources with "X" in the columns. I have no way of determining whether John or Jenny has the skill or resource, whether they have middle initials, or much else. Even separating out the HoH from Spouse is a manual process. I can't ditch this data - so breaking it down too extensively, while probably the right thing to do for Access, will also be very difficult to accurately accomplish.

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    I should have created a separate "household" table, a separate table for the head of the household and a separate table for the spouses
    not quite - a separate table for the household and another for people - 'occupants', family members', however you want to describe them. This latter table would also include a field called say 'position' or 'relationship' and would be populated from a choice of say Head/spouse/son/daughter/lodger etc.

    I assume the address field and other contact information would go in the Household table?
    yes to address field and say landline, but mobile/email would go in the other table since these are usually personal to the individual. If individuals can have more than one mobile then these to would be stored in a 'communications' table

    It's counter-intuitive to me. I think of Household as "family" rather than "location".
    interesting. I think 'here is a place and these are the people who live here (who may or may not be related)'. You think 'here is a group/family of people and this is where they live'. But it does not change the concept of how the data should be stored and related. You can think of your data like this and you can see how the tables are arranged and linked. Take each bit of data, think can they only have one or can they have many? and work your way down until you find the place it fits.

    Code:
    households 
                          have many people
                                                have many skills
                                                have many mobiles
    What it does change is the way you come into the data - but it doesn't matter where you come in, you can search up and down the relationships. If you are going to search the data you will probably start off with skills which is actually down the bottom of the relationships, then look at household address at the top of the relationships for those nearest to where the skills are required and finally at the people in the middle relationship and their contact details. If you have yellow pages in your neck of the woods, think how you use that.

    Not sure is that is helpful or not - hope so

    I'm trying to keep an eye on the need to import the spreadsheet data into the access tables, which is already proving to be tricky.
    best way is to either link to excel or import to a temporary table then use append queries to append different columns to the appropriate tables - starting with household so your append to people can include a link on the address or similar field to get the household ID to put into the people table as a family key

  10. #10
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    37
    That's all very helpful - thank you for the more in depth explanation. I'm still struggling with the problem that the existing data doesn't contain sufficient information to attach skillsets or resources (or even phone numbers or emails) to specific individuals. In fact, there are only two name fields at all! The first is labeled "Head of Household and Spouse" ("Doe, John and Jane") and the second is "Preferred Name" ("Doe, John"). I'm using the latter field to determine which name to use as HoH, but all the remaining name fields (first, last, mi, & those of the spouse) are all my attempts to break the information down within the parameters of the available data.

    The spreadsheet (about 3000 records) literally looks like this:
    Preferred Name Hoh & Spouse Name Address City Zip Phone Email Appliance Repair Automotive Repair Other Specify Other Light Truck Heavy Truck Backhoe
    Doe, John Doe, John& Jane 1234 Main St Mytown 11111 (111) 222-3333 me@here.com X X Tidlywink Champe X X

    So whatever I come up with, the data above has to squeeze into it. While I can happily create a separate table for Households and People, I can't tie the skillset to an individual, because that information isn't available in the existing data (is John the Tiddlywink champ? Or is that Jane? Whose email address is me@here.com?).

    I guess it's mostly this limitation that makes me wonder whether I really can break it down much further than the three tables I already have? I almost feel like I should be working on two of these databases - one that helps them access (no pun intended) the data now, and another one from the ground up that they can use for new records.

    In retrospect, I probably should have started off by explaining about the existing data. I'm sorry if my failing to mention it wasted your time! (but I learned a lot, so thank you very much for that!)

  11. #11
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    I can't tie the skillset to an individual, because that information isn't available in the existing data
    ...
    ...
    I guess it's mostly this limitation that makes me wonder whether I really can break it down much further than the three tables I already have? I almost feel like I should be working on two of these databases - one that helps them access (no pun intended) the data now, and another one from the ground up that they can use for new records.
    My own view is depends on where you plan to go from here. If you don't know who has what skillset and don't plan to find out in the future then you would probably link to the household

    Code:
    households 
                            have many skills
                            have many people
                                                have many mobiles
    but if you plan to find out in the future or think you may want to, I would leave the structure as I previously suggested and attach all the skills, unless otherwise identified, to the HOH people record - perhaps with an additional 'could be anyone' flag field in skills. same goes for mobiles and perhaps emails

  12. #12
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    again - this is the excel way

    Various yes/no fields related to potential skillsets, such as:
    AutoRepair
    EmergencyMedicalTechnician
    Counselor
    Plumber
    Also, the table has an occasional text field for more specific information about a skillset.
    The problem is if you add an additional skill you will need to modify the table plus all the related queries, forms and reports.

    The normalised way would be

    tblHouseholds
    HouseholdPK autonumber
    HouseholdAddress text
    ...
    ...

    tblPeople
    PersonPK autonumber
    HouseholdFK long - link to tblHouseholds
    PersonName text
    Position text - HOH/Spouse etc
    ...
    ...

    tblSkills
    SkillPK autonumber
    SkillDesc text
    SkillNotes text or memo

    tblPeopleSkills (many to many relationship - one person can have many skills and one skill can have many people
    PeopleSkillsPK autonumber
    PersonFK long - link to tblPersons
    SkillFK - long - link to tblSkills
    AnyoneFlag - Boolean - true means this skill could be held by anyone in the household, false means only this person has the skill

  13. #13
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    37
    It seems I have almost as much un-learning to do as learning! Thanks for your patience in putting up with my questions! This is really very educational for me.

    I'm still puzzling over two points, and then I think I have enough to get started redoing what I've done.
    First, you've mentioned that doing it "the excel way" (aptly observed!) means that I'd have to modify the tables, queries forms and reports. What I don't understand is how that won't also be true in access. I think you're saying that if I design the database correctly to begin with, I'll just need to add the skill or resource to the table, and the rest will pretty much take care of itself? You needn't explain how just yet (though I'll no doubt be back to pester you about it when I get that far!)

    Secondly, this database will be used by 9 different people in 9 different locations, so I need to make entering the data as simple as possible. Right now, it's just entering the names and addresses, then putting checks in the various boxes for skills and/or resources and clicking the submit button. Since the Access way doesn't rely on yes/no for those skills fields, how would you have the end user most easily tie the available skills to the person? What does that part of the data entry form look like? There are probably 60 skills and resources to choose from.

  14. #14
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    as previously said - the 'excel' way is to combine data and presentation into one view - typically 'short and wide (e.g. your tickboxes in a horizontal row). In access (or any db for that matter) data and presentation are completely separate - data is stored efficiently in tables, presentation is handled through queries. forms and reports. Consider why you think skills need to be presented horizontally? It is probably because that is the way excel does it. In excel that is repeated in multiple columns, in a database it is effectively defined in one column.

    So explain why having nine different people in nine different locations means you need to make data entering as simple as possible? Data entry should be as simple as possible, regardless of number of people/locations. Why is it more difficult for users to select skills from a list rather than scanning across a row and ticking boxes?

  15. #15
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    37
    I agree! it does need to be as easy as possible, regardless of who or how many are using it. I only meant that the users are not likely to open up a table in dataview and add users, or in the like. I've been watching too many Access tutorials! In most, the instructors typically create the tables, then manually add the students by typing into the table in dataview and assigning the classes by manually typing in the "class #" in dataview, then later show the joy of being able to identify which students are in which classes easily. There's very little about how the students got assigned to those classes in a user friendly form. I'm sorry if it came across wrong.

    I'm eager to do it the right way - I've rebuilt the household and people tables in accordance with your guidance, and will tackle the skillsets and resources later this evening. When I'm done, I'll post back with the new general layout for your critique (if you don't mind, that is).

    But I'm still trying to imagine the data entry form. Vertically, horizontally, or in a grid, the checkboxes were just the simplest way I saw for the end user to assign multiple possible skillsets and multiple possible resources to a single household. Click, click, click done. I'm not arguing for their inclusion here - I'm just not sure of the alternative. What control should I be looking at for the data entry form itself? (Here's what I had before)

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

Similar Threads

  1. Replies: 5
    Last Post: 05-13-2016, 11:00 AM
  2. Calculated field on results in the same query
    By becka11 in forum Queries
    Replies: 1
    Last Post: 05-07-2013, 05:11 PM
  3. Replies: 1
    Last Post: 06-26-2012, 05:02 PM
  4. Replies: 3
    Last Post: 06-26-2012, 02:54 PM
  5. Replies: 3
    Last Post: 03-03-2012, 10:39 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 - Senior Forums