Update one table from another

I am trying to update one table that has one record for each employee(table 
1) with available vacation time.  The other table records every time off 
request(table 2) and how much time they want off.  I have the update query 
and it works fine.  The problem is that everytime it is ran every requested 
time off amount(from table2) is subtracted from the available time(table1) 
again and again.  I want the records for requested time(table2) to update the 
employee available time off(table1) only once, but keep the records on the 
table as that is the basis for a report.

Thank You,
Brett

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
0
Utf
2/14/2008 5:20:06 PM
access 16762 articles. 3 followers. Follow

3 Replies
455 Views

Similar Articles

[PageSpeed] 37

On Thu, 14 Feb 2008 09:20:06 -0800, yoshimarine
<yoshimarine@discussions.microsoft.com> wrote:

>I am trying to update one table that has one record for each employee(table 
>1) with available vacation time.  The other table records every time off 
>request(table 2) and how much time they want off.  I have the update query 
>and it works fine.  The problem is that everytime it is ran every requested 
>time off amount(from table2) is subtracted from the available time(table1) 
>again and again.  I want the records for requested time(table2) to update the 
>employee available time off(table1) only once, but keep the records on the 
>table as that is the basis for a report.
>
>Thank You,
>Brett

Don't store the available time off in ANY table anywhere. Instead, calculate
it using a totals query on demand.
-- 
             John W. Vinson [MVP]
0
John
2/14/2008 8:03:30 PM
"John W. Vinson" wrote:

> On Thu, 14 Feb 2008 09:20:06 -0800, yoshimarine
> <yoshimarine@discussions.microsoft.com> wrote:
> 
> >I am trying to update one table that has one record for each employee(table 
> >1) with available vacation time.  The other table records every time off 
> >request(table 2) and how much time they want off.  I have the update query 
> >and it works fine.  The problem is that everytime it is ran every requested 
> >time off amount(from table2) is subtracted from the available time(table1) 
> >again and again.  I want the records for requested time(table2) to update the 
> >employee available time off(table1) only once, but keep the records on the 
> >table as that is the basis for a report.
> >
> >Thank You,
> >Brett
> 
> Don't store the available time off in ANY table anywhere. Instead, calculate
> it using a totals query on demand.
> -- 
>              John W. Vinson [MVP]
> 
I have to have a record of the total available time somewhere so that a 
report can be ran to show how much time an employee has available.    I don't 
want to total the time used, but subtract the amount scheduled to be used 
from what's available.  Employees sometimes schedule vacations months in 
advance and I don't want to total or subtract those numbers until the 
vacation dates start.  If there isn't a simple way to do this, the only other 
thought I had was to create another table that will store all of the vacation 
scheduled dates and amounts as historical data and have another table that 
the employees can enter vacation times, this table will run an update to the 
available time table and then run an append to the historical data table(for 
the reports) and then run a delete query so that the same records will not be 
updated twice.

Thank You,
Brett




0
Utf
2/14/2008 8:21:00 PM

"John W. Vinson" wrote:

> On Thu, 14 Feb 2008 09:20:06 -0800, yoshimarine
> <yoshimarine@discussions.microsoft.com> wrote:
> 
> >I am trying to update one table that has one record for each employee(table 
> >1) with available vacation time.  The other table records every time off 
> >request(table 2) and how much time they want off.  I have the update query 
> >and it works fine.  The problem is that everytime it is ran every requested 
> >time off amount(from table2) is subtracted from the available time(table1) 
> >again and again.  I want the records for requested time(table2) to update the 
> >employee available time off(table1) only once, but keep the records on the 
> >table as that is the basis for a report.
> >
> >Thank You,
> >Brett
> 
> Don't store the available time off in ANY table anywhere. Instead, calculate
> it using a totals query on demand.
> -- 
>              John W. Vinson [MVP]


I have to have a record of the total available time somewhere so that a 
report can be ran to show how much time an employee has available.    I don't 
want to total the time used, but subtract the amount scheduled to be used 
from what's available.  Employees sometimes schedule vacations months in 
advance and I don't want to total or subtract those numbers until the 
vacation dates start.  If there isn't a simple way to do this, the only other 
thought I had was to create another table that will store all of the vacation 
scheduled dates and amounts as historical data and have another table that 
the employees can enter vacation times, this table will run an update to the 
available time table and then run an append to the historical data table(for 
the reports) and then run a delete query so that the same records will not be 
updated twice.

