Make table query based on another table value

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

Guest

I am trying to create a make table query that if one value in the table is
"NEW" return the value in table. If the value is "EXTEND" return the value
"/".

Example: from Table

Example 1st column 2nd column Return value in make table
001 NEW 0.481 0.481
002 EXTEND 0.481 /

I would like to know how to build an IF statement based on this type of
conditions. I know what it would look like in MS Excel but not MS Access.

Thanks,

HJ
 
Add the following in a new colomn of the design view of the query --
X: Iif([Your1stColumn] = "NEW", [Your2ndColumn], "/")
OR
X: Iif([Your1stColumn] = "EXTEND", "/", [Your2ndColumn])

In both cases you will get the latter information if the statement is not
true such as if Your1stcolumn contains "BREAD."
 
Thanks for your help but I am getting the following error when I tried the
following:

FIELD: Expr1
Table:
Total: Group By
Sort:
Show: Checked
Criteria: IIf([DECT 0A MATERIAL MASTER]![New or Extend]="NEW",[DECT 0A
MATERIAL MASTER]![Gross weight],"/")

When I did this I get the error that says: You tried to execute a query
that does not include the specified expression '[DECT 0A MATERIAL
MASTER].[Material Type]="ZMAT-DM" and [BREGEW]=Iif(DECT 0A MATERIAL
MASTER]!(New or Extend]="NEW",[DECT 0A MATERIAL MASTER]![Gross Weight],"/")'
as part of an aggregates function.

--
Harold Renninger
Global SAP Data Specialist


KARL DEWEY said:
Add the following in a new colomn of the design view of the query --
X: Iif([Your1stColumn] = "NEW", [Your2ndColumn], "/")
OR
X: Iif([Your1stColumn] = "EXTEND", "/", [Your2ndColumn])

In both cases you will get the latter information if the statement is not
true such as if Your1stcolumn contains "BREAD."

HaroldJames said:
I am trying to create a make table query that if one value in the table is
"NEW" return the value in table. If the value is "EXTEND" return the value
"/".

Example: from Table

Example 1st column 2nd column Return value in make table
001 NEW 0.481 0.481
002 EXTEND 0.481 /

I would like to know how to build an IF statement based on this type of
conditions. I know what it would look like in MS Excel but not MS Access.

Thanks,

HJ
Click to expand...
Click to expand...
 
FIELD: Expr1: [BRGEW]

HaroldJames said:
Thanks for your help but I am getting the following error when I tried the
following:

FIELD: Expr1
Table:
Total: Group By
Sort:
Show: Checked
Criteria: IIf([DECT 0A MATERIAL MASTER]![New or Extend]="NEW",[DECT 0A
MATERIAL MASTER]![Gross weight],"/")

When I did this I get the error that says: You tried to execute a query
that does not include the specified expression '[DECT 0A MATERIAL
MASTER].[Material Type]="ZMAT-DM" and [BREGEW]=Iif(DECT 0A MATERIAL
MASTER]!(New or Extend]="NEW",[DECT 0A MATERIAL MASTER]![Gross Weight],"/")'
as part of an aggregates function.

--
Harold Renninger
Global SAP Data Specialist


KARL DEWEY said:
Add the following in a new colomn of the design view of the query --
X: Iif([Your1stColumn] = "NEW", [Your2ndColumn], "/")
OR
X: Iif([Your1stColumn] = "EXTEND", "/", [Your2ndColumn])

In both cases you will get the latter information if the statement is not
true such as if Your1stcolumn contains "BREAD."

HaroldJames said:
I am trying to create a make table query that if one value in the table is
"NEW" return the value in table. If the value is "EXTEND" return the value
"/".

Example: from Table

Example 1st column 2nd column Return value in make table
001 NEW 0.481 0.481
002 EXTEND 0.481 /

I would like to know how to build an IF statement based on this type of
conditions. I know what it would look like in MS Excel but not MS Access.

Thanks,

HJ
Click to expand...
Click to expand...
Click to expand...
 
You did not mention your were using a TOTALs query.

If you do not need a TOTALs query then turn it off and there should be no
problem. If you need it then post your complete SQL statement.

HaroldJames said:
Thanks for your help but I am getting the following error when I tried the
following:

