Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 59
  1. #31
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I will be keeping the addresses seperate I think. It makes sense in my head to have it that way.
    just to help you with the concept, realize that it duplicates fields when you split the same information based on type. Not exactly completely normalized, but it looks like you're off to a good start. Plus, I think you have done a lot to avoid the pitfalls that so many novices fall into because they don't do their homework as thoroughly as you!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #32
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Thanks Micron. If I'm going to do it, I like to do it right. That's not to say I won't make mistakes but I'm willing to put the work in to understand what I'm doing and to get somewhere near to where I need to be.

    I've done all the changes suggested and hinted at:

    Tables are renamed with tbl at the beginning.
    / and + have been removed.
    I've moved the marketing options from tblClientData to tblMarketing, and then added a junction table between them (tblClientMarketing) as each client can have many marketing options ( I think I've done this right, judging by the video and the query results I've run to test it).

    So next steps are... have a rest because my brain hurts Tomorrow I'll move onto queries and then look into forms.

  3. #33
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Click image for larger version. 

Name:	relationship1.jpg 
Views:	41 
Size:	98.7 KB 
ID:	24776

    The story so far...

  4. #34
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some other things to consider (these are my opinions and subject to disagreement with my wise peers)
    - Client_ID: unique index?
    - EC_ID: indexed (dupes allowed)
    - tblClientData.ClientID linked to tblECData. The way you have it (tblClientData.EC_ID on tblECData.EC_ID) will require a repeat of the entire ClientData row in order to have a second EC. The other way, a second EC_ID row would have the same ClientData ID, which would make sense since the second EC would be a totally different person. Thus, only the ID would be repeated - the rest of the ECData would pertain to another contact. Surely a client can have more than one EC?
    - the ECAddress link should be between tblECData.ECID and tblAddress.EC_ID. The address info is related to the EC, not the client. Look at the way you have done ClientID to ClientID from ClientData to ClientMarketing, then to Marketing. This is correct.
    - not a fan of the food design. Every cat who is not on WetFood is an empty data hole for dry (and other, and gravy, and jelly). Every other cat who is not on Dry but is on Wet is an empty data hole for Dry(and etc. etc.). Every cat who is on a tuna-only diet (other) is an empty hole for... and so on. You should reconsider a tblFeeding or whatever.
    - table booking data. OK, client id 125 is bringing one of their cats. Which one?? Link BookingData between the catID in CatData via catID (which you don't have yet) and remove ClientID. You get the client name via the link from tblCatData.ClientID to tblClientData.ClientID

    If you like, I can keep looking, but I have to do the rest later. It might be worth your while to wait if I have earned your trust and not made link design errors. I invite my learned peers to double-check my thoughts.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #35
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you "exercised your model" with some test data? Do you have a clear set of specifications/requirements to share with readers?
    Micron's question/scenarios are "right on" in my view. If you have some info that you need the system to produce/query samples or report descriptions, this testing and vetting is the place to do it. You don't necessarily need Access queries and forms/reports at this point. This is just a test of the "evolving blueprint" of your database.

    It's looking very good.

  6. #36
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Quote Originally Posted by Micron View Post
    - Client_ID: unique index?
    - EC_ID: indexed (dupes allowed)
    - tblClientData.ClientID linked to tblECData. The way you have it (tblClientData.EC_ID on tblECData.EC_ID) will require a repeat of the entire ClientData row in order to have a second EC. The other way, a second EC_ID row would have the same ClientData ID, which would make sense since the second EC would be a totally different person. Thus, only the ID would be repeated - the rest of the ECData would pertain to another contact. Surely a client can have more than one EC?
    Agreed and implemented.

    - the ECAddress link should be between tblECData.ECID and tblAddress.EC_ID. The address info is related to the EC, not the client. Look at the way you have done ClientID to ClientID from ClientData to ClientMarketing, then to Marketing. This is correct.
    This and the above part are from the same issue. When I was entering the data I found a shared EC for 2 cats (my partner for her Mother's and Grandmother's cats on seperate bookings). I figured that this would be the only time that this happens so I added tblDataCLient.EC_ID rather than adding a third table for a many to many relationship which would mostly be redundant. When I split the marketing data and created tblClientMarketing I wondered if I should just go back and do the same with the EC addresses as a future proofing excercise. Implemented.

    - not a fan of the food design. Every cat who is not on WetFood is an empty data hole for dry (and other, and gravy, and jelly). Every other cat who is not on Dry but is on Wet is an empty data hole for Dry(and etc. etc.). Every cat who is on a tuna-only diet (other) is an empty hole for... and so on. You should reconsider a tblFeeding or whatever.
    Finally agreeing and so implemented

    - table booking data. OK, client id 125 is bringing one of their cats. Which one?? Link BookingData between the catID in CatData via catID (which you don't have yet) and remove ClientID. You get the client name via the link from tblCatData.ClientID to tblClientData.ClientID
    Haha Well spotted. Implemented.

    If you like, I can keep looking, but I have to do the rest later. It might be worth your while to wait if I have earned your trust and not made link design errors. I invite my learned peers to double-check my thoughts.
    You can take as along as you want. Like I said originally, there is no time restraint or even a business need for this. I'm just learning something new that, ironically, will probably take many hours of work to build just to save me a few minutes of re-writing out a booking in form

    Quote Originally Posted by Orange
    Have you "exercised your model" with some test data? Do you have a clear set of specifications/requirements to share with readers?


    I have inputted 5 bookings so far that have lead to a few tweaks and additions, luckily some of these have been 'awkward' ones - multiple cats, joint EC's and so on. As for specifications or requirements do you mean what I would like to be able to produce at the end or something else?

  7. #37
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Click image for larger version. 

Name:	relationship3.jpg 
Views:	37 
Size:	98.8 KB 
ID:	24777

    New changes and links added. Its a bit more complex than I originally envisaged

  8. #38
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have inputted 5 bookings so far that have lead to a few tweaks and additions, luckily some of these have been 'awkward' ones - multiple cats, joint EC's and so on. As for specifications or requirements do you mean what I would like to be able to produce at the end or something else?
    The tweaks are the important refinements. The "touch of reality" if you will when real data/scenario meets the model. That is expected, and the secret is to get all or as many tweaks sorted out before you get too involved with queries and forms. The data structure is the big issue.

    Regarding specifications or requirements do you mean what I would like to be able to produce at the end or something else?:

    These are the facts, more detailed narrative (with pictures/examples...) that you would provide to a developer/designer if you were to have your database built by some other person. The analogy is the info you would supply a designer/ builder if you were to have them build you a house. You know what you want; the difficulty is expressing that clearly to a third party.

    Good luck.

  9. #39
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    The overall aim of this database is to provide a more efficient system when I book cats in. I want to be able to get a client in, open up a form that allows me to input a brand new client and their indeterminate number of cats, including all the data you have seen in the tables, in one sitting. This can then be printed off and then signed and filed away.

    In the case of a repeat client, I want to be able to see a complete history of their previous bookings, so that I can save time entering the data that I have already collected over their previous bookings and updating those fields that need updating - retaining some of the previous entries (a new emergency contact for example) and over-writing others (the new food being fed to the cat). This can also be printed as for a new client.

    The last requirement is that I want staff to be able to open the historical file of a client showing all the contact information in the vent it is needed or look at a cat's data, update its weight and store that information for retrieval later to identify trends and patterns. I would like to be able to see which clients book with us the most, which spend the most money etc.

  10. #40
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff.
    But I don't see any invoicing or cost or payment info in the model???

    This can then be printed off and then signed and filed away
    . Is this system to support your current manual records, or replace most of it?

  11. #41
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This doesn't look right:
    - ClientData.ClientID to ECData.ECID? Should be ClientID on Client_ID
    - ECData.ECID on ECAddressID? Should be ECID on EC_ID.
    You are trying to join two primary keys in a one-to-many relationship.
    - you should move FoodNotes from CatData to CatFood. It applies to "Food"
    - the 3 Feeding fields in CatData are a toss-up I guess. Not for me to decide if they apply more to the cat or feeding/food table. I might be splitting cat hairs!
    IF you wanted a historical account of feeding/food options, you must move them.
    - In CatData: wondering if you'd have use for field Breed (better than Notes). You could do more with that, such as grouping data to know how many were Persians; even how many Persians were on any food type or any other relationship you could devise. Maybe use "Notes" instead of Description (trying to avoid user using Description to include breed info) for info that might be useful in flagging cats that have deformaties or some such special need).

    Your two table approach to food is not absolutely necessary if cat>food option is a one to one relationship, but it will provide more flexibility in implementing future options. Best part would be the control you can exert, say in a combo box. If it's not there, it can't be input by the user - lest you allow field additions. Put "NotInList Event" on your memo board for when you get to form design.

    Hate to raise this now, but better late than never:
    What is the idea behind the medical history field? If this is memo field where you will keep a rambling narrative that you have to read through to get information you need, it will work ok. If your desire is to have a history vs timeline, it won't. You would not be able to search by timeline, such as "What happened last year?" Consider moving worming, flea and vaccination to their own table (tblMedHist). I do not think you will regret it, rather you will find the history useful later on. In that case, I'm not seeing a need for MedicalHistory field since each field in tblMedHist should be able to record whatever aspects make up medical history.

    Orange has a point about costing/invoicing. A db is a good choice for keeping track of costs and producing invoices as reports if that's what you'd like to have down the road. I think you are well on your way to being able to implement that now or when you get around to adding it. Just try to think of "things" as entities and keep them and their related aspects/details together and exclude those that are their own entity and you will be OK. This forum will be able to help with that as well.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #42
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    The incorrect key link was because I was fiddling with things earlier and then didn't put it back together right.

    Splitting cat hairs... haha! There's no requirement for a food history.
    There's no requirement to know the cat breeds or any relationships between them.

    The cat food split was because I originally had in checkboxes which I read was bad, and a cat can have wet, dry, jelly and gravy and like you say, there is a future proof element to it too.

    The medical history field is for when a client says "our cat has 3 legs", or "she had an operation a year ago" and so on. It's just so that staff have a rough idea of any past conditions that may affect the current booking. It can be overwritten on the next booking as there is no requirement for it to become a timeline. Its called that just because that is what it was on the original form. Probably not the best name...

    Our finance is run on a completely different accounting system so I don't see a need as yet to incorporate it into this. The final product from this databse will have a pricing element to it but I had read that that sort of calculation should be done on the form side rather than in the tables... I was thinking a simple count days function for the dates with the daily rates and any discounts applied to provide a total for the overall stay. I can do it in tblPricing I guess and that allows for future changes to pricing and discounts/offers. I take it that would be the right way of doing it?

  13. #43
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Quote Originally Posted by orange View Post
    Is this system to support your current manual records, or replace most of it?
    It's just to replace most of it - The booking in and out is the biggest piece of admin we have. There are still two other 'systems' that will remain paper based due to locations and needing to have some sort of mobile data solution - hourly checks done in pens, food diaries for every meal in the kitchen and so on. These just take a few seconds for each cat so to have to come to the computer and update a spreadsheet would be overkill for what is essentially a tick in a box and line of text. Its not to say that it wont be added in the future but it wouldn't be difficult to add those in should I go that way.

  14. #44
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Agree with Micron - you've done an very good job on your table structure. (before jumping into forms and queries )


    Just note that "Option" and "Description" are reserved words. (Plus "Option" is not very descriptive)


    Current Suggested Alternative
    tblFood.Option FoodType FoodDesc
    tblMarketing.Option MarketType MarketDesc
    tblCatData.Description CatDesc

  15. #45
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    that sort of calculation should be done on the form side rather than in the tables
    Other than rare exceptions, ALL calculations should be done on forms or reports and never stored.

    I was thinking a simple count days function for the dates
    See DateDiff and other functions - http://www.techonthenet.com/access/functions/

    Re: Option not being very descriptive: especially when you use it in different tables. It becomes ambiguous, and Access does not like ambiguity.

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

Similar Threads

  1. Replies: 7
    Last Post: 03-23-2015, 07:36 AM
  2. Beginner struggling with form creation
    By audmkamp in forum Forms
    Replies: 1
    Last Post: 01-22-2013, 01:40 PM
  3. Replies: 1
    Last Post: 12-13-2012, 04:37 PM
  4. Absolute beginner qu - transferring a database
    By kirstywing in forum Access
    Replies: 2
    Last Post: 09-07-2010, 02:12 AM
  5. Absolute Beginner Help
    By jonesbp in forum Access
    Replies: 1
    Last Post: 08-17-2009, 08:23 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