Update and Append query

Hi,

I am currently writing a stores inventory database and have nearly completed
it except for one issue. When a stock take is carried out the user will
update the stock levels via the Audit process. I have therefore written 2
queries. One is an update query which will update the QtyInStock field in the
tbl_parts to the new value. The append query will append the  tbl_parts to
the tbl_auditadj. I do this so that the user can interrogate the tbl_adjust
via a date range to see when and what parts were adjusted via the audit. In
the tbl_auditadj i have the following fields to record the new values

StockPrevious
StockNewLevel

StockNewLevel is eqaul to txtAdj field on the form i use for updating the
stock level. What i also wish to do is record the old stock level in the
StockPrevious field. This will enable me to see what the changes are between
these 2 values and so know what the qty has been adjusted by. Unfortunately
the txtAdj field is being written to the StockPreviuos as well. Is there
anyway i can get the queries to "hold and write" the old stock level to the
StockPreviuos field. I presume this would be some VBA code but i am not sure
how to tackle it.

Hope this makes some kind of sense.

Thanks
Sean

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200803/1

0
ODR
3/20/2008 3:59:51 PM
access.queries 6343 articles. 1 followers. Follow

0 Replies
1241 Views

Similar Articles

[PageSpeed] 36

Reply:

Similar Artilces:

IMF Updates and UceContentFilter.xml
When I install an IMF update, it creates a new subdirectory under the MSCFV2 directory. Because I'm using customized content filtering, my understanding is that I must manually copy my MsExchangeUceContentFilter.xml file to the new directory, and then manually run 'regsvr32' to register the new MsExchangeUceContentFilter.dll file. Has anyone written a script to automate this process? Or is Microsoft considering automating this process when a new verion of IMF is installed? -- Kevin Weilbacher [SBS-MVP] "The days pass by so quickly now, the nights are seldom long" ...

