unioning 2 querys

G

Guest

I have been working for several days now to try and query 2 different
databases into one table or output. I have a working query for each that
returns the data as expected. I seem to be having trouble getting the second
query to read 2 fields from the first query as criteria. I am thinking that
maybe a "union" or "union All" is the answer. could someone please take a
look at the 2 querys below and lend me thier expertese please.
Thanx in advance for any help.
///////query #1 (works fine)//////////
SELECT idshrc.trandate, idshrc.id, idfile.custso, pcmkid.pcmark,
pcmkid.pccolor, idshrc.tranqty, idshrc.transtatus, idshrc.tranworker
FROM (idshrc INNER JOIN idfile ON idshrc.id = idfile.id) INNER JOIN pcmkid
ON (idfile.custso = pcmkid.custso) AND (idfile.pcmark = pcmkid.pcmark) AND
(idfile.seqnum = pcmkid.seqnum) AND (idfile.jobid = pcmkid.jobid)
WHERE (((idshrc.trandate)=[Enter a Date ]))
ORDER BY idshrc.id, idfile.custso, pcmkid.pcmark, pcmkid.pccolor;
/////////////////////////////////////////////

///Query #2 works fine as long as I supply a job # that it should get from
the return of query #1////////////////
SELECT labor_base.BIDNUMBER, bid_base.MKDET, bid_base.UNIQUEKEY,
bid_base.DRAWING, labor_base.QUAN, labor_base.OPERATION, labor_base.MINUTES,
bid_base.BIDNUMBER, labtable_base.DEPT, labtable_base.AUTOQUAN,
labtable_base.CODELABEL, labtable_base.CODENUM, labtable_base.FUNCTION,
labtable_base.LABEL, labtable_base.OPERATION
FROM (bid_base LEFT JOIN labor_base ON bid_base.UNIQUEKEY =
labor_base.UNIQUEKEY) LEFT JOIN labtable_base ON labor_base.CODE =
labtable_base.CODE
WHERE (((labor_base.QUAN)>0));
//////////////////////////////////
Thanx again for your time,

Klutzz
 
K

Ken Snell \(MVP\)

A union query will not do what you seek... one query cannot "see" the other
query in a union query.

Tell us what you're wanting to do with these two queries. Do you need to see
the data from both? Or do you just want to see the results of the second
query? How are you using these queries?
 
C

Chris2

Klutzz said:
I have been working for several days now to try and query 2 different
databases into one table or output. I have a working query for each that
returns the data as expected. I seem to be having trouble getting the second
query to read 2 fields from the first query as criteria. I am thinking that
maybe a "union" or "union All" is the answer. could someone please take a
look at the 2 querys below and lend me thier expertese please.
Click to expand...

Klutzz
Click to expand...

Klutzz,

///////query #1 (works fine)//////////
SELECT idshrc.trandate
,idshrc.id
,idfile.custso
,pcmkid.pcmark
,pcmkid.pccolor
,idshrc.tranqty
,idshrc.transtatus
,idshrc.tranworker
FROM (idshrc
INNER JOIN
idfile
ON idshrc.id = idfile.id)
INNER JOIN pcmkid
ON (idfile.custso = pcmkid.custso)
AND (idfile.pcmark = pcmkid.pcmark)
AND (idfile.seqnum = pcmkid.seqnum)
AND (idfile.jobid = pcmkid.jobid)
WHERE (((idshrc.trandate)=[Enter a Date ]))
ORDER BY idshrc.id
,idfile.custso
,pcmkid.pcmark
,pcmkid.pccolor;

///Query #2 works fine as long as I supply a job # that it should
get from
the return of query #1////////////////
SELECT labor_base.BIDNUMBER
,bid_base.MKDET
,bid_base.UNIQUEKEY
,bid_base.DRAWING
,labor_base.QUAN
,labor_base.OPERATION
,labor_base.MINUTES
,bid_base.BIDNUMBER
,labtable_base.DEPT
,labtable_base.AUTOQUAN
,labtable_base.CODELABEL
,labtable_base.CODENUM
,labtable_base.FUNCTION
,labtable_base.LABEL
,labtable_base.OPERATION
FROM (bid_base
LEFT JOIN
labor_base
ON bid_base.UNIQUEKEY = labor_base.UNIQUEKEY)
LEFT JOIN
labtable_base
ON labor_base.CODE = labtable_base.CODE
WHERE (((labor_base.QUAN) > 0));


You mentioned: "I seem to be having trouble getting the second query
to read 2 fields from the first query as criteria."

I can see no place where Query #2 references Query #1.

Can you provide more details on what exactly this "read 2 fields
from the first query" difficulty is?


You also mentioned: "Query #2 works fine as long as I supply a job #
that it should get from the return of query #1"

Query #2 does not appear to take any parameters, so how is a job #
being supplied to it?


