Select value in query
I have the following sample field & data in one table MyValue Value1 Value2 Value3 100 20 30 90 200 null 210 150 My goals here is to divide myvalue with either value1 or value2 or value3 whichever is higher. e.g in line one 100/90 and 200/210 in line two. how to create the expression in my query thanks
|
0 |
|
Utf
2/4/2010 1:26:01 PM
access
16762 articles. 
3 followers.
2 Replies
1018 Views
On Thu, 4 Feb 2010 05:26:01 -0800, zyus <zyus@discussions.microsoft.com> wrote: select MyValue/ iif(v1 > v2, iif(v1 > v3, v1, v3),iif(v2>v3,v2,v3)) Because you have nulls, you may need to slap some Nz() functions around each v. -Tom. Microsoft Access MVP >I have the following sample field & data in one table > >MyValue Value1 Value2 Value3 >100 20 30 90 >200 null 210 150 > >My goals here is to divide myvalue with either value1 or value2 or value3 >whichever is higher. > >e.g in line one 100/90 and 200/210 in line two. > >how to create the expression in my query > >thanks
|
|
0 |
|
Tom
2/4/2010 2:07:12 PM
On Feb 4, 8:26 am, zyus <z...@discussions.microsoft.com> wrote: > I have the following sample field & data in one table > > MyValue Value1 Value2 Value3 > 100 20 30 90 > 200 null 210 150 > > My goals here is to divide myvalue with either value1 or value2 or value3 > whichever is higher. > > e.g in line one 100/90 and 200/210 in line two. > > how to create the expression in my query > > thanks "zyus," If no negative values are possible, try: IIf(Abs(Nz(Value1, 0) + Nz(Value2, 0) + Nz(Value3, 0)) = 0, Null, MyValue / IIf(Nz(Value1, 0) > Nz(Value2, 0), IIf(Nz(Value1, 0) > Nz(Value3, 0), Nz(Value1, 0), Nz(Value3, 0)), IIf(Nz(Value2, 0) > Nz(Value3, 0), Nz(Value2, 0), Nz(Value3, 0)))) or by using IIf(X IS NULL, 0, X) instead of Nz(X, 0): IIf(Abs(IIf(Value1 IS NULL, 0, Value1) + IIf(Value2 IS NULL, 0, Value2) + IIf(Value3 IS NULL, 0, Value3)) = 0, Null, MyValue / IIf(IIf(Value1 IS NULL, 0, Value1) > IIf(Value2 IS NULL, 0, Value2), IIf(IIf(Value1 IS NULL, 0, Value1) > IIf(Value3 IS NULL, 0, Value3), IIf(Value1 IS NULL, 0, Value1), IIf(Value3 IS NULL, 0, Value3)), IIf(IIf(Value2 IS NULL, 0, Value2) > IIf(Value3 IS NULL, 0, Value3), IIf(Value2 IS NULL, 0, Value2), IIf(Value3 IS NULL, 0, Value3)))) What if negative values are allowed, such as: MyValue Value1 Value2 Value3 100 Null -20 -30 Do you want the largest absolute value or the largest actual value for the division? If negative values are possible, then IsNull(Value1, 0) used in a comparison to get the largest value might yield incorrect results when only negative values exist. That makes the expression more complex. You'd still want to return a Null value if the three values are all either Null or 0. The expression is already getting somewhat complicated: IIf(Abs(Nz(Value1, 0) + Nz(Value2, 0) + Nz(Value3, 0)) = 0, Null, X) where X includes finding the greatest non-Null value out of the three values -- not exactly short or trivial when negative values are possible. At least you know that there exists some non-Null or non- Zero value among the three fields once you get to the second part of the IIf() function. Obtaining a proper SQL expression is possible, but it could get as ugly as when Robert Trapp told Geni Wallace that she had a nose like a bloodhound :-). If you still want to limit yourself to use just SQL to obtain a solution, you should probably change the structure of your table: MyValue ValueNum TheValue 100 1 20 100 2 30 100 3 90 200 1 Null 200 2 210 200 3 150 Then the Max() function can be used in a subquery without having to go through all the Null logic by taking advantage of the fact that the Max() function ignores Null values. Again, I'd say that the expression you want, done properly, is complex enough to warrant creating a module level public function if negative values are allowed. In fact, even the expression for non-negative values is a little long and not trivial to change. James A. Fortune MPAPoster@FortuneJames.com RT: Geni, you should go under your home to find out where the gas leak is. You've got a nose like a bloodhound. GW: Are you calling me a b****? RT: No, not at all. I meant like a huntin' dog. GW: So now I'm goin' around huntin' for it too?
|
|
0 |
|
James
2/4/2010 5:11:33 PM
Similar Artilces:
Addition Query
I have a field called destruction date in a table which I enter a year, for example 2002. I also have a field called years which is a selection from a table years. In my query I am adding the destruction date (2002) plus the years field (5) in a field called expression. My problem is that if I do not select any years in the expression field it show the original destruction date (2002). How can I keep this addition function working but if there is no Years selected if remains blank. Thanking you in advance. Confused I am. If you do not select any years to add to the destruction ...
Pivot table and value range
Hi, I have built a simple pivot table with 2 columns : Amounts and count of amounts eg: amount/Nb of amount 100 /1 150 /5 200 /6 250 /2 300 /1 450 /5 500 /9 Would it be possible to have a range of value in the pivot table to have a result like this: amount/Nb 0-200 /12 201-400 /3 >401 /14 Thank you for your help Thank you I would add a column to the raw data that categorized the amounts. if(a2<=200,200,if(a2<=400,400,"400+")) Then drag this down the data. And use it as the field in the pivottable. Nicawette wrote: > > Hi, ...
Multiple Cell Values, please help
Hi! I have a problem and i would be very happy if someone could resolve it. This is my question to you: a would like to have one table in one sheet that has, let's say 20 columns and 80 rows. And now somewhere on the sheet there is something like a autofilter function in which I choose which date i want. And now i choose one date and this table is blank and i write things in the table,now i choose some other date and again i write things in this table and so on. So I want to have like 20,30,40... tables at the same spot in one sheet and just to pick one date and the table has values for t...
Smartlist Search Query Doesn't Produce Data
Using the payroll history trx (PHT) option in smartlist, we attempt to search for transactions using the audit control code column name in the search definition. If we search on UPRCC00000001 thru 7, records are returned. If we set the filter to UPRCC00000999, no results are displayed. UPRCC0000099 has been verified as a valid control code in the system. I've found some KB articles that refer to other smartlist item searches not working, but non refernecing PHT and nothing about intermittant search problems. Any help is appreciated! ...
SELECT @@IDENTITY in A2010 with Triggers
Over on StackOverflow someone was asking about how to get the last Autonumber value, and I suggested SELECT @@IDENTITY in the same connection as the insert is made, and said "just like SQL Server." Someone responded that in SQL Server you should use the SCOPE_IDENTITY() function instead, so I looked it up: http://msdn.microsoft.com/en-us/library/ms190315.aspx ....and that made me think that in A2010 with table-level data macros, SELECT @@IDENTITY might end up not being reliable if the insert causes a trigger to insert a record in another table (as described in the MS...
How can I select pivot table row totals to fill color them
I have a pivot table with total rows. Other people in my team at work can hover over the left edge of one of the total rows and all total rows get selected so that a fill color can be applied. It does not work for me From the PivotTable tool bar, Click on the PivotTable dropdown, select - Enable selection. -- Best Regards, Luke M "Ruth5155" <Ruth5155@discussions.microsoft.com> wrote in message news:47441F7C-709C-4AC6-987A-5806C9F6E7DD@microsoft.com... >I have a pivot table with total rows. Other people in my team at work can > hover over the left e...
Query 08-24-07
I have two tables. One contains contigent employees the other full-time employees. I would like to create a query that combines these two tables, so the contigent and full-time employees are all on one report. How can I accomplish this? Thanks for your help Is there a business reason these two tables are kept separated? For instance, the data elements you keep about "contingent" employees differs from the data you keep about "full-time" employees? If you have two identical tables (same structure), one for each "type" of employee, your data is structured ...
Change the default value for F11
The default value to automatically create a line chart is to highligh data and press F11. I would like to change the line chart to a pi graph when hitting F11. Any suggestions -- Message posted from http://www.ExcelForum.com To change the default chart type: Select a chart Choose Chart>Chart Type Select the Pie chart type, and one of the subtypes Click the 'Set as default chart' button Click Yes, to confirm Click Cancel ih8elvis < wrote: > The default value to automatically create a line chart is to highlight > data and press F11. I would like to change the line chart ...
Limit Values
Hello, I have the following issue which I am not able to solve. I want to limit the value possibilities of a cell to #,0 and #,5 For example: 8,5 or 7,0 but not 6,2 and so on. I tried to do it with validation but it doesn't work. Hope anyone can solve this out for me. Many thanks. Noepie assumed the cell A1 need to limit the number to ( 0,5,7,8) Go to data | validaton | allow:custom | formula : =3DOR (A1=3D0,A1=3D5,A1=3D7,A1=3D8) | ok On Nov 30, 2:33=A0pm, Noepie <Noe...@discussions.microsoft.com> wrote: > Hello, > > I have the following issue which I am not able t...
how do i increase the value of numbers in a column by a percentage
i have an excel stocklist with 6000 items on it ,i just need to know how to increase the whole price list column by a percentage , thanks Say we want to increase a column of values by 6%. In an unused cell, enter 1.06. Copy this cell. Select the column of numbers. PasteSpecial > Multiply -- Gary''s Student - gsnu200909 "colinchurcher" wrote: > i have an excel stocklist with 6000 items on it ,i just need to know how to > increase the whole price list column by a percentage , thanks 1. In cell H 1 I have:- 10 2. In cell I 1 I have:- ...
Pass through query and variables
I'm currently building a pass through query which ties to an IBM Iseries ODBC driver. Is there any way to include an "in" statement from another non-pass through table or query in the same access database? For the sake of example, let's say I have a static table of user names (name: tbl_users). I have a pass through query built that I would like to only return results based off of the tbl_users content. What would be the syntax to accomplish this? In theory, the logic would look like this but I'm not sure of the exact syntax: Select ODBC.userid From ODBC Where O...
Queries & Reports
QUESTION . First I will enter info into TBL Radio Receipt, from there I want the information to jump to TBL Radio Info and prompt me to complete this table. Then it will jump to either TBL New Radio Information OR TBL Repair Information. This will need to be completed. How does this happen, I mean-- How do I create the relationships to reflect this path? PS How do I attach a pic of my problem? Jeannette Cunningham already posted a response to your question back on 12/21, but perhaps you didn't see/understand it so we can try again. Your question is a little hard to understand, so...
parameter value in command line switch
I have a database that is opened from an AS400 database using a command line. I would like to pass the record ID the user is on into Access to open a form on that record. Is there any way to pass that info in the command line? If so, how do I capture that on the access side to use as a parameter? You can use the /cmd switch to pass a value, and the Command function to retrieve it. To auto-fire this when Access starts, use the AutoExec macro, or code in the Open event of your startup form. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbr...
Update workbook each time a different worksheet is selected
I need to update a workbook each time the user goes to a new worksheet either by selecting the tab for that worksheet or by hyperlink. How can I to this without using a macro? (using macro is ok if necessary but I am trying to keep things simple) DJ "Update" means what? More details please. Gord Dibben Excel MVP On Tue, 13 Dec 2005 13:50:02 -0800, DJL <DJL@discussions.microsoft.com> wrote: >I need to update a workbook each time the user goes to a new worksheet either >by selecting the tab for that worksheet or by hyperlink. How can I to this >without us...
Chart
Have the specific knowledge base instructions. Instructions state to in the chart, select the data series, then via Format menu, Axis tab, click the secondary axis. I can't seem to select the individual data series. It tends to select all the data series and the Axis tab grays out the secondary axis toggle. I have a simple chart requirement total # projects totals $ value of projects need left axis to scale to # projects need right axis to scale to $ value of projects. these data are divergent and doesn't display well in a single y-axis chart. Hi, Here is another way to sele...
Using Access form to assign values of variables in an Excel VBA program
I have created a VBA program for Ms Excel.But,to get desired results, every time, I go into the code to change the values of variables.Is there a way to get the values of variables stored in an Access table? YOu need to do a search for ADO method (ActiveX Data Object). There are plenty of example of code on the web. You need to add two references in your VBA to use the method. from VBA menu tools - References 1) Microsoft Access XX.X object library 2) Microsfot ActiveX Data Objects X.X library Use the latest versio of the two libraries on your PC. You will make a conn...
Append Query problem
I have created an append query in vba code which is inside a For...Next loop. I create an SQL statement to find matching related records in one table, and then insert these matching records into a temporary table. I repeat for each value of unique primary key that is provided (in an array). The issue I have is that the append query only works for the first matching record, not the subsequent records. I have verified that correct SQL statements are generated during each iteration of the loop, but for some reason no inserts occur after the first one. The temp table is cleared at the beginning o...
This item's make/buy type has not been selected...
hello - we are setting up inventory and PO and when testing our setups, after we enter an item on the PO we get this error. It will not let us use the item until you go to the item engineering screen. We do not have manufacturing registered. Any help is appreciated. -- MP ...
asset depreciation
Hi, Fixed asset part is really new to me and this would be a very simple question.(However, I could not find 'how to' from the manual.) Several assets have been recorded to GP manually last year. Therefore, I am going to update the asset value directly. Which window should I need to use? I tried to change at the window : "cards>fixed assets>book," but I saw the following message. " IF LTD Depreciation is entered, Depreciation to Date cannot be greater than the last day of the year for this book." * How can I find the last day of the book of each asset?...
Select Statement in query to append to a table
Hello, I have a table with the following Master Stock Code Header ID and I want to append this information to a table, kind of ike a cross tab query but I want the value of the header ID to be in the column not as a column name. I have a max of 12 header id's, so I have 12 column names in the table i want to append to (ie. Hdr1, Hdr2 etc) There could be more than one Header ID per stock code. WHat is the proper syntax for the select statement in my query so I don't get duplicate rows per stock code. ...
Union Query with two crosstabs
Hi…I attempted to do a Union Query with two crosstabs. I received an error “Characters found after end of SQL statement. I then read some posts but couldn’t find the solution. It appears that it is possible and I read something about not having two TRANSFORM statements but it wasn’t clear to me. I have the same number of fields and defined the PIVOT part by IN(“x”, “y”, “z”) and used UNION ALL. What else do I need to do to join the two crosstabs? Thanks for any suggestions. Do you have a semi-colon after the first query? If so that can cause the error? If the two cr...
Pass-Through Query problem in access 2007
I have an Access 2007 SQL Pass-Through Query to MS SQL Server 2000 that executes a stored procedure with parameters. The SP just inserts some rows in a table and returns an integer as a return code indicating success or failure. When the "ReturnRecords" property is turned on so the code is returned to the client, it appears that the query runs twice since the table shows twice as many rows inserted than it should. When the "ReturnRecords" property is turned off, the correct number of rows are inserted. This is a strange bug. Any ideas? I'm using an ODBC connection. Th...
Secondary Axis Values Disappear
Office 2003 When I elect to "show data table" my secondary axis values disapper. As soon as I uncheck "show data table" in chart options, the axis values reappears. Can't I have both (secondary axis values AND data table)? TIA, Karen Hi Karen, Excel's chart data tables have many limitations. It depends what type of chart you have. Some types won't let you have a data table at all, some only for data on one axis. Some, like a column chart, will let you have a data table even if you have both primarty and secondary axes. If you have a combination chart,...
Query Question... 02-25-10
I have filenames listed in a DB with '.pdf', I would like to to run an update query to loose the '.pdf' since everything is scanned as a pdf. There are hundreds of entries. Can someone give me a hand with the script? Thanks David In an update query AFTER BACKING UP DATABASE use this -- Replace([YourField], ".pdf", "") -- Build a little, test a little. "116" wrote: > I have filenames listed in a DB with '.pdf', I would like to to run an update > query to loose the '.pdf' since everything is scann...
Selecting property pages with a tree control
Hello MFCers I'd like to construct a dialog box similar to a property sheet, except that instead of selecting pages using tabs, the user chooses the page by selecting entries in a tree control on the left side of the dialog. Is this within the bounds of possibilty, or better yet, been done before? Cheers mark-r -- Simon and Garfunkel lyric snippets for sale. You'd better hurry up and order one, our limited supply's very nearly gone. See http://www.codeproject.com/property/saprefs.asp -- Regards, Nish [VC++ MVP] "Mark Robinson" <mark@simsol.co.uk> wrote i...
I have a field called destruction date in a table which I enter a year, for example 2002. I also have a field called years which is a selection from a table years. In my query I am adding the destruction date (2002) plus the years field (5) in a field called expression. My problem is that if I do not select any years in the expression field it show the original destruction date (2002). How can I keep this addition function working but if there is no Years selected if remains blank. Thanking you in advance. Confused I am. If you do not select any years to add to the destruction ...
Pivot table and value range
Hi, I have built a simple pivot table with 2 columns : Amounts and count of amounts eg: amount/Nb of amount 100 /1 150 /5 200 /6 250 /2 300 /1 450 /5 500 /9 Would it be possible to have a range of value in the pivot table to have a result like this: amount/Nb 0-200 /12 201-400 /3 >401 /14 Thank you for your help Thank you I would add a column to the raw data that categorized the amounts. if(a2<=200,200,if(a2<=400,400,"400+")) Then drag this down the data. And use it as the field in the pivottable. Nicawette wrote: > > Hi, ...
Multiple Cell Values, please help
Hi! I have a problem and i would be very happy if someone could resolve it. This is my question to you: a would like to have one table in one sheet that has, let's say 20 columns and 80 rows. And now somewhere on the sheet there is something like a autofilter function in which I choose which date i want. And now i choose one date and this table is blank and i write things in the table,now i choose some other date and again i write things in this table and so on. So I want to have like 20,30,40... tables at the same spot in one sheet and just to pick one date and the table has values for t...
Smartlist Search Query Doesn't Produce Data
Using the payroll history trx (PHT) option in smartlist, we attempt to search for transactions using the audit control code column name in the search definition. If we search on UPRCC00000001 thru 7, records are returned. If we set the filter to UPRCC00000999, no results are displayed. UPRCC0000099 has been verified as a valid control code in the system. I've found some KB articles that refer to other smartlist item searches not working, but non refernecing PHT and nothing about intermittant search problems. Any help is appreciated! ...
SELECT @@IDENTITY in A2010 with Triggers
Over on StackOverflow someone was asking about how to get the last Autonumber value, and I suggested SELECT @@IDENTITY in the same connection as the insert is made, and said "just like SQL Server." Someone responded that in SQL Server you should use the SCOPE_IDENTITY() function instead, so I looked it up: http://msdn.microsoft.com/en-us/library/ms190315.aspx ....and that made me think that in A2010 with table-level data macros, SELECT @@IDENTITY might end up not being reliable if the insert causes a trigger to insert a record in another table (as described in the MS...
How can I select pivot table row totals to fill color them
I have a pivot table with total rows. Other people in my team at work can hover over the left edge of one of the total rows and all total rows get selected so that a fill color can be applied. It does not work for me From the PivotTable tool bar, Click on the PivotTable dropdown, select - Enable selection. -- Best Regards, Luke M "Ruth5155" <Ruth5155@discussions.microsoft.com> wrote in message news:47441F7C-709C-4AC6-987A-5806C9F6E7DD@microsoft.com... >I have a pivot table with total rows. Other people in my team at work can > hover over the left e...
Query 08-24-07
I have two tables. One contains contigent employees the other full-time employees. I would like to create a query that combines these two tables, so the contigent and full-time employees are all on one report. How can I accomplish this? Thanks for your help Is there a business reason these two tables are kept separated? For instance, the data elements you keep about "contingent" employees differs from the data you keep about "full-time" employees? If you have two identical tables (same structure), one for each "type" of employee, your data is structured ...
Change the default value for F11
The default value to automatically create a line chart is to highligh data and press F11. I would like to change the line chart to a pi graph when hitting F11. Any suggestions -- Message posted from http://www.ExcelForum.com To change the default chart type: Select a chart Choose Chart>Chart Type Select the Pie chart type, and one of the subtypes Click the 'Set as default chart' button Click Yes, to confirm Click Cancel ih8elvis < wrote: > The default value to automatically create a line chart is to highlight > data and press F11. I would like to change the line chart ...
Limit Values
Hello, I have the following issue which I am not able to solve. I want to limit the value possibilities of a cell to #,0 and #,5 For example: 8,5 or 7,0 but not 6,2 and so on. I tried to do it with validation but it doesn't work. Hope anyone can solve this out for me. Many thanks. Noepie assumed the cell A1 need to limit the number to ( 0,5,7,8) Go to data | validaton | allow:custom | formula : =3DOR (A1=3D0,A1=3D5,A1=3D7,A1=3D8) | ok On Nov 30, 2:33=A0pm, Noepie <Noe...@discussions.microsoft.com> wrote: > Hello, > > I have the following issue which I am not able t...
how do i increase the value of numbers in a column by a percentage
i have an excel stocklist with 6000 items on it ,i just need to know how to increase the whole price list column by a percentage , thanks Say we want to increase a column of values by 6%. In an unused cell, enter 1.06. Copy this cell. Select the column of numbers. PasteSpecial > Multiply -- Gary''s Student - gsnu200909 "colinchurcher" wrote: > i have an excel stocklist with 6000 items on it ,i just need to know how to > increase the whole price list column by a percentage , thanks 1. In cell H 1 I have:- 10 2. In cell I 1 I have:- ...
Pass through query and variables
I'm currently building a pass through query which ties to an IBM Iseries ODBC driver. Is there any way to include an "in" statement from another non-pass through table or query in the same access database? For the sake of example, let's say I have a static table of user names (name: tbl_users). I have a pass through query built that I would like to only return results based off of the tbl_users content. What would be the syntax to accomplish this? In theory, the logic would look like this but I'm not sure of the exact syntax: Select ODBC.userid From ODBC Where O...
Queries & Reports
QUESTION . First I will enter info into TBL Radio Receipt, from there I want the information to jump to TBL Radio Info and prompt me to complete this table. Then it will jump to either TBL New Radio Information OR TBL Repair Information. This will need to be completed. How does this happen, I mean-- How do I create the relationships to reflect this path? PS How do I attach a pic of my problem? Jeannette Cunningham already posted a response to your question back on 12/21, but perhaps you didn't see/understand it so we can try again. Your question is a little hard to understand, so...
parameter value in command line switch
I have a database that is opened from an AS400 database using a command line. I would like to pass the record ID the user is on into Access to open a form on that record. Is there any way to pass that info in the command line? If so, how do I capture that on the access side to use as a parameter? You can use the /cmd switch to pass a value, and the Command function to retrieve it. To auto-fire this when Access starts, use the AutoExec macro, or code in the Open event of your startup form. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbr...
Update workbook each time a different worksheet is selected
I need to update a workbook each time the user goes to a new worksheet either by selecting the tab for that worksheet or by hyperlink. How can I to this without using a macro? (using macro is ok if necessary but I am trying to keep things simple) DJ "Update" means what? More details please. Gord Dibben Excel MVP On Tue, 13 Dec 2005 13:50:02 -0800, DJL <DJL@discussions.microsoft.com> wrote: >I need to update a workbook each time the user goes to a new worksheet either >by selecting the tab for that worksheet or by hyperlink. How can I to this >without us...
Chart
Have the specific knowledge base instructions. Instructions state to in the chart, select the data series, then via Format menu, Axis tab, click the secondary axis. I can't seem to select the individual data series. It tends to select all the data series and the Axis tab grays out the secondary axis toggle. I have a simple chart requirement total # projects totals $ value of projects need left axis to scale to # projects need right axis to scale to $ value of projects. these data are divergent and doesn't display well in a single y-axis chart. Hi, Here is another way to sele...
Using Access form to assign values of variables in an Excel VBA program
I have created a VBA program for Ms Excel.But,to get desired results, every time, I go into the code to change the values of variables.Is there a way to get the values of variables stored in an Access table? YOu need to do a search for ADO method (ActiveX Data Object). There are plenty of example of code on the web. You need to add two references in your VBA to use the method. from VBA menu tools - References 1) Microsoft Access XX.X object library 2) Microsfot ActiveX Data Objects X.X library Use the latest versio of the two libraries on your PC. You will make a conn...
Append Query problem
I have created an append query in vba code which is inside a For...Next loop. I create an SQL statement to find matching related records in one table, and then insert these matching records into a temporary table. I repeat for each value of unique primary key that is provided (in an array). The issue I have is that the append query only works for the first matching record, not the subsequent records. I have verified that correct SQL statements are generated during each iteration of the loop, but for some reason no inserts occur after the first one. The temp table is cleared at the beginning o...
This item's make/buy type has not been selected...
hello - we are setting up inventory and PO and when testing our setups, after we enter an item on the PO we get this error. It will not let us use the item until you go to the item engineering screen. We do not have manufacturing registered. Any help is appreciated. -- MP ...
asset depreciation
Hi, Fixed asset part is really new to me and this would be a very simple question.(However, I could not find 'how to' from the manual.) Several assets have been recorded to GP manually last year. Therefore, I am going to update the asset value directly. Which window should I need to use? I tried to change at the window : "cards>fixed assets>book," but I saw the following message. " IF LTD Depreciation is entered, Depreciation to Date cannot be greater than the last day of the year for this book." * How can I find the last day of the book of each asset?...
Select Statement in query to append to a table
Hello, I have a table with the following Master Stock Code Header ID and I want to append this information to a table, kind of ike a cross tab query but I want the value of the header ID to be in the column not as a column name. I have a max of 12 header id's, so I have 12 column names in the table i want to append to (ie. Hdr1, Hdr2 etc) There could be more than one Header ID per stock code. WHat is the proper syntax for the select statement in my query so I don't get duplicate rows per stock code. ...
Union Query with two crosstabs
Hi…I attempted to do a Union Query with two crosstabs. I received an error “Characters found after end of SQL statement. I then read some posts but couldn’t find the solution. It appears that it is possible and I read something about not having two TRANSFORM statements but it wasn’t clear to me. I have the same number of fields and defined the PIVOT part by IN(“x”, “y”, “z”) and used UNION ALL. What else do I need to do to join the two crosstabs? Thanks for any suggestions. Do you have a semi-colon after the first query? If so that can cause the error? If the two cr...
Pass-Through Query problem in access 2007
I have an Access 2007 SQL Pass-Through Query to MS SQL Server 2000 that executes a stored procedure with parameters. The SP just inserts some rows in a table and returns an integer as a return code indicating success or failure. When the "ReturnRecords" property is turned on so the code is returned to the client, it appears that the query runs twice since the table shows twice as many rows inserted than it should. When the "ReturnRecords" property is turned off, the correct number of rows are inserted. This is a strange bug. Any ideas? I'm using an ODBC connection. Th...
Secondary Axis Values Disappear
Office 2003 When I elect to "show data table" my secondary axis values disapper. As soon as I uncheck "show data table" in chart options, the axis values reappears. Can't I have both (secondary axis values AND data table)? TIA, Karen Hi Karen, Excel's chart data tables have many limitations. It depends what type of chart you have. Some types won't let you have a data table at all, some only for data on one axis. Some, like a column chart, will let you have a data table even if you have both primarty and secondary axes. If you have a combination chart,...
Query Question... 02-25-10
I have filenames listed in a DB with '.pdf', I would like to to run an update query to loose the '.pdf' since everything is scanned as a pdf. There are hundreds of entries. Can someone give me a hand with the script? Thanks David In an update query AFTER BACKING UP DATABASE use this -- Replace([YourField], ".pdf", "") -- Build a little, test a little. "116" wrote: > I have filenames listed in a DB with '.pdf', I would like to to run an update > query to loose the '.pdf' since everything is scann...
Selecting property pages with a tree control
Hello MFCers I'd like to construct a dialog box similar to a property sheet, except that instead of selecting pages using tabs, the user chooses the page by selecting entries in a tree control on the left side of the dialog. Is this within the bounds of possibilty, or better yet, been done before? Cheers mark-r -- Simon and Garfunkel lyric snippets for sale. You'd better hurry up and order one, our limited supply's very nearly gone. See http://www.codeproject.com/property/saprefs.asp -- Regards, Nish [VC++ MVP] "Mark Robinson" <mark@simsol.co.uk> wrote i...