Reply:

function in query summing results of 2 switch functions?
can I build an expression in a query that sums the values of 2 computed fields in the same query. E.g, Sum(1-CF_1 + CF_2) ? CF_1 & CF_2 are 2 other fields that use Switch statements. THanks Zx slight correction. Can I do this in the Field builder window: field_B: (CF_1 + CF_2) if both CF_1 & CF_2 are computed fields in the query themselves (basically fields computed from switch statements). Access is giving me an error saying it does not recognize these as "a valid field name or expression". I'm not sure why - aren't they expressions :) ? ...

Charting challenge/query
The following quote is posted on behalf of a client using Windows and Office 2003 Standard. Please email responses directly to me, too, since I do not read this group much. Any help/insight would be most appreciated. I'll do my best to relay any questions people have and bring back any answers to said questions. <quote> The Goal: In an effort to come up with a new way of looking at organizational structure, I employed Excel's 'Donut Chart' function. My goal was to have the head of the organization in the middle of the chart and then that person's direct re...

Filter and Query Records Mismatch
When I perform a filter on a table I get 371 records but when I design a query based on the same criteria, I get 63 records. And the result in my report is not showing the correct data based on this. I can't figure out what I am doing wrong. Any help will be greatly appreciated. Thanks. ...

Payables Management query
Hi everybody - can somebody tell me which table holds the invoice information for payables management. I have to make a query that show which checks paid for which invoices and I have to include the invoice description as well. Thanks!! PM10000 - PM Transaction WORK File PM20000 - PM Transaction OPEN File PM30200 - PM Paid Transaction History File Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www.gp2themax.blogspot.com Michael, I have posted code for a SQL view that may already have everything you need except the invoice descrip...

Query-Based Distribution Groups #2
I'm looking to create an LDAP query in Exchange 2003 that will show domain users that are currently logged on. This will allow me to create an email distribution group that contains only people who are in the office at that moment. Surely this must be possible as the query is searching Active Directory, but I've not been able to find a "logged on" attribute and I've trawled the web and newsgroups for answers but found nothing ... if anyone knows how to do this, I'd be very grateful of your help! Thanks, Rob "Robert Stokes" <rob@robertstokes.net...

Exchange Information Store crashing
Hello all, About every 24 hours I get this error. Store.exe _ Application Error The instruction at &#8220;0x0042466b&#8221; referenced memory at &#8220;0x13547000&#8221;. The memory could not be &#8220;read&#8221;. Click on OK to terminate the application Click on CANCEL to debug application The first number is always the same the second one varies. Anyone have any thoughts? Please e-mail me at tmesick@ose.net Thanks My first guess would be hardware problems. Are you checking your system and application event logs on your server? -- regards, Michael Abb...

No data returned from query
I can't seem to figure out why this query will not return any data. SELECT AsiaContracts.AsiaContractShipper, AsiaContracts.AsiaContractShipLine, AsiaContracts.AsiaContractNumber, OceanFreightRate.OceanFreigthRateOriginPort, OceanFreightRate.OceanFreightRateDischargePort, OceanFreightRate. OceanFreightCharge, OceanFreightRate.OceanFreightAdditionalFees, EuropeContracts.EuropeContractShipLine, EuropeContracts.EuropeContractShipper FROM OceanFreightRate INNER JOIN (EuropeContracts INNER JOIN (AsiaContracts INNER JOIN ContractJunction ON AsiaContracts.AsiaContractID = ContractJunc...

Web Query Formatting not ready for Production
I do web queries from several internal systems and I continually run into problems where the returned data is formatted to Microsoft's wishes instead of my formatting requirements. 2 Sample are: 1) one column is cage code, a 5 digit alphanumeric number (sometimes starting with a zero). System want to remove the first zero even if I set the column to text. 2) Part Numbers column comes in all shapes and formats as a string, some starting with multiple zeros, some having all numbers with "E" in the middle, etc. When importing, even if column set to text, web query...