Two different queries return same results
I’m calculating average returns for stocks over two time periods; 30-days and 90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)>=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![c...

rgxextract() in a query to extract any one of many possibilities
I am using access 2003. rgxExtract() from http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm#Code has helped me solve a problem partially but if anyone can tell me how to get rgxextract() to find in a text Field "this or that or the other" heres whats going on; I am pasting information into a Field, and rgxextract in a query looks through the data that can be in any order to find what I ask it to. However the data doesn't cooperate as easily. for example the pasted data will read "a cute 3bdr house for rent" and the following will extract the ...

Updates Failing
Howdy, I just got Microsoft Money 2007 Premium Version 16.0.125.162 and it will not download/install the updates no matter what I do. I've installed and uninstalled the software, attempted to download the updates again and it is not working. Can anyone give me a work around or a fix for this? I've been looking around the MNYCoreFiles and it appears the files are downloading it just is not applying the updates preoperly. Thanks, owen Delete your temp files. Then open the money sample file and see if the updates are applied. If you still are having problems search the news grou...

Cross Tab Query
I have a access database with SQL backend. The table labproofs has charges listed by report date, with the below query, I can run a cross tabquery with the end date selected from a form (monthly report), and it gives me the 12 month data from the end date selected. However, the problem I face is for example if the end date selected is feb 28th, I get the data for previous months as Jan 28, Dec 28, Nov 28 ......, I want data for the month end numbers like feb 28, Jan 31, Dec 31....., I am not sure what I am doing wrong here. Please help. Gmen PARAMETERS [Forms]![Monthly Reports]...

T-SQL query for finding shared user and team records
Hi all, I'm new to CRM and would appreciate any help. I need to find account records that record owners have shared to users or teams in CRM. I've found out that this requires direct query to filtered view tables. Microsoft responded as below by I need actual T-SQL query. Can anyone help with this? You would need to write a query against the PrincipalObjectAccess table and you would need to have joins to the other related tables. If you tried to have it all displayed using one query, it would be a pretty complex query. This is because you would need to join to the FilteredA...

Updating Accounts #2
I just upgraded to Money Plus Deluxe (Have XP) When I go into Money it states its "updating account" and my CPU usage is at 99% when I run Money. There are NO accounts that are being automatically updated as I do them all manually. The "updating accounts" icon stays up until I finally have to turn off computer because I cannot work on any other part of money as it says it is doing an online function. PLEASE HELP.. It also goes online to seek its own updates. Do you have SSL2 enabled in Internet Options? Do you have firewall software enabled beyond the XP firewall...

Updating 2004 FI list
Does anyone know how to update Money 2004's Financial Institution list? When I check to see if my FI is online and supported, it is. When I check the Money 2004 list, it is not there. Any solutions? Thank you, any help would be great. -- Thanks, Andrew ...

queries written inside MS-Access are getting deleted
Some of the queries I have written inside MS-Access are getting deleted automatically. And while I run the queries through code, I get this error 'Query should have one destination field' Explanation: I created a query in MS-access. Ran it from the code. Closed the database. Started it again, and now for that particular query, it is showing 'SELECT ;' only. Strange. I am in panic mode now What can be the possible reason? My first thought is that the database file is corrupt. Unfortunately it's a type of corruption that Compact and Repair probably won...

Import and Export from/to Excel query
I need to import and export Excel files in and out of Outlook 2003. When I try and do it I am told I don't have the correct translator and do I want to install it - when I say yes it tells me to put in the CD - when I put in the CD it doesn't install but just freezes up and I have to cancel - the file it tells me I need is L4561403.CAB How and where do I install L4561403.CAB which I assume that I can find on the CD?? TIA for any help. Brian Tozer KiwiBrian <briantoz@ihug.co.nz> wrote: > I need to import and export Excel files in and out of Outlook 2003. > When ...

Table and query question
I would like to create a table that will relate an account to its parent(s). 1 account can have multiple parents, and 1 parent can have multiple accounts. And, a parent can have a master parent. For ex: Account 1 can have Parent 1. Account 2 can have Parent 1 and Parent 2. Parent 1 can have Parent 2. And, the queries that I will perform will request these data: 1. give me all parents of Account 1. The query should return Parent 1 and Parent 2 (because Parent 2 is the parent of Parent 1) 2. give me all parents of Account 2. The query should return Parent 1 and Parent 2 3. give...

Stumped with LIKE query
I have a MS Access database (from Office XP) to catalog my movies in. Here's my dilemma... I have Shrek 1 an 2. From within Access, how can I query the names so Shrek and Shrek 2 are both returned? I either get one or nothing. Here's what I have tried (among other things): SELECT Table1.MovieName, Table1.MovieYear, Table1.Category, Table1.Rating, Table1.Plot FROM Table1 WHERE Table1.MovieName Like [Enter Movie Name]; That returns only "Shrek" and not "Shrek 2". ------------------------------------------------------------------------------ ----- SELECT Table1.M...

home page won't change when updated, how do i fix?
Publisher 2007 used to create web page. After initial publishing of web page, some changes were made to update site. All other pages updated, however, the home page won't update. How do I fix this? Thanks When you Publish to the Web, Publisher produces an index.htm file (your home page) and an index_files folder that contain all the other pages on the site plus the supporting graphics. You must upload both the index.htm file and the index_files folder. Perhaps you did not upload the index.htm file. Perhaps your old index.htm file was not overwritten. Try uploading it again, ...

"No objects need to be updated"
I am getting the message "No objects need to be updated" even though I am changing some field properties of an object that has been placed in a form. For example, I am: -Changing the column width of a lookup value list. -The change in width shows in the Field Properites in the table design view. -I receive the update objects icon and I select "yes". -I then receive the message "No objects need to be updated" when that is untrue. I've never had this happen before, so I'm unclear about how to troubleshoot from this point. Any suggestions? ...

xpath query #4
I’m trying to pull up a NodeList of all the Grant elements in the AccessControlList element of the xml document displayed below. However I’m not getting the list using the following code: XmlDocument doc = new XmlDocument(); doc.LoadXml(“MyXml”); XmlNodeList nodes = doc.SelectNodes("/AccessControlPolicy/AccessControlList"); What am I missing? Scott My Xml: <?xml version="1.0" encoding="UTF-8" ?> <AccessControlPolicy xmlns="http://s3.amazonaws.com/doc/2006-03-01/"> <O...

Need help with updating XML file
Hi, I am using an xml bound to a datalist to display photos in a gallery. XML file is generated and then bound to the datalist. What i want to do is edit the captions for the photos by clicking Edit in the datalist and then update. My file looks like the snippet below. For example i want to just update the caption for the first photo. Any help is greatly appreciated. XML is not my forte. <Photos> <Photo> <PhotoID>1</PhotoID> <Caption></Caption> <FileName>DSC00067.jpg</FileName> <FilePath>~/photos/Stills/DSC00067.jpg</FilePath&g...

Need Help with a formula in a query!
I am working in a query. I have a field called DOB and a field called Classroom. I am trying to come > up > with a formula that will calculate the Visiting date that I put in my form. The formula for the form does not work in the query. The Visiting date that will show if a Child's > Classroom = Bumble Bears, the visiting date needs to tell me the date > on his 15 months, when the Child's Classroom = Doodlebugs, the > visiting date needs to tell me the date on his 33 months. > > Can anyone help me. I have such a hard time with IF statements. Your > help is ...

Parameter Query For Selected Dates or All Dates
Hi Folks - I'm having a senior moment. I have a query with a date field. I want to use Between [Start Date] and [End Date] to choose a date range, but if the user leaves the parameters blank, I want all records returned. I was able to do this when I passed a form control to the query, but I can't figure out how to do it within the criteria row of the query. Any help would be appreciated. Thanks. -- Michael On Wed, 12 Dec 2007 17:02:00 -0500, Michael wrote: > Hi Folks - I'm having a senior moment. I have a query with a date field. I > want to use Between [Start Da...

One prompt for Four Queries
I have four queries that are used as subreports in a summary report. All four queries use the same prompt (for the same field): [Enter Patient Contact ID] How do I get only one prompt so that the keyed in patient contact id is used for all four queries? =?Utf-8?B?V2VuZGVsaW4=?= wrote: > > I have four queries that are used as subreports in a summary report. > > All four queries use the same prompt (for the same field): [Enter Patient > Contact ID] > > How do I get only one prompt so that the keyed in patient contact id is used > for all four qu...

Refernce other files and auto update info
I need to create a master file which lists all projects an employee is working on all in one tab. It also has to automatically update as the employees enter their information in order to keep track of employees who might be looking for work or not. So I need in Master File cells from A1 to D6 reference a file from employee #1. Than in the master file have cells from A8 to D13 reference a file from employee #2 and so on. I know it can be done, but just don't know where to start. If desired, send your file to my address below. I will only look if: 1. You se...

Chinese letters in a Query
Hi all, i have this strange issue: I just converted to 2007..and in a memo field of a query i receive chinese letter???? what have i done wrong? See if this link about memo fields in queries helps. http://allenbrowne.com/bug-16.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Peter" <Peter@discussions.microsoft.com> wrote in message news:BC86408F-9337-4E09-BE4A-52E50FC912A2@microsoft.com... > Hi all, i have this strange issue: > > I just converted to 2007..and in a memo field of a query i receive > chinese > ...

See the corrective update from M$ Corporation
--baafsoueluxicnnqg Content-Type: multipart/related; boundary="nskitywk"; type="multipart/alternative" --nskitywk Content-Type: multipart/alternative; boundary="ycrgymulabjsh" --ycrgymulabjsh Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to ...

Crosstab Query
I've created a crosstab query that counts the number of calls per month. The call field only contains a date. I've used that field as the column header and chose to group it by month, but can't get it to seperate the months by year. For instance all of March is grouped together whether it's March, 2009 or March, 2010. I'm pretty new to Access so I'm not familiar with SQL or VBA. Any assistance would be greatly appreciated. Thanks, Nate Access 2003 "Nate" <Nate@discussions.microsoft.com> schreef in bericht news:CAC55303-3AA4-4057...

Date query 08-03-07
Hi I'm trying to write a query that selects records based on a date range and Uses the Date of Birth if the Date entered is blank (null) or uses the date entered if that date is filled in. My query works fine for the first condition where I say if "date entered is null" and DOB between the 2 dates. My problem comes in when I put in the second line in the criteria and say if the date entered is between the 2 dates. Any help would be appreciated. Thanks Jeff ...

Using Queries in Excel
What are the best practices for using database-like queries in Excel. Let's say you wish to join to sheets together och view a subset of columns in a third sheet. I've tried several different methods, but I dont think any of them are completely good. I've used VLookup, Index, MS Query. (MS Query must be the must forgotten MS product in history. It's like a time machine back to Windows 3.11) I've also tried alot of different methods for searching a range, based on more than one criteria, and display the result, either a single value or a sum based on several rows. Here i&#...