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.
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 :) ?
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.
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.
...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,
MCP-GP, MCT, MVP
East Coast Dynamics
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!
"Robert Stokes" <email@example.com...Exchange Information Store crashing
About every 24 hours I get this error.
Store.exe _ Application Error
The instruction at “0x0042466b” referenced memory
at “0x13547000”. The memory could not be “read”.
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 firstname.lastname@example.org
My first guess would be hardware problems. Are you checking your system and
application event logs on your server?
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,
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:
ID a b c
1 1 2
2 3 4
3 5 6
4 7 8 9
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);
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
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
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
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
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!!!
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.
www.In...Public Folders Stores on Front-ends
The only folders I see under the public folder store are:
Public Folder Instances
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
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
You won't get that fixed. Can't you use as header CAT_STREET:
> I have a query with a ...SQL Trace: "Normalizing" TextData Column Values to Obtain "Query Signatures" for Hashing
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
On Wed, 21 Mar 2007 12:20:25 -0700, JBeckett [MCSA]
>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
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
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
1. [FROM_ADD] > 0
2. the [FROM_ADD] > [TO_ADD]
3. if there is more than one segment with the same name...
the [FROM_ADD] < the previous road segment with the same
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,
> 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
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
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]!
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
In Access 2007 I want to display the result of a crosstab query in a
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,
In a crosstab query, the column names come from the field you specified for
Column Headings. There...