Sort multiple query results

O

Opal

I have a query based on 3 separate tables. The query calculates
inventory results for 13 Die sets. One table holds the total pallets
counted. The second table provides the number of panels per pallet
and the third table provides the shift usage of each part. The query
provides 13 calculated results, i.e. (DieSet47 * Pallet47)/
ShiftUsage47 = and (DieSet43*Pallet43)/ShiftUsage47 and so on....
These results tell us which Die Set needs to be run first -- i.e the
results tell us which part we will run out of first if we don't set it
up to run. I need to be able to sort these 13 results from lowest to
highest number.

Is there a way to accomplish this in a query that I can then run a
report off of? Is there a better way to run this query?
 
M

Michel Walsh

You can order by on computed expressions (don't use the alias, but the
expression itself) such as:

ORDER BY (DieSet47 * Pallet47)/ ShiftUsage47,
(DieSet43*Pallet43)/ShiftUsage47


Note that order by is about ordering the records between themselves, not the
fields.


Hoping it may help,
Vanderghast, Access MVP
 
J

Jamie Collins

You can order by on computed expressions (don't use the alias, but the
expression itself) such as:

ORDER BY (DieSet47 * Pallet47)/ ShiftUsage47,
(DieSet43*Pallet43)/ShiftUsage47
Click to expand...

Alternatively, use the ordinal position of the column in the SELECT
clause e.g.

ORDER BY 1;

to order on the first column etc.

Jamie.

--
 
O

Opal

Alternatively, use the ordinal position of the column in the SELECT
clause e.g.

ORDER BY 1;

to order on the first column etc.

Jamie.

--
Click to expand...
So, should I take the results from my inventory query and store them
in a table and then
run a query off of that table with the "ORDER BY" clause?
 
M

Michel Walsh

If you want to order the rows, you can add the ORDER BY to your original
'inventory query'.

If you want to order the values from 2 fields, the highest values under f1,
the second under f2, you can use a simple iif:

SELECT ... , iif( value1 > value2, value1, value2 ) AS f1,
iif( value1>value2, value2, value1) AS f2
FROM ...


but if you have more than 2 values to place horizontally, it could be easier
to normalize the data into a temp table, rank the values, per 'group', then,
use a crosstab for the final presentation.




Hoping it may help,
Vanderghast, Access MVP
 
O

Opal

So, should I take the results from my inventory query and store them
in a table and then
run a query off of that table with the "ORDER BY" clause?- Hide quoted text -

- Show quoted text -
Click to expand...

If I sort all the records as "Ascending" and look at the SQL view of
this query, I get the following:

SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, ([DS47]*[PLT47])/[ADC47] AS TInv47,
([DS43]*[PLT43])/[ADC43] AS TInv43, ([DS5]*[PLT5])/[ADC5] AS TInv5,
([DS4]*[PLT4])/[ADC4] AS TInv4, ([DS6]*[PLT6])/[ADC6] AS TInv6,
([DS37]*[PLT37])/[ADC37] AS TInv37, ([DS38]*[PLT38])/[ADC38] AS
TInv38, ([DS39]*[PLT39])/[ADC39] AS TInv39, ([DS40]*[PLT40])/[ADC40]
AS TInv40, ([DS57]*[PLT57])/[ADC57] AS TInv57, ([DS59]*[PLT59])/
[ADC59] AS TInv59, ([DS64]*[PLT64])/[ADC64] AS TInv64,
([DS41]*[PLT41])/[ADC41] AS TInv41
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
ORDER BY ([DS47]*[PLT47])/[ADC47], ([DS43]*[PLT43])/[ADC43],
([DS5]*[PLT5])/[ADC5], ([DS4]*[PLT4])/[ADC4], ([DS6]*[PLT6])/[ADC6],
([DS37]*[PLT37])/[ADC37], ([DS38]*[PLT38])/[ADC38], ([DS39]*[PLT39])/
[ADC39], ([DS40]*[PLT40])/[ADC40], ([DS57]*[PLT57])/[ADC57],
([DS59]*[PLT59])/[ADC59], ([DS64]*[PLT64])/[ADC64], ([DS41]*[PLT41])/
[ADC41];


Which is what I think Michael suggested, but unfortunately not what I
wanted. I need to be able to sort the fields (i.e. the indivdual
query results one row of data) in order. So for example, the
calculated result of (DieSet47 * Pallet47)/ ShiftUsage47 =
TotalInventory47 and (DieSet43*Pallet43)/ShiftUsage43 =
TotalInventory43 and so on..... I need to sort:
TInv47, TInv43, TInv5, TInv6......and so on from the lowest value to
the highest. Similar to the "=SMALL" function in
Excel where the results of the calculated field i.e. 0.13; 1.1; 2.7;
0.01; 2.3 are sorted as 0.01; 0.13; 1.1; 2.3; 2.7
Does this explain it better?
 
