Solved

Trying to take use variables in Access Jet SQL, possible?

Posted on 2007-03-23
6
273 Views
Last Modified: 2008-02-01
I'm trying to select a value from a table, but I'm trying to use a value from another table to tell which column to use. So basically I'm trying to use a variable. Here's what I'm trying to do:

SELECT TABLE2.(TABLE1.COL1) FROM TABLE2

What this is saying is that the value for table1.col1 is a heading for table2. Can I do this in Access via SQL?
0
Comment
Question by:bemara57
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18781719
you need to code it as an IIF i believe

e.g.  Select IIF(table1.col1 = 'x',X,Y) from table2
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18781771
You can use VBA...

strSQL = "SELECT Table2." & dlookup("Col1", "TABLE1", "SomeField = SomeValue") & " FROM Table2"
                                                                                                ^^^-- criteria, if needed

The concatenated dlookup will allow for a variable field name.
This sql string can then be used in a recordsource, rowsource, etc.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18782003
you can use DLookup directly in a query, no need to use VBA.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 39

Expert Comment

by:stevbe
ID: 18782007
how do you know which record in table1 to use?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 18783252
To clarify the answer from lowfatspread:

Select IIF(table1.col1="fld1",table2.fld1) FROM table1,table2 ON table1.ID=table2.ID;

There has to be some value (ID?) in table2 which restricts table1 to a single record.  This any clearer?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18784591
thanks GRayL

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question