SQL not working

D

DevilDog1978

SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature, FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty, [ICP_MET] AS
Expr1
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON (FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE = [dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se t1].item_nbr =
[dbo_tams_icp_ce t1].item_nbr
WHERE ((([ICP_MET])=IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True,[T],[F])))
WITH OWNERACCESS OPTION;

I am trying to accomplish a query that will compare by item_nbr the quantity
called for in the FY09_CVN_CALSEL table and the dbo_tams_icp_ce t1 table. I
want it to populate a field with a T or an F based on
IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce t1]![ce_qty])=True. Where
am I going wrong?
 
J

Jeff Boyce

What happens when you run the SQL you provided? You need to tell us what's
happening (or not) so we have something to compare to...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

DevilDog1978

I get a message box asking me for the parameter value for the field Test that
should contain Y or N. So the code I came up with does not work. I want it to
compare the quantities between the two tables based on the item number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or equal to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field Test,
otherwise I want it to place a N.

Jeff Boyce said:
What happens when you run the SQL you provided? You need to tell us what's
happening (or not) so we have something to compare to...

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature, FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty, [ICP_MET] AS
Expr1
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON
(FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE =
[dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se t1].item_nbr =
[dbo_tams_icp_ce t1].item_nbr
WHERE ((([ICP_MET])=IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True,[T],[F])))
WITH OWNERACCESS OPTION;

I am trying to accomplish a query that will compare by item_nbr the
quantity
called for in the FY09_CVN_CALSEL table and the dbo_tams_icp_ce t1 table.
I
want it to populate a field with a T or an F based on
IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce t1]![ce_qty])=True.
Where
am I going wrong?
Click to expand...
Click to expand...
 
J

Jeff Boyce

?WHERE something >= something else = True?

Something doesn't seem right about this part...

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
I get a message box asking me for the parameter value for the field Test
that
should contain Y or N. So the code I came up with does not work. I want it
to
compare the quantities between the two tables based on the item number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or equal
to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field
Test,
otherwise I want it to place a N.

Jeff Boyce said:
What happens when you run the SQL you provided? You need to tell us
what's
happening (or not) so we have something to compare to...

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature,
FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty, [ICP_MET]
AS
Expr1
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON
(FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE =
[dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se t1].item_nbr
=
[dbo_tams_icp_ce t1].item_nbr
WHERE ((([ICP_MET])=IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True,[T],[F])))
WITH OWNERACCESS OPTION;

I am trying to accomplish a query that will compare by item_nbr the
quantity
called for in the FY09_CVN_CALSEL table and the dbo_tams_icp_ce t1
table.
I
want it to populate a field with a T or an F based on
IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce t1]![ce_qty])=True.
Where
am I going wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

DevilDog1978

What doesnt seem right? I am using the builder to try and put this together.

Jeff Boyce said:
?WHERE something >= something else = True?

Something doesn't seem right about this part...

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
I get a message box asking me for the parameter value for the field Test
that
should contain Y or N. So the code I came up with does not work. I want it
to
compare the quantities between the two tables based on the item number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or equal
to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field
Test,
otherwise I want it to place a N.

Jeff Boyce said:
What happens when you run the SQL you provided? You need to tell us
what's
happening (or not) so we have something to compare to...

Regards

Jeff Boyce
Microsoft Office/Access MVP


SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature,
FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty, [ICP_MET]
AS
Expr1
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON
(FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE =
[dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se t1].item_nbr
=
[dbo_tams_icp_ce t1].item_nbr
WHERE ((([ICP_MET])=IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True,[T],[F])))
WITH OWNERACCESS OPTION;

I am trying to accomplish a query that will compare by item_nbr the
quantity
called for in the FY09_CVN_CALSEL table and the dbo_tams_icp_ce t1
table.
I
want it to populate a field with a T or an F based on
IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce t1]![ce_qty])=True.
Where
am I going wrong?
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

