Results 1 to 6 of 6
  1. #1
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52

    calculation in a query

    hi i have one main form that i enter data in.this data then appears in 6 more forms and a search is generated according to the dat entered in the main form.



    I designed a query that retrieves the information that i get from the search but i am battling to retrieve the totals that each form generates in query.I have attached the database please show me where i am going wrong in the report query which generates the output list.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your query is a bit too complex, makes it difficult to maintain and to troubleshoot.

    1) Don't get your data from forms, get them from tables
    2) Use only one form - the company names can be a drop-down list (I assume they can only select one?), show only that one form as a subform (you can use visible/invisible, or if they are all the same then you can change the record source for one form depending on which company they select)
    3) As there is only one total on each table, show only one total on the form - have a drop-down list for the weight (I assume they can only select one?)

    Once the forms are "normalised" - i.e. conform more to the table structure - then it will be a lot easier to get the data from the tables. Your report is quite simple yet the path to get there is convoluted.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Actually, now that I come to think about it, your structure should be normalised first. You should not have separate tables per company as the data is all the same, the only thing different on each table is the company.

    Create a master table of company names with autonumber ID.
    Have one table containing the destination, fees, etc, and have the company ID

  4. #4
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    i cannot normalise the the data since the totals in the tables differ from one to another

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry, but you MUST normalise your tables. In any database design, this is the foundation of your house, if you build it with cracks then in a year's time it will fall down! I have attached your database with some changes. I hope I haven't scared you or overwhelmed you!

    There is a new table called NewTable. This is where all the information you enter will be stored. I used BEX as my example so there may be some fields missing.
    Never store totals or calculated fields on tables - calculate values as and when you require them (forms, reports, etc).
    Don't add complexity to tables with combo box lookups, formatting, etc - this adds a huge amount of complexity and overhead (fine when you have 10 records but what happens when you hit 10 000?)

    The form I used is Main Form 2. There will no doubt be more "If" statements required, again I have only done Bex. Check out how I calculate the transport fee. Other things might differ between companies, such as mode of transport (?). We can use the AfterUpdate on the company to populate this data.

    NewOutput_rpt is the report - not pretty but you can make whatever changes you want. I am just trying to give you a general idea.

  6. #6
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    the form is not supposed to add new records it is working as a search form.it is up to the user to enter any value for the weight,in return the user must get a calculated total from the form.The user does not know what the minimum charge is.let me normalise my data first and then i'll take you through my goal for the database

    thanks a lot aytee111
    Last edited by funi.t; 01-27-2012 at 04:39 AM.

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

Similar Threads

  1. Query calculation
    By Charter in forum Queries
    Replies: 8
    Last Post: 12-30-2011, 11:50 AM
  2. Query calculation from a form???
    By daisy19 in forum Queries
    Replies: 1
    Last Post: 07-08-2010, 10:10 AM
  3. calculation in a query
    By elmartinez in forum Queries
    Replies: 3
    Last Post: 02-12-2010, 03:23 PM
  4. IIF() in a query calculation
    By bbylls in forum Queries
    Replies: 4
    Last Post: 12-03-2009, 04:42 PM
  5. Query Calculation
    By kellyd in forum Queries
    Replies: 5
    Last Post: 08-31-2009, 11:14 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