Page 1 of 6 123456 LastLast
Results 1 to 15 of 84
  1. #1
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282

    Form used to select records from one table, then use this to add records to another table

    OK, this is a tough one, and from what I've gathered from my previous threads on this site, may well be impossible.

    I need a form to display a list of records, which are in the behaviour table (just the behaviourID and BehaviourDescription, restricted so that just those with certain RiskIDs appear - for instance, those with a RiskID of 1 appear on the LowRiskAssessment page).

    I then need the BehaviourID from this record, to be used to create an entry in the TblAssessmentDetails table, along with other information the user enters on this page - "Present", "Assessment", "3rdPartyIntelligence" (all of which are tick boxes) and a comments box.

    It seems entirely plausible in my head, but because I think I need the form to be based on both a table and a query - which is obviously impossible - I'm struggling to come up with how it would work.

    To be abundantly clear, there is a list of behaviours, each categorised as low, medium or high. During an assessment, an assessor selects whether the behaviour was present etc. and then adds a comment. Ideally, the entry would be added even if the assessor doesn't select anything in any of the boxes, but if that is not possible, I can try to work around it.



    Any suggestions would be much appreciated (although I would prefer the general layout of the form to stay the same if at all possible), and if my description doesn't make sense, just ask.
    Attached Files Attached Files

  2. #2
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    First of all, I'm struggling to understand the logic of your tables a bit. What is the key in the AssessmentDetails table? Can one assesssment involve multiple behavior IDs? If not, why not link Behaviors and Assessments directly?
    Secondly, let's assume for a second that you rightly want to write to the Assessment Details table (I'm not saying you necessarily shouldn't). You should create a form based on that table and use lookup fields in the form to help the user choose the behavior ID. Like a simple list box with the Behavior table as its source. Then let the user add the data he wants and save to the table.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I wasn't sure how to get to the form you mention in your last statement, which form are you referring to and how does one get there?

    If I could change your sentence - you want to add a record to the TblAssesmentDetails and the BehaviourID is just another field on that table. You can make it a combobox for them to select one of the records from TblBehaviour, controlling what they are shown based on your other criteria.

  4. #4
    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
    Heathey94,

    I just looked at your post and database. Your post sounds like the HOW answer to a need/requirement.
    I need a form....I then need the BehaviourID from this record
    That's really not how it works. I have this business issue/opportunity. Here is description of our business in plain English.......this answers the old

    'what we do generally
    'what subject matter we use
    'who does what to whom, and when
    'how often is this done

    then you build probable tables/entities and use the business facts to identify the relationships between the "tables". I see a little too much how and not enough what in terms of business requirements.

    In your database, the relationships window, it appears there is something in your Team and Districts that is missing. Your Team includes DistrictID, but is not related to your District table??
    Team and District both have relationships with Assessor.
    What is an Assessor?
    How do Assessor, Team and District relate?
    Are Assessment and Review different things?
    How are Assessment and Review related?
    Can an Assessment have more than 1 Review?

    My suggestion is to get your data model designed to support your business needs. Test it with some sample data. Reconcile any/all anomalies and repeat until it works. Then you have a blueprint for your database - a blueprint that you have confirmed works.

    Good luck with your project.

  5. #5
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Okay, so I'll try to explain the database using orange's lists:

    An assessor is part of a team, which is within a district - like so:
    District1
    Team1 has Assessor1, Assessor2 and Assessor3
    Team2 has Assessor4, Assessor5, Assessor6 and Assessor7
    District2
    Team3 has Assessor8, Assessor9 and Assessor10
    Team4 has Assessor11, Assessor12

    What we do:
    Assess a child's vulnerability to sexual exploitation

    What subject matter we use:
    A form was created in Word as a basis for holding the information, but we need to store it in a database so we can then report on it.

    Who does what to whom and when
    An assessor has a meeting with a child to determine the child's vulnerability. they then complete the form and upload it.
    Another person (the user) then downloads this file and inputs the information into the access database.

    How often is this done
    The assessment is completed when the assessor feels the child may be at risk.
    The inputting of data will become a regular task - possibly weekly or monthly.

    The District and Team were originally linked, but I was advised to rearrange the layout of my tables by ssnafu on the following thread: https://www.accessforums.net/showthread.php?t=61917

    An assessment is completed, then the form is completed, then a review is held about the contents of the assessment, then the form is uploaded.

    Quote Originally Posted by warmslime View Post
    First of all, I'm struggling to understand the logic of your tables a bit. What is the key in the AssessmentDetails table? Can one assesssment involve multiple behavior IDs? If not, why not link Behaviors and Assessments directly?
    Yes, each assessment has a list of behaviours which could be assessed against. Basically, I'm trying to get a database to hold information completed in an assessment, which looks like this:
    Click image for larger version. 