Sincerely,

Chris O.

PS Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.
 
G

Guest

Sorry please let me try and clarify a bit.
Query #1 is connected to a database that records barcode transactions.
During my flailing today I needed to remove the user inputed criteria for
"idshrc.trandate".
This returns several fields for each transaction, a job# and a piece # are
the main items.
Query #2 performs the same action on a seperate database that has all of the
inventory estimating and labor data. I did have it setup with a user inputed
criteria as well. This needs to return the labor and estimating data for the
records shown in the job#, piece# fields returned by query #1.
I was trying to use the
![ col. name] in the criteria fields of
query #2 but couldnt seem to get access to recognize them.

well time for some sleep, dont see how you guys do this full time.
Thanx again for the response,
klutzz

Chris2 said:
Klutzz said:
I have been working for several days now to try and query 2 different
databases into one table or output. I have a working query for each that
returns the data as expected. I seem to be having trouble getting the second
query to read 2 fields from the first query as criteria. I am thinking that
maybe a "union" or "union All" is the answer. could someone please take a
look at the 2 querys below and lend me thier expertese please.
Click to expand...

Klutzz
Click to expand...

Klutzz,

///////query #1 (works fine)//////////
SELECT idshrc.trandate
,idshrc.id
,idfile.custso
,pcmkid.pcmark
,pcmkid.pccolor
,idshrc.tranqty
,idshrc.transtatus
,idshrc.tranworker
FROM (idshrc
INNER JOIN
idfile
ON idshrc.id = idfile.id)
INNER JOIN pcmkid
ON (idfile.custso = pcmkid.custso)
AND (idfile.pcmark = pcmkid.pcmark)
AND (idfile.seqnum = pcmkid.seqnum)
AND (idfile.jobid = pcmkid.jobid)
WHERE (((idshrc.trandate)=[Enter a Date ]))
ORDER BY idshrc.id
,idfile.custso
,pcmkid.pcmark
,pcmkid.pccolor;

///Query #2 works fine as long as I supply a job # that it should
get from
the return of query #1////////////////
SELECT labor_base.BIDNUMBER
,bid_base.MKDET
,bid_base.UNIQUEKEY
,bid_base.DRAWING
,labor_base.QUAN
,labor_base.OPERATION
,labor_base.MINUTES
,bid_base.BIDNUMBER
,labtable_base.DEPT
,labtable_base.AUTOQUAN
,labtable_base.CODELABEL
,labtable_base.CODENUM
,labtable_base.FUNCTION
,labtable_base.LABEL
,labtable_base.OPERATION
FROM (bid_base
LEFT JOIN
labor_base
ON bid_base.UNIQUEKEY = labor_base.UNIQUEKEY)
LEFT JOIN
labtable_base
ON labor_base.CODE = labtable_base.CODE
WHERE (((labor_base.QUAN) > 0));


You mentioned: "I seem to be having trouble getting the second query
to read 2 fields from the first query as criteria."

I can see no place where Query #2 references Query #1.

Can you provide more details on what exactly this "read 2 fields
from the first query" difficulty is?


You also mentioned: "Query #2 works fine as long as I supply a job #
that it should get from the return of query #1"

Query #2 does not appear to take any parameters, so how is a job #
being supplied to it?


Sincerely,

Chris O.

PS Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.
Click to expand...
 
C

Chris2

Klutzz said:
Sorry please let me try and clarify a bit.
Query #1 is connected to a database that records barcode transactions.
During my flailing today I needed to remove the user inputed criteria for
"idshrc.trandate".
This returns several fields for each transaction, a job# and a piece # are
the main items.
Query #2 performs the same action on a seperate database that has all of the
inventory estimating and labor data. I did have it setup with a user inputed
criteria as well. This needs to return the labor and estimating data for the
records shown in the job#, piece# fields returned by query #1.
I was trying to use the
![ col. name] in the criteria fields of
query #2 but couldnt seem to get access to recognize them.
Click to expand...


Klutzz,

The "!" syntax exists mailinly for referring to controls on forms.

In a query, you must include a table on the FROM clause (in SQL
View) or in a subquery in order to be able to refer to it.

In the QBE grid, I believe you must add the table in question and
link it to one of the other tables (this will have all the normal
effects of any JOIN).

Then it's: <table-name>.<column-name>

If you right-click a table's title-bar, and select properties, you
can set an alias for the table name.

Then it's <table-alias>.<column-name>

This tends to make your code (in SQL VIEW) vastly easier to read.
Also, some things are impossible without table aliases.

well time for some sleep, dont see how you guys do this full time.
Thanx again for the response,
klutzz
Click to expand...

I don't really review everything. Some questions I can't answer
from the get go, others are already answered, and yet more often
there is simply not enough information to work with, and I just move
on.


Sincerely,

Chris O.
 
Top