Joining two tables

P

Pam

Hi,

I have the following SQL that pulls info specific for tech name entered into
combo box on form.

SELECT qJobList.JobAssignment, qJobList.MachineAssignment,
qJobList.ExpectedPartsRecdDate, qJobList.PumpType, qJobList.JobNumber,
qJobList.CustomerName, qJobList.ReceiptOfGoods, qJobList.PriceQuote,
qJobList.ReadyToRepair, qJobList.Completed, qJobList.HotJob,
qJobList.MachineStart, qJobList.MachineFinish FROM qJobList WHERE
(((qJobList.JobAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) Or
(((qJobList.MachineAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) ORDER BY qJobList.JobNumber,
qJobList.ReceiptOfGoods, qJobList.ReadyToRepair, qJobList.MachineStart;

Everything was working great. Now users want to enter another type of job
for same techs that are not in the auto number format as the job number in
the SQL above. I created a new table with fields needed, made a query,
combined it to the query above (see SQL below) and when view in datasheet
view on form, I get list of repair job numbers with the same pkg job number
on the row as each repair job for that tech.

Combo Box Tech = Don
Repair Job Pkg Job
6700 52774a
6701 52774a
6702 52774a

I know this is working as it should, but I'm thinking there is a way to show
like this.

Combo Box Tech = Don
Repair Job Pkg Job
6700
52774a
6701
6702

Here's the SQL with second table added:

SELECT qPkgWork.JobNumber, qPkgWork.Name, qPkgWork.Description,
qPkgWork.ReadyToAssemble, qPkgWork.Complete, qJobList.JobAssignment,
qJobList.MachineAssignment, qJobList.ExpectedPartsRecdDate,
qJobList.PumpType, qJobList.JobNumber, qJobList.CustomerName,
qJobList.ReceiptOfGoods, qJobList.PriceQuote, qJobList.ReadyToRepair,
qJobList.Completed, qJobList.HotJob, qJobList.MachineStart,
qJobList.MachineFinish
FROM qJobList LEFT JOIN qPkgWork ON qJobList.JobAssignment =
qPkgWork.JobAssignment
WHERE (((qJobList.JobAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null)) OR
(((qJobList.MachineAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null))
ORDER BY qJobList.JobNumber, qJobList.ReceiptOfGoods,
qJobList.ReadyToRepair, qJobList.MachineStart;

I hope what I want to accomplish is explained clearly and I'm sorry for the
length of msg. Any help with this is greatly appreciated!!
Thanks,
Pam
 
T

Tom Ellison

Dear Pam:

I reproduce your queries here, simplified and formatted for my reading
preferences:

SELECT JobAssignment, MachineAssignment, ExpectedPartsRecdDate,
PumpType, JobNumber, CustomerName, ReceiptOfGoods, PriceQuote,
ReadyToRepair, Completed, HotJob, MachineStart, MachineFinish
FROM qJobList
WHERE Forms!fSwitchboard!Combo8 IN (JobAssignment, MachineAssignment)
AND Completed Is Null
ORDER BY JobNumber, ReceiptOfGoods, ReadyToRepair, MachineStart;


SELECT W.JobNumber, W.Name, W.Description, W.ReadyToAssemble,
W.Complete, L.JobAssignment, L.MachineAssignment,
L.ExpectedPartsRecdDate,
L.PumpType, L.JobNumber, L.CustomerName, L.ReceiptOfGoods, L.PriceQuote,
L.ReadyToRepair, L.Completed, L.HotJob, L.MachineStart, L.MachineFinish
FROM qJobList L
LEFT JOIN qPkgWork W
ON L.JobAssignment = W.JobAssignment
WHERE Forms!fSwitchboard!Combo8 IN (L.JobAssignment, L.MachineAssignment)
AND L.Completed Is Null
ORDER BY L.JobNumber, L.ReceiptOfGoods, L.ReadyToRepair, L.MachineStart;


The above do not reference any columns like Repair Job or Pkg Job. I would
guess that Repair Job is a column in qJobList, perhaps it is the
JobAssignment column. Pkg Job might be some column in qPkgWork.

I think perhaps the best way for me to try to see what the situation is
would be for you to show me the source data from the two sources, qJobList
and qPkgWork, and identify there the values Repair Job and Pkg Job
originate.

Tom Ellison


Pam said:
Hi,

I have the following SQL that pulls info specific for tech name entered
into combo box on form.

SELECT qJobList.JobAssignment, qJobList.MachineAssignment,
qJobList.ExpectedPartsRecdDate, qJobList.PumpType, qJobList.JobNumber,
qJobList.CustomerName, qJobList.ReceiptOfGoods, qJobList.PriceQuote,
qJobList.ReadyToRepair, qJobList.Completed, qJobList.HotJob,
qJobList.MachineStart, qJobList.MachineFinish FROM qJobList WHERE
(((qJobList.JobAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) Or
(((qJobList.MachineAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) ORDER BY qJobList.JobNumber,
qJobList.ReceiptOfGoods, qJobList.ReadyToRepair, qJobList.MachineStart;

Everything was working great. Now users want to enter another type of job
for same techs that are not in the auto number format as the job number in
the SQL above. I created a new table with fields needed, made a query,
combined it to the query above (see SQL below) and when view in datasheet
view on form, I get list of repair job numbers with the same pkg job
number on the row as each repair job for that tech.

Combo Box Tech = Don
Repair Job Pkg Job
6700 52774a
6701 52774a
6702 52774a

I know this is working as it should, but I'm thinking there is a way to
show like this.

Combo Box Tech = Don
Repair Job Pkg Job
6700
52774a
6701
6702

Here's the SQL with second table added:

SELECT qPkgWork.JobNumber, qPkgWork.Name, qPkgWork.Description,
qPkgWork.ReadyToAssemble, qPkgWork.Complete, qJobList.JobAssignment,
qJobList.MachineAssignment, qJobList.ExpectedPartsRecdDate,
qJobList.PumpType, qJobList.JobNumber, qJobList.CustomerName,
qJobList.ReceiptOfGoods, qJobList.PriceQuote, qJobList.ReadyToRepair,
qJobList.Completed, qJobList.HotJob, qJobList.MachineStart,
qJobList.MachineFinish
FROM qJobList LEFT JOIN qPkgWork ON qJobList.JobAssignment =
qPkgWork.JobAssignment
WHERE (((qJobList.JobAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null)) OR
(((qJobList.MachineAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null))
ORDER BY qJobList.JobNumber, qJobList.ReceiptOfGoods,
qJobList.ReadyToRepair, qJobList.MachineStart;

I hope what I want to accomplish is explained clearly and I'm sorry for
the length of msg. Any help with this is greatly appreciated!!
Thanks,
Pam
Click to expand...
 
P

Pam

Hi Tom,

Thanks for looking at this for me. I realize I wasn't clear on my examples.
It should have read like this:

JobAssignment = Don
I apologize for the confusion. Hopefully, this will help to clear things up
and a solution can be found.
Thanks again for your help.
Pam

Tom Ellison said:
Dear Pam:

I reproduce your queries here, simplified and formatted for my reading
preferences:

SELECT JobAssignment, MachineAssignment, ExpectedPartsRecdDate,
PumpType, JobNumber, CustomerName, ReceiptOfGoods, PriceQuote,
ReadyToRepair, Completed, HotJob, MachineStart, MachineFinish
FROM qJobList
WHERE Forms!fSwitchboard!Combo8 IN (JobAssignment, MachineAssignment)
AND Completed Is Null
ORDER BY JobNumber, ReceiptOfGoods, ReadyToRepair, MachineStart;


SELECT W.JobNumber, W.Name, W.Description, W.ReadyToAssemble,
W.Complete, L.JobAssignment, L.MachineAssignment,
L.ExpectedPartsRecdDate,
L.PumpType, L.JobNumber, L.CustomerName, L.ReceiptOfGoods,
L.PriceQuote,
L.ReadyToRepair, L.Completed, L.HotJob, L.MachineStart, L.MachineFinish
FROM qJobList L
LEFT JOIN qPkgWork W
ON L.JobAssignment = W.JobAssignment
WHERE Forms!fSwitchboard!Combo8 IN (L.JobAssignment, L.MachineAssignment)
AND L.Completed Is Null
ORDER BY L.JobNumber, L.ReceiptOfGoods, L.ReadyToRepair, L.MachineStart;


The above do not reference any columns like Repair Job or Pkg Job. I
would guess that Repair Job is a column in qJobList, perhaps it is the
JobAssignment column. Pkg Job might be some column in qPkgWork.

I think perhaps the best way for me to try to see what the situation is
would be for you to show me the source data from the two sources, qJobList
and qPkgWork, and identify there the values Repair Job and Pkg Job
originate.

Tom Ellison


Pam said:
Hi,

I have the following SQL that pulls info specific for tech name entered
into combo box on form.

SELECT qJobList.JobAssignment, qJobList.MachineAssignment,
qJobList.ExpectedPartsRecdDate, qJobList.PumpType, qJobList.JobNumber,
qJobList.CustomerName, qJobList.ReceiptOfGoods, qJobList.PriceQuote,
qJobList.ReadyToRepair, qJobList.Completed, qJobList.HotJob,
qJobList.MachineStart, qJobList.MachineFinish FROM qJobList WHERE
(((qJobList.JobAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) Or
(((qJobList.MachineAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) ORDER BY qJobList.JobNumber,
qJobList.ReceiptOfGoods, qJobList.ReadyToRepair, qJobList.MachineStart;

Everything was working great. Now users want to enter another type of
job for same techs that are not in the auto number format as the job
number in the SQL above. I created a new table with fields needed, made
a query, combined it to the query above (see SQL below) and when view in
datasheet view on form, I get list of repair job numbers with the same
pkg job number on the row as each repair job for that tech.

Combo Box Tech = Don
Repair Job Pkg Job
6700 52774a
6701 52774a
6702 52774a

I know this is working as it should, but I'm thinking there is a way to
show like this.

Combo Box Tech = Don
Repair Job Pkg Job
6700
52774a
6701
6702

Here's the SQL with second table added:

SELECT qPkgWork.JobNumber, qPkgWork.Name, qPkgWork.Description,
qPkgWork.ReadyToAssemble, qPkgWork.Complete, qJobList.JobAssignment,
qJobList.MachineAssignment, qJobList.ExpectedPartsRecdDate,
qJobList.PumpType, qJobList.JobNumber, qJobList.CustomerName,
qJobList.ReceiptOfGoods, qJobList.PriceQuote, qJobList.ReadyToRepair,
qJobList.Completed, qJobList.HotJob, qJobList.MachineStart,
qJobList.MachineFinish
FROM qJobList LEFT JOIN qPkgWork ON qJobList.JobAssignment =
qPkgWork.JobAssignment
WHERE (((qJobList.JobAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null)) OR
(((qJobList.MachineAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null))
ORDER BY qJobList.JobNumber, qJobList.ReceiptOfGoods,
qJobList.ReadyToRepair, qJobList.MachineStart;

I hope what I want to accomplish is explained clearly and I'm sorry for
the length of msg. Any help with this is greatly appreciated!!
Thanks,
Pam
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Pam:

With that, could you now explain in word and example what it is you want
from the query?

Tom Ellison


Pam said:
Hi Tom,

Thanks for looking at this for me. I realize I wasn't clear on my
examples. It should have read like this:

JobAssignment = Don
I apologize for the confusion. Hopefully, this will help to clear things
up and a solution can be found.
Thanks again for your help.
Pam

Tom Ellison said:
Dear Pam:

I reproduce your queries here, simplified and formatted for my reading
preferences:

SELECT JobAssignment, MachineAssignment, ExpectedPartsRecdDate,
PumpType, JobNumber, CustomerName, ReceiptOfGoods, PriceQuote,
ReadyToRepair, Completed, HotJob, MachineStart, MachineFinish
FROM qJobList
WHERE Forms!fSwitchboard!Combo8 IN (JobAssignment, MachineAssignment)
AND Completed Is Null
ORDER BY JobNumber, ReceiptOfGoods, ReadyToRepair, MachineStart;


SELECT W.JobNumber, W.Name, W.Description, W.ReadyToAssemble,
W.Complete, L.JobAssignment, L.MachineAssignment,
L.ExpectedPartsRecdDate,
L.PumpType, L.JobNumber, L.CustomerName, L.ReceiptOfGoods,
L.PriceQuote,
L.ReadyToRepair, L.Completed, L.HotJob, L.MachineStart,
L.MachineFinish
FROM qJobList L
LEFT JOIN qPkgWork W
ON L.JobAssignment = W.JobAssignment
WHERE Forms!fSwitchboard!Combo8 IN (L.JobAssignment,
L.MachineAssignment)
AND L.Completed Is Null
ORDER BY L.JobNumber, L.ReceiptOfGoods, L.ReadyToRepair, L.MachineStart;


The above do not reference any columns like Repair Job or Pkg Job. I
would guess that Repair Job is a column in qJobList, perhaps it is the
JobAssignment column. Pkg Job might be some column in qPkgWork.

I think perhaps the best way for me to try to see what the situation is
would be for you to show me the source data from the two sources,
qJobList and qPkgWork, and identify there the values Repair Job and Pkg
Job originate.

Tom Ellison


Pam said:
Hi,

I have the following SQL that pulls info specific for tech name entered
into combo box on form.

SELECT qJobList.JobAssignment, qJobList.MachineAssignment,
qJobList.ExpectedPartsRecdDate, qJobList.PumpType, qJobList.JobNumber,
qJobList.CustomerName, qJobList.ReceiptOfGoods, qJobList.PriceQuote,
qJobList.ReadyToRepair, qJobList.Completed, qJobList.HotJob,
qJobList.MachineStart, qJobList.MachineFinish FROM qJobList WHERE
(((qJobList.JobAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) Or
(((qJobList.MachineAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) ORDER BY qJobList.JobNumber,
qJobList.ReceiptOfGoods, qJobList.ReadyToRepair, qJobList.MachineStart;

Everything was working great. Now users want to enter another type of
job for same techs that are not in the auto number format as the job
number in the SQL above. I created a new table with fields needed, made
a query, combined it to the query above (see SQL below) and when view in
datasheet view on form, I get list of repair job numbers with the same
pkg job number on the row as each repair job for that tech.

Combo Box Tech = Don
Repair Job Pkg Job
6700 52774a
6701 52774a
6702 52774a

I know this is working as it should, but I'm thinking there is a way to
show like this.

Combo Box Tech = Don
Repair Job Pkg Job
6700
52774a
6701
6702

Here's the SQL with second table added:

SELECT qPkgWork.JobNumber, qPkgWork.Name, qPkgWork.Description,
qPkgWork.ReadyToAssemble, qPkgWork.Complete, qJobList.JobAssignment,
qJobList.MachineAssignment, qJobList.ExpectedPartsRecdDate,
qJobList.PumpType, qJobList.JobNumber, qJobList.CustomerName,
qJobList.ReceiptOfGoods, qJobList.PriceQuote, qJobList.ReadyToRepair,
qJobList.Completed, qJobList.HotJob, qJobList.MachineStart,
qJobList.MachineFinish
FROM qJobList LEFT JOIN qPkgWork ON qJobList.JobAssignment =
qPkgWork.JobAssignment
WHERE (((qJobList.JobAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null)) OR
(((qJobList.MachineAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null))
ORDER BY qJobList.JobNumber, qJobList.ReceiptOfGoods,
qJobList.ReadyToRepair, qJobList.MachineStart;

I hope what I want to accomplish is explained clearly and I'm sorry for
the length of msg. Any help with this is greatly appreciated!!
Thanks,
Pam
Click to expand...
Click to expand...
Click to expand...
 
P

Pam

Hi Tom,

What I want is like my second example:

JobAssignment = Don
qJobList.JobNumber qPkgWork.JobNumber
6700
52774a
6701
6702

I want to enter a JobAssignment (tech name) in the combo box on my main form
and have the subform (SQL statement is for) list the job numbers from
qJobList AND from qPkgWork without the same qPkgWork.JobNumber showing up
for every entry on the qJobList.JobNumber. I want it to look like the
example above. If there is a job number in qJobList, then qPkgWok.JobNumber
should be blank and vice versa. I think what I need is a Union query.
Thanks for your help.
Pam

Tom Ellison said:
Dear Pam:

With that, could you now explain in word and example what it is you want
from the query?

Tom Ellison


Pam said:
Hi Tom,

Thanks for looking at this for me. I realize I wasn't clear on my
examples. It should have read like this:

JobAssignment = Don
qJobList.JobNumber qPkgWork.JobNumber
6700 52774a
6701 52774a
6702 52774a
Click to expand...

I apologize for the confusion. Hopefully, this will help to clear things
up and a solution can be found.
Thanks again for your help.
Pam

Tom Ellison said:
Dear Pam:

I reproduce your queries here, simplified and formatted for my reading
preferences:

SELECT JobAssignment, MachineAssignment, ExpectedPartsRecdDate,
PumpType, JobNumber, CustomerName, ReceiptOfGoods, PriceQuote,
ReadyToRepair, Completed, HotJob, MachineStart, MachineFinish
FROM qJobList
WHERE Forms!fSwitchboard!Combo8 IN (JobAssignment, MachineAssignment)
AND Completed Is Null
ORDER BY JobNumber, ReceiptOfGoods, ReadyToRepair, MachineStart;


SELECT W.JobNumber, W.Name, W.Description, W.ReadyToAssemble,
W.Complete, L.JobAssignment, L.MachineAssignment,
L.ExpectedPartsRecdDate,
L.PumpType, L.JobNumber, L.CustomerName, L.ReceiptOfGoods,
L.PriceQuote,
L.ReadyToRepair, L.Completed, L.HotJob, L.MachineStart,
L.MachineFinish
FROM qJobList L
LEFT JOIN qPkgWork W
ON L.JobAssignment = W.JobAssignment
WHERE Forms!fSwitchboard!Combo8 IN (L.JobAssignment,
L.MachineAssignment)
AND L.Completed Is Null
ORDER BY L.JobNumber, L.ReceiptOfGoods, L.ReadyToRepair,
L.MachineStart;


The above do not reference any columns like Repair Job or Pkg Job. I
would guess that Repair Job is a column in qJobList, perhaps it is the
JobAssignment column. Pkg Job might be some column in qPkgWork.

I think perhaps the best way for me to try to see what the situation is
would be for you to show me the source data from the two sources,
qJobList and qPkgWork, and identify there the values Repair Job and Pkg
Job originate.

Tom Ellison


Hi,

I have the following SQL that pulls info specific for tech name entered
into combo box on form.

SELECT qJobList.JobAssignment, qJobList.MachineAssignment,
qJobList.ExpectedPartsRecdDate, qJobList.PumpType, qJobList.JobNumber,
qJobList.CustomerName, qJobList.ReceiptOfGoods, qJobList.PriceQuote,
qJobList.ReadyToRepair, qJobList.Completed, qJobList.HotJob,
qJobList.MachineStart, qJobList.MachineFinish FROM qJobList WHERE
(((qJobList.JobAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) Or
(((qJobList.MachineAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) ORDER BY qJobList.JobNumber,
qJobList.ReceiptOfGoods, qJobList.ReadyToRepair, qJobList.MachineStart;

Everything was working great. Now users want to enter another type of
job for same techs that are not in the auto number format as the job
number in the SQL above. I created a new table with fields needed,
made a query, combined it to the query above (see SQL below) and when
view in datasheet view on form, I get list of repair job numbers with
the same pkg job number on the row as each repair job for that tech.

Combo Box Tech = Don
Repair Job Pkg Job
6700 52774a
6701 52774a
6702 52774a

I know this is working as it should, but I'm thinking there is a way to
show like this.

Combo Box Tech = Don
Repair Job Pkg Job
6700
52774a
6701
6702

Here's the SQL with second table added:

SELECT qPkgWork.JobNumber, qPkgWork.Name, qPkgWork.Description,
qPkgWork.ReadyToAssemble, qPkgWork.Complete, qJobList.JobAssignment,
qJobList.MachineAssignment, qJobList.ExpectedPartsRecdDate,
qJobList.PumpType, qJobList.JobNumber, qJobList.CustomerName,
qJobList.ReceiptOfGoods, qJobList.PriceQuote, qJobList.ReadyToRepair,
qJobList.Completed, qJobList.HotJob, qJobList.MachineStart,
qJobList.MachineFinish
FROM qJobList LEFT JOIN qPkgWork ON qJobList.JobAssignment =
qPkgWork.JobAssignment
WHERE (((qJobList.JobAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null)) OR
(((qJobList.MachineAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null))
ORDER BY qJobList.JobNumber, qJobList.ReceiptOfGoods,
qJobList.ReadyToRepair, qJobList.MachineStart;

I hope what I want to accomplish is explained clearly and I'm sorry for
the length of msg. Any help with this is greatly appreciated!!
Thanks,
Pam
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Pam:

I believe what is needed may be this:

Build a set of columns for sorting and display, looking like this:

6700 52774a 1 6700
6700 52774a 2 52774a
6701 52774a 1 6701
6702 52774a 1 6702

I agree about the union. One query would produce the rows with the value 1.
That's pretty simple. The other query would create the rows with 2 in them.
That query would GROUP BY the qPkgWork.JobNumber and use the MIN() of the
qJobList.JobNumber.

The 1 and the 2 would be constants inserted in the select list of the query.

SELECT L.JobNumber AS LJobNumber, W.JobNumber AS WJobNumber,
1 AS Seq, L.JobNumber AS LJobNumberA, "" AS WJobNumberA
FROM qJobList L
LEFT JOIN qPkgWork W
ON L.JobAssignment = W.JobAssignment
WHERE Forms!fSwitchboard!Combo8 IN (L.JobAssignment, L.MachineAssignment)
AND L.Completed Is Null
UNION ALL
SELECT MIN(L.JobNumber), W.JobNumber, 2, "", W.JobNumber
FROM qJobList L
LEFT JOIN qPkgWork W
ON L.JobAssignment = W.JobAssignment
WHERE Forms!fSwitchboard!Combo8 IN (L.JobAssignment, L.MachineAssignment)
AND L.Completed Is Null
GROUP BY W.JobNumber
ORDER BY L.JobNumber, Seq

This is a rough idea what it would look like. There are other columns you
probably want, but I recommend starting with something to produce just this
much.

Can you make something work from this?

Tom Ellison


Pam said:
Hi Tom,

What I want is like my second example:

JobAssignment = Don
qJobList.JobNumber qPkgWork.JobNumber
6700
52774a
6701
6702

I want to enter a JobAssignment (tech name) in the combo box on my main
form and have the subform (SQL statement is for) list the job numbers from
qJobList AND from qPkgWork without the same qPkgWork.JobNumber showing up
for every entry on the qJobList.JobNumber. I want it to look like the
example above. If there is a job number in qJobList, then
qPkgWok.JobNumber should be blank and vice versa. I think what I need is
a Union query. Thanks for your help.
Pam

Tom Ellison said:
Dear Pam:

With that, could you now explain in word and example what it is you want
from the query?

Tom Ellison


Pam said:
Hi Tom,

Thanks for looking at this for me. I realize I wasn't clear on my
examples. It should have read like this:

JobAssignment = Don
qJobList.JobNumber qPkgWork.JobNumber
6700 52774a
6701 52774a
6702 52774a

I apologize for the confusion. Hopefully, this will help to clear
things up and a solution can be found.
Thanks again for your help.
Pam

Dear Pam:

I reproduce your queries here, simplified and formatted for my reading
preferences:

SELECT JobAssignment, MachineAssignment, ExpectedPartsRecdDate,
PumpType, JobNumber, CustomerName, ReceiptOfGoods, PriceQuote,
ReadyToRepair, Completed, HotJob, MachineStart, MachineFinish
FROM qJobList
WHERE Forms!fSwitchboard!Combo8 IN (JobAssignment, MachineAssignment)
AND Completed Is Null
ORDER BY JobNumber, ReceiptOfGoods, ReadyToRepair, MachineStart;


SELECT W.JobNumber, W.Name, W.Description, W.ReadyToAssemble,
W.Complete, L.JobAssignment, L.MachineAssignment,
L.ExpectedPartsRecdDate,
L.PumpType, L.JobNumber, L.CustomerName, L.ReceiptOfGoods,
L.PriceQuote,
L.ReadyToRepair, L.Completed, L.HotJob, L.MachineStart,
L.MachineFinish
FROM qJobList L
LEFT JOIN qPkgWork W
ON L.JobAssignment = W.JobAssignment
WHERE Forms!fSwitchboard!Combo8 IN (L.JobAssignment,
L.MachineAssignment)
AND L.Completed Is Null
ORDER BY L.JobNumber, L.ReceiptOfGoods, L.ReadyToRepair,
L.MachineStart;


The above do not reference any columns like Repair Job or Pkg Job. I
would guess that Repair Job is a column in qJobList, perhaps it is the
JobAssignment column. Pkg Job might be some column in qPkgWork.

I think perhaps the best way for me to try to see what the situation is
would be for you to show me the source data from the two sources,
qJobList and qPkgWork, and identify there the values Repair Job and Pkg
Job originate.

Tom Ellison


Hi,

I have the following SQL that pulls info specific for tech name
entered into combo box on form.

SELECT qJobList.JobAssignment, qJobList.MachineAssignment,
qJobList.ExpectedPartsRecdDate, qJobList.PumpType, qJobList.JobNumber,
qJobList.CustomerName, qJobList.ReceiptOfGoods, qJobList.PriceQuote,
qJobList.ReadyToRepair, qJobList.Completed, qJobList.HotJob,
qJobList.MachineStart, qJobList.MachineFinish FROM qJobList WHERE
(((qJobList.JobAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) Or
(((qJobList.MachineAssignment)=Forms!fSwitchboard!Combo8) And
((qJobList.Completed) Is Null)) ORDER BY qJobList.JobNumber,
qJobList.ReceiptOfGoods, qJobList.ReadyToRepair,
qJobList.MachineStart;

Everything was working great. Now users want to enter another type of
job for same techs that are not in the auto number format as the job
number in the SQL above. I created a new table with fields needed,
made a query, combined it to the query above (see SQL below) and when
view in datasheet view on form, I get list of repair job numbers with
the same pkg job number on the row as each repair job for that tech.

Combo Box Tech = Don
Repair Job Pkg Job
6700 52774a
6701 52774a
6702 52774a

I know this is working as it should, but I'm thinking there is a way
to show like this.

Combo Box Tech = Don
Repair Job Pkg Job
6700
52774a
6701
6702

Here's the SQL with second table added:

SELECT qPkgWork.JobNumber, qPkgWork.Name, qPkgWork.Description,
qPkgWork.ReadyToAssemble, qPkgWork.Complete, qJobList.JobAssignment,
qJobList.MachineAssignment, qJobList.ExpectedPartsRecdDate,
qJobList.PumpType, qJobList.JobNumber, qJobList.CustomerName,
qJobList.ReceiptOfGoods, qJobList.PriceQuote, qJobList.ReadyToRepair,
qJobList.Completed, qJobList.HotJob, qJobList.MachineStart,
qJobList.MachineFinish
FROM qJobList LEFT JOIN qPkgWork ON qJobList.JobAssignment =
qPkgWork.JobAssignment
WHERE (((qJobList.JobAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null)) OR
(((qJobList.MachineAssignment)=[Forms]![fSwitchboard]![Combo8]) AND
((qJobList.Completed) Is Null))
ORDER BY qJobList.JobNumber, qJobList.ReceiptOfGoods,
qJobList.ReadyToRepair, qJobList.MachineStart;

I hope what I want to accomplish is explained clearly and I'm sorry
for the length of msg. Any help with this is greatly appreciated!!
Thanks,
Pam
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

On Click Code Problem 14

Top