Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 59
  1. #16
    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'm sure you can do it. The bottom line of my previous post is that get your tables and relationships designed and tested before jumping in to queries, forms, reports etc.
    We are advising you based on what you have described to readers. We have all seen well-intended people get in "over their heads".
    Most issues/problems can be related to poor/bad table design.
    Get your tables and relationships designed and tested first.



    Good luck with your project.

  2. #17
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Right. I have read through the information that you all provided and watched some of the videos relevant to where I am in this process.

    I have gone through my tables and there are now 7 - ClientData, ClientAddress, CatData, CatWeight, BookingData, ECData, ECAddress (EC = Emergency Contact). There are now no redundant columns or shared data, there are no calculations - these will all be done on the forms. The fields have all been set to show the data how I need it to, like weights with decimal places and dates in the format I want. I have followed the naming conventions.

    I have set the relationships as follows: ClientData links to ClientAddress, ECData, BookingData, CatData. CatData links to CatWeight. ECData links to ECAddress. The thinking behind these is that one client can have many addresses, emergency contacts, bookings and cats. Cats can have many weights. One emergency contact can have many addresses.

    Other than the above question, am I now nearer to where I need to be to move forwards?
    Last edited by TheOakster; 05-29-2016 at 05:15 AM. Reason: Grammar / Posted question solved ;)

  3. #18
    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
    Looking good. Can you please explain
    Cats can have many weights.
    It would seem that you would weigh catX on some WeighDate and record the weight, date and CatId as a record in your table.
    Have you tested your model with some sample data? Always a good practice.

    You may find this article useful.

    Good luck.

  4. #19
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    That's exactly right. We weigh the cats on booking in, at various times through out and then again when they leave. I would like to see a history of the cats weight over time so I need to record the weights each time, rather than just keep writing over the previous entry.

    Sample data has gone in for 2 Clients so far. 1 client has multiple cats too. I added various missing or new columns as I went through that I might need in the future and found some mistakes with field data types and formats that have been corrected.

    I'm up to queries - concatenation and totals in the videos, so I'm now creating basic queries that I think I will be running and working out what data it is that I actually need at anyone time

    I've read that post and without realising, I've been doing that as I go along rather than as a specific undertaking.

  5. #20
    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
    You would not overwrite your weight information. Each weight/date and CatId would be a separate record in your table.
    A query against the table could show the weight history of a CatId.

    Post a copy of your relationships window as a jpg. Make sure you expand the table representations to show all fields in your tables.
    A copy of some test data would also help readers with context.

    You are correct, analysis and design is often an iterative process and it is a learning experience for sure.

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