Thank You,
Brett

> 
0
Utf
2/14/2008 8:23:03 PM
Reply:

Similar Artilces:

Launching Form from another Form
Hi guys, I am sure there are a few ways, but what is the preferred way to launch a Form from another? When my user saves a new Case, I want to pop up the Contact form. -Ernst Ernst Kuschke (C# MVP) wrote: > Hi guys, > > I am sure there are a few ways, but what is the preferred way to launch > a Form from another? When my user saves a new Case, I want to pop up > the Contact form. > > -Ernst FWIW, a "Contact Form" can be displayed by navigating to the aspx page at "./SFA/conts/edit.aspx?id={contactGuid}" -Ernst ------=_NextPart_0001_08B36A04 Con...

HOW TO SORT A PIVOT TABLE
How to automatically make that the pivot tables sorts data by itself. Every time a change one value it mixes up my figures.... Right click in the field to be sorted to get a menu Click Field Click Advanced Choose the sort option. Regards BrianB ============================================= "Alex" <alexdfsvsdffgdfgsdg@skynet.be> wrote in message news:<e6Ur9J3hDHA.1692@TK2MSFTNGP09.phx.gbl>... > How to automatically make that the pivot tables sorts data by itself. Every > time a change one value it mixes up my figures.... THANK YOU VERY VERY MUCH "Br...

users can't login on one PC
I have 1 PC that was just upgraded to GP 10 SP 2 and now only the SA can login. When a regular user account tries to login they get the error: "The login failed. Attempt to login again or contact your system administrator" That same id is able to login on every other PC so I know it is not SQL DB security and the SA can login from this PC so i am pretty sure it is not the ODBC. Any help would be appreciated. Fliehigh OK So I figured it out this DSN was setup using the IP address of the SQl server and the rest where using the DNS name. I changed it and it worked great. Fli...

Question about moving Outlook 2003 from an old computer to a new one
Microsoft Office Assistance has the following article, which describes the details for moving Outlook 2000 to a new computer: http://office.microsoft.com/en-us/assistance/HA010549451033.aspx I was wondering what, if anything, has changed for Outlook 2003? I'm asking only becuase I can't find a similar article, and I'm going to need to do this later next week. Thanks! Larry Larry Kahm <lkahm@nospam_heliotropicsystems.com> wrote: > Microsoft Office Assistance has the following article, which > describes the details for moving Outlook 2000 to a new computer: &g...

Combo Box from data in another tab
Is it possible to create a combo box from data that's in another tab? I have a combo box that's from data in hidden columns. But now I need to move all that hidden data to another tab in the same file. But when I go into properties and go to the ListFillRange option, it won't let me enter another tab name. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 I have called data from another tab with a combo box by doing th following: First - name the range of the data on the other tab. This can be don with the shortcut on the toolbar (l...

Auto Update in Forms
Please help. I am totally terrible in Access and need simple instructions. I have a form that all it has is the employee name control and the employee id number control listed like this: requestor (employee name) tag number (employee id) Can someone give me a simple method for putting in the requestor name and the tag number will pop up automatically. Created a query called qrytaguser which has both fields in it to try to make this happen. Read several forums, but I don't understand VBA code to translate the suggestions. Thanks in advance. -- Johnny ...

cannot download updates for office mac 2004 student/teacher edition
Hello, I have not been able to download office updates for my 2004 version of Office Mac student/teacher since 2005/2006 at home. I first had it on my iBook G4 and received updates no problem until late 2005 early 2006. Then I could only get the updates when I used the open internet connection at the Apple store or another open connection not in my house. When I bought my new iMac G5 and installed the second of three licenses on this computer, I could not get updates for this one either. However, my friend, who is using the third license, had no problem getting the updates for his Macbook...

ALTER TABLE statements showing up in my stored procedures
Hi, Recently, I noticed some ALTER TABLE statements in my stored procedures. I have no idea where they came from. The only thing I can think of is that I do generate scripts to keep my local copy of the database where I develop my application and the production server in sync. I think these statements started showing up after I upgraded to SQL Server 2008 R2 (full version on production server and Express on the local machine). I just tried removing them from one of my stored procedures and they came right back. I then deleted the stored procedure and recreated it without t...

An automated way of comparing an old spreadsheet with an updated version
Hi, I recieve an updated spreadsheet each month showing the newer entries at the bottom of the sheet. What i need to do is find a way that this newer data can be compared to previous entries to see if it has been entered before (i.e Mr Smith was entered in Jan and again in May) Currently I am having to manually search through the spreadsheet (about 400 cells) and I figure there must be an easier way! Thanks in advance Alex Hi Alex Assuming that you have headers in row 1 and your data you are wanting to search is in column A, enter in a blank column =COUNTIF(A:A,A2) and copy down as far a...

Updating all Fields in Word Document
I need to update all fields in the Word document. I have some of the fields in drawing canvas too. I have tried this, which works fine, but except drawing canvas. http://word.tips.net/Pages/T003879_Updating_a_Field_in_a_Text_Box.html Thank you -- Regards Jan Kratochvil Windows Vista Business SP2, Office 2007 SP 2 Sub UpdateAllFields() Dim oStory As Range For Each oStory In ActiveDocument.StoryRanges oStory.Fields.Update If oStory.StoryType <> wdMainTextStory Then While Not (oStory.NextStoryRange Is Nothing) Set oStory = oStory.NextStoryRange ...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

Prevent auto recoloring of chart when using Pivot Table data
I have crated a Pivot tabel with data I want to show in a Chart. My problem is when I refresh data the colours of the "data series" changes. For exaple I want one of my series "OK" always be shown in green colour, but after a refresh of pivot all colours changes. This problem is not only colouring. If I have made changes to my chart so that one series ar plotted on a secondary axis, and I change it to another chart type (e.g. the secondary should be shown as a line instead of column) this also changes when refreshing the data. Most irritating! Anyone that knows how to p...

League Table
Hello - newbie poster here. I've been scouring this group during my current project and found much useful information - thanks to all. However I've come to a halt and need to ask for help on a specific question. I have a spreadsheet working out the Rugby Zurich Premiership scores (LOTS of formulae entering!). The ultimate is to display an automatically calculating league table from the datasheet. I've used the LARGE function to order the points for each team and VLOOKUP to pull in the corresponding team name, games won, lost, drawn, points for and against and the goal differen...

converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct?? Excel should recognize a contiguous range as a table, or list. Include headings in the first row, and leave at least one blank row and column between the table and other items on the worksheet. You can use the built-in data form to add records to the table (Data>Form). Or,...

display only one formula
i'm taking a computer test and i need to know how to display my formula when it prints so the formula doesn't calculate, but i only need one of the formulas to be shown the rest need to stay in calculated form. How would i do this? Hi Format the cell as Text, select the cell, press F2, and then Enter keys. -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets "norcalchick2207" <norcalchick2207@discussions.microsoft.com> wrote in message news:6F223ADF-4546-44C9-8BC8-6D2825872C10@microsoft.com... > i'm taking a computer test and i need ...

Updating a form after using a workflow rule to update the entity.
I have a workflow rule that, on create of a contact, calls an assembly and updates an attribute of that assembly. It works a treat, but after saving, the attribute does not appear in the form. I have to close and re-open the contact to see the attribute. Is there any way to have this display immediately? Thanks Saira ...

Problem with KB982526 Microsoft .NET Framework 3.5 SP1 Update for
If I install this update and then reboot my computer freezes right after the boot screen animation... The only way I can get my system back is to boot safe mode and uninstall the update.... Tried this three times, twice thru windows updates, once with the downloaded update files from microsoft I'm running Win7 ultimate x64 HELP! HOW TO Hide an [Optional, non-security] Update... http://www.sevenforums.com/tutorials/24376-windows-update-hide-restore-hidden-updates.html Tim S wrote: > If I install this update and then reboot my computer freezes right after > the...

News: Mac BU sweetens Office 2008 upgrade deal (Updated)
Microsoft has modified its special offer for potential Mac Office users. Those who are just dying for a copy of Office now but are waiting for the January release of Office 2008 can now buy any version of Office 2004 for Mac and upgrade to 2008 Special Media Edition for the cost of shipping. That's $6.99 in the US or $10 in Canada-I guess Microsoft hasn't heard yet that the US dollar is valued below the Canadian dollar now. But how is this different from the previous offer? In September, the "free" upgrade to 2008 was for an equivalent version of Office. So, for exampl...

Four New KBs Issued on Updating Office 2003 SP1 Aug 18, 2004 (from Sloan Crayton?--Thanks)
*Recent KBs that May Help with the Epidemic or Pandemic of Office 2003 Installation Problems* You cannot update your Office 2003 program to Service Pack 1 (August 17, 2004) http://support.microsoft.com/default.aspx?scid=kb;EN-US;884298 Description of numbering scheme for product code GUIDs in Office 2003 (August 17, 2004) http://support.microsoft.com/default.aspx?kbid=832672 Frequently asked questions about the local install source feature in Office 2003(August 17, 2004) http://support.microsoft.com/default.aspx?kbid=830168 How to troubleshoot an update installation by using log file...

Change multple table names
I have over 100 similar worksheets with two tables on each sheet, currently with excel inscrutable names. I would like to automatically change the names of the tables to reflect two things: 1. the name of the worksheet 2. conatentate sfr on the first table in the sheet and ct to the second table on the sheet. I've spent the last four hours searching the internet and playing around with this to no avail. Recording a macro gives me this: Range("Table.AthertonSFR[[#Headers],[Date]]").Select ActiveSheet.ListObjects("Table811131517192123").Name = &q...

Update Problem 06-29-10
Recently, I downloaded & installed updates by going to the Windows Update site -- just like I regularly do, . The update process went fine -- all updates were installed. NOW, however, the program (Acronis) that I regularly use to backup my hard-drive crashes with a BSOD specifying a kernel error. I guess that I will have to UNinstall these updates to be able to run that program. Is there any way I can find out which update is causing the problem so I only have to UNinstall only one of these updates? I use WinXP Pro SP2. Is there an echo in here? => http://groups.google...

Forward E-Mail to another account
Hello, Can Outlook-6 be setup to forward a copy of a e-mail message to another account. Thanks Clinton Yes, you can use the -Message Rules- option to do what you're askin for. Just go to Tools -> Message Rules -> Mail.. and from there on it really self-explanatory. In your case.. 1. pick whatever conditions you need (where the from line contains...) 2. select the option forward it to.. And you're done -- Heritag ----------------------------------------------------------------------- Heritage's Profile: http://www.msusenet.com/member.php?userid=425 View this th...

Pivot Table Problem #14
Hi All! I am using Excel 2003. I created a report by connecting to a OLAP cube having many Calculated members. When I am trying to group the members of a dimension which is connected to a cube,I am unable to see any of my calculated measure. Can anyone help in this regard..... Thanks in Advance Siv ...

Programmatically change an existing Pivot Table
Hello to everyone, wondering if someone has figure out how to change existing Pivot Tables. Once a pivot table is been created the SourceData is read only. Creating a new pivot table using VBA is too complex and time-expensive. Any suggestions? >Once a pivot table is been created the SourceData is read only. Naah. I've managed to update SourceData and refresh my pivot tables programatically (a whole set of 20+ pivot tables, actually). If I remember correctly, the hoop I had to jump through was making sure the string I fed SourceData was in the *exact* correct format. It's ...

Multiple maildomains on one exc2k3 srv
Hi Our company have multiple maildomains on one exchange 2003 server And we would like to send emails with all of them. its like this: if you send me an email on a user@new.com emailadress i want to answer u back on that adress...but my primary adress in exchange is user@old.com How do i set this up so my users can use this easy. i know i can change my primary adress...but thats not a solution for us...( we have 6 emaildomains) We are using w2k3 server with e2k3 with smtp. Hope someone can answer me Lars Have a look at ChooseFrom application here: http://www.ivasoft.biz/choosefr...