+ Reply to Thread
Results 1 to 14 of 14

Beginner designing a complicated (to me) query: Ambiguous outer joins etc

  1. #1
    Registered User
    Join Date
    06-11-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2007-2010
    Posts
    25

    Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    I'm sorry I can't come up with a more descriptive title. I can't figure out the right words to use to learn to fix this myself.

    Let's say I have three tables for an inventory management/profit calculation system.

    First table has ID numbers for each item I use, and it matches those IDs up to human-readable names, for example:
    Please Login or Register  to view this content.
    Second table has the quantity of each item needed to create each final product, for example
    Please Login or Register  to view this content.
    Third table has prices that include multiple possible suppliers, for example
    Please Login or Register  to view this content.
    I have a query that will return the minimum price as follows:
    Please Login or Register  to view this content.
    But what I REALLY want to do is write a query that will take the item IDs, look up the minimum price of each, and calculate how much the final item will cost to make. In this example I want it to take the recipe I gave it in table #2 and understand that 4 tbs flour ($0.20) plus 4 tbs sugar ($0.40) plus 2 tbsp cocoa powder ($0.36) plus 2 tbs vegetable oil ($0.16) plus 2 tbs water (0) = $1.12 which would mean the brownie is $0.88 profit.

    Whenever I try to do this, Access gets very antsy about trying to refer back to ID multiple times in the same record of the Recipe table. It will either return no matches or it will get on my case about ambiguous joins. And I have NO idea how to stop it, and NO idea what keywords I need to use to figure it out. I'm good at figuring things out with google and experimentation, but I can't even figure out how to start in this case.

    Thanks in advance

    By the way, the example here is is a super awesome Mug Brownie recipe if that's any encouragement to help, ha.
    Last edited by Shanyn; 08-16-2014 at 07:00 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    LOL.

    This seems simple.

    But I would need to use a macro for this.

    Is that ok?

    Someone else might have a formula based solution.

  3. #3
    Registered User
    Join Date
    06-11-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2007-2010
    Posts
    25

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Oh... is it not possible to do this in a query? I thought if I could get the joins right....

    I'm just-okay with Excel macros but today is my first time even booting up Access. It's much better suited for what I want to do with my data than Excel was, since an array min if formula in thousands of rows brought my computer to a screeching halt, and I love it, but it's still so unfamiliar.
    Last edited by Shanyn; 08-16-2014 at 07:06 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    an array min if formula in thousands of rows
    Perhaps there is something that could be done to improve that formula to speed it up a bit?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Try Query.

    I will try and come up with an excel solution as a backup.

  6. #6
    Registered User
    Join Date
    06-11-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2007-2010
    Posts
    25

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Sorry, double post
    Last edited by Shanyn; 08-16-2014 at 07:18 PM. Reason: no idea how that happened

  7. #7
    Registered User
    Join Date
    06-11-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2007-2010
    Posts
    25

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Quote Originally Posted by FDibbins View Post
    Perhaps there is something that could be done to improve that formula to speed it up a bit?
    It was just one of these types of formulas, but the actual list of products is about 4k lines long and the actual list of sellers is several times that, so Excel simply couldn't handle the volume of the calculation. I really think Access is the solution I need.

    Quote Originally Posted by mehmetcik View Post
    Try Query.

    I will try and come up with an excel solution as a backup.
    I would love to try a query, but mine are all backfiring! Ha That's why I'm here!

    Thanks to both of you for taking the time to read and respond so far.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    I know you are (now) looking for an access answer here, but maybe dont give up on excel that quick.

    working with 20 000 rows shouldnt be that much of a problem, and there are often ways around CSE formulas. Can you show me a sample of your formula? (is there just 1? - column)

  9. #9
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Ok

    I wrote my first array formula for this:

    Enter this formula in B2. Enter it using Ctrl Shift Enter

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Ok I have just tried to create the formulas using a macro,

    Delete row 3 where my formulas are. and run this macro.

    Please Login or Register  to view this content.

    It rebuilds the formulas exactly as I intended.


    So.......................

    I can build a powerful macro to sole your problem if you need it.

  11. #11
    Registered User
    Join Date
    06-11-2012
    Location
    Calgary
    MS-Off Ver
    Excel 2007-2010
    Posts
    25

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Guys... I mean, thanks, but that's literally exactly what I used to have. I switched away from it because it hangs for a frighteningly long time with as large a spreadsheet as I was dealing with. Can I please get some help with Access?

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Any chance of a small sample workbook?

  13. #13
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    Your second table is not normalized. For Access to work easily with the data you will need to normalize your data.

    Your table should be set up in the following manner

    Table2
    Prod (PK)
    Ing
    Qty

    Attached is a database representing your sample data and the second table normalized. You will need as you see two queries to get the results.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  14. #14
    Registered User
    Join Date
    04-28-2011
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Beginner designing a complicated (to me) query: Ambiguous outer joins etc

    It seems to me you would use a report to do the calculations (cost, retail cost), with an underlying query to pull the ingredients and their costs from the various sources. You could choose whether to average the cost between suppliers, or maybe do a combo form to choose recipe and vendor.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. MS Query - Left Outer Join to Subquery possible??
    By Wannabe Guru in forum Access Tables & Databases
    Replies: 2
    Last Post: 06-14-2013, 02:23 AM
  2. Query Help - ambiguous outer join
    By jammy1812 in forum Access Tables & Databases
    Replies: 3
    Last Post: 10-18-2012, 09:43 AM
  3. Complicated Query (At Least I think So)
    By Lil-Diabo in forum Excel General
    Replies: 2
    Last Post: 04-10-2008, 05:23 AM
  4. Adding Criteria To Query With 4 Tables Outer Joined
    By SatuMaarit in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-27-2007, 07:02 AM
  5. Complicated query
    By Btbxgirl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-06-2006, 12:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1