Insert query using date as where condition
I have an insert query that runs every day at midnight that records our
client's current environmental status (it's for a mental health facility).
Here's the code:
INSERT INTO dbo.tblCensusHistoryWithDischarge (FullName, LastName,
FirstName, FileNumber, ClientNumber, StaffID, Environment, Location,
FundSource, Act10, StartDate, ExpEndDate, EndDate, CensusTimestamp)
SELECT TOP (100) PERCENT dbo.tblClients.LastName + N', ' +
dbo.tblClient...Counting Number Records per Unique ID, saving it to another table:
I have a form frmVendors with a subform frmPayments. The main form shows
information on vendors, and the subform is a continuous form which lists the
checks paid out to each vendor. Users are typically entering new payments
into the subform on the main form.
The forms are linked to tables: Linked by VendorID (one to many)
tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor
tblPayments: Key=CheckID, VendorID, PaymentAmount
There is a field in tblVendor called NumberChecks to show the total amount
of checks paid out to that vendor. There is a text box on th...Writing to web-based database
I am trying to build VBA code that will write to a database locatedona web server. Database is MS Access now, but could use SQL Server orsomething else SQL based. How can I automate the process of opening,appending, and closing a database when the Office application is onthe local computer but the database is on a web server?...Update Query question 06-26-07
Hello, I am trying to write what I assume would be an update query. I have 2
fields, acct_num and brnch_num in my table. I need to combine these 2 fields
into a new field called cust_num. So, if:
Then I would need the cust_num to read: 12345678, 12300012, 01200001
I'm not sure how to get the zeroes into the cust number so that that number
is always 8 diguts. Any help would be greatly appreciated!
I wouldn't use an update query for this. Keep the data separat...Query Not Returning Correct Amount of Records
I have 6 tables that I have built 6 different queries on. Individually,
these queries return the correct amount of records in the tables. Which is
80 records in all tables involved.
Then I built a query that collects data from the 6 queries and this query
returns 75 records. What am I doing wrong?
As a guess, your query is not correctly structured for the data you have.
For instance, if one of the 6 tables does not have a matching record that
could cause you to "lose" a record or two in the query if you have set up a
join to that table and have n...Query based Subform will not allow editing
I have a maintenance DB that has energy lockout points associated with pieces
of equipment so we can safely do maintenance.
I have an 'edit existing lockout' form, based on a query, that lists in a
subform the various points required for a certain piece of equipment.
My system used to work, but now I can edit the main, but the subform has
locked me out. All I get is a doorbell tone when I attempt to enter info. I
can enter the req'd info in the tables, but but my form went snafu.
Any ideas? Thanks in advance
Does your query allow editing? Check the asterisk in the record...how do I record times from track meets and find averages
I need toot been able keep track of athletes time and find averages. I have
not been able to figure it out.
go over to debra's site:
some files have been provided for download. check what fits you
Message posted from http://www.ExcelForum.com
...Need help with SQL Query
Our database consists of over 10,000 items. I'd like to re-organize
and format alot of the items we have in our database. For instance,
I'd like to do something simple like search all items in the database
with a description of "7 1/8" and replace it with "718" and so on.
Is there a site or a point of reference to refer to for using basic SQL
commands for use with RMS? Another example of where I could use SQL
would be for assigning select dept's/cats/items for a certain sale
promotion instead of having to goto the properties of each item and
manually selecti...Using an equation to count non-duplicated items in column
I have a list of trailers in a sheet that are designated as T-####. Is
there a way to count the number of non-duplicated entries in this
column by using a formula?
Thanks for any assistance,
Please respond to hers2keep @ yahoo . com.
Try Chip Pearson's site........
BTW. The customary response is to the News Group, not email.
That way we all learn.
Gord Dibben Excel MVP XL2002
On 28 Aug 2003 14:30:58 -0700, email@example.com (carla) wrote:
>I have a list of trailers in a sheet that are designated as...textbox query
I am using a userform to have data transfered to a sheet. I currently use
the code below to send it to a worksheet but what I would like to have
happen is for it to add the total of the textbox to the total already in the
R = 3 'or whatever desired row variable
Sheets(2).Cells(R, 5).Value = TextBox4.Text
Any help would be great
Thanks in advance
With Sheets(2).Cells(R, 5)
.Value = .Value + CDbl(TextBox4.Text)
"Greg B" <firstname.lastname@example.org> wrote in message
> I am using a userfo...export query with formulas to excel
Is there a way to export my Access query to Excel 2003 while keeping the
formulas in Excel. For example, in my Access query I have a field called
"Other" which is defined as the following:
[DiffTotal] and [BudTo] are "real" fields in the query and become columns in
Excel. Instead of having the value of "Other" appear in Excel, I'd like the
"Other" column to have the formula where, for example in row 1, it could look
When Access exports to Excel, it exports the...#Deleted# records in Form
I've struggled with this before. It's been quite a while since I programmed
When you delete a record from the source of a form, how do you get it to
stop showing up as #Deleted#? I've tried Requery.... ??
Here is a function that will delete the current record and make that
annoying #Deleted# disappear.
If you have a Delete command button on the form, call it like this:
I have this function is a Standard Module so I can call it from any form.
Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset
O...DNS issues, reverse lookup, MX records
I've done some searching in the groups, but I'd like to pose a specific
scenario. We are getting these errors:
#4.7.1 smtp;450 4.7.1 Client host rejected: cannot find your reverse
This is the situation. Our domain name is registered with network
solutions. Our website is hosted with Yahoo on a business account.
Yahoo used to host our email as well. Recently we put in a local SBS
2003 server to host our Exchange locally. Originally our Network
solutions account had all DNS pointing to Yahoo's name servers, as it
should have. We wanted to switch the MX reco...Display a count of unique records in a query in Access 2007
I'm not sure if I should be in this group, or Reports, but I'll start here.
I have a user who would like to do a count of unique records and display the
information in a query, or a report. Here are the basics:
She has a list of donors, some of the donors donate more than once to the
same cause. What she would like is a list of the number of donors, per cause.
But, if the donor ID repeats for the same cause, she does not want that ID
We can get a query to return a count of the records per cause. It is
returning a value of "7" for a cause,...can't record a new macro or insert module in editor
When i try to record a new macro it tells me it is unalbe
to record. When i go into the VB editor and go to the
insert menu all the items are greyed out so I can't
select module and so create a new macor that way. I have
changed the security off from high (tried on both medium
and low). Does anybody know why I can't make new macros?
Unfortunately the "Unable to record" message has several potential causes.
Suggest you wander through these google search results dealing with this
issue. Perhaps something will do the trick.
The Personal.xls file ...Run query from date input on form
I have what I hope is a easy situation. I have a table that has information
that is entered daily with a field "Completed Date". I want to add a text
box to a form where a user can enter a date and hit a button that will run a
query for that specific completion date so I can have the query results
exported to Excel.
Would I use an unbound text box on the form? How would I set up the query to
take the date entered in the form?
Thank you very much!
Message posted via http://www.accessmonster.com
Yes, an unbound textbox would be one w...Conditional formatting query #2
I thought I knew how to do this, but it turns out I don't.
If I have names in column A (eg Alan, Bob, Cathy), and values in column B
(see below), is there any way I can use conditional formatting to change a
cell in A, based on a value in B?
1 Alan 4
2 Bob 1
3 Cathy 3
Essentially what I'd like is to highlight the name, if the value is 3 or
I know how to change the cells in B using conditional formatting, but can't
figure out how to change A cells.
Select all the cells you want Conditionally formatted.
Try this with Formul...Subquery select field from previous record
I have the following table setup
Table Name - tblInventoryCounts
Field Names - Store Number, Count Date, Item name, Count Amount
Store Number Count date Item Name Count Amount
9 1/4/2010 Gear 10
9 1/11/2010 Gear 18
9 1/18/2010 Gear 18
I have a parameter query where the user is asked for the store number and
Count Date. I need for the query to return the information for the date
entered and the Count Amount from 7 ...able to record & run macro but not edit !
When I open a file with an existing macro (or create a new spreadshee
and simply record an entry in a cell ) and go to Tools/Macro/Macros th
macro list is visible and I can run the macro but if I try to edit it
get a Microsoft Visual Basic pop-up 'Unexpected error ; quitting' o
if I simply try to open the VBE i get a similar pop-up with ' Out o
Same files work fine at work (Excel 97) where I edited macros n
I've tried uninstalling/reinstalling Excel from the office 97 dis
along with data access & Office tools (and their elements) ti...Double Results in Query
New to Access
I am trying to create a query that returns data from 2 tables. one table can
have multiple entries on one day per person and the other table will only
have one entry per person.
2 tables are
DataEntry - will have multiple entries per day per person
CallLog - one entry per person per day
SELECT DataEntry.OrderTakenBy, DataEntry.Company, DataEntry.Date, DataEntry.
Category, DataEntry.ShortCode, DataEntry.ContactName, DataEntry.PositionTitle,
DataEntry.PostDateExt, DataEntry.FirmPreview, DataEntry.AllorProgramCodes,
DataEntry.Units, DataEntry.value, DataEntry.Mark...Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B
fileA Col B of FileB as shown in example.
Each col have about 1000 rows.
Each row contains thousands of words and phrases separated by Comma.
As you can see from example, my data has soo many words and phrases
separated by comma in each row of two colomn A and B.
i Need to merge data of corresponding row from COLA$FileB TO
corresponding row of COLA$FileB
Also merge data of corresponding row from COLB$FileB TO correspondin...querying similar databases plan
I'm working with hideously unnormalized databases, most of which have
similar structures. (Of course, if they had the same structures, it
would be too easy.)
I work at a company that does cancer research, so they collect
information about their patients over a series of visits. The
questions they ask (queries) for parts of the final report are
standard across all databases, and then some are specific to a the
type of study they're doing.
What is the best way to approach this, given that I have to summarize
like 20 databases in maybe two weeks, and the column names are not
necessari...Query Needed 01-04-10
A sample of my data table looks as so:
Underlying ID StrikePrice
AAPL APVA10C155.00 155
AAPL APVM10P155.00 155
AAPL APVA10C160.00 160
AAPL APVM10P160.00 160
AAPL APVA10C165.00 165
AAPL APVM10P165.00 165
AAPL APVD10C160.00 160
AAPL APVP10P160.00 160
AAPL APVD10C165.00 165
AAPL APVP10P165.00 165
AAPL APVD10C155.00 155
AAPL APVP10P155.00 155
AAPL APVB10C155.00 155
AAPL APVN10P155.00 155
AAPL APVB10C160.00 160
AAPL APVN10P160.00 160
AAPL APVB10C165.00 165
AAPL APVN10P165.00 165
GS GPYA10C155.00 155
GS GPYM10P155.00 155
GS GPYA10C160.00 160
GS GPYM10P160.00 160
...Query in Excell
I would like to create bolean statements in one or more of
the columns that result from a query. Basically,
I want to test a column for a factor and return a
simple 'true' or 'false'. Using this I, hopefully, will
eliminate the need to pass the data to Excel and perform
the function there.
I want to replicate an ODBC report for a number of different countrie
on different databases.
Currently I have to create a new file and query for each differen
Is it possible to change which database a query points to withou
re-writing it completely, as this would be much faster.
The databases appear to have identical attributes (tables, fields
Message posted from http://www.ExcelForum.com
usually all you have to do is change the file path. what
method are you using. how is the query pointing now.
>I want to replicate an...