How can you compare the value in one field with the value in another field
(while that one is being compared to "True")?

Can you give an example of the kind of data that this expression would be
used on, and how you want it to turn out?

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
What doesnt seem right? I am using the builder to try and put this
together.

Jeff Boyce said:
?WHERE something >= something else = True?

Something doesn't seem right about this part...

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
I get a message box asking me for the parameter value for the field Test
that
should contain Y or N. So the code I came up with does not work. I want
it
to
compare the quantities between the two tables based on the item number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or
equal
to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field
Test,
otherwise I want it to place a N.

:

What happens when you run the SQL you provided? You need to tell us
what's
happening (or not) so we have something to compare to...

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature,
FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty,
[ICP_MET]
AS
Expr1
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON
(FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE =
[dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se
t1].item_nbr
=
[dbo_tams_icp_ce t1].item_nbr
WHERE ((([ICP_MET])=IIf(([FY09_CVN_CALSEL].[Auth
Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True,[T],[F])))
WITH OWNERACCESS OPTION;

I am trying to accomplish a query that will compare by item_nbr the
quantity
called for in the FY09_CVN_CALSEL table and the dbo_tams_icp_ce t1
table.
I
want it to populate a field with a T or an F based on
IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True.
Where
am I going wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

DevilDog1978

Ok maybe the True is not necessary.
I want it to compare the quantities between the two tables based on the item
number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or equal to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field Test,
otherwise I want it to place a N.

Table: FY09_CVN_CALSEL Table: dbo_tams_icp_ce t1
Field: item_nbr Value: 07896 Field: item_nbr Value: 07896
Field: qty Value: 02 Field: ce_qty Value: 04
Field: item_nbr Value: 02811 Field: item_nbr Value: 02811
Field: qty Value: 02 Field: ce_qty Value: 02
Field: item_nbr Value: 03512 Field: item_nbr Value: 03512
Field: qty Value: 03 Field: ce_qty Value: 01

In the above scenario comparing the two tables would yield a ‘Y’ in the test
field for item_nbr 02811 and 03512


Jeff Boyce said:
How can you compare the value in one field with the value in another field
(while that one is being compared to "True")?

Can you give an example of the kind of data that this expression would be
used on, and how you want it to turn out?

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
What doesnt seem right? I am using the builder to try and put this
together.

Jeff Boyce said:
?WHERE something >= something else = True?

Something doesn't seem right about this part...

Regards

Jeff Boyce
Microsoft Office/Access MVP


I get a message box asking me for the parameter value for the field Test
that
should contain Y or N. So the code I came up with does not work. I want
it
to
compare the quantities between the two tables based on the item number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or
equal
to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field
Test,
otherwise I want it to place a N.

:

What happens when you run the SQL you provided? You need to tell us
what's
happening (or not) so we have something to compare to...

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature,
FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty,
[ICP_MET]
AS
Expr1
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON
(FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE =
[dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se
t1].item_nbr
=
[dbo_tams_icp_ce t1].item_nbr
WHERE ((([ICP_MET])=IIf(([FY09_CVN_CALSEL].[Auth
Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True,[T],[F])))
WITH OWNERACCESS OPTION;

I am trying to accomplish a query that will compare by item_nbr the
quantity
called for in the FY09_CVN_CALSEL table and the dbo_tams_icp_ce t1
table.
I
want it to populate a field with a T or an F based on
IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True.
Where
am I going wrong?
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I get a message box asking me for the parameter value for the field Test that
should contain Y or N. So the code I came up with does not work. I want it to
compare the quantities between the two tables based on the item number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or equal to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field Test,
otherwise I want it to place a N.
Click to expand...

Do you have a field named Test that you want to permanently update (probably a
bad idea)? or do you want to dynamically create a field named Test with Y or N
as its value?

Assuming the latter, do it as a calculated field in the query (e.g. by typing
an expression into a vacant Field cell) rather than putting the expression in
the criteria.

Try

SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature, FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty, [ICP_MET] AS
Expr1, IIf([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1].[ce_qty],"T", "F") AS Test
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON (FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE = [dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se t1].item_nbr =
[dbo_tams_icp_ce t1].item_nbr
WITH OWNERACCESS OPTION;

Not sure what ICP_MET is nor why you're aliasing it as Expr1 but that would
seem to be a separate issue.
 
J

Jeff Boyce

But your SQL statement doesn't appear to be updating anything, let alone a
field named [Test]. I believe your SQL statement is a SELECT statement.
Take a look at Access HELP on UPDATE queries...

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
Ok maybe the True is not necessary.
I want it to compare the quantities between the two tables based on the
item
number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or equal
to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the field
Test,
otherwise I want it to place a N.

Table: FY09_CVN_CALSEL Table: dbo_tams_icp_ce t1
Field: item_nbr Value: 07896 Field: item_nbr Value: 07896
Field: qty Value: 02 Field: ce_qty Value: 04
Field: item_nbr Value: 02811 Field: item_nbr Value: 02811
Field: qty Value: 02 Field: ce_qty Value: 02
Field: item_nbr Value: 03512 Field: item_nbr Value: 03512
Field: qty Value: 03 Field: ce_qty Value: 01

In the above scenario comparing the two tables would yield a 'Y' in the
test
field for item_nbr 02811 and 03512


Jeff Boyce said:
How can you compare the value in one field with the value in another
field
(while that one is being compared to "True")?

Can you give an example of the kind of data that this expression would be
used on, and how you want it to turn out?

Regards

Jeff Boyce
Microsoft Office/Access MVP


DevilDog1978 said:
What doesnt seem right? I am using the builder to try and put this
together.

:

?WHERE something >= something else = True?

Something doesn't seem right about this part...

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
I get a message box asking me for the parameter value for the field
Test
that
should contain Y or N. So the code I came up with does not work. I
want
it
to
compare the quantities between the two tables based on the item
number
(item_nbr). If the quantity in FY09_CVN_CALSEL is greater than or
equal
to
the quantity in dbo_tams_icp_ce t1. I want it to place a Y in the
field
Test,
otherwise I want it to place a N.

:

What happens when you run the SQL you provided? You need to tell
us
what's
happening (or not) so we have something to compare to...

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
SELECT [dbo_tams_se t1].item_nbr, FY09_CVN_CALSEL.[Model Number],
FY09_CVN_CALSEL.CAGE, FY09_CVN_CALSEL.Nomenclature,
FY09_CVN_CALSEL.[Auth
Qty], [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].ce_qty,
[ICP_MET]
AS
Expr1
FROM (FY09_CVN_CALSEL INNER JOIN [dbo_tams_se t1] ON
(FY09_CVN_CALSEL.[Model
Number] = [dbo_tams_se t1].part_nbr) AND (FY09_CVN_CALSEL.CAGE =
[dbo_tams_se
t1].cage)) INNER JOIN [dbo_tams_icp_ce t1] ON [dbo_tams_se
t1].item_nbr
=
[dbo_tams_icp_ce t1].item_nbr
WHERE ((([ICP_MET])=IIf(([FY09_CVN_CALSEL].[Auth
Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True,[T],[F])))
WITH OWNERACCESS OPTION;

I am trying to accomplish a query that will compare by item_nbr
the
quantity
called for in the FY09_CVN_CALSEL table and the dbo_tams_icp_ce
t1
table.
I
want it to populate a field with a T or an F based on
IIf(([FY09_CVN_CALSEL].[Auth Qty]>=[dbo_tams_icp_ce
t1]![ce_qty])=True.
Where
am I going wrong?
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

Help with Coding 6
The Recordset is Not Updateable 4
Code works in query but not in vba - help 4
SQL statement not working 2
tricky SQL problem using VBA Excel to read Access File 3
Run Time Error 1004 5
Formula will not work 14
Add a (sequential not autonumber) number in append query 5

Top