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

    All suggestions adopted. So next steps?

  2. #47
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.
    Do these two and your new system all use the same Customer/Client ID? Same Cat Id?
    Do they have any formal requirements/specifications?

    I would recommend that you identify -even at the most conceptual level - where potential linkages between these systems would occur. Not details, but the "major thing/things" that flow/connect the systems. Knowing where the linkage would be (if ever built) and recording it during analysis will save effort down the road. Again the analogy is your house blueprints--if you know where the pipes and electrical are "hidden" in the walls, it will save time to connect and/or adjust in future.

    You do not want to miss the opportunity to note a link or key concept for potential unification of your systems. You don't have to build it and you may never build it, but it is a fact that you know now, and may not happen upon it so easily again. These things get recorded, just as "if we ever redesign this, we'll have to consider x,y..." These become the "wouldn't it be nice if..." rather than "we must change the calculation in query 14..".

    Also, it is always better to ignore something from a point of knowledge, rather than assuming something and putting it off for future, or ignoring without thought. A little analysis can usually determine the "potential for/importance of" X and you can keep, record or ignore having considered it.

    Good luck.

  3. #48
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I create tblDevNotes with fields for the db functional areas (this could be Client data entry, cat history entry, reporting...) for issues that pop up, along with fields for particulars and a yes/no field. These could be called Function, Details and Complete. Yes I know Function is a reserved word, but I will never query or code on this table and it suits my purpose, which is having a record of things I think of that need to be created/corrected, ideas to implement that pop up, where I left off in a process, etc. Something that is easy to get at, which for me, is the db since I'm working in it.

    Everyone's approach would not be the same. After creating my FE (front end) I would forge ahead by entering a few rows of real data in tables, building queries on that, then build forms to work with the data. You know that all queries, forms, reports and modules should go into their own db (FE) and you link to the tables, right? If you can retrieve related records that are accurate and use the forms to enter data, it would seem your relationships and indexes are correct, data types have not been mismatched, etc. I think it's easier to build a form with the wizard based off of a query that returns something, or a table with data. NOTE: I immediately go through the controls and give them proper names so that control names are not the same as the underlying fields. CatID becomes txtCatID. However, I might have an edge in that I would know whether or not I needed a form/subform relationship, tab control, datasheet view or whatever so I might be overlooking something that would help you as a beginner. There are others here that I think have a better mindset around that, so I hope they will weigh in.

    What I think is important is that at various points in the development, you compact more often in the beginning and save a file once in a while with the date as part of the name (Whatever06012016.accdb) as you develop. If things become corrupted and you cannot recover from a compact/repair, you will have something to fall back on. The last version is what I'd create the working version from by importing all the objects into fresh files. You have the option to keep the records you have entered or start from scratch. Some cannot abide the fact that their working version auto numbers don't start at 1. If that describes you, you will have to start without data and delete and reinstate those fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #49
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    The other systems would fit right into this database as the checks done in the pens relate directly to each cat. The Food diaries are the same. Now I've got my head around the basics of tables I think that it would be simple to add them in the future with out any disruption to what I have achieved so far.

    You know that all queries, forms, reports and modules should go into their own db (FE) and you link to the tables, right?
    This has confused me...

    I've planned my form on paper. It's simple and in my head should be achievable fairly easily. Like you say though, the links need to work, the data needs to show up, and I need to be able to enter data in via the form.

    Here goes...

  5. #50
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Confused about what? It's pretty much the defacto standard approach. Tables go in one db, everything else in another, which you open and link to said tables. An exception is that temp tables are sometimes kept in the FE, but these should be limited. Individuals who have their own pc or thin client to access the database front end on a network usually get their own FE copy rather than sharing one. Even if it's only one terminal (regardless of the user count) you do NOT want to be doing design changes to forms or whatever in the same db as your tables. Which brings me to a point not mentioned yet.

    If you
    - will have several users
    - want to keep track of who created an appointment or other data
    - keep lower lever users from accessing certain functions
    - keep certain staffers out altogether
    - or effect any other type of user control that you can think of
    you need to create a user table with the necessary fields such as FName, LName, Lvl (just not "Level") etc. This is not necessary right now, I think, but if you can foresee it, jot it down in your tblDevNotes!
    Good luck!

  6. #51
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Haha. A plan never survives first contact so they say

    Having planned the form out, it turns out its a bit more complex (but only slightly so - a mixture of split forms and subforms are needed) but I will read up and work out how to do what I want.

    However, the good thing about drawing the form out is that I've worked out the data I want to display, which has highlighted the queries I need to create to display that data. I've now created those queries, obviously re-doing all the data mismatches, relationship types and wrong joins I had which I thought I had nailed first time... Bloody beginners!

    Onwards and upwards

  7. #52
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    I had been considering the staff side of it but it's not something I will be opening up to them yet. There is a lot of data to go in first and so I will get everything up and running, make sure it works and then look at adding that side of it later, It's in the list of things to consider

    With regards to the form going in a seperate db. Do you mean I use the db splitter wizard? Why shouldn't you have the form in the db with your tables and queries?

    I've split it anyway (saved and backed up first) and now it all looks the same but the tables have a small arrow next to the icons in the All Access Objects pane but the queries and the form don't. Is that right and do I carry on with the form as I was?

  8. #53
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    However, the good thing about drawing the form out is that I've worked out the data I want to display, which has highlighted the queries I need to create to display that data.
    This is part of the data model vetting process. You take a sample scenario of a form or report (some desired output) and see if you have the inputs required to give the outputs.

    You can also mock up a form with some textboxes, listboxs,combos and buttons, then instead of detail data manipulation, just have a msgbox display what was clicked and what should be presented/done next. Once you get the flow/logic organized , you can add in the details. This is often a good strategy to get user feedback and suggestions and to communicate the logic/interaction between user and form.

    There are many ways to skin this cat. The key is to get your data structure (the foundation) correct before adding on the veneer and paint.

  9. #54
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by TheOakster View Post
    I had been considering the staff side of it but it's not something I will be opening up to them yet. There is a lot of data to go in first and so I will get everything up and running, make sure it works and then look at adding that side of it later, It's in the list of things to consider

    With regards to the form going in a seperate db. Do you mean I use the db splitter wizard? Why shouldn't you have the form in the db with your tables and queries?

    I've split it anyway (saved and backed up first) and now it all looks the same but the tables have a small arrow next to the icons in the All Access Objects pane but the queries and the form don't. Is that right and do I carry on with the form as I was?
    Easier for me to not repeat what reasons you can find here and in many other places if you research the topic
    http://www.techrepublic.com/blog/10-...cess-database/
    You should have NO forms, reports, queries or standard modules in the back end. You could use the splitter wizard or just create a new db file (it's best to start clean) and import all the tables into it. In another new file (FE) you Get External Data and LINK not import, to the tables. That is the file you put the forms, reports, queries and standard modules into.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #55
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Thanks for the link Micron. I had read some similar things at the beginning and sitting here right now it seems a bit of overkill for my uses but I'll follow the standard and like you all say, if I build for the future now, it's easier down the line.

    I've done a clean split into a new db and I'll now start working on the form. I'm going to try and work most of it out myself using those previously linked videos and what I can find on here. I'll post updates and any questions as I go along.

  11. #56
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Further to this, just to help me understand:

    I now have a _be file name which is the back end db. If I open this I can see all the tables. When I open the non _be, so the fe db, I can see the tables with an arrow to show that they are linked and when I hover the mouse it shows that there location is in the _be file. All good and as expected.

    The queries are still sat in the fe db though. If I move them, drag and drop, copy and paste or import them into the _be db they never show up in the fe one. The only way I can see them is to have them in the fe side. Is that right? From your explanation I expected to see them linked to the _be the same as the tables are with an arrow to show that they are linked?

  12. #57
    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
    Only tables in the backend. Queries, forms, reports, macros and modules in the front end.
    And a separate copy of the front end on each users's PC.
    That's the way it's done --most efficient; minimum corruption... we hope.

    Additional info re backend/performance etc.

  13. #58
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    Ah yes. Having re-read Micron's post I realise that that is what they said! I thought it said it was just the forms in the FE and everything else in the _be, that's what you get for trying to do some of this at 2am when you can't sleep...

  14. #59
    TheOakster is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    30
    I'm still here. Struggling through the forms. I'm going to keep trying and work out what I need but its looking like I'm going to be back answering questions soon

Page 4 of 4 FirstFirst 1234
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