Name:	relationship.jpg 
Views:	40 
Size:	160.5 KB 
ID:	24774

    You would not overwrite your weight information. Each weight/date and CatId would be a separate record in your table.
    A query against the table could show the weight history of a CatId.
    - Yep. That's why I opted for a seperate table rather than just a field in the CatData table.

  7. #22
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    I could split some of these tables up further but I couldn't see a reason really so opted for the less table version - E.G. the cat food check boxes could be a table of their own. Veterinary Surgeon can be in either CatData or ClientData.

    Could I also merge the two address tables?

    Other than that, any obvious issues?

  8. #23
    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 would suggest you move the Facebook, Twitter, Yell.... to a separate table. The rationales is if you get one (or more new) you would have to change your structure. Whereas, if you create a table (not sure of the proper name) SocialMediaLink, a lookup table with structure like
    tblSocialMediaLink
    SocialMediaId
    SocialMediaName

    you would just add a new record to your table.

    Then a junction table

    tblClientSocialMedia (to record the various social media links for this client)

    ClientID 'composite primary key
    SocialMediaID
    ClientSocialMediaUsername
    other comments/info?


    Similarly with the tblFoodPreferences (or whatever you'd call it) as a lookup table

    tblFoodPreferences
    FoodPrefId
    FoodType

    Then a junction table tblCatFoodPreference

    CatID 'composite primary key
    FoodPrefID
    Comments ' details or comments about this cat and his/her food preference. This allows a cat to have many preferences???


    ??Questions for your testing and business rules??

    Can one cat have multiple food preferences?
    Can a Client be recorded with multiple SocialMedia links?

  9. #24
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Thanks for looking into this

    The social media check boxes are not to show the clients accounts that they have or how they link to us, it was used as a marketing tool to see where clients hear about us when they first book with us. Once they have been to us once, they then get the repeat customer check mark.

    The cats could have a change in food preferences but I don't think that there is a need for us to record that change. Its only important that we know what they want when they stay. If they stay in a week, month or year's time and they have a different preference then I am happy to just update the existing entry and lose the previous information.
    Last edited by TheOakster; 05-29-2016 at 09:13 AM. Reason: Grammar

  10. #25
    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
    Re the social media stuff, I would have a field in the Client table to represent where did you hear about us.
    Seems you only want 1 of the social media types and you'd only want it once.

    I
    'd keep the lookup table.

    Seems you have hings under control.

  11. #26
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    So I will split off the Social Media to another table called tblSocialMedia and add a Marketing field to the ClientData table. New clients can just pick the main social media outlet that they have seen us on.

    I'll also rename the tables to have tbl at the front too.

    Can you think of anything else with my tables before I move on?

  12. #27
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You asked about combining the address tables. I think I would not, however, I wonder about the split of the address tables from their parent. If a client can have many addresses, or an EC many addresses, you have it right (one to many, client to addresses or EC to address). If you will only keep one address per client, and one address for ED, then the address fields could go into the client ad EC tables. Or, combine the addresses into one table and distinguish by a type field (EC vs CL). If one thinks of addresses as a separate entity for business reasons, then you would do the latter, but I don't see them that way for your business model. That does not mean I am correct, it's just a judgement call based on my limited understanding of the business model. Then there are phone numbers. Why allow a client to have two listed by an EC only one?
    For example:
    - when you see AddressID in tblCLaddress (you should have used type prefixes) you know it is the client address id. I wouldn't call it ClientAddressID - minor point.
    - I'd use FName, LName. You will find that Access distinguishes their parent table as tblClient.Fname or tblEC.Fname
    - consider Tele1, Tele2, Tele3 (lest you limit types to home and cell - what about Office? Or whatever else may come up? They say 'normalize until it hurts', which would mean putting phone numbers into a different table rather than have a bunch of holes in your data where there are no extra contact numbers. You'd join them by type EC/CL as already mentioned.

    You are forgetting or have missed a very important point from prior posts in this thread. NO WAY should you have a field named "HouseNumber/Name". No special characters other than the underscore _. I recommend you shorten names as well. If you have to write code in the future, long names become a pain (as do underscores, which is why I rarely use them). This includes Google+.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #28
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The cats could have a change in food preferences but I don't think that there is a need for us to record that change. Its only important that we know what they want when they stay. If they stay in a week, month or year's time and they have a different preference then I am happy to just update the existing entry and lose the previous information.
    So what if you no longer do/can purchase a food type in your table? Or the client brings their own which you do not keep?
    Foods are not cats (ok obvious). My point is, they are separate entities thus belong in separate tables. To accommodate the possibilities I just mentioned, you'd have to add table fields and that would be Bad. It will mess up every query, form and report you have. Losing data would be the least of your problems. I would take Orange's advice on that for sure.
    I'll keep looking.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #29
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    I missed the / and the + ! Thanks for pointing that out.

    The emergency contact only has one contact number as that's all I have got in the data so far - clients seem to have only ever given one. It's another oversight that I have corrected - there are now two

    I will be keeping the addresses seperate I think. It makes sense in my head to have it that way.

  15. #30
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    The food preference has an 'Other' field that allows for text entry if food is supplied (now renamed to FoodNotes. And having thought about what you've said, I have deleted the brand names completely, as in all honesty they are not needed. I need to know if they are on dry or wet food, whether they prefer jelly or gravy but the brand can be entered in the text field if they are fed a specific one.

Page 2 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