Results 1 to 5 of 5
  1. #1
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    48

    Advice On Form Design

    Hi



    A brief description

    The recordset fot the database is not large, around 6000 records. However, the details per record is quite extensive. Probably around 100 different fields.

    There are 6 teams that each have input into these fields. They all Currently use a separate Excel spreadsheet.

    I have taken all the common fields from the 6 spreadsheets and put it into my main table so that there is no duplication of data.

    I need advice on the following:

    The users LOVE filtering in Excel. Its their main tool along with VLOOKUP (which is a discussion for another day).
    I would like to keep the transition to using access as painless as possible.

    What would be the best option to use? They would like to see a number of records at one time and not just one. Would the best option be a form with datasheet view?

    Could I create a button that would export ONLY the filtered records to Excel where they can build their charts etc?

    Im no expert in Access and would appreciate any assistance/guidance?

    Please let me know if any more info/explanation is required

    Thank You

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by archie1_za View Post
    The recordset fot the database is not large, around 6000 records. However, the details per record is quite extensive. Probably around 100 different fields
    I can't exclude the possibility not having a clue what those fields are and what kind of data they contain, that you really need so many fields, but somehow I have a feeling your data in not normalized. To put it simple way - when you can collect data from several fields to single field in separate table adding fields for linking and for entry classification, then do it!

    Code:
    What would be the best option to use? They would like to see a number of records at one time and not just one. Would the best option be a form with datasheet view?
    I'd prefer form - subform structure with contionous subform.

    Code:
    Could I create a button that would export ONLY the filtered records to Excel where they can build their charts etc?
    You can, and here are surely people who can advice, how to do that. I myself prefer Excel report file querying data from access database. Every user can have his/her own report file(s), and data will be refreshed when user needs them. Access database doesn't tend to wander around in different catalogues and computers like Excel files often do, so it will be much easier to query data from database having a fixed location, as keeping track of various Excel files over whole network. And there will be no problems with user having workbook open when Access wants to write some data into it!

  3. #3
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    48
    Hi

    The fields are spread across the six teams. About 15 fields are common to each site (physical site in the real world which represents a record). Each team has between 12 and 16 fields of info that that they input/track.

    I have put the common 15 fields into 1 table and have separate tables for each teams info.

    They normally filter on those 15 fields in a variety of different "sequences" to get the info relevant to their team.

    The fields for each team is unique and is not duplicated in any other team. Its a combination of date fields, text boxes, binary options...

    The users dont input any information into the 15 common fields in the main table. That data is already imported into the db for them. They then filter according to those fields to see a group of records and enter their specific information.

    I thought of the following:

    two forms to be put onto a Main Form.

    Form 1 - Contains the common fields using combo boxes and and lists (where they can choose more than one option)
    Form 2 - A datasheet view form (based on their particular team, so I would have 6 of these based on their particular tables) that displays the results of the options chosen in form 1.

    They can then edit/view their fields in datasheet view (or is this not recommended). Once they have updated etc they can export the results to Excel and then do as they please.

    I dont know if this makes sense.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Access is a relational database. This means, that whenever possible, you have to organize your data in such a way, that:
    You avoid entering same information into several tables;
    You avoid entering same information several times (not entering same info into same table several times);
    You foresee means to link tables having related info (having primary and foreign keys in tables).

    Again, you haven't given a clue, what kind of info your tables contain, so it is almost impossible for us to give some real help or advice.

    When guessing, then probably you have (or must have) some file like
    tblSites: SiteID, Field1, Field2, ..., Field15

    where you define every site, and which probably will be a source for your main form (a single form type). In this form you select a site, add or delete sites, edit site info etc.

    Then following your setup you have a file for every site
    tblSite1Info: SiteRowID, SiteID, Date1, Field1, Field2, ..., and so on for every site (I added SiteID to make it easier to link tables, but SiteID will be same in specified site info table),
    or you can consolidate the site info into single table like
    tblSiteInfo: SiteRowID, SiteID, Date1, Field1, Field2, ..., where entries for different sites are identified by SiteID.

    Now you create continuous form(s) based on site info table(s) (6 forms, or 1 form, depending the path you choosed), hiding SiteRowID and SiteID controls. I advice to use continuous forms instead of datasheet forms (have never used them).

    After that you drag designed form(s) into main form, this triggers automatic subform creating. Usually the subform will be automatically linked to main form through SiteID - when not, then tou have to set the link manually in subform's Properties window.

    When you used 6 subforms, you have in main form's OnCurrent event to set only one of them visible at time - the one with matching SiteID. And you have to adjust all 6 forms into same position, so they are overlapping.

    When all was done properly, whenever you select a site in main form, all entries for this site in site info table are displayed in (visible) subform. And when you add a new info row into subform, it will be automatically attached to active site.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by archie1_za View Post
    The recordset fot the database is not large, around 6000 records. However, the details per record is quite extensive. Probably around 100 different fields.
    Would/could you Post the dB? 100 field in a table is quite a lot. Change any sensitive data. Only need 5 - 10 records for example data.

    ----------------------------------------------
    Quote Originally Posted by archie1_za View Post
    There are 6 teams that each have input into these fields.
    How many people per team?

    ----------------------------------------------
    Quote Originally Posted by archie1_za View Post
    The users LOVE filtering in Excel. Its their main tool along with VLOOKUP (which is a discussion for another day).
    Remember Access is not Excel. Two completely different models. Can create a search for or have the search criteria in the form header.

    ----------------------------------------------
    Quote Originally Posted by archie1_za View Post
    They would like to see a number of records at one time and not just one. Would the best option be a form with datasheet view?
    I never use datasheet view. I would use a main form (possibly in continuous forms view) and a sub form in continuous forms view.

    ----------------------------------------------
    Quote Originally Posted by archie1_za View Post
    Could I create a button that would export ONLY the filtered records to Excel where they can build their charts etc?
    Sure. that is easy to do........



    Arvil described the design pretty well. But it would help to be able to be "hands on" with the dB to be sure.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Advice to learn database design
    By cib in forum Database Design
    Replies: 2
    Last Post: 01-27-2014, 09:10 PM
  2. Advice on Database Design
    By JoeM in forum Database Design
    Replies: 1
    Last Post: 08-15-2013, 08:50 AM
  3. Need some design advice
    By messingerjc in forum Database Design
    Replies: 3
    Last Post: 06-18-2012, 06:16 PM
  4. Replies: 5
    Last Post: 04-02-2012, 08:24 AM
  5. design advice
    By Sarge, USMC in forum Database Design
    Replies: 4
    Last Post: 10-06-2010, 07:53 AM

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