Page 1 of 4 1234 LastLast
Results 1 to 15 of 59
  1. #1
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30

    Thumbs up Setting Up Brand New Database - Absolute Beginner. Struggling with the basics.

    Good afternoon,



    Having googled all morning trying to find the answers that I've been looking for, and not really understanding any of it, I figured that it was time to join a forum to get some proper answers relevant to my situation.

    I am trying to replace my paperbased system with a database solution.
    I run a cattery and I think that I am looking to build (no specific time limit - it will take as long as it takes) the following database (please excuse any wrong terminology, and feel free to correct as appropriate):

    A start up form with 3 buttons.

    The first button will bring up a Booking In form that will have a drop down combobox of client names (humans).
    When the name is selected, the form will auto-populate the various text boxes that show the clients' full contact details, emergency contact details and marketing details. It will also auto-populate that persons cat details, for as many cats as they have, including previous food preferences and details such as microchip etc.
    These fields can be updated to reflect any changes since the last booking.
    When the new booking dates are added, they will add to a history of booking details, rather than updating the previous ones.
    The from can be printed for signing.

    The second button will open a form that allows the cat details to be updated by staff after they have been booked in such as new weight and so on. This will allow the staff to see the history of the cat including previous weights, behaviours, food preferences and so on.

    The third button will open a form that allows for the client stats to be seen by staff such as all previous bookings, amount spent over time, number of bookings per year, average length of stay etc.

    I think I need 3 tables. Cat details. Client Details. Booking Details.

    The Client needs to be linked with their bookings and their cats.

    For starters, is this a monumental task or is it achievable and have I got the tables right? Once I know what I am up against then I will start asking questions as I build all the parts, if I cant find the answers here

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have I got the tables right?
    as far as names are concerned, probably (but don't use spaces in table and field names). However it depends on what you are going to put in those tables which matters. Before worrying about what the forms look like, get you data (tables) right first

    for example cat details.

    catname
    immunized or immunized date - but do you need to build a history, know what the immunization period is etc?
    owner - link to client details
    likes - do you need a list of 'standard' likes, if so, another table required
    dislikes - ditto
    diet - ditto
    special care - ditto

    clients - is it one person or a couple/family? does it matter? do you need to keep a history for regulatory purposes? so if they move you still retain the old address

    booking - how do you know if you have a suitable kennel available - perhaps you also need a table for kennel


    etc

    Draw it out on a bit of paper - a number of boxes and put data where it only needs to appear once

  3. #3
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Hi Ajax,

    I'm happy with the data I need to put in the tables as it's what I've been collecting on the paper forms already. I don't need a kennel one as that's done through the diary. The old addresses are not important as such, because the paper copies will still be filed as they need signing so on the slim chance that I would need to find old addresses I can look through the paperwork.

    I drew it out originally which is where the 3 tables came from (they are currently called BookingData, ClientData and CatData. I have also got ideas of how I want it to work but I want to start in the right place, rather than get down the line and have to change something big.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm only trying to provide some examples of things to look out for
    I am trying to replace my paperbased system with a database solution.
    I don't need a kennel one as that's done through the diary.
    isn't the diary paper? If using outlook or similar, wouldn't you want to tie these together?

    on the slim chance that I would need to find old addresses I can look through the paperwork
    I was thinking moving forward - people will move in the future...

    have also got ideas of how I want it to work but I want to start in the right place, rather than get down the line and have to change something big
    precisely why you need to draw it all out first. As you describe it, you have a sketch with none of the detail filled in.

    For starters, is this a monumental task or is it achievable and have I got the tables right?
    Not really possible to answer this question at the moment - it is the equivalent of saying 'I've got some wheels, an engine and a gearbox and some good ideas on what the colour should be - can I build a car'

    You can probably knock something up with three tables and a few forms in a day or so, but whether it really does what you want, will save the business time/money, is a good foundation for building additional functionality (e.g. auto emails, bill generation, sms text reminders, etc etc) only you can answer.

    for example

    The first button will bring up a Booking In form that will have a drop down combobox of client names (humans).
    You haven't specified how client names get added/changed or what the user does if they can't find the client.

  5. #5
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Talking of Data. There are some calculated fields I think I need but I'm struggling with the way to do it so far.

    1. Counting the number of days between 2 dates on the BookingData table.
    2. Checking if the previous vaccination date is more than a year from the current date on the CatData table.

    I will also need to be able to add up a Clients total number of days stay, money spent and so on. But that's a way off for now.

  6. #6
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Haha yep I know what you mean with the car analogy

    The reasoning behind it is not to save money or make money, its just a slight time saver by not having to find the previous booking form and then just rewrite all the old data into a new form everytime someone books, especially as a lot is repeat custom. Its also just something to keep me busy and keep my brain working so I like the challenge

    Let me know what you need to know, and I'll tell you how I think it's going to work in my head.

    For the Clients there will be a drop down with existing Client names in. Staff select the names and it auto populates. As for new bookings I'm hoping that there can be a text box that will allow you to add names manually and fill in the rest of the form from there, rather than having to have a 'NewClient' form...

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It is possible that you would find a template db that you could work with if you look. If not, try to think in terms of entities and relationships. Consider immunization as an example. If there is a chance that down the road a new immunization becomes necessary, you'd have to add a field to your table for the cats for that shot. This would be BAD. So maybe you'll bank on that never happening. Then if some cats won't get certain treatments, you end up with a lot of holes in your data - bad with a small 'b'. These considerations are what's known as normalization, which is something you need to read up on if you have not. A tblImmunized is probably the way to go, which would be linked to the cat via CatId in tblCats.
    Here's some things you might want to review:
    - words you should not use in names http://allenbrowne.com/AppIssueBadWord.html
    - ideas on naming http://access.mvps.org/access/general/gen0012.htm ; http://www.access-programmers.co.uk/...d.php?t=225837
    - functions you may need http://www.techonthenet.com/access/functions/
    - what autonumbers are/are not http://www.utteraccess.com/wiki/index.php/Autonumbers
    - look at the table topics (left nav pane) http://www.fmsinc.com/free/newtips/primarykey.asp
    - Google 'ms access normalization' - you will get a ton of results; find a few that explain it in a way that suits your project and level of knowledge.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    calculated fields should be in queries, not in tables. Although a limited facility exists, I've yet to see a genuine reason for having a calculation in a table. Also avoid lookups in tables and although multivalue fields look pretty, they tend to be used for the wrong reasons - they can be used for a relatively small static list or one that has very few changes - a list of weekdays for example. They should not be used for dynamic lists (such as cats or clients)

    counting days etc - there are functions available to do that.

    a tip on googling - prefix your search phrase with 'access vba' or 'access form' - you will eliminate a lot of (but not all) links to do with excel and other applications. e.g. 'access vba counting days'

    Let me know what you need to know
    I don't need to know anything I'm happy to provide advice and direction, but not do the job for you - I have paying clients who pay me to do that.

    I'm hoping that there can be a text box that will allow you to add names manually and fill in the rest of the form from there, rather than having to have a 'NewClient' form...
    there you go again - you need a client form, you don't need separate ones for new and existing. Having got your form, you can set the data entry and add new properties to switch between the two 'views'

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    calculated fields should be in queries, not in tables.
    So true, but I think Ajax would agree that it is ok to do calculations in form controls as well. An example would be to present the age of a cat in an unbound, calculated control by subtracting DOB from Date. Not so good would be to store the calculation unless the business case absolutely required it (e.g. legacy/history requirements).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Hi Micron, thanks for the links. I'll be using them soon

    I've just found out about the calculated fields in tables thing so I now know I have to have a text box that does the calculations which has saved me some confusion as I was trying to do it in the table

    I'm working out fairly quickly that this may be as big a challenge as I want to make it! In all fairness I'm just looking for a simple way to save me having to write stuff out over and over and to get information that would be handy without pulling out binders full of previous bookings. Sounds simple huh?

    The ClientData form has been created. It looks rubbish thanks to the Wizard but that can be changed later. I'm now working through what I need it to do. I'm following the diagram I've got and so the first thing I'm working on is to make sure that I can input all the data to the 3 tables from the one form which I think is simple... I'll let you know if I get stuck.

    I really appreciate the help. I get that I have no idea what I'm doing but my perseverance will keep me going and I hope that you can all push me back on track when I go askew

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I think Ajax would agree that it is ok to do calculations in form controls as well.
    I agree. Same for reports

  12. #12
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Word of advice - stop designing until you review the info lest you have to scrap everything and start again.

  13. #13
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    I'm looking through it all now

  14. #14
    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
    Just to emphasize the advice/comments from Ajax and Micron. I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper to develop and refine a data model. Create some test data and vet your model.

    Let me also suggest that you can prototype a "user experience" sort of thing using a minimal database an prudent use of msgboxes. There is nothing(no detail) behind this approach but it can help with the logic of what should be shown to the user, what should be kept from the user, what options should be available now. This approach --we used to call stub processing in the "olden days"- is just a series of simple forms, buttons and msgboxes of what might happen. It's a good strategy to get feedback, comments and experience.

    Sample of this is along this line:

    Opening interface Form was opened
    Select new Registration or Update existing Client (button option
    )

    Selected Option NewRegistration.
    New Registration details would go here.

    I see too many people, who, having the latest HW and Access, jump in head first thinking the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

    You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help.


    Good material on database and Access and vba programming in links here.

    Good luck.
    Last edited by orange; 06-22-2016 at 02:47 PM.

  15. #15
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Hi Orange. Thanks for the link

    I'm not sure if I'm underestimating what it takes or I've over complicated the description of what I'm trying to achieve.

    In my mind, what I need is almost as simple as a database can surely be, but you've all said it's far more complicated than that and made me wonder if it's even possible for me to do it. Maybe it is but I'll enjoy the challenge anyway

Page 1 of 4 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