Query on two tables with matching null fields
Suppose I have two tables: Table 1: ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 Table2 ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 The following SQL: SELECT Table1.ID, Table2.ID FROM Table1 INNER JOIN Table2 ON (Table1.c = Table2.c) AND (Table1.b = Table2.b) AND (Table1.a = Table2.a); Returns: Table1.ID Table2.ID 4 4 I do want these tables connected by inner joins not left or right (in other words I want only those records where all t...

Ranking results of a Query
I have set of queries running for employee performance ranking culminating in a final query that shows their name, employee ID and overall score, i would like the final step to also add a column with rank, so the person with the highest score has a 1, next highest has a 2 etc...... I am kind of new to this, like everyone else who posts it seems so lamens terms are appreciated. If it helps the name of the query is "Query-Shift Bid Part C", and the columns are "First", "Last","Login","Hours","QA" and "Score" I want "...

Crosstab query...
I am frustrated with this one... I have a crosstab query used to display results on a graph. I want to filter "risklevel" depending on what is selected in a combobox using a value list. When I put in the filter and run the query, I get the error "The Microsoft Jet Database Engine does not recognize [Forms]![frmLOSA_Chart_Trend]![risk] as a valid field name or expression" Here is the SQL.... TRANSFORM Sum(qryLOSATrend_Risk.RiskLevelQTY) AS SumOfRiskLevelQTY SELECT qryLOSATrend_Risk.OBMonth FROM qryLOSATrend_Risk WHERE (((qryLOSATrend_Risk.RiskLevel)=[Forms]![...

Group same type of field from 3 different queries
Can anyone help me with this? I have 3 different queries each summing up a value for a given month. Past sales, present sales, future sales, these only have two fields Month and value. What I want to do is create something like Month Past Present Future ------------------------------------- Jun 5278 3424 3454 Jul 5625 6254 5462 Jun 2562 5264 5262 Where it will merge the two values for Jun, this will account for more than one product showing a summary of all sales. Ive tried using crosstabs queries, nested queries, even writing ...

SQL Query needed
Sample Data Type # Amt # Trno # DC # Code # Subcode ADV # 1000 # 1 #D # 103625 # ADV # 1000 # 1 #C # 103400 # S1 ADV # 2000 # 2 #D # 103625 # ADV # 2000 # 2 #C # 103400 # S2 VES # 5000 # 1 #D # 103400 # S1 VES # 5000 # 1 #C # 300070 # S1 Now I wanna find out the details of Code & Subcode of Type(VES) which does not match with Type ADV Expected result should come with subcode of the other record ADV # 2000 # 2 #D # 103625 # S2 Thanks in advance Nirene -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2010...

Need Query or Macro to Update Form
I have a form with two textboxes, both tied to field names in the table I am using for the form. The two fields are GroupName1 and Groupname2. I need to build a query or macro to run where if there is any text in Groupname2, to transfer the text to a new record where the text from Groupname2 is now in Groupname1 field. Please help!!! Thanks. Chris You've described "how" you want to solve a situation... but we have no idea what situation this is supposed to solve! More specific descriptions will probably lead to more specific suggestions. -- Regards Jeff Boyce www.In...

Public Folders Stores on Front-ends
The only folders I see under the public folder store are: Logon Public Folder Instances Public Folders Repication Status Full Text Indexing Does this mean that their are acutally no public folders on the front-ends? The reason I am asking is because I want to dismount and delete the public folder stores on my front-ends. The last time I did this mail started queueing up on the servers. Those aren't the folders you're looking at. Those are Exchange System Manager containers. What is in the Public Folders container? You should be able to remove the public store on your front end...

Use Of ":" In Query As Part Of Header
I have a query with a field name of "Street", however, I would like it to read "CAT:STREET". This query is exported to a csv file the requires the format of the later. Currently the csv file is modified manually afer the fact. In the query I have been unable to create an expression that does not choke on the ":". Any suggestion as to how allow this in the query, or maybe allow this in the export. Thanks in advance Hi Clint, You won't get that fixed. Can't you use as header CAT_STREET: Cloggy "Clint" wrote: > I have a query with a ...

SQL Trace: "Normalizing" TextData Column Values to Obtain "Query Signatures" for Hashing
Hi All, The TextData column in a SQL Trace table will contain a distinct string for any invocation of the captured SQL statement that was invoked with a set of parameters distinct from those before. So, for instance, 'EXEC dbo.PayThisInvoice 130254' is naturally different than 'EXEC dbo.PayThisInvoice 342902'. But, clearly, both statements involve invocation of the same stored procedure. For monitoring purposes over time, I need to be able to recognize that these and any similar statements are actually using the same underlying query. Standard practice, as I under...

Information Store keeps stopping
We are running Exchange Server 5.5 and 1 or 2 times a day the Information Store is stopping. So far we have been able to restart it with no problems. In the system event log is the following message: The Microsoft Exchange Information Store service terminated unexpectedly. It has done this 15 time(s). The following corrective action will be taken in 0 milliseconds: No action. I have searched everywhere that I can think of and any description of a problem that sounds even close to this just says to install the latest service pack to fix it. We already have the latest SP on it t...

Prevent queue from committing messages to temp store
Is there a way that I can prevent messages that are in my smtp queue from being committed to a temporary information store? Could I simply pause the service? On Wed, 21 Mar 2007 12:20:25 -0700, JBeckett [MCSA] <JBeckettMCSA@discussions.microsoft.com> wrote: >Is there a way that I can prevent messages that are in my smtp queue from >being committed to a temporary information store? Could I simply pause the >service? Dismounting the store is the sure solution! Is there a problem you are trying to solve? ...

Show Query Design Toolbar
I am starting my database with the database window and built-in toolbars hidden. Certain people have the right to created new queries, so I have created a button (which is visible only for these people) that will display the database window so they can access queries. The problem is the Query Design Toolbar is not displayed when they go into Design view. I have tried to show the toolbar using the following statement, without success: ---DoCmd.ShowToolbar "Query Design", acToolbarYes I have tried putting this statement before and after the statement that opens the database window, a...

SQL query question 06-04-07
Hi all, I have a SQL query question... I have a table with road segments that have a low and high address (see sample QUERY below) I am wanting to produce a report of the roads that meet the following... 1. [FROM_ADD] > 0 AND ( 2. the [FROM_ADD] > [TO_ADD] OR 3. if there is more than one segment with the same name... the [FROM_ADD] < the previous road segment with the same name's [TO_ADD] ) So if I ran it on the query below, I would end up with... AARONDALE ROAD Because third segment's [FROM_ADD] ...

Query Criteria Question
I'm trying to make a query of two tables however they both have multiple entries of the same information. One table has about 2300 Item Numbers with lots of duplicates; the other table has about 10000 Item Numbers with even more duplicates. What criteria do I need to use to get the query to find the first matchings in both tables and then move on to the next without using the previously matched numbers? Thanks In Advance, Rob Rob wrote: > I'm trying to make a query of two tables however they both have > multiple entries of the same information. One table has about 2300 >...

combo box in a query criteria
Hi All, I have a query were I get the Qty on hand of items based on a project. The query works fine and to get the users input i have in the criteria a prompt asking the user the project no and the query shows filtered records for that project. My question is how can i make this prompt a combo box containing my project nos so that the user can select values from the combo or list box. any ideas or direction would be appreciated. I am not sure how to proceed with this. thanks in advance The combo box must be on a form. You would need to open the form, select from the combo box, and ...

proper bracketing to get combo column into a query
I am trying to get the text value from a combobox into a query where clause like so Like "*" & Chr(34) & [Forms]![frmItemTypes]![cboItemDivision]. [Column(1)] & Chr(34) but nothing works. This works but only for numbers [Forms]! [frmItemTypes]![cboItemDivision]. How do I bracket for text from columns in the combo box? On Jul 28, 3:13 pm, asc4john <j...@kinnear.ca> wrote: > I am trying to get the text value from a combobox into a query where > clause like so > > Like "*" & Chr(34) & [Forms]![frmItemTypes]![cboItemDivision]. > [C...

Display result of crosstab query in form
Hi. In Access 2007 I want to display the result of a crosstab query in a (continuous) form. I've code that puts the field names of the query into the Caption of the labels and ControlSource of the textboxes, but when I open the form the #NAME? error is displayed in the textboxes. Even in design when I select the names into the ControlSource I get the message that the name is not in the list! What is happening and how can I get it working? Thanks in advance, Ronald. In a crosstab query, the column names come from the field you specified for Column Headings. There...