Looking up multiple values to perform a calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am pretty new, so I am sorry if my question is confusing. Please let me
know if you need any more clarification or information.

Here is what I am trying to do, but I don't know the codes to use:

If value in X field from FORM table is greater than 0, then use ABC table to
lookup value in Y field, then use value in Y field to look up value in Z
field in DEF table.

After I get the Z value, I would multiply it times the X value.

Thanks,
 
Please post the field names of the tables and their datatype. Post sample
data for the tables to show what you want to do.
 
Thanks for the quick response.

Here is a list of the fields with their datatypes and I also inserted the
field & table names into my previous post for further understanding.

[5]= number
[BULK]=text
[FILL LINE]= text
[HOURS PER CAN]= number


If value in [5] field from [9-4-07 Schedule] table is greater than 0, then
use [BULK] field in the [9-4-07 Schedule] table to look up [FILL LINE] field
in [SKUS 5] table, then use value in [FILL LINE] field to look up value in
[HOURS PER CAN] field in [VESSELS AND HOURS PER CAN] table.

After I get the [HOURS PER CAN] value, I would multiply it times the [5]
value, which is the number of cans.

9-4-07 Schedule table:

BULK= ABC123= product
5= 300 cans

SKUS 5 table
BULK=ABC123
FILL LINE= G1

VESSELS AND HOURS PER CAN= fill lines and how long each can takes
FILL LINE=G1
HOURS PER CAN= .10

So the resulting calculation would be: 300 cans times .10 hours per can.

Thanks, Emily
 
Try this ---
SELECT [9-4-07 Schedule].[5], [VESSELS AND HOURS PER CAN].[HOURS PER CAN],
[HOURS PER CAN]*[5] AS Fill_Time
FROM ([9-4-07 Schedule] LEFT JOIN [SKUS 5] ON [9-4-07 Schedule].BULK = [SKUS
5].BULK) LEFT JOIN [VESSELS AND HOURS PER CAN] ON [SKUS 5].[FILL LINE] =
[VESSELS AND HOURS PER CAN].[FILL LINE];

--
KARL DEWEY
Build a little - Test a little


Emily said:
Thanks for the quick response.

Here is a list of the fields with their datatypes and I also inserted the
field & table names into my previous post for further understanding.

[5]= number
[BULK]=text
[FILL LINE]= text
[HOURS PER CAN]= number


If value in [5] field from [9-4-07 Schedule] table is greater than 0, then
use [BULK] field in the [9-4-07 Schedule] table to look up [FILL LINE] field
in [SKUS 5] table, then use value in [FILL LINE] field to look up value in
[HOURS PER CAN] field in [VESSELS AND HOURS PER CAN] table.

After I get the [HOURS PER CAN] value, I would multiply it times the [5]
value, which is the number of cans.

9-4-07 Schedule table:

BULK= ABC123= product
5= 300 cans

SKUS 5 table
BULK=ABC123
FILL LINE= G1

VESSELS AND HOURS PER CAN= fill lines and how long each can takes
FILL LINE=G1
HOURS PER CAN= .10

So the resulting calculation would be: 300 cans times .10 hours per can.

Thanks, Emily


KARL DEWEY said:
Please post the field names of the tables and their datatype. Post sample
data for the tables to show what you want to do.
Click to expand...
Click to expand...
 
I put this

Fill_Time: (SELECT [9-4-07 Schedule].[5's], [FILL LINES AND HOURS PER
CAN].[HOURS PER CAN], [HOURS PER CAN]*[5's] AS Fill_Time
FROM ([9-4-07 Schedule] LEFT JOIN [SKUS 5] ON [9-4-07 Schedule].BULK = [SKUS
5].BULK) LEFT JOIN [FILL LINES AND HOURS PER CAN] ON [SKUS 5].[FILL LINE] =
[FILL LINES AND HOURS PER CAN].[FILL LINES];)

into a new query to find the fill time and this error message popped up:
"You have written a subquery that can return more than one field without
using the EXISTS reserved work in the main query's FROM clause. Revise the
SELECT statemtn of the subquery to request only one field."

What can I do to fix this?


KARL DEWEY said:
Try this ---
SELECT [9-4-07 Schedule].[5], [VESSELS AND HOURS PER CAN].[HOURS PER CAN],
[HOURS PER CAN]*[5] AS Fill_Time
FROM ([9-4-07 Schedule] LEFT JOIN [SKUS 5] ON [9-4-07 Schedule].BULK = [SKUS
5].BULK) LEFT JOIN [VESSELS AND HOURS PER CAN] ON [SKUS 5].[FILL LINE] =
[VESSELS AND HOURS PER CAN].[FILL LINE];

--
KARL DEWEY
Build a little - Test a little


Emily said:
Thanks for the quick response.

Here is a list of the fields with their datatypes and I also inserted the
field & table names into my previous post for further understanding.

[5]= number
[BULK]=text
[FILL LINE]= text
[HOURS PER CAN]= number


If value in [5] field from [9-4-07 Schedule] table is greater than 0, then
use [BULK] field in the [9-4-07 Schedule] table to look up [FILL LINE] field
in [SKUS 5] table, then use value in [FILL LINE] field to look up value in
[HOURS PER CAN] field in [VESSELS AND HOURS PER CAN] table.

After I get the [HOURS PER CAN] value, I would multiply it times the [5]
value, which is the number of cans.

9-4-07 Schedule table:

BULK= ABC123= product
5= 300 cans

SKUS 5 table
BULK=ABC123
FILL LINE= G1

VESSELS AND HOURS PER CAN= fill lines and how long each can takes
FILL LINE=G1
HOURS PER CAN= .10

So the resulting calculation would be: 300 cans times .10 hours per can.

Thanks, Emily


KARL DEWEY said:
Please post the field names of the tables and their datatype. Post sample
data for the tables to show what you want to do.
--
KARL DEWEY
Build a little - Test a little


:

Hello,

I am pretty new, so I am sorry if my question is confusing. Please let me
know if you need any more clarification or information.

Here is what I am trying to do, but I don't know the codes to use:

If value in X field from FORM table is greater than 0, then use ABC table to
lookup value in Y field, then use value in Y field to look up value in Z
field in DEF table.

After I get the Z value, I would multiply it times the X value.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
What I posted was a complete query. It appears as if you pasted it in the
query design view grid as an output field.
Open the query in design view, click on menu VIEW - SQL View and paste over
what is there in the window. Edit to remove any hard returns the post adds
at the end of a display line.
--
KARL DEWEY
Build a little - Test a little


Emily said:
I put this

Fill_Time: (SELECT [9-4-07 Schedule].[5's], [FILL LINES AND HOURS PER
CAN].[HOURS PER CAN], [HOURS PER CAN]*[5's] AS Fill_Time
FROM ([9-4-07 Schedule] LEFT JOIN [SKUS 5] ON [9-4-07 Schedule].BULK = [SKUS
5].BULK) LEFT JOIN [FILL LINES AND HOURS PER CAN] ON [SKUS 5].[FILL LINE] =
[FILL LINES AND HOURS PER CAN].[FILL LINES];)

into a new query to find the fill time and this error message popped up:
"You have written a subquery that can return more than one field without
using the EXISTS reserved work in the main query's FROM clause. Revise the
SELECT statemtn of the subquery to request only one field."

What can I do to fix this?


KARL DEWEY said:
Try this ---
SELECT [9-4-07 Schedule].[5], [VESSELS AND HOURS PER CAN].[HOURS PER CAN],
[HOURS PER CAN]*[5] AS Fill_Time
FROM ([9-4-07 Schedule] LEFT JOIN [SKUS 5] ON [9-4-07 Schedule].BULK = [SKUS
5].BULK) LEFT JOIN [VESSELS AND HOURS PER CAN] ON [SKUS 5].[FILL LINE] =
[VESSELS AND HOURS PER CAN].[FILL LINE];

--
KARL DEWEY
Build a little - Test a little


Emily said:
Thanks for the quick response.

Here is a list of the fields with their datatypes and I also inserted the
field & table names into my previous post for further understanding.

[5]= number
[BULK]=text
[FILL LINE]= text
[HOURS PER CAN]= number


If value in [5] field from [9-4-07 Schedule] table is greater than 0, then
use [BULK] field in the [9-4-07 Schedule] table to look up [FILL LINE] field
in [SKUS 5] table, then use value in [FILL LINE] field to look up value in
[HOURS PER CAN] field in [VESSELS AND HOURS PER CAN] table.

After I get the [HOURS PER CAN] value, I would multiply it times the [5]
value, which is the number of cans.

9-4-07 Schedule table:

BULK= ABC123= product
5= 300 cans

SKUS 5 table
BULK=ABC123
FILL LINE= G1

VESSELS AND HOURS PER CAN= fill lines and how long each can takes
FILL LINE=G1
HOURS PER CAN= .10

So the resulting calculation would be: 300 cans times .10 hours per can.

Thanks, Emily


:

Please post the field names of the tables and their datatype. Post sample
data for the tables to show what you want to do.
--
KARL DEWEY
Build a little - Test a little


:

Hello,

I am pretty new, so I am sorry if my question is confusing. Please let me
know if you need any more clarification or information.

Here is what I am trying to do, but I don't know the codes to use:

If value in X field from FORM table is greater than 0, then use ABC table to
lookup value in Y field, then use value in Y field to look up value in Z
field in DEF table.

After I get the Z value, I would multiply it times the X value.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top