473,699 Members | 2,401 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Use Select Query variable against a table and return value to same select query

Can anyone help me??

I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.

Ex.
SalesManID SalesManName
AT Alan Time

I have the SalesManID in my main select Query, but I want to Query
tblSalesman (which contains both SalesManID and SalesManName), run a
dlookup against the table based on SalesManID in my select query and
then have it return the SalesManName back to the same select query.

Can anyone help ??

Thanks in advance.

Dec 24 '05 #1
6 4843
huh? Skip the DLookup and just use two tables.

Dec 24 '05 #2
I can't use two tables because I keep getting ambiguous join errors.

I want to manually get the Salesman Name using the SalesmanID that's
found in my main Select Query.

Dec 25 '05 #3
Tell us the SQL script/string of your query.
Describe the table(s) and fields involved.

These things will help us to understand what you are trying to do.
Perhaps, DLookup is not the most effective way.

Dec 25 '05 #4
Ok,
here is an example
This works for me

I have a query that calculates how many bottles are sold say 12 btls
per case. So if I have 60btls sold I will show 60btls in my master
query.

I order to convert that to cases. I have another variable in my master
query that shows how many bottles there are per case, and I can perform
the division within my master query.

Quantity:Bottle s (is a field that has the number of bottles sold)
BottlesPerCase is another field within my master query.

So I do Quantity:=Bottl es/BottlesPercase

This works for me no problem

The problem I am having is taking a value called SalesManID from the
master query which has a value say 'AT'

Then I have another Field within my Master Query that is called
SalesManName(th is field is currently blank)

