Results 1 to 8 of 8
  1. #1
    AVINANAIR is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2019
    Posts
    7

    Connecting Forms and Query

    Sir, I am beginner in ms access. I have created 3 tables for a programme to find volume of a standing tree.


    Table1-species
    Table-2-Girth
    Table-3-Volume
    I have entered concerned datas to the table since all datas are fixed.
    Now I want to show the volumes of species have a particular girth in a form.

    Eg: In the form, I want to select species names from a combo box

    Then select girth value (ie 75,85,95,...405) from another combo box
    Another text box will show the concerned volume of that timber species having the selected girth range

    I have also created query with the above three table.. Can I make a form as decribed above, with the help of above three tables and query...
    Kindly help me

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Form should be bound to Table 3 - Volume. At a minimum, table needs fields: ID, DateMeasure, Species, Girth. The volume would be a calculation, not a saved value.

    Will you be measuring same trees more than once? Do you need to keep track of these measurements by tree?

    Only want to allow girth measurements as multiples of 5 incremented by 10?

    This is all basic Access functionality. Have you studied an introductory tutorial?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    AVINANAIR is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2019
    Posts
    7
    Sir I have already created three table with sufficient fields. Relation ship among the tables are also assigned as the picture below. The volume of the timbers are not a calculated value. There were no formula to find out the volume. The volumes of timbers of concerned standing species were discovered after comprehensive research, which taken after years of analysis and datas from the field. Hence volume are also pre determined. So I have enter the datas of all tables and all fields. Now I have to retrieve the values as explained in my question through a form, from which I can print out the datas of a large plantation consists of 1000 trees. For example in Forest department we have to prepare a Marking register for a felling teak plantation. For which we have to mark all teak trees in the plantation and took the girth of each tree at the breast height. Now they are written in a book with concerned volume after looking from a book, which is known as NR nair volume table. I try to reduce the effort, with the help of an application. So that I tried this...There will be hundreds trees of different species in plantation. So just select a tree species from a combo box and type the girth value in a text field. The value of timber volume will be desplayed in a text box. If so somany species can be find in single data sheet form (?) and can be print out in A4 pages.. I think it is possible.. Kindly instruct me the right way.. The relation ship table is shown below.. kindly help me..since I am noviceClick image for larger version. 

Name:	SCREEN.jpg 
Views:	28 
Size:	131.4 KB 
ID:	38146
    Last edited by AVINANAIR; 04-18-2019 at 10:08 AM. Reason: add

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sorry, relationships make no sense to me. For one thing, it's circular, and makes my head spin trying to understand. As best I can understand, here's how I think should be.

    tblSpecies
    SpeciesID_PK
    SpeciesName

    tblTrees
    TreeID_PK
    SpeciesID_FK
    DatePlanted

    tblVolumes
    VolumeID_PK
    SpeciesID_FK
    Girth
    Volume

    tblMeasurements
    ID
    DateMeasure
    TreeID_FK
    Girth

    With this arrangement tblVolume does not have a direct relationship with any other table. Consider this query:

    SELECT Query1.TreeID_FK, Query1.SpeciesName, Query1.SpeciesID_FK, Query1.DatedPlanted, Query1.Girth, Query1.DateMeasure, tblVolumes.Volume
    FROM tblVolumes RIGHT JOIN (SELECT tblMeasurements.TreeID_FK, tblSpecies.SpeciesName, tblTrees.SpeciesID_FK, tblTrees.DatedPlanted, tblMeasurements.Girth, tblMeasurements.DateMeasure FROM tblSpecies RIGHT JOIN (tblTrees RIGHT JOIN tblMeasurements ON tblTrees.TreeID_PK = tblMeasurements.TreeID_FK) ON tblSpecies.SpeciesID_PK = tblTrees.SpeciesID_FK) AS Query1 ON (tblVolumes.SpeciesID_FK = Query1.SpeciesID_FK) AND (tblVolumes.Girth = Query1.Girth);
    Last edited by June7; 04-19-2019 at 01:27 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    AVINANAIR is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2019
    Posts
    7
    Thanks sir, it is wonder full experience, a master hear my nonsense.. and giving precious advise..thank you very much sir... I will try with your querry...Actually the volume table has relation ship with other tables.. There is a specific voulume for a species having a girth range.. Teak has a fixed volume at the girth 75cm, fixed voulume at the girth 85 cm and so on .. I had fill all the data of volume for each species having girth 75, 85 and so on..Any way I will try your query...But did not know how to apply the same

  6. #6
    AVINANAIR is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2019
    Posts
    7
    where I have to written the query...is it in form builder..or in a text box?

  7. #7
    AVINANAIR is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2019
    Posts
    7
    Date planted is not required in this case, sir

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Query SQL statement goes in query object. Open query designer and build query. Have to build the tables I describe first.

    DatePlanted just sounded nice. What other info about an individual tree would be of interest? My design assumes each tree will be measured more than once over years (question in post 2). If this is not correct, then data structure could be simpler.

    In the design I describe, tblVolumes cannot be directly linked to other tables. That's why the query has a subquery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Connecting Forms to a Report
    By jbates in forum Forms
    Replies: 4
    Last Post: 08-15-2014, 02:22 AM
  2. Replies: 6
    Last Post: 07-18-2014, 11:35 AM
  3. Connecting input masks to your forms
    By cmc14 in forum Forms
    Replies: 3
    Last Post: 04-25-2013, 02:40 PM
  4. connecting SQL code for a Query
    By Huddle in forum Queries
    Replies: 1
    Last Post: 02-08-2012, 03:42 PM
  5. connecting many sub forms in one main form
    By Dasun in forum Programming
    Replies: 2
    Last Post: 07-01-2011, 02:39 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