O

Opal

If you want to order the rows, you can add the ORDER BY to your original
'inventory query'.

If you want to order the values from 2 fields, the highest values under f1,
the second under f2, you can use a simple iif:

SELECT ... , iif( value1 > value2, value1, value2 ) AS f1,
iif( value1>value2, value2, value1) AS f2
FROM ...

but if you have more than 2 values to place horizontally, it could be easier
to normalize the data into a temp table, rank the values, per 'group', then,
use a crosstab for the final presentation.

Hoping it may help,
Vanderghast, Access MVP






- Show quoted text -
Click to expand...

MIchael,

I'm sorry....I'm confused. I converted the original SELECT query to a
Make Table query
and created a table with the data from the inventory query - was this
what you meant
by "normalize"? In creating the cross tab query, I used each
inventory field as a row
heading.....but that just creates the same information I already
had.....I still need to
sort each inventory field from lowest result value to
highest.....perhaps I need to look
at this whole thing from another approach?
 
J

Jamie Collins

If I sort all the records as "Ascending" and look at the SQL view of
this query, I get the following:

SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, ([DS47]*[PLT47])/[ADC47] AS TInv47,
([DS43]*[PLT43])/[ADC43] AS TInv43, ([DS5]*[PLT5])/[ADC5] AS TInv5,
([DS4]*[PLT4])/[ADC4] AS TInv4, ([DS6]*[PLT6])/[ADC6] AS TInv6,
([DS37]*[PLT37])/[ADC37] AS TInv37, ([DS38]*[PLT38])/[ADC38] AS
TInv38, ([DS39]*[PLT39])/[ADC39] AS TInv39, ([DS40]*[PLT40])/[ADC40]
AS TInv40, ([DS57]*[PLT57])/[ADC57] AS TInv57, ([DS59]*[PLT59])/
[ADC59] AS TInv59, ([DS64]*[PLT64])/[ADC64] AS TInv64,
([DS41]*[PLT41])/[ADC41] AS TInv41
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
ORDER BY ([DS47]*[PLT47])/[ADC47], ([DS43]*[PLT43])/[ADC43],
([DS5]*[PLT5])/[ADC5], ([DS4]*[PLT4])/[ADC4], ([DS6]*[PLT6])/[ADC6],
([DS37]*[PLT37])/[ADC37], ([DS38]*[PLT38])/[ADC38], ([DS39]*[PLT39])/
[ADC39], ([DS40]*[PLT40])/[ADC40], ([DS57]*[PLT57])/[ADC57],
([DS59]*[PLT59])/[ADC59], ([DS64]*[PLT64])/[ADC64], ([DS41]*[PLT41])/
[ADC41];

Which is what I think Michael suggested, but unfortunately not what I
wanted. I need to be able to sort the fields (i.e. the indivdual
query results one row of data) in order. So for example, the
calculated result of (DieSet47 * Pallet47)/ ShiftUsage47 =
TotalInventory47 and (DieSet43*Pallet43)/ShiftUsage43 =
TotalInventory43 and so on..... I need to sort:
TInv47, TInv43, TInv5, TInv6......and so on from the lowest value to
the highest. Similar to the "=SMALL" function in
Excel where the results of the calculated field i.e. 0.13; 1.1; 2.7;
0.01; 2.3 are sorted as 0.01; 0.13; 1.1; 2.3; 2.7
Does this explain it better?
Click to expand...

I'm thinking something like:

SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift,
'TInv4' AS calc_name,
([DS4]*[PLT4])/[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift,
'TInv5' AS calc_name,
([DS5]*[PLT5])/[ADC5] AS cal_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift,
'TInv6' AS calc_name,
([DS6]*[PLT6])/[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
....
etc
....
ORDER BY 1, 2, 3, 5;

Does this get you any further?

Jamie.

--
 
O

Opal

If I sort all the records as "Ascending" and look at the SQL view of
this query, I get the following:
Click to expand...
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, ([DS47]*[PLT47])/[ADC47] AS TInv47,
([DS43]*[PLT43])/[ADC43] AS TInv43, ([DS5]*[PLT5])/[ADC5] AS TInv5,
([DS4]*[PLT4])/[ADC4] AS TInv4, ([DS6]*[PLT6])/[ADC6] AS TInv6,
([DS37]*[PLT37])/[ADC37] AS TInv37, ([DS38]*[PLT38])/[ADC38] AS
TInv38, ([DS39]*[PLT39])/[ADC39] AS TInv39, ([DS40]*[PLT40])/[ADC40]
AS TInv40, ([DS57]*[PLT57])/[ADC57] AS TInv57, ([DS59]*[PLT59])/
[ADC59] AS TInv59, ([DS64]*[PLT64])/[ADC64] AS TInv64,
([DS41]*[PLT41])/[ADC41] AS TInv41
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
ORDER BY ([DS47]*[PLT47])/[ADC47], ([DS43]*[PLT43])/[ADC43],
([DS5]*[PLT5])/[ADC5], ([DS4]*[PLT4])/[ADC4], ([DS6]*[PLT6])/[ADC6],
([DS37]*[PLT37])/[ADC37], ([DS38]*[PLT38])/[ADC38], ([DS39]*[PLT39])/
[ADC39], ([DS40]*[PLT40])/[ADC40], ([DS57]*[PLT57])/[ADC57],
([DS59]*[PLT59])/[ADC59], ([DS64]*[PLT64])/[ADC64], ([DS41]*[PLT41])/
[ADC41];
Click to expand...
Which is what I think Michael suggested, but unfortunately not what I
wanted. I need to be able to sort the fields (i.e. the indivdual
query results one row of data) in order. So for example, the
calculated result of (DieSet47 * Pallet47)/ ShiftUsage47 =
TotalInventory47 and (DieSet43*Pallet43)/ShiftUsage43 =
TotalInventory43 and so on..... I need to sort:
TInv47, TInv43, TInv5, TInv6......and so on from the lowest value to
the highest. Similar to the "=SMALL" function in
Excel where the results of the calculated field i.e. 0.13; 1.1; 2.7;
0.01; 2.3 are sorted as 0.01; 0.13; 1.1; 2.3; 2.7
Does this explain it better?
Click to expand...

I'm thinking something like:

SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift,
'TInv4' AS calc_name,
([DS4]*[PLT4])/[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift,
'TInv5' AS calc_name,
([DS5]*[PLT5])/[ADC5] AS cal_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift,
'TInv6' AS calc_name,
([DS6]*[PLT6])/[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
...
etc
...
ORDER BY 1, 2, 3, 5;

Does this get you any further?

Jamie.

--- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Jamie,

So, if I understand you correctly, my SQL statement should read as
follows:

SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13

Maybe I need another pair of eyes to check this because I get an error
statement that reads:

"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
 
J

Jamie Collins

So, if I understand you correctly, my SQL statement should read as
follows:

SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13

Maybe I need another pair of eyes to check this because I get an error
statement that reads:

"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
Click to expand...

Lose the UNION ALL immediately before the ORDER BY.

Jamie.

--
 
J

Jamie Collins

So, if I understand you correctly, my SQL statement should read as
follows:
Click to expand...
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
Click to expand...
Maybe I need another pair of eyes to check this because I get an error
statement that reads:
Click to expand...
"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
Click to expand...

Lose the UNION ALL immediately before the ORDER BY.
Click to expand...

....and make the ORDER BY read:

ORDER BY 1, 2, 3, 4, 5

because you've now only got five columns in the resultset.

Jamie.

--
 
O

Opal

So, if I understand you correctly, my SQL statement should read as
follows:
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
Maybe I need another pair of eyes to check this because I get an error
statement that reads:
"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
Click to expand...
Click to expand...
Lose the UNION ALL immediately before the ORDER BY.
Click to expand...

...and make the ORDER BY read:

ORDER BY 1, 2, 3, 4, 5

because you've now only got five columns in the resultset.

Jamie.

--- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Jamie,

I removed the last UNION ALL and shortened the ORDER BY (why 5 in the
resultset?)
and I still get the same error message.....?
 
O

Opal

So, if I understand you correctly, my SQL statement should read as
follows:
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
Maybe I need another pair of eyes to check this because I get an error
statement that reads:
"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
Click to expand...
Click to expand...
Lose the UNION ALL immediately before the ORDER BY.
Click to expand...

...and make the ORDER BY read:

ORDER BY 1, 2, 3, 4, 5

because you've now only got five columns in the resultset.

Jamie.

--- Hide quoted text -

- Show quoted text -
Click to expand...

Never mind, Jamie....went blind but found some extra spaces and as
soon as I removed them it ran fine.

This is really helpful. Thank you so much....now I see why the 5
columns. D'uh :)
 
O

Opal

So, if I understand you correctly, my SQL statement should read as
follows:
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
Maybe I need another pair of eyes to check this because I get an error
statement that reads:
"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
Lose the UNION ALL immediately before the ORDER BY.
Click to expand...
Click to expand...
...and make the ORDER BY read:
Click to expand...
ORDER BY 1, 2, 3, 4, 5
Click to expand...
because you've now only got five columns in the resultset.

--- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Never mind, Jamie....went blind but found some extra spaces and as
soon as I removed them it ran fine.

This is really helpful. Thank you so much....now I see why the 5
columns. D'uh :)- Hide quoted text -

- Show quoted text -
Click to expand...

One more thing....my "calc_result" calculates to 7 decimal places. I
have gone back to the source tables and set each to 2 decimal places
and I still get 7 decimal places in my calculated result. I have
created a report based on the query and formatted the text boxes to 2
decimal places and I still get 7 decimal places. How can I get this
"calc_result" formatted to only 2 decimal places?
 
O

Opal

So, if I understand you correctly, my SQL statement should read as
follows:
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
Maybe I need another pair of eyes to check this because I get an error
statement that reads:
"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
Lose the UNION ALL immediately before the ORDER BY.
...and make the ORDER BY read:
ORDER BY 1, 2, 3, 4, 5
because you've now only got five columns in the resultset.
Jamie.
--- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Never mind, Jamie....went blind but found some extra spaces and as
soon as I removed them it ran fine.
Click to expand...
This is really helpful. Thank you so much....now I see why the 5
columns. D'uh :)- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

One more thing....my "calc_result" calculates to 7 decimal places. I
have gone back to the source tables and set each to 2 decimal places
and I still get 7 decimal places in my calculated result. I have
created a report based on the query and formatted the text boxes to 2
decimal places and I still get 7 decimal places. How can I get this
"calc_result" formatted to only 2 decimal places?- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry, again.... must be brain dead this evening....got it. Changed
the text box
in the report from General to Fixed. Double d'uh!!
 
O

Opal

So, if I understand you correctly, my SQL statement should read as
follows:
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv47'AS calc_name,([DS47]*[PLT47])/
[ADC47] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv43' AS calc_name, ([DS43]*[PLT43])/
[ADC43] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv5' AS calc_name,([DS5]*[PLT5])/
[ADC5] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv4' AS calc_name, ([DS4]*[PLT4])/
[ADC4] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv6' AS calc_name, ([DS6]*[PLT6])/
[ADC6] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv37' AS calc_name, ([DS37]*[PLT37])/
[ADC37] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv38' AS calc_name, ([DS38]*[PLT38])/
[ADC38] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv39' AS calc_name, ([DS39]*[PLT39])/
[ADC39] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv40' AS calc_name, ([DS40]*[PLT40])/
[ADC40] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv57' AS calc_name,([DS57]*[PLT57])/
[ADC57] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv59' AS calc_name,([DS59]*[PLT59])/
[ADC59] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv64' AS calc_name, ([DS64]*[PLT64])/
[ADC64] AS calc_ result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
SELECT [1AInventoryTable].txtDate, [1AInventoryTable].txtTime,
[1AInventoryTable].txtShift, 'TInv41' AS calc_name, ([DS41]*[PLT41])/
[ADC41] AS calc_result
FROM 1AInventoryTable, 1APalletTable, 1AShiftUsage
UNION ALL
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
Maybe I need another pair of eyes to check this because I get an error
statement that reads:
"The SELECT statement contains a reserved word or an argument name
that is misspelled or missing, or the
punctuation is incorrect."
Lose the UNION ALL immediately before the ORDER BY.
Click to expand...
Click to expand...
...and make the ORDER BY read:
Click to expand...
ORDER BY 1, 2, 3, 4, 5
Click to expand...
because you've now only got five columns in the resultset.

--- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Never mind, Jamie....went blind but found some extra spaces and as
soon as I removed them it ran fine.

This is really helpful. Thank you so much....now I see why the 5
columns. D'uh :)- Hide quoted text -

- Show quoted text -
Click to expand...

Jamie,

Can I still add parameters to this query? For Example I want to be
able to select the shift and I want the date of the query to be the
current date?
 
J

Jamie Collins

Can I still add parameters to this query? For Example I want to be
able to select the shift and I want the date of the query to be the
current date?
Click to expand...

I can't see why not e.g. this example using Northwind (ANSI-92 Query
Mode syntax):

CREATE PROCEDURE GetOrderDates
(
arg_latest_date DATETIME = NULL
)
AS
SELECT DT1.OrderID, DT1.date_type, DT1.date_value
FROM
(
SELECT OrderID, 'OrderDate' AS date_type, OrderDate AS date_value
FROM Orders
UNION ALL
SELECT OrderID, 'RequiredDate' AS date_type, RequiredDate AS
date_value
FROM Orders
UNION ALL
SELECT OrderID, 'ShippedDate' AS date_type, ShippedDate AS
date_value
FROM Orders
) AS DT1
WHERE DT1.date_value <= IIF(arg_latest_date IS NULL, DT1.date_value,
arg_latest_date)
ORDER BY 3, 1, 2;

Jamie.

--
 

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

Query Returning Multiple Results 1
How can I show missing matches within my query results? 1
Sorting a query with a twist 3
Query returns incorrect results 2
query results in Access 3
Query returning duplicate/unexpected results 3
Query shows no results when using 2 tables with multiple relations 3
Compare results of two expressions within query 1

Top