Name:	Assessment Form Word.PNG 
Views:	39 
Size:	34.9 KB 
ID:	25892

    Ideally, I'd like all of the behaviours listed so that it is obvious if the user misses one, but if that is not possible, we can probably make do. (which is why I was hoping I wouldn't need to use the listbox/combobox idea as suggested by yourself and @aytee111)

    The form I am talking about is "AddLowRiskAssessment" and can be accessed by searching for a childID on the homepage (54 for example), click search, then on the "ChildRecord" page select the "Add New Assessment" button, then "Next Page" when you've reached the "AddAssessmentRecord" page.

    Hopefully that's everything. Any queries, feel free to ask.

  6. #6
    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
    As I read your posts, I think a District has 1 or more Teams and
    A Team is comprised of 1 or More Assessors
    An Assessor conducts 1 or more Assessments

    District-->Team-->Assessor-->Assessment

  7. #7
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Yes, that is correct.

  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
    Then that would change your relationships. Have you updated your database?
    Have you tested your current model with test data?
    Do you have a test database that you can share? remove confidential info and use names like Porky Pig,
    Ima Phish, Donald Duck, Heeza Payne....

    Regarding
    Ideally, I'd like all of the behaviours listed
    you could do that on a form, but do all behaviors require some check box completion or comment???

  9. #9
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    As mentioned above, I did have the relationships like in the image below:

    Click image for larger version. 

Name:	OldRelationships.PNG 
Views:	39 
Size:	31.6 KB 
ID:	25899

    But on thread https://www.accessforums.net/showthread.php?t=61917, ssnafu suggested I change it to

    Click image for larger version. 

Name:	NewRelationships.jpg 
Views:	39 
Size:	31.3 KB 
ID:	25900

    so that I could get some comboxes to work.

    There is not yet any real data in my database, they are all either common names (e.g. John Smith) or fictional characters.

    Quote Originally Posted by orange View Post
    Regarding

    Ideally, I'd like all of the behaviours listed
    you could do that on a form, but do all behaviors require some check box completion or comment???
    No, they don't but (In my opinion) it will mean that the user is less likely to miss a behaviour, and they will be able to go back and make changes if something is missed/an updated form is uploaded by the assessor.

  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
    Have you tested your model with some sample data? Does it work?

    How about posting a copy of hat you have? It would make things easier for people responding/trying to assist.

  11. #11
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I posted a version in my initial post, not much has changed since (and nothing on the form I'm querying about here) but I'll post the most recent one anyway.

    My database works so far... it currently falters in two places, one on the AddAssessmentRecord form - where it's just not quite perfect (working on it in a separate thread) - and the other on the AddLowRiskAssessment form, which I'm querying here.
    Attached Files Attached Files

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Now that you know what you are doing, here are some instructions for you AddLowRiskAssessment form:

    You are losing the AssessmentID each time you add a record (it is going to NewRec automatically). Either you can leave the previous form open and keep replacing it with that, or you could have two ID fields - one not bound, just to keep it for all time, or you can make the detail part of this screen a separate subform linked by the ID, that way the one on the main part of the form won't disappear.

    Remove Behaviour Description and change BehaviourID to a combobox, then instead of displaying the ID when they select you could display the Risk/Desc.

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If you do make the detail part of the form a subform, you could put a ListBox of the Behaviours somewhere on the header part, so they will know which one to select. Then you won't need to have too much information on the ID combobox.

  14. #14
    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
    From a quick look at your tables, I think the Behaviour table may be hiding some info, but I'm not familiar with the details or your other posts/threads.

    Is an AssessorCase an Assessment?

    My database works so far... it currently falters in two places
    Have you worked with a list of questions/facts you want to get out of the database using your model?
    Seems to me you are knee deep in forms etc and still haven't got the details or data structure to support the details. But, as they say, there's more than 1 way to skin a cat.

    Good luck with your project.

  15. #15
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I've given up on trying to have all of the behaviours displayed, it doesn't look like it's possible.

    I tried the subform version, but that meant I couldn't make it a continuous form, and so the user would have to complete the form one behaviour at a time, which isn't ideal.

    So, combo box it is... It all seems to work fine, but once you select a behaviour from the combobox, it automatically assumes you want the same behaviour in the next bit of the form, which is one thing you can assume the user doesn't want... I tried to get it to have a default value, but that didn't seem to work, any ideas? It seems quite a simple question...

    I've attached the database so you can see what I mean.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2016, 04:06 PM
  2. Replies: 1
    Last Post: 12-20-2015, 01:09 PM
  3. Replies: 4
    Last Post: 07-14-2015, 06:49 PM
  4. Replies: 2
    Last Post: 05-28-2013, 04:00 PM
  5. Sub form based on table select specific records
    By ReluctantGeek in forum Forms
    Replies: 0
    Last Post: 01-21-2012, 11:24 AM

Tags for this Thread

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