Results 1 to 5 of 5
  1. #1
    Gjb1982 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    3

    Question Possible to present values from a table in a query that also is the query that created the values?

    Bear with me. I'm self-taught at access and don't know all the lingo.


    • I have a table (#1) that has vehicle, RPO (regular production option), and component.
    • I have a query (A) that joins those three together to create a unique value, which looks like this: "Corvette - J56 - Front Rotor".
    • I have another table (#2) with all the different Vehicle-RPO-Component combinations and whether we supply them or not: "Yes" or "No".
    • What I would like to do is have a field in query A that presents the "yes" or "no" based on the vehicle/rpo/component field that's also created in that query. Is there a way to do that or is it impossible?
      • Because right now the only way I've been able to get the results I want is to create a query (X) that joins the information and then create query (Y) that uses the results from query (X). I'm not sure that's a wise or optimal way to do it. I feel like I would have to run query X before query Y every time to make sure everything lines up.


    Any help is appreciated before I spend more time fiddling around and getting nowhere. Thanks in advance.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you shouldn't be using queries for data input/update. Use a form.

    In your case I suspect you need a main form based on table#1 and a subform based on table#2 linked on the PK in the first table to the FK in the second table (the subform control linkmaster/child properties)

    If this does not make sense, provide a screenshot of your relationships and some example data

  3. #3
    Gjb1982 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    3
    Thank you for responding. Perhaps I'm doing things wrong or not describing it properly. I'm not trying to input anything. I'm trying to have the query display data based on the values it generated compared to the tables already present. Almost like a vlookup table in excel.

    For example...in query A
    1) Concatenate column A with column B with column C of table #1.
    2) Using the concatenated value A B C, lookup that value in column A of table #2.
    3) Using the value from column B of table #2, present it in query A.

    Make sense? If not, I'll get some screenshots tomorrow.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    2) Using the concatenated value A B C, lookup that value in column A of table #2.
    think this is where you are going wrong. Each table should have a primary key (PK) and this is what you use to link to other tables

    Almost like a vlookup table in excel.
    databases are completely different to excel, and work in a completely different way. Trying to apply excel methods to access or any other database will at best be difficult and at worst unworkable.

    Access has a function called Dlookup which is similar to Vlookup, however using it in a query is the slowest way of doing something. If that is all you want to do then it can be made to work providing you don't have too many records and there are no typos in your data, but it is not a basis for building any kind of application.

    Google Dlookup to find out more, perhaps you can make it work, but otherwise will need to see your relationships and some example data to be able to help further - and I suspect will need to make some design changes to your tables

  5. #5
    Gjb1982 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    3
    Thank you. I'll look into it.

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

Similar Threads

  1. Change Table Index Values Based on query Values
    By thuzkee02 in forum Import/Export Data
    Replies: 2
    Last Post: 11-24-2015, 11:45 PM
  2. Replies: 15
    Last Post: 10-18-2015, 04:05 PM
  3. Replies: 7
    Last Post: 11-24-2014, 01:32 PM
  4. Replies: 2
    Last Post: 11-12-2012, 03:52 AM
  5. Replies: 7
    Last Post: 04-17-2012, 11:53 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