Results 1 to 11 of 11
  1. #1
    Bugzapper is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    5

    Selectable fields within a form

    Hi,

    I'm not sure exactly how to articulate what I want to do here, so forgive me if I'm using the wrong terminology. I am a novice when it comes to Access.

    Basically I want to be able to fill out a data table using a form.

    The first 4 fields are simple and would be included for every line of data. Then there are about 140 fields, most of which will be zero values. Basically, for each line of data, anywhere from 1 to 20 of the 140 fields would have a number to input, but it won't always be the same fields.

    Is there a way to set up a form that has 20 inputs, where I can select which field I am inputting, then input a number. Like a drop down or something, but for the field instead of the value. So say the first line of data, I need to put a number in fields X, Y, and Z. Then, the next line, I need to put numbers in fields A, M, L, X, and Z. And so on...

    Any ideas on how I might be able to accomplish this?

    Thanks!

  2. #2
    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
    To maximize communication, I recommend that you move from theoretical eg X, Y, Z etc and give us a scenario in plain English that illustrates the issue. Use simple terms - plain English, no jargon - just as you would use to describe "your requirement" to an 8 yr old or an 80 yr old granny.
    Once readers understand what you are dealing with, I'm sure responses and options will follow.

  3. #3
    Bugzapper is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    5
    Okay.

    I want to record the data for trap catches of insects.

    Each line of data will have sample number, rep, trap, and date. Then anywhere from 1 to maybe 20 different species and a count of the number of individuals from that species.

    Some lines may only have 2 species, some may have 10, but they won't always be the same ones. There are about 140 possible species, so I don't want a form with an input for every one, just the ones that I got in each sample.

    Right now, I just input data manually in excel, but there is a lot of scrolling around the worksheet for each sample. It would be nice to be able to just use a form to input the data.

  4. #4
    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
    So are you looking for an Excel form, or are you on the Access forum to design and develop a database?
    Database is quite different than spreadsheet.

    Here are some free concept videos for Database concepts

    Intro to database
    The relational model
    Basics of data modelling

    Spend 30-45 minutes working through one of these tutorials from RogersAccessLibrary. They show the Problem, the Process and include solutions. You can use this technique with any database.

    You have to work through the exercise to appreciate the process and result.

    Class information

    Consolidated Widgets


    Good luck.

    I have attached a jpg of a very draft model of the subjects identified in your post.
    Attached Thumbnails Attached Thumbnails trappingInsects.jpg  
    Last edited by orange; 09-25-2016 at 05:42 AM.

  5. #5
    Bugzapper is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    5
    Orange,

    I appreciate the responses. I don't necessarily need a database, per se. I just need an easier way to get my data into a single table, which I can then export to stats software to do my analysis. I thought maybe Access would have more built in tools to create a custom form for this purpose. If you think this can be done in Excel, fine. It doesn't matter to me.

    Perhaps what I envision isn't even possible (seems like a simple concept, but what do I know). A form where fields can be changed on the fly as you're entering data.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Bugzapper View Post
    ...I just need an easier way to get my data into a single table...
    What you are describing is something we might refer to as a Report or a View or a Query. In order to make things easy, you will need a way for your Users to perform data entry. Provide the correct data structure via a model that defines the entities.

  7. #7
    Bugzapper is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    5
    Quote Originally Posted by ItsMe View Post
    In order to make things easy, you will need a way for your Users to perform data entry.
    Right. That was my question. I asked about a way to perform data entry (via a form or whatever). I have data on paper that needs to be in a table. I can do it manually I guess (I have been for years). I just thought maybe a form could be created to make it a bit easier.

  8. #8
    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
    I am not an Excel person, so have no suggestions on that end.
    Database is a different animal. If you want to get data out of a data base efficiently, you have to get the table(s) designed and relationships established so they support your requirements.

    My quick take on your original post suggested 5 or 6 tables as per the draft model. The statistic you are most interested in relates to Catch (or capture). That draft deals with the following:

    There are several Traps
    You place Traps in 1 or Many Locations
    There are many Species
    There are many Insects
    A Species identifies 1 or Many Insects
    An Insect may be caught in 0,1 Trap (assuming there are no live releases)

    When an Insect is caught in a Trap, you identify the Trap, the Location, the Insect, the Species and the CaptureDate. These are the attributes of a Catch (or capture).

    You mentioned
    I have data on paper that needs to be in a table.
    Can you tell us more about the table you envision? Or show us a layout with some sample data?

  9. #9
    Bugzapper is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    5
    Okay, here's what the table will look like.

    Click image for larger version. 

Name:	Untitled.jpg 
Views:	16 
Size:	131.7 KB 
ID:	25896


    And so on. Except instead of the 7 species in this table there are 145.

    All I want to do is be able to fill out this table, without having to scroll back and forth all day long. Access won't allow a form with 145 fields (and frankly that would be just as annoying as scrolling around), so I thought maybe there was a way to have a form which has say twenty text boxes that you can designate which fields they populate as you go.

    Sounds like Access can't do this.

  10. #10
    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
    You are telling us How something should to be done if you combine your storage and display based on your "table". There is a big difference with data storage and data presentation. Access deals with relational data. Your table represents a spreadsheet, and since you have been using Excel, that is to be expected.

    Access uses relational tables. The tables are designed based on some fundamental concepts --first of which is Normalization. Once you get the tables Normalized, and the relationships designed based on your "facts", you have identified the storage aspect. You can then set up/design a form or forms by which data is added/stored in the tables. These can be very different structures.

    What you want to do can be done with Access or another relational database. But you need to structure the tables and relationships based on relational concepts.

    You are not the first poster with an Excel background to be confounded by relational concepts. M$oft and some other marketing literature would have you believe moving from spreadsheet to database was straight forward. If you scan the forums, you'll see some of the issues which tend to boil down to " Excel and Access are different products, built on different object models with different concepts".

    As I say, it can be done, but getting your table(s) set up "relation-ally" may be the most important step.

    Let's see if others join the conversation and have other approaches.

    Update: I knew I had seen a sample of a properly normalized database with a "spreadsheet-type" form.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Based loosely (very loosely) on orange's model in Post #4, I threw together this demo.

    When adding the insect name for a catch, in the subform, you can start typing the insect name/species and the list will automatically jump to the name that begins with the letters you typed. For example, if an insect name began with "T", just type "T" or "Tha", then click on the down arrow and the dropdown list will be at those names that begin with "T" or "Tha".
    Attached Files Attached Files

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

Similar Threads

  1. User-Selectable Fields in Datasheet View
    By Chim20 in forum Forms
    Replies: 9
    Last Post: 02-16-2016, 03:12 PM
  2. How can you have a multi selectable form?
    By techtony in forum Forms
    Replies: 4
    Last Post: 08-07-2014, 11:30 PM
  3. How to make a selectable list???
    By Fengtiger in forum Forms
    Replies: 3
    Last Post: 06-27-2012, 12:54 PM
  4. Combo Box to selectable
    By Paul1762 in forum Forms
    Replies: 11
    Last Post: 08-15-2011, 11:05 AM
  5. non-selectable combo box
    By Ted C in forum Forms
    Replies: 1
    Last Post: 06-22-2010, 03:22 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