query returns empty recordset

[jozino01]

hi,
i have three queries:

qPROFIT_BY_CUT
CUTS_CUTID NUMBER CUTDATE TOT_COST TOT_REVENUE TOT_PROFIT
13 5-13 1/01/06 $1,352,532.50 $1,454,353.74 $101,821.24
12 5-14 1/01/06 $861,096.14 $1,016,153.93 $155,057.79
11 5-15 1/01/06 $961,637.20 $1,009,796.82 $48,159.62
10 5-16 2/01/06 $1,244,170.94 $1,378,142.81 $133,971.87
9 5-17 3/01/06 $984,409.30 $1,142,987.42 $158,578.12
8 5-18 3/01/06 $1,073,738.27 $1,231,585.96 $157,847.69
7 5-19 3/01/06 $118,107.24 $119,604.00 $1,496.76
6 5-20 3/01/06 $773,001.12 $880,698.28 $107,697.16
5 5-21 4/01/06 $174,886.37 $203,871.43 $28,985.06
4 5-22 4/01/06 $1,320,394.62 $1,514,050.84 $193,656.22
3 5-23 4/01/06 $721,462.83 $766,974.88 $45,512.05
2 5-24 5/01/06 $1,187,100.81 $1,327,530.79 $140,429.98
1 5-25 5/01/06 $966,084.92 $1,053,639.75 $87,554.83

qLOGS_BY_CUT_SUM
CUTID NUMBER LOGS_VALUE TOT_VOLUME
1 5-25 $601,043.66 5695.545
2 5-24 $805,964.25 5904.5
3 5-23 $478,747.86 3156.1
4 5-22 $877,788.80 7142.8
5 5-21 $68,789.24 1709.061
6 5-20 $453,813.91 4986.028
7 5-19 $59,572.50 916.5
8 5-18 $705,916.75 5917.357
9 5-17 $562,971.93 6456.29
10 5-16 $849,464.30 5625.61
11 5-15 $579,881.39 6257.02
12 5-14 $558,090.15 4811.122
13 5-13 $906,919.00 5965.7

qHUSBY_LOGS_SUM
CUTID NUMBER HFPVOLUME HFPTOTAL
1 5-25 751.004 $76,852.75
2 5-24 5904.5 $805,964.25
3 5-23 3156.1 $478,747.86
4 5-22 7142.8 $877,788.80
5 5-21 1463.658 $59,463.93
6 5-20 625.298 $58,770.53
8 5-18 5917.357 $705,916.75
9 5-17 1582.466 $146,666.36
10 5-16 1251.01 $190,983.55
11 5-15 1060.464 $98,943.55
12 5-14 4811.122 $558,090.15
13 5-13 592.3 $76,999.00

i want to create new query, which would return:
- TOT_REVENUE and CUTS_CUTID from qPROFIT_BY_CUT
- LOGS_VOLUME from qLOGS_BY_CUT_SUM
- HFPVOLUME from qHUSBY_LOGS_SUM
where CUTID/CUTS_CUTID is the same for all of them

note: qHUSBY_LOGS_SUM is missing CUTID #7

i created the following query:

SELECT qLOGS_BY_CUT_SUM.TOT_VOLUME, qHUSBY_LOGS_SUM.HFPVOLUME, qPROFIT_BY_CUT.TOT_PROFIT, qPROFIT_BY_CUT.CUTS_CUTID, qHUSBY_LOGS_SUM.CUTID, qLOGS_BY_CUT_SUM.CUTID
FROM qPROFIT_BY_CUT, qHUSBY_LOGS_SUM, qLOGS_BY_CUT_SUM
WHERE (((qPROFIT_BY_CUT.CUTS_CUTID)=[qLOGS_BY_CUT_SUM].[CUTID] And (qPROFIT_BY_CUT.CUTS_CUTID)=[qHUSBY_LOGS_SUM].[CUTID]));

but it returns the empty recordset.

any idea how to fix it, please?

 

[Melagan]

You need to relate the data from query to query using joins. I would suggest starting by going to help and search for "INNER JOIN OPERATION". Also have a look at Right and Left joins.

~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

And read Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jozino01]

well, I got the same result - empty recordset - when using INNER JOIN:

SELECT qHUSBY_LOGS_SUM.CUTID, qHUSBY_LOGS_SUM.NUMBER, qHUSBY_LOGS_SUM.HFPVOLUME, qLOGS_BY_CUT_SUM.TOT_VOLUME, qPROFIT_BY_CUT.TOT_PROFIT
FROM qPROFIT_BY_CUT INNER JOIN (qHUSBY_LOGS_SUM INNER JOIN qLOGS_BY_CUT_SUM ON qHUSBY_LOGS_SUM.CUTID = qLOGS_BY_CUT_SUM.CUTID) ON qPROFIT_BY_CUT.CUTS_CUTID = qLOGS_BY_CUT_SUM.CUTID;

 

[lespaul]

SELECT H.CUTID, H.NUMBER, H.HFPVOLUME, L.TOT_VOLUME, P.TOT_PROFIT
FROM qPROFIT_BY_CUT P
INNER JOIN qHUSBY_LOGS_SUM H ON P.CUTS_CUT = H.CUTID
INNER JOIN qLOGS_BY_CUT_SUM L ON P.CUTS_CUT = L.CUT


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Are qPROFIT_BY_CUT.CUTS_CUTID, qLOGS_BY_CUT_SUM.CUTID and qHUSBY_LOGS_SUM.CUTID ALL numeric ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

thanks, it's getting there, only I got "Syntax error (missing operator) in query expression" and I just don't see what's missing...?

 

[jozino01]

to PHV: yes, the are all muneric (long integer)

 

[PHV]

Syntax error (missing operator) in query expression
With lespaul's query ?
SELECT H.CUTID, H.NUMBER, H.HFPVOLUME, L.TOT_VOLUME, P.TOT_PROFIT
FROM [!]([/!]qPROFIT_BY_CUT P
INNER JOIN qHUSBY_LOGS_SUM H ON P.CUTS_CUT = H.CUTID[!])[/!]
INNER JOIN qLOGS_BY_CUT_SUM L ON P.CUTS_CUT = L.CUT

What happen if you try to join only 2 queries at the time ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

thanks, there was no syntax error now, but I got empty recordset again.

however, when i changed INNER join to LEFT join:

SELECT H.CUTID, H.NUMBER, H.HFPVOLUME, L.TOT_VOLUME, P.TOT_PROFIT
FROM (qPROFIT_BY_CUT P
left JOIN qHUSBY_LOGS_SUM H ON P.CUTS_CUTID = H.CUTID)
left JOIN qLOGS_BY_CUT_SUM L ON P.CUTS_CUTID = L.CUTID

i got this resuls:

CUTID NUMBER HFPVOLUME TOT_VOLUME TOT_PROFIT
13 5-13 592.3 5965.7 $101,821.24
12 5-14 4811.122 4811.122 $155,057.79
11 5-15 1060.464 6257.02 $48,159.62
10 5-16 1251.01 5625.61 $133,971.87
9 5-17 1582.466 6456.29 $158,578.12
8 5-18 5917.357 5917.357 $157,847.69
916.5 $1,496.76
6 5-20 625.298 4986.028 $107,697.16
5 5-21 1463.658 1709.061 $28,985.06
4 5-22 7142.8 7142.8 $193,656.22
3 5-23 3156.1 3156.1 $45,512.05
2 5-24 5904.5 5904.5 $140,429.98
1 5-25 751.004 5695.545 $87,554.83