|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 2nd, 2009, 05:38 PM
posted to microsoft.public.access
|
|||
|
|||
SQL not working
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? 
|
|
#2
July 2nd, 2009, 06:07 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
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" wrote in 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?
|
|
#3
July 2nd, 2009, 06:22 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
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" wrote: 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" wrote in 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?
|
|
#4
July 2nd, 2009, 06:29 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
?WHERE something = something else = True?
Something doesn't seem right about this part... Regards Jeff Boyce Microsoft Office/Access MVP "DevilDog1978" wrote in 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. "Jeff Boyce" wrote: 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" wrote in 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?
|
|
#5
July 2nd, 2009, 06:38 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
What doesnt seem right? I am using the builder to try and put this together.
"Jeff Boyce" wrote: ?WHERE something = something else = True? Something doesn't seem right about this part... Regards Jeff Boyce Microsoft Office/Access MVP "DevilDog1978" wrote in 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. "Jeff Boyce" wrote: 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" wrote in 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?
|
|
#6
July 2nd, 2009, 07:18 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
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" wrote in message ... What doesnt seem right? I am using the builder to try and put this together. "Jeff Boyce" wrote: ?WHERE something = something else = True? Something doesn't seem right about this part... Regards Jeff Boyce Microsoft Office/Access MVP "DevilDog1978" wrote in 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. "Jeff Boyce" wrote: 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" wrote in 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?
|
|
#7
July 2nd, 2009, 07:42 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
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" wrote: 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" wrote in message ... What doesnt seem right? I am using the builder to try and put this together. "Jeff Boyce" wrote: ?WHERE something = something else = True? Something doesn't seem right about this part... Regards Jeff Boyce Microsoft Office/Access MVP "DevilDog1978" wrote in 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. "Jeff Boyce" wrote: 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" wrote in 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?
|
|
#8
July 2nd, 2009, 07:49 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
On Thu, 2 Jul 2009 10:22:30 -0700, DevilDog1978
wrote: 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. 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. -- John W. Vinson [MVP]
|
|
#9
July 2nd, 2009, 08:41 PM
posted to microsoft.public.access
|
|||
|
|||
|
SQL not working
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" wrote in message news  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" wrote: 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" wrote in message ... What doesnt seem right? I am using the builder to try and put this together. "Jeff Boyce" wrote: ?WHERE something = something else = True? Something doesn't seem right about this part... Regards Jeff Boyce Microsoft Office/Access MVP "DevilDog1978" wrote in 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. "Jeff Boyce" wrote: 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" wrote in 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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
