Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 57
  1. #16
    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
    It's good to see someone making a real effort. Quite often we see posts by those who believe that Access is just an extension to Excel (wrong).
    I was building a draft data model while you were posting - it seems.
    Take a look at the model and see if it makes sense to you.
    What's missing?


    Questions?

  2. #17
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    So looking at your data model, you're saying it may be more efficient to establish the relationships between the tables instead of creating the queries? Conceptually i just assumed i had to create the query to tie things together but i'm starting to get the picture. This is a relational database and as long as i have relationships between the tables i can call realtive data when and where i need it via combobox, list box, subform or whatever. ?? right track?

  3. #18
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    To address your previous post, thanks. i never like simply asking for an answer to a question. i want to know "why" and "how". I don't necessarily want to be spoon fed.

    and i'm starting to understand that Access is merely an interface for SQL. it's designed to give people like me a graphical interface so we don't have to learn sql code. But a fundamental foundation in some SQL would make understanding Access, or any other, much easier.

  4. #19
    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
    Quote Originally Posted by bbilotta View Post
    So looking at your data model, you're saying it may be more efficient to establish the relationships between the tables instead of creating the queries? Conceptually i just assumed i had to create the query to tie things together but i'm starting to get the picture. This is a relational database and as long as i have relationships between the tables i can call realtive data when and where i need it via combobox, list box, subform or whatever. ?? right track?
    Yes, that is correct. The rules of your business eg A Person may own 0,1 or Many Dogs represents a business rule/fact
    If you get your business rules identified --through creating descriptions of processes, what they are for, what goes in; what comes out, etc. Generally this represents your requirements.

    You can use the rules to draw out a draft data model (pencil and paper is more than adequate).
    You should also have some sample scenarios, eg. I want to add 5 dogs to an Event. I want to have 2 Stakes for this Event.
    You make a list of test data values, and using the model, you "play test the model" to see if you can get get the info from the various tables to correctly resolve your scenarios. If not, you got to find out what's wrong and reconcile it --then test again. Repeat until the model matches your requirements. Now you have the model - a "blue print" from which you create your database.

    Now you proceed to forms and queries as a means of accessing/displaying the data.

    Access is a bit of a strange product. It includes a database management system (ACE), and it has tools/wizards to help you create/modify the Access supported objects (tables/queries/forms....). It also has a more technical programming environment (vba). So Access is more than an interface to save the uninitiated from SQL, but you can use SQL directly. Access does many things for you , and hides you from some nitty-gritty, but you can chose to use/unhide these various "hidden parts".

    If you go back to the data model. and watch the Dr. Soper videos, some of the components should make sense.
    Intro to Database
    The Relational Model
    Data Modelling and the ER Model


    Good luck.
    Last edited by orange; 03-10-2017 at 02:09 PM.

  5. #20
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Thank you again Orange. Everything you share makes sense. I've gone back in and redesigned the tables along those lines, and i've established the proper relationships between the tables. Yes, that means starting over on the queries and forms, but i'm okay with that. i don't mind going back and recreating. I'm basically in a long-term test phase. i create, test, find out what works, and start again.

    We have a trial next weekend and don't have our next one until October. I had no thoughts of using this for the March trial, so i'm not in a rush. I'd rather get it right.

    I was going to ask a structural question but as i typed it out i basically found my way to the answer.

    Thanks again.

  6. #21
    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
    Good stuff.

    For any development/design use a test database -just enough data to make sure you're doing and achieving as you go.
    Don't work on your production database. Back up often (from experience).
    Feel free to ask or PM.
    Hang in --stay positive.

    orange

  7. #22
    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
    Here is my best guess at your current set up based on discussion to date. I'm not that familiar with this modeling product so I'm doing my best to represent the Entities Attributes and Relationships based on posts. You may get to a point where the level of detail is more wish list than fits with majority of model. You may have to identify a Black Box along the lines of ( I need to do more analysis and get better handle on the intricacies of this topic). Also at some point you may have to use code/queries etc to resolve/enforce some rules.

    This is an update to a previous version. Notice 2 types of Roles ---ClubMember and Event. Also you have jumped from People to DogClubMembers. My guess is that People involved in Events don't have to be DogClubMembers necessarily.
    You can test the model by using some sample data in a list for each table, then looking at your requirements and some scenarios, see if you can get the data from the model.
    eg. Which Club has most members? Who hasn't paid Dues? What is Amount of unpaid Dues? How long have Members been Members? List of Dogs by Title..... etc. etc.

    Hope it's useful.

    Click image for larger version. 

Name:	DogClubPeopleEvent_mar142017.jpg 
Views:	41 
Size:	79.7 KB 
ID:	27865

  8. #23
    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
    bbilotta,

    Not sure where you are in the development at this point.
    I took the most recent model (using Toad Data Modeler) and generated a script file to create an Access database.
    Here is a copy of the empty database. It may be helpful to you.

    Just saying if you get a model (at least close to your business facts), there are tools that can create the database tables and relationships physically. It certainly is a good starting point. But may have to be adjusted as new details or info based on deeper analysis of facts are encountered.

    orange
    Attached Files Attached Files

  9. #24
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Thanks Orange, that's very cool.
    I'm comparing what you sent to what i have to try and learn where i went wrong. It seems i was mixing too many variables at one time. the construct that came from you seems to be much cleaner.

    Right now i'm still stuck trying to figure out how to create the dues form. And it's not so much that I'm trying to figure out the actual form as it is trying to figure out how these various components can and/or should come together. I know they can come together, I'm just not seeing the mechanics of how.

    In this picture you see i have a form. this form allows me to drop down the concatenated field of Club & Year and then fills in the Dues amount based on my selection. The record source is ClubDuesTbl (you can see that table in my PM to you showing the Relationship window). In the blank subform portion i'm trying to see the ClubMembers in a dropdown and mark them paid in the same row. I want this to give me a record in the ClubMemberDuesTbl.

    The ClubMemberDuesTbl has a foreign key DuesID which is the key field for ClubDuesTble, the record source of the form. So i "think" i need to create a subform based on the CluMembersDuesTble and link the master to the child with DuesID.

    I'm using ClubMemberID instead of People ID because i don't want to see the entire list of People. I only want to see those that are members. I know i didnt need to create a MemberID for that, i could have build the query on the rowsource to filter down people to those who have a record in the ClubMemberRoleTbl, but that is simply where my logic lead me. (I'm reminded here of an old calculus professor, a Greek mathematician, who would deduct points if we didn't find the most efficent route to the correct answer).

    Anyway, can you tell if i'm on the right track?Click image for larger version. 

Name:	duesform.jpg 
Views:	34 
Size:	46.2 KB 
ID:	27895

  10. #25
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    After looking at what you provided i revised my table structure. Here's what it looks like right now.

    I apparently have an issue somewhere in my "owner, Co-Owner, Breeder, Co-Breeder) fields in the dog table that is not allowing me to enforce referential integrity from the PeopleTbl. I'll have to dig into those existing values. I probably have a value listed in the dog table that doesn't exist in the PeopleTbl, that's my guess right now anyway.
    And please ignore fields "Title" and "Obedience" in the dog table. they are text fields i need to delete, they don't mean anything.

    Click image for larger version. 

Name:	RvsdRltnshp.jpg 
Views:	34 
Size:	253.6 KB 
ID:	27896

  11. #26
    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
    bbilotta,

    The database shell was just to show you what the tool made directly from the latest data model I posted. I did remove the self links in the model for Sire and Dam. I was having trouble to go from a logical model to a physical access model using the Toad product.

    It may not suit your every detail, but it may give you some insight for design. Also I did not necessarily include all fields in the model. I was offering an attempt to put the pieces you mentioned into the model. Vetting the model is a key part of the design process.

    owner, Co-Owner, Breeder, Co-Breeder all relate to People and Dog but that may not be a major issue at this time. It depends on your priorities.

    Good luck. I think you have most of this under control.

  12. #27
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Orange,

    Your shell was a big help. As i mentioned in an earlier post, at times i seem to make tremendous efforts to outsmart myself. I needed to simplify my tables and relationships a bit and your shell pointed that out. It also made the path to getting past this sticking point more clear. i haven't actually overcome that sticking point, but i think i'm seeing the light.

  13. #28
    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
    Good stuff.
    When some of the detail gets/seems too complex --make a black box; sort of to do list--and stick it in the model.
    Make a reference somewhere as to what it means; some of the thoughts you have re how to approach it; what must be done in steps; and carry on. The last thing you want to do/have happen when you're designing is to get caught up in a small piece at a very detailed level when you're working at the overall structure. You can go back to the black box; model it; test it.....

    Map out an overall plan - the artist's concept. Sort out the priorities
    Do the important/urgent/high priority first - keep referencing/updating your overall plan.

    Glad the model and generated database structure are useful.

  14. #29
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Yes, i completely agree. My problem is that i don't quite know enough about how all the pieces come together to give the end user a workable product. Because i don't know if my overall plan will work i get stuck in the mud on those minute details until i figure some things out, then go back to the overall structure. by that time i've usually created some queries and forms that ultimately become useless, but that's ok. it's part of the learning process for me.

  15. #30
    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
    Agreed. But that is why you build the model and vet (review, adjust, refine - cycle) with test data, expected results and users to ensure the model(blueprint) meets the requirements (at some acceptable level) before building the database.

    Similar in concept to building an airplane, or shopping center ---what are we building; who is it for; what are key things it must do.....
    Think of focus groups that are brought together to get "insight/ideas" from potential users/consumers on some proposed scheme.
    Easier to modify things at the concept/prototype stages than after you've spent big bucks trying to out-guess the public.

    Getting requirements is not trivial. Understanding what someone is describing in abstract terms isn't always productive. A list of things or a demo-prototype is often better --and helps that someone clarify their own thoughts.

    Just look through some posts on the forum - quite often the issues are lack of details of the business/processes to be automated/supported.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2015, 07:25 AM
  2. How to filter bound subform from unbound main form?
    By ittechguy in forum Programming
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  3. Replies: 1
    Last Post: 01-16-2015, 09:28 AM
  4. Replies: 6
    Last Post: 11-30-2013, 02:41 PM
  5. Replies: 2
    Last Post: 08-01-2011, 11:35 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