Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: 3 Tables to one Query

    Hi all,

    Newbie here to the Forum and Access so please be gentle, although I will try and be as verbose as possible. I have little knowledge of Access and probably should have started on a tutorial, but had to jump right into working with it blindly for work. So far finding my way through it ok and do have reasonable knowledge of Excel, so formulas etc don't scare me lol.. However terminology is lacking...

    What I am doing is exporting data from a drawing program (Autodesk Revit) for quantities to estimate and cost. At a later date we will be importing that data into our Estimating program to fine tune & create orders and such, but for now Access will be used to get ballpark figures whilst doing the preliminary design.

    Here is where I am stuck - so far. Revit is a component based drawing program (BIM). When the data is exported from Revit it creates 2 tables - 1 with the properties of the components (Model, Description, Cost etc) and 1 with a list & quantities of the components used. However some of the components have multiple items attached to when it comes to costing in the Estimating program.

    The scenario - A front entry door component in Revit contains the door & door frame as one item and can only assign 1 id to the component, but the estimating program the door and framing components are separate, for example - Door component id is BOQ1234, Frame Component is BOQ4321.

    I have created a Query where it takes the information from the 2 exported tables and quantifies to get a total cost as the id numbers between the 2 tables are linked, however this does not include the framing components.

    In Access, can & how do I automatically attach the frame component, and list, to the door component in one Query if another table is created with the frame components and properties?

    Hopefully this will explain what I am wanting to achieve in the Query -

    MODEL - DESCRIPTION - COST - TOTAL
    BOQ1234 - External Door - $350 - $350
    BOQ4321 - External Door Frame - $150 - $150

    - OR -

    MODEL - DESCRIPTION - COST - TOTAL
    BOQ6789 - External Door With Sidelight & Glazing - $350 - $350
    BOQ9876 - External Door Frame with Sidelight & Glazing - $200 - $200

    (Door cost will remain the same, but assigned a different model number as it is a different component in Revit, and the framing dictates the variance in price.)

    The next thing I will need to show is the total for each Query created and build Reports, but need to get over this hurdle first.

    Hope that explains it reasonably thorough. Alternatively - am I looking at it the wrong way? As mentioned, terminology is lacking so not helping me find the right results with Google searches.

    Cheers

    Dan
    Last edited by Sully1975; 05-12-13 at 20:10.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    It would be helpful if you could post some examples of records from both tables for a given export. Feel free to remove or change any confidential or privileged information first!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    2
    Thanks for the reply Weejas.

    Attached Tables and Query exported to Excel.

    The "Door Types - Table" (Item Properties) & "Doors - Table" (Items Used) are created by Revit.

    The "Door Qty - Query" I created to quantify and cost.

    The "Door Entry Frame - Table" I created in Excel with the cost for the frame. This was only setup as an experiment.

    I want to quantify the Door Frame and show in the "Door Qty - Query" when the specific door exists in the "Doors -Table".

    Hope this makes sense.

    Cheers again.

    Dan.
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Apologies - I should have been clearer.
    Can you post a zipped version of the database? Seeing the tables and queries together will make it easier to work out what's needed.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •