Simple calc query
I am new to Excel and have Excel 2007 if that makes any difference.
I am just trying to write my own vehicle logbook for my own vehicle which is
used personally and for work. To denote this I have done the following:
value 1 value2 Notation work column Personal column
The above are different cells. Value 1 is the start mileage. Value2 is the
end mileage. Notation is either P or W. Work Column has the formula
=IF(Ex="P",Dx-Cx,) where the "x" equals the cell number (eg, E6 or whatever)
and of course it goes for rows from A downwards. The end result, so far, is
that...Database with only query relationships
I have been asked if there are any functional problems with a database having
only relationships defined by queries. Will it be stable? Will there be
issues and if so of what nature? Thanks!
On Wed, 23 May 2007 12:48:03 -0700, Cheese <Cheese@discussions.microsoft.com>
>I have been asked if there are any functional problems with a database having
>only relationships defined by queries. Will it be stable? Will there be
>issues and if so of what nature? Thanks!
If there are no relationships defined between the Tables, then relational
integrity will *not* be enfo...need help with query ...
I am looking to write a query with multiple factors and do not know
the best way to do it.
I want use Event Type, Status, and Region as my queries variables. By
default I want show "all", but then be able to use drop downs to
Can anyone please give me an idea on the best way to do this? If this
does not seem to make sense, please let me know.
To be able to have combo boxes for query parameters, you must create a form
with an unbound combo box for each parameter.
See http://www.mvps.org/access/forms/frm0043.htm at "The Access Web" ...Number of query values and destination fields are not the same.
[Microsoft][ODBC Microsoft Access Driver] Number of query values and
destination fields are not the same.
I am getting this error when i insert the data through JDBC
You are probably appending records to a certain table. In this case you might
have selected 10 fields to append when there are only 9 fields in the
receiving tabel or something like that.
Go at it field by field. The field that i want to append does it exist in
the receiving table etc... do this one by one and you'll see the one that's
> [Mic...Update/Append Queries minus confirmation Dialouge
Does anyone know how to silence the confirmation dialogue appear when you
run an update or append query. The first message box asks for confirmation
before you run and update table records. The second shows the number of
records that will be updated. I'm trying to automate an application with
various queries and macros and need to eliminate these message boxes from
Thanks for the help!
In a macro, you just need to put a SetWarnings/No action before the
first OpenQuery action.
Steve Schapel, Microsoft Access MVP
> Does anyo...Query-Based DL with email addresses that are external to exchange.
Exchange 2003 Enterprisse with SP2, running on Windows 2003 Server Enterprise
I am trying to setup a Query-based Distribution List that will allow us to
send to our students. The student accounts are in AD, but they do not have
Exchange mailboxes, their mail goes to another non-exchange system (IMail
Secure). The QBDL matches on the email field on the users account properties.
The QBDL does find all the email addresses that we want to send mail to. The
problem is that when we send a message to the QBDL (from either Outlook 2003,
Outlook 2007, or from the OWA) the message tha...Dcount: referencing a saved query field in a mathematical expression
Quick question regarding referencing a saved query field in a
mathematical expression for a dcount function.
I have a saved query called GetTmpTotPayoutFSR with the following
SELECT payee_id, period_id, manager_flg, financial_center, cp_spread
AS spread, annual_goal
This saved query is referenced in the following update statement that
utilizes a dcount:
UPDATE WORK_INDV_MGR_BUMP AS IMB SET tier_1_count =
dcount("[payee_id]","[GetTmpTotPayoutFSR]","financial_center = '"...Query leaving out some data
I think this must be a pretty easy question, but unfortunately I'm unable to
identify the solution . . .
CustomerID Company Name
3301 ABC Industries
3302 My Store, Inc.
3304 A1 Company
3305 Mom & Pop's Shop
Query should pull CustomerIDs for all Orders and list the CustomerID,
OrderID and Company Name. (SQL = SELECT Deliveries.CustomerID,
Cu...Dont need to use query and indexing feature of moss in project ser
we are going to deploy sharepoint portal server 2007 in existing environment
which is two server installation of project server 2007 . In which first is
server and second is database server.
Is it necessary that we start Sharepoint server search and use Query and
Indexing feature in
front end application server of project server 2007 after installation of
MOSS 2007 in front
end server ?
Because I need only sharepoint server 2007 in existing enviornment of
project server 2007
for proposal review workflow of...Show queries
How do I show all queries that a attached to a spreadsheet?
Varies a little depending on version, but if you go to Data>'Get External
Data'>'command referring to queries' it will take you to a navigator window
that lists the queries or a dialog box with a Queries tab. (The content in
single quotes will vary with Excel version.)
> How do I show all queries that a attached to a spreadsheet?
...Querying a list with Excel
I am starting out with a list like
Tech Date Code Actual Billed
DON 11/15/2004 E 5.40 6.00
DON 11/15/2004 W 3.00 4.00
DON 11/15/2004 E 4.00 4.00
DON 11/15/2004 DO 0.30 4.00
DON 11/15/2004 HD 8.00 0.00
Paul 11/15/2004 E 3.00 4.00
Paul 11/15/2004 E 2.30 2.00
Paul 11/15/2004 E 0.40 0.50
Paul 11/15/2004 E 0.60 0.50
Paul 11/15/2004 E 3.00 3.00
Paul 11/16/2004 W 3.00 0.90
Paul 11/16/2004 CV 2.00 2.30
Paul 11/16/2004 CV 2.40 2.00
Fred 11/15/2004 PDI 3.50 4.50
Fred 11/15/2004 W 0.30 0.20
Fred 11/15/2004 W 0.10 0.20
Fred 11/15/2004 W 2.30 3.00
Fred 11/16/2004 LD 2.00 1.50
Paul 11/16/2004 LD 3....Datasheet Subform Query
I have a Datasheet subform for which I have a field name CalendarYear which
basically lets me know that fees were received for that Calendar Year. I have
entered information for the 2009 Calendar Year and some information for the
2010 Calendar Year. I am trying to get my query to give me all records for
which fees have not been received for the 2009 and 2010 Calendar Years. I
tried the NULL function but that isn't working or maybe I am not using it
correctly. I placed IS NULL in the criteria for Calendar Year. Can this not
be done in a datasheet?
Thanks in advance for your assi...Query help 11-27-07
I'm trying to create a query that will calculate how much someone has
contributed year-to-date to their supplemental health care plan. Basically
the employee agrees to contribute an amount of their choosing. This amount
is then divided out so that the individual pays a certain portion of that out
of each bi-weekly paycheck. The part that is causing me headaches is that we
want to stop calculating a running total for those employees who terminate,
and instead calculate their contributions only up to their termination date.
Here's what I have so far:
This is what I had origin...can't rename field in MS Query?
I'm using Microsoft Query to bring in some data. I have the query in SQL so i
can do some groupings and count values in one of the fields. My problem is
I'm having difficulty renaming a field, so it comes back with no name at all.
The SQL line is:
SELECT [lots of fields], Count(StudentHistory.Status) AS CountStatus
The count comes back properly, but the changed field name (i.e.,
CountStatus) doesn't appear. The field name is blank. How does this need to
be written so the field name comes back as CountStatus?
...Total Within A Query
I have the following query the gives me the total Balance by pool. I'm
Trying divide the Sum of each pool to the over all balance within a query.
SELECT POOL, Sum(Portfolio.Balance) AS SumOfBalance
GROUP BY Portfolio.POOL
Pool Balance %
2006-01 10 67%
2006-02 5 33%
One way to approach this:
SELECT Pool, Sum(Balance) as SumOfBalance,
SumOfBalance/DSUM("Balance", "Portfolio") as Pct
GROUP BY PortFolio
Another that would work is the following. I believe this would be faster
with a lar...Query based on field in form, not working in 2007
After upgrading to Access 2007 (from 2003), I have a query that has stopped
working. It is something like
FROM [tbl names]
WHERE ([tbl names].firstname = [forms]![My form][firstname]);
It does not seem to register the value of the "firstname" textbox in the
form [My Form]. Accordign to the SQL profiler, the query just sends an empty
field "". Therefore instead of searching for a specific entry, I get all my
entries... The query seemed to work fine in Access 2002 and 2003.
WHERE ([tbl names].firstname LIKE "*" & [forms]![My for...I need to query the field name of "true" value out of 3 fields
On a dental screening form for kindergardeners, I have 3 fields (Class I
Cavities, Class II Cavities, Class III Cavities) with True/False datatype
(check box on form).
In any given record, all three fields may be False (no cavaties...) however
no more than one can be True, (when cavaties are present, are you with me?)
My question is this. How can I set up a query that tells me only the one
field name where value is "True", so I can build it into a report for each
I didn't design this database. If I had I would have made one field called
"Severity", with ...Help...Query Results Posted on a Form
I have created a form w/ almost all the fields from one table. I have three
particular fields that the users will input data, costs, into. I want the
fourth field to populate w/ the results of those three and then store itself
w/ the info the user input into the form.
I have created a query which will calculate that cost. I have attempted to
add it to the form with a combo/list box and added a button to run the query
for the user. I get the results but it's all the results of each record I
create versus just one.
I appreciate any help or insights..
Start by basing your form...Query to count time range
I need to count the number of calls between a time range. Data would need
to look like this:
Time # of calls
SELECT DatePart("h",[TableName].[Time]) as HourNumber
, Count([TableName].[Time]) as NumCalls
GROUP BY DatePart("h",[TableName].[Time])
In the query grid
-- add your table
-- add the time field twice
-- Modify the first to read
Field: HourNumber: DatePart("h",[Time])
...How to run a 'total' query
Hi,I'm relatively new to access and Im using access 2003. I created asmall database in an effort to track how much my friends owe me (justas a learning experience). I have two tables. 'Debts' and 'Debtors'The primary key in 'debtors' is used in 'debts' as the refference.In 'debts' I enter data in this format:Debtor ID (Primary Key from 'debtors')ReasonDateDueAmountPaid (checkbox y/n)On time (checkbox y/n)In the 'debtor' database I want a field for each debtor called 'TotalDebt'. How can I add up all the 'Amount Due' ...Query or code?
I have two fields: Category and NumberOfContracts. Category takes 3 values,
call them A, B, and C. For each record there are a number of Contracts
associated with the choice of Category. So my records look like:
There are other fields as well, of course.
What I want is to sum the number of contracts for each Category. I want to
generate a report that looks something like:
Count of All Contracts: 14 Total of A: 1 Total of B: 5 Total of C: 8
Can I do this using a q...Cross-Tab Query Question
I have a report that uses a cross-tab query. I had to set the column
headings in order to get the query to display in the report.
This is a report that is put out monthly and will only have details for 6
months worth of information at most. Once created, this report will get
handed off to another person to update and distribute.
Is it possible to get the cross-tab query to display without having to
manually type the months into the ColumnHeader property? Essentially, short
of writing code, is there a way to automate that process?
Sorry. Figured it out from an earlier post. Rerea...Adding filter to query
I'm trying to export to excel the filtered results of an underlying query on
a split form. In the on click event of a command button I create a querydef
and then use transfer spreadsheet to create an Excel workbook. The code
creates the spreadsheet, but it includes all the records. I need to make it
create only the filtered records. I've tried several methods, but can't seem
to figure this out. Any help will be greatly appreciated.
I just answered a question just like this yesterday. I got a green check for
it! Maybe I will get another today...
...Calculation In Query
I am new to Access, and am unsure how to run a calculation based off data in
my table using a query. I have 2 multi-column lookup fields in my table and
want to divide one by the other. I have a feeling that Access is trying to
use the data from the other column in the multi-column lookup. How do I make
certain that Access is only looking at the data that shows in the field to
perform the calculation? For instance, the multi-column looks up 1234 in the
first column of the drop-down and has 5678 in the second portion. When you
click it 1234 shows in the field, this is what I want it to d...dynamically choose columns in query
I'm trying to create a query such that when the user runs it, the proper
columns (fields appear). We have 12 columns, one for each month of our
fiscal year (which goes from July to June). Basically, we run the query once
a month. When we run it, for example in April, the months prior to April &
including April (this would be July thru April), will have $0. The months
after April (May and June) will have dollars. When we run this query in
April, we'd rather not see each of the columns for July thru April because
all the records have all $0 in those columns.