FIELD: Expr1
Table:
Total: Group By
Sort:
Show: Checked
Criteria: IIf([DECT 0A MATERIAL MASTER]![New or Extend]="NEW",[DECT 0A
MATERIAL MASTER]![Gross weight],"/")

When I did this I get the error that says: You tried to execute a query
that does not include the specified expression '[DECT 0A MATERIAL
MASTER].[Material Type]="ZMAT-DM" and [BREGEW]=Iif(DECT 0A MATERIAL
MASTER]!(New or Extend]="NEW",[DECT 0A MATERIAL MASTER]![Gross Weight],"/")'
as part of an aggregates function.

--
Harold Renninger
Global SAP Data Specialist


KARL DEWEY said:
Add the following in a new colomn of the design view of the query --
X: Iif([Your1stColumn] = "NEW", [Your2ndColumn], "/")
OR
X: Iif([Your1stColumn] = "EXTEND", "/", [Your2ndColumn])

In both cases you will get the latter information if the statement is not
true such as if Your1stcolumn contains "BREAD."

HaroldJames said:
I am trying to create a make table query that if one value in the table is
"NEW" return the value in table. If the value is "EXTEND" return the value
"/".

Example: from Table

Example 1st column 2nd column Return value in make table
001 NEW 0.481 0.481
002 EXTEND 0.481 /

I would like to know how to build an IF statement based on this type of
conditions. I know what it would look like in MS Excel but not MS Access.

Thanks,

HJ
Click to expand...
Click to expand...
Click to expand...
 
I got it to work. Here is what I had to do to make the If Statement work on
a make table query:

Field: BRGEW: IIf([DECT 0A MATERIAL MASTER]![New or Extend]="NEW",[DECT 0A
MATERIAL MASTER]![Gross weight],"/")
Table: Blank
Total: Group By
Sort: Blank
Show: Checked
Criteria: Blank
or: Blank

What this did was instead of just bringing in the Gross Weight value it
asked is this a new material? If yes return the gross weight value. If No
return "/" which SAP sees as skip.

Thanks
--
Harold Renninger
Global SAP Data Specialist


KARL DEWEY said:
You did not mention your were using a TOTALs query.

If you do not need a TOTALs query then turn it off and there should be no
problem. If you need it then post your complete SQL statement.

HaroldJames said:
Thanks for your help but I am getting the following error when I tried the
following:

FIELD: Expr1
Table:
Total: Group By
Sort:
Show: Checked
Criteria: IIf([DECT 0A MATERIAL MASTER]![New or Extend]="NEW",[DECT 0A
MATERIAL MASTER]![Gross weight],"/")

When I did this I get the error that says: You tried to execute a query
that does not include the specified expression '[DECT 0A MATERIAL
MASTER].[Material Type]="ZMAT-DM" and [BREGEW]=Iif(DECT 0A MATERIAL
MASTER]!(New or Extend]="NEW",[DECT 0A MATERIAL MASTER]![Gross Weight],"/")'
as part of an aggregates function.

--
Harold Renninger
Global SAP Data Specialist


KARL DEWEY said:
Add the following in a new colomn of the design view of the query --
X: Iif([Your1stColumn] = "NEW", [Your2ndColumn], "/")
OR
X: Iif([Your1stColumn] = "EXTEND", "/", [Your2ndColumn])

In both cases you will get the latter information if the statement is not
true such as if Your1stcolumn contains "BREAD."

:

I am trying to create a make table query that if one value in the table is
"NEW" return the value in table. If the value is "EXTEND" return the value
"/".

Example: from Table

Example 1st column 2nd column Return value in make table
001 NEW 0.481 0.481
002 EXTEND 0.481 /

I would like to know how to build an IF statement based on this type of
conditions. I know what it would look like in MS Excel but not MS Access.

Thanks,

HJ
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

Checking if data exists another table and, if yes, extracting a value 0
Make table query: [Enter 1] AS Test 1
Access Query Help - Select from 2 tables 2
how do i perform a Count(Distinct) query for 2 different column va 1
dynamic calculated field in query 3
vlookup in access ? query 2
Group by date on employeeID 4
Parameter Query with Wildcards 1

Back
Top