What I want to do is a lookup in a tbl called tblSalesMan which has
both the SalesManID ('AT' as well as other ID's) and return the full
SalesmanName (Alex Time) to my master query field SalesManName.

I can use Dlookup with a static value of 'AT' but then all of my
records in the record set for the SalesManName = Alex Time and that is
not what I want because there are more SalesManID's like 'gg' 'dd' and
about 8 more.

Dec 25 '05 #5
jj*****@hotmail .com wrote:
Ok,
here is an example
This works for me

I have a query that calculates how many bottles are sold say 12 btls
per case. So if I have 60btls sold I will show 60btls in my master
query.

I order to convert that to cases. I have another variable in my master
query that shows how many bottles there are per case, and I can perform
the division within my master query.

Quantity:Bottle s (is a field that has the number of bottles sold)
BottlesPerCase is another field within my master query.

So I do Quantity:=Bottl es/BottlesPercase

This works for me no problem

The problem I am having is taking a value called SalesManID from the
master query which has a value say 'AT'

Then I have another Field within my Master Query that is called
SalesManName(th is field is currently blank)

What I want to do is a lookup in a tbl called tblSalesMan which has
both the SalesManID ('AT' as well as other ID's) and return the full
SalesmanName (Alex Time) to my master query field SalesManName.

I can use Dlookup with a static value of 'AT' but then all of my
records in the record set for the SalesManName = Alex Time and that is
not what I want because there are more SalesManID's like 'gg' 'dd' and
about 8 more.


Based on my experience I would not use DLookup in this situation.
I would use a Left Join. You have stated that you are receiving an
ambiguous outer join error when you try to do this. I would correct my
SQL so that I did not receive an ambiguous outer join error.

Clearly, our styles are not compatible. We can hope that someone who
uses DLookup in the way you propose will come to the rescue. (As I never
use the Domain functions, I probably should not have answered in this
thread at all).

Good Luck!

--
Lyle Fairfield
Dec 25 '05 #6

<jj*****@hotmai l.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...
Ok,
here is an example
This works for me
jjturon,

Please understand that a text narrative can be very difficult to
decipher.

I have a query that calculates how many bottles are sold say 12 btls per case. So if I have 60btls sold I will show 60btls in my master query.
The first sentence is clear, but it ends with "say 12 btls per
case." May I ask what the number of bottles per case has to do with
"how many bottles are sold"?


I order to convert that to cases.
I apologize, but do you mean, "I have to convert that to cases."?

I have another variable in my master query
Please understand that Queries in MS Access do not have variables.

that shows how many bottles there are per case, and I can perform
the division within my master query.

Quantity:Bottle s (is a field that has the number of bottles sold)
BottlesPerCase is another field within my master query.

So I do Quantity:=Bottl es/BottlesPercase

This works for me no problem

The problem I am having is taking a value called SalesManID from the

I am assuming you meant, "[...] taking a column called SalesManID
from [...]"
master query which has a value say 'AT'

Then I have another Field within my Master Query that is called
SalesManName(th is field is currently blank)

What I want to do is a lookup in a tbl called tblSalesMan which has both the SalesManID ('AT' as well as other ID's) and return the full SalesmanName (Alex Time) to my master query field SalesManName.
Place the following subquery on the SELECT clause of the SELECT
statement of the Master Query (without seeing your SQL, the
following is a *guess*, and should be thought of as such).

SELECT MQ1.SalesManID
,(SELECT S1.SalesManName
FROM tblSalesMan AS S1
WHERE S1.SalesManID = MQ1.SaleManID)
,MQ1.ColThree
,MQ1.ColFour
FROM (UnknownTable AS U1
INNER JOIN
UnknownTable AS U2
ON U1.PrimaryKey = U2.PrimaryKey) AS MQ1

This assumes that tblSalesMan is not already JOINed in with the
other tables on the FROM clause (which can still be done).


I can use Dlookup with a static value of 'AT' but then all of my
records in the record set for the SalesManName = Alex Time and that is not what I want because there are more SalesManID's like 'gg' 'dd' and about 8 more.
I apologize, but I am not able to follow the references described in
this last paragraph. Without knowing your table structures and
sample data, it is impossible for my to know for certain what is
going on.
Can you please post your table structures, sample data, desired
results, and the SQL created so far?
The following contains information on how you can improve your
chances of getting your question answered:

-------------------------------

Formatting:

Please use a monospace font (Courier New, etc.) when writing out
your examples (all descriptions, charts, SQL, etc.).

-------------------------------

Process Description:

Please only include the shortest possible narrative of what is going
on with the query. (Include all that is necessary, and nothing
more.)

When parts of your query make calculations, show the exact code or
nearest readable plain-text math formula you can create.

When you are done with this section, re-read it several times before
posting to assure yourself that you are accurately describing the
situation in a way you believe others will understand.

-------------------------------

Table Structures/Description:

Post a description of your table structures.

Although it can be a source of information, please do not copy and
paste information directly from MS Access' Documenter. It is
virtually unreadable. Please distill down and legibly format only
the relevant table information.

If reading the information in MS Access' Documenter is too
intimidating (I know what its output says, myself, and I still
dislike going over its output listings), open your table in Design
View, view the column names and data types in it, and then type out
the column names and data types *that are necessary* (do not include
columns that are not absolutely necessary for the query). Use the
Index dialog box (you can get at it by clicking on the "lightning
bolt and stacked lines" icon on the toolbar) to locate information
on primary and foreign keys and other indexes and type out that
information, as well.

Note: For table descriptions (or DDL) lining up the column names,
data type names, and key/index information in neat columns is quite
helpful.

Note: If you know how to write DDL SQL (CREATE TABLE), please post
that (including constraints) instead of text descriptions. (Please
post only the portion of the DDL that is relevant.)
Example (text description):

MyTableOne
MyTableOneID AUTOINCREMENT PK
ColTwo INTEGER NOT NULL
ColThree TEXT(10)

MyTableTwo
MyTableTwoID AUTOINCREMENT PK\
MyTableOneID INTEGER PK/-- Composite Primary Key
FK -- MyTableOne MyTableOneID
ColThree INTEGER
ColFour DATETIME
ColFive CURRENCY
ColSix BIT
ColSeven TEXT(1)
ColEight TEXT(1)

etc., etc., etc.
Example (DDL SQL/CREATE TABLE):

CREATE TABLE MyTableOne
(MyTableOneID AUTOINCREMENT
,ColTwo INTEGER NOT NULL
,ColThree TEXT(10)
,CONSTRAINT pk_MyTableOne
PRIMARY KEY (MyTableOneID)
)

CREATE TABLE MyTableTwo
(MyTableTwoID AUTOINCREMENT
,MyTableOneID INTEGER
,ColThree INTEGER
,ColFour DATETIME
,ColFive CURRENCY
,ColSix BIT
,ColSeven TEXT(1)
,ColEight TEXT(1)
,CONSTRAINT pk_MyTableTwo
PRIMARY KEY (MyTableTwoID)
,CONSTRAINT fk_MyTableTwo_M yTableOne_MyTab leOneID
FOREIGN KEY (MyTableOneID)
REFERENCES MyTableOne (MyTableOneID)
)

The Primary Key and Foreign Key notes (or constraints in the DDL
SQL) are *critical*.

-------------------------------

Sample Data (using comma delimited lists):

Note: If your sample data is "wide" across the screen, and you can't
trim out any columns because they are needed, make *two* (or more)
charts, and then clearly note that the second chart is the
continuation of the first chart for the same table. It is far
easier to convert a comma delimited chart into a table in MS Word or
import it directly into MS Excel (where the data can be copied and
pasted into a new table in MS Access) or even MS Access than it is
to manually undo the line-break on *every* row of a line-wrapped
chart (in fact, manually undoing the line-breaks caused by newsgroup
posting is a huge pain in the neck).

Note: In a comma delimited list, it is not absolutely necessary
(although it is nice) to have the data in the columns lined straight
up and down, like I have in my examples below. When the data is
finally imported into MS Access, a quick glance at the table in
datasheet view will show things lined up straight. It is not
necessary to expend extra effort on your chart here. (The right
data does have to be in the right position of each row of the chart,
of course.)

Note: Use the real table and column names whenever possible. Use
invented table names and column names (like I use below in my
example) only when you absolutely have to.

Note: When naming the columns on this chart, use the same column
names as is the table structures above. Using shortened names (or
completely different names, for whatever reason) may save space and
prevent line-wraps, but it can be severely confusing. If the chart
gets too "wide", make two (or more) charts if you have to, as noted
above in Table Structures.

Note: Please include just enough rows of sample data so that
sufficient tests of the various possibilities ("test cases") can be
made.

Note: Please do not attempt to post endless rows of data. 3-5 rows
are probably the minimum, and 10-20 row are probably the normal
maximum. (Post only what is necessary, and no more.)

Note: Please try and use real data when possible. However, real
people's personal information, or private information (banking,
proprietary, etc.), should never be posted. When you have
information that cannot be posted, you will have to invent test data
that can produce results similar to what the real data would
produce.
MyTableOne
MyTableOneID, ColTwo, ColThree
1, 2, a
3, 4, b
5, 6, c

MyTableTwo (Part One)
MyTableTwoID, MyTableOneID, ColThree, ColFour, ColFive
1, 5, 1, 01/01/06, 1.01
MyTableTwo (Part Two)
ColSix, ColSeven, ColEight
-1, g, h

-------------------------------

Desired Results

.. . . <whatever it is you want your query to produce; "the right
stuff", if you will forgive the pun>

(Same chart style as found in the Sample Data section.)

-------------------------------

Query:

Your SQL query code attempts to date. (If "SQL code" throws you for
a loop, open your Query in Design View, and then use the menus, View SQL View, to switch to a window that will show the SQL code. Copy

and paste that into your new post to the newsgroup.)

Note: There is a huge temptation to merely copy and past the SQL
code. Usually, this is completely unreadable, and whoever reads it
must re-align the code in order to make heads or tails of it (yes,
there are a few out there who can read endless unbroken streams of
code packed together, but I am not one of them). If you know how,
spend some time straightening out and aligning the SQL before
posting it.

Note: In some situations, of course, you will have no query or SQL
code at all.

-------------------------------

Current Results:

.. . . <the incorrect results the current query(s) is producing>

(Same chart style as found in the Sample Data section.)

-------------------------------

Lots Of Work:

Does all this sound like a lot of work?

Remember, whatever work you haven't done will have to be done by
whoever tries to answer your question.

Any information that is not included may have to be asked for,
necessitating additional posts (sometimes many) before someone can
begin answering your question.

Time spent doing these things is time spent not answering your
question.

-------------------------------

I hope that the above can be of assistance in helping you receive an
answer to your various MS Access questions.
Sincerely,

Chris O.
Dec 25 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2535
by: Mark | last post by:
Hi - I have set-up security for my users - the security is held in a text field, separated by a comma. If the users a member of groups 1, 5 and 6 - the usergroups field is set to 1,5,6 - to check if they are allowed access to the books, I need to check each of these numbers (by using split,"," and building a SQL statement - this needs to check against the membergroups of each book title - which again is set as a text field - eg....
0
10205
by: Jan | last post by:
I store sql-commands in a database table. In the first step I get the sql command out of the database table with embedded sql. In the second step I try to execute the command, which i got from the database table, using dynamic sql. Executing 'EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;' the error code -2149 is returned That means "Specified partition does not exist". Does anybody know if it is a database problem or a problem of
2
2412
by: skidvd | last post by:
Hello: I have just recently converted to using the InnoDB table type so that I can enforce FOREIGN key constraints. I have been using MyISAM tables (accessed via JDBC) successfully for some time. However, I have just come across a problem with the new configuration that boggles my mind.... First some configuration data:
5
3903
by: Jason | last post by:
The following stored procedure is taking too long (in my opinion). The problem seems to be the SUM line. When commented out the query takes a second or two. When included the response time climbs to minute and a half. Is my code that inefficient or is SUM and ABS calls just that slow? Any suggestions to spead this up? Thanks, - Jason
5
5389
by: Pat L | last post by:
I have a function that is designed to return a variable that contains concatenated values from a partinular field in the returned rows: DECLARE @output varchar(8000) SELECT @output = CASE WHEN @output IS NULL THEN CAST(TSD.ScheduledTime AS varchar(4)) ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime AS
5
11503
by: malcolm | last post by:
Example, suppose you have these 2 tables (NOTE: My example is totally different, but I'm simply trying to setup the a simpler version, so excuse the bad design; not the point here) CarsSold { CarsSoldID int (primary key) MonthID int DealershipID int NumberCarsSold int
6
26536
by: Terentius Neo | last post by:
Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a select statement? I mean something like this: Select c.number, call procedure( c.number ) as list from table c With best regards
5
4068
by: robecflo | last post by:
Hi Forum, i have a problem, hope somebody can give me ideas. I'm developing with windows forms and vb.net, and oracle as a database. At this moment i have a table called amortizaciones, this table has a field called id_pasivo, which is foreign key to another table called pasivo, a consecutive field called no_cupon and a third field called date. So when i make a query (i.e select * from amortizaciones where id_pasivo = 522 order by...
21
29797
by: Leena P | last post by:
i want to basically take some information for the product and let the user enter the the material required to make this product 1.first page test.php which takes product code and displays prodcut anme have used ajax to avoid refreshing of page this works fine 2.now i have created one row with checkbox|select box|text|text|text|text| where in the select box values are fetched from table here also i have used ajax for getting the m_name...
0
8703
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8935
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8893
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7773
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4389
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3069
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2015
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.