Help! - Appending only the new records on one table to another tab

I am trying to automatically take the "badge number" fields on one table and 
automatically have them added to the "badge number" fields on another related 
table.  I only want the new records entered in on the inital form to be added 
to the secondary table.  I already have the old records added.
0
Utf
9/10/2007 8:02:04 PM
access 16762 articles. 3 followers. Follow

4 Replies
863 Views

Similar Articles

[PageSpeed] 5

Why?

If you already have the values in one table, why are you trying to add them 
into another table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CarrierCunliffe" <CarrierCunliffe@discussions.microsoft.com> wrote in 
message news:795DE5D5-AFA6-4DDC-94CA-C64F2BF963C7@microsoft.com...
>I am trying to automatically take the "badge number" fields on one table 
>and
> automatically have them added to the "badge number" fields on another 
> related
> table.  I only want the new records entered in on the inital form to be 
> added
> to the secondary table.  I already have the old records added. 


0
Jeff
9/10/2007 9:15:18 PM
It isn't necessary (and is somewhat undesirable) to have "empty" records in 
a second table so as to be able to count who has NOT done something.

Instead, a simple query matching folks in the employee table with folks in 
the training table also gives you a count of those who are NOT in the 
training table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CarrierCunliffe" <CarrierCunliffe@discussions.microsoft.com> wrote in 
message news:399C2D33-B215-4A3B-87FE-8DEA7AC9C5B6@microsoft.com...
>I have an employee data information table and then an employee training 
>table
> that will  calculate the precentages of those who have gone thru training 
> as
> well as lists those who have not.  The main employee data information is 
> used
> and related to several tables along with this training table.  In order 
> for
> our precentages to stay up-to-date every time a new employee is
> added/deleted, I need that information to automatically be added/deleted 
> to
> the training table.  When I created a relationship to cascade it would do
> just what i needed to on the form for the traini ng but would not add the 
> new
> badge number to the table so i tried running an append query after new
> employees are added to the employee information table. However, I don't 
> want
> it to try to add all the badge numbers every time, just the new ones 
> entered
> - otherwise i will always get an error message saying so many records 
> can't
> be added due to duplicates (which is fine that's what i want i just don't
> want to see the error message all the time)
>
> "Jeff Boyce" wrote:
>
>> Why?
>>
>> If you already have the values in one table, why are you trying to add 
>> them
>> into another table?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "CarrierCunliffe" <CarrierCunliffe@discussions.microsoft.com> wrote in
>> message news:795DE5D5-AFA6-4DDC-94CA-C64F2BF963C7@microsoft.com...
>> >I am trying to automatically take the "badge number" fields on one table
>> >and
>> > automatically have them added to the "badge number" fields on another
>> > related
>> > table.  I only want the new records entered in on the inital form to be
>> > added
>> > to the secondary table.  I already have the old records added.
>>
>>
>> 


0
Jeff
9/11/2007 12:09:29 AM
On Mon, 10 Sep 2007 13:02:04 -0700, CarrierCunliffe
<CarrierCunliffe@discussions.microsoft.com> wrote:

>I am trying to automatically take the "badge number" fields on one table and 
>automatically have them added to the "badge number" fields on another related 
>table.  I only want the new records entered in on the inital form to be added 
>to the secondary table.  I already have the old records added.

What in your table identifies new records?

Are you trying to create empty "placeholder" records in a new table? If so,
don't; they're essentially NEVER necessary. Normally one would use a Subform
on the badge-number form to enter data into the related table; if you use the
badge number as the Master/Child Link Field it will fill in automatically when
you add data to the secondary table. It's not necessary to "preload" them.

             John W. Vinson [MVP]
0
John
9/11/2007 12:19:25 AM
On Mon, 10 Sep 2007 14:36:01 -0700, CarrierCunliffe
<CarrierCunliffe@discussions.microsoft.com> wrote:

>In order for 
>our precentages to stay up-to-date every time a new employee is 
>added/deleted, I need that information to automatically be added/deleted to 
>the training table. 

Ummm...

No, you don't.

You need to correct your query to use a Left Join so that employees without
training records are handled correctly, without any need to create empty
records in the training table.

If you'll post the SQL of the query that calculates your percentages, someone
should be able to show you how this can be done.


             John W. Vinson [MVP]
0
John
9/11/2007 12:22:04 AM
Reply:

Similar Artilces:

Coloured tabs in tabular form
I would like to change the background colour of the pages of a tabular form. Is it possible to te pages in a tabular form another colour instead of white/grey? Is it possible to highlight (boldface text e.g.) the tab header of the selected tab? Thanks for any suggestion/answer to the questions above. Ron Have a look at http://www.lebans.com/tabcolors.htm --=20 Joan Wild Microsoft Access MVP "RoBo" <RoBo@discussions.microsoft.com> wrote in message = news:9E45C48E-D0D5-4C5A-86F0-A41D130759A3@microsoft.com... >I would like to change the background colour of the pages ...

120= rupees one hundred twenty only
i just want to know the formula which translate digit to text for example if 120 written in any cell and other cell we want result "rupees one hundred twenty only" See one or more of these Excel converting numbers to text http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 http://www.codearchive.com/code/0200/0220-currencytextfunction.txt http://www.xldynamic.com/source/xld.xlFAQ0004.html http://www.ozgrid.com/VBA/ValueToWords.htm best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEM...

get SQL Server text data type into Excel pivot table
Is it possible to get the full text of SQL Server TEXT data types into Excel pivot tables? SQL Server 7 and Excel 2002 SP3 I have a table that has "text" data type column. I have an ODBC connection to the database. I go to Excel's PivotTable Wizard and select the ODBC data source, and then the table and columns, but I only get a max of 253 characters in my pivot table for the text type column (I know it's being truncated). I've found some VB code to get CLOB data from various DB types but I still don't know if it's possible to get that into my pivot ta...

multiple reminders for one calendar event?
I would like to schedule a reminder about any work appointment, and then have a 2nd reminder for 45 days from the appt. to remind me when a payment check is available. I am using Outlook 2002, but will upgrade to 2003 in the near future. Is this possible in either version?Thanks, David ...

New Chart in Excel 2007 is NOT showed unless preview
I have a sheet with multiple charts in Excel 2007 one of the new created charts is hidden when I open the file Unless preview is selected, then finally turned on BUT many formats are changed and objects on charts (titles,...) can not be moved ! what's wrong ? Make sure you have installed SP2 for Office 2007 as this fixes some (not all) chart problems best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rolando" <Rolando@discussions.microsoft.com> wrote in message news:0B0A3E5C-AABD-4982-962B-8C5C09A2CC5A@microsoft...

I get wrong dates when i paste from a different sheet into a new s
Hello again, My problem now is when I open an old excel sheet with data on it and try to paste that info on a new sheet I get wrong information. example: I'm trying to copy dates from an excel sheet I have. The dates are for the year 2004. When I copy the data and paste it on a new sheet the dates I now see are 2000. I'm puzzled. What I find wierd is when I have clipboard open the information shows the correct dates but when it's actually pasted on the sheet it shows as 2000. I'm using Excel 2003, but I get the same thing when using excel 2002(xp) Thanks for your help,...

New users loose SMTP address
Scenario: W2k3, Exhange2k3 2 domain servers create a user on either domain controller, add the smtp address we want (from a large list), come back a few minutes later and the address will change to the system default. Notes; we dont run the smtp recipient policies as such - just a general one with all our smtp address suffixes, this problem only occurs on every four/five accounts created. At first we thought it was the individual support person, then blamed his PC, his MMC connection, now Tech servoces have found it occurrs to anyone creating large numbers of users. Thought it could be...

dynamically referencing to another worksheet
Hi all, I'm relatively new to Excel but I'm working on a spreadsheet for work. Let's say I have a workbook with a separate tab (worksheet) called "exam1". On the first worksheet I can reference to a cell (let's say A2) in the other by using "=exam1!A2". Here's what I need to do though. On the first worksheet in column A, I will have a list of all the tabs that will eventually be included in the spreadsheet (e.x. A1 = exam1, A2 = exam2, A3 = exam3, etc.). Is there a way I can modify the code from the first worksheet above (=exam1!A2) to be dynamic? ...

emails being lost
Hi we run an SBS2003 server with exchange. We also have GFI Mail essentials One student is sending us an email with an attachments. I can see that GFI are receiving the email and processing it OK if I then look at Exchange Tools > Message Tracking Centre, I can see the email. there are 5 entries: SMTP: Message submitted to advanced queue SMTP: Started message submission to advanced queue SMTP: Message submitted to catagorizer SMTP: Message catagorized and queued for routing SMTP: Non-delivery report generated. Can some tell me what is happening please? This student can send messag...

Multiple Conditions Formula Help Needed
I currently have a formula that I have used to calculate commisio rates. We had a 2 rate plan. If sales were below a certail threshol then apply this rate and if above apply this rate. I used an IF/THE formula. We have recently switched to a 4 rate plan, and I am unsur how to create a formula to reflect this. Please advise.. thanks dav -- Message posted from http://www.ExcelForum.com One approach would be a table to use with VLOOKUP. In the formula, where you now have a rate, use VLOOKUP. On Wed, 21 Jul 2004 16:53:11 -0500, chiwavdg <<chiwavdg.19rkim@excelforum-nospam.com>...

New Email addresses
I am looking for a way to change all of my users email addresses to use one standard naming convention. Currently we have a wide mix of formats. I would like to settle on firstname.lastname@domain.com Is there a tool or utility that I can use to force a change and make the new ones the default address as well as keeping the original address. thanks LDIFDE will do this. You could also add the new address to your recipient policy and force it to run, but that might have side effects you don't like. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!...

Adding new account to same bank
In Money, I have an checking account set up for a particular bank. I have since opened a new credit card account with the same bank. When I try add this account via. Account List- Add a New account - Choose account type (select credit card) - (Choose the bank) - I get the message "Your account list already contains accounts with (particular banks name). After Money updates your accounts, review the account list to see if it shows duplicate copies of any accounts... click "OK". If I click OK Money changes the exiting accounts type to a credit card account instead of adding...

how do i insert a background on one page only?
HOW DO I INSERT A BACKGROUND COLOR ON ONE PAGE ONLY? What version Publisher are you using? In 2002-03, format, background. The background will be on one page only. If you are using older versions, don't put the background on the "Background Page", insert it on the publication page. Draw a rectangle to fit your page, fill, fill effects...send it to the back. Or, insert, picture, new drawing, expand the canvas to fit your page, draw a rectangle to fit, fill, fill effects, picture tab, browse to the picture you want on the background. -- Mary Sauer MSFT MVP http://office.mi...

Help with linking information from one sheet to another
I'm trying to setup an excel workbook like this: First sheet is a master list with peoples names in the first row and and their stats in 5-6 cataogries along with a final point total in the colums to the left of each name. Then I want to use a second sheet so that when I type in the first couple of letters in a persons name it will finish the name for me and automatically copy the players stats and total into the colums on the left. I know it's possible to do since I've seen it before I just have no clue how they did it and was hoping someone hear could help me. Thanks in advan...

Is there a way to export Word Quick Styles to another computer?
Is there a way to export Word Quick Styles to another computer so you don't have to re-creat them? Custom Quick Style sets are stored as *.dotx files in the %appdata%\Microsoft\QuickStyles folder. Just make a copy of the file and paste it into that same location on the other computer. -- Stefan Blom Microsoft Word MVP "Joe" wrote: > Is there a way to export Word Quick Styles to another computer so you don't > have to re-creat them? ...

help #9
???? There... all better now? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Having searched the archives, hygdd@eedd2.com <hygdd@eedd2.com> typed: | ???? This is a multi-part message in MIME format. ------=_NextPart_000_0078_01C35EC3.E4226980 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable with what exactly? I only see a questionmark? -- Please go to my website: The Site Resource Vote For The Site Resource! <hygdd@eedd2.com> wrote in message = news:a34bjvk9fvkgdb...

New Calendar event only showing as all day tas
When I create a new calendar event, say for an hour. It creates itself as an all day event. (Outlook 2002) If I view the event in OWA the event shows properly as the one hour event. If I view the event and look at the scheduling tab, the event shows properly Sometimes the event will disappear in Outlook, but I can still see it using OWA. The users mailbox has been deleted and recreated (mail reimported using a pst file). This problem can be recreated on a different workstation. New profile did not fix problem. Thanks ...

how do I print multiple print areas on one page
I have a wide worksheet that is not very deep i.e. lots of columns but not many rows. I have it in landscape but it is still too wide to go on one page. I would like to be able to print the right hand side of the worksheet underneath the left hand side of the worksheet, on one page, rather than having to print out two pages. Is this possible? This is a common question. The only way to do this is to create what you want printed. In other words, copy all of your data to a blank sheet, then cut/paste to get what you want, then print it. You can do this with a macro and nev...

Need help plz! Unable to Send/Recv emails in OE.
I'm sorry if this is a dumb question but I just set up my Outlook Express and I'm unable to Send/Recv emails which is strange because I already have an msn.com email account which works perfectly but I wish to make OE my default email and that doesn't work. Whenever I try to send an email or press the Send/Recv button I keep getting these two error messages: 1: "The service is currently unavailable. Try again later. Account: 'MSN Mail Server', Server: 'http://services.msn.com/svcs/hotmail/httpmail.asp', Protocol: HTTPMail, Server Response: 'Service Unavail...

How do I subtract 20% from one column in Excel and place totals i.
I would like to know if someone knows the formula I use to subtract 20% from totals in one column on my spreadsheet to show totals in another column on same spreadsheet. Can anyone help? Spent way to long trying to figure out correct formula. =A30*80% assuming totals in A30 -- HTH RP (remove nothere from the email address if mailing direct) "Full Effect Landscaping" <Full Effect Landscaping@discussions.microsoft.com> wrote in message news:7ACF893B-54DF-4EE4-A931-3BB5FA365821@microsoft.com... > I would like to know if someone knows the formula I use to subtract 2...

How can I just count the records where the check boxes are "No"
I have a audit form and when something is not completed then it is not checked. I need to have a total at the end of each item to count how many records are out of compliance. Please HELP need by this afternoon Julie Gilliam <Julie Gilliam@discussions.microsoft.com> wrote: >I have a audit form and when something is not completed then it is not >checked. I need to have a total at the end of each item to count how many >records are out of compliance. Use a text box with an expression like any of: =Count(IIf([check field], Null, 0)) or =Sum(IIf([check field...

Using workflow to "share" a record with a "team"
Hi everyone, We were working on a project where, for one example, when someone creates a lead depending on some conditions the lead needs a "share" added to it for a particular "team". Any ideas? I can only see a way to "assign" the lead to someone. Thanks, Todd ...

Publisher Template page??? help
Okay I'm working in Publisher 2003 with a document that somebody else created. Whenever I go to "Insert page... " (by right clicking on the pages tabs at the bottom) and everytime I do I get a "Newsletter Pages" window asking me what kind of Page I want to insert. I have the options of selecting story, order form calender, etc and each option comes up with a different layout for the page. I can tell they are custom because each page has information specific to this company. My question is how to I modify (edit) these pages? They seem to be templates but only fo...

Outlook 2003 locks up while adding address to new mail message
I have Outlook 2003 installed with Business Contact Manager and Word as my email editor. Frequently, Outlook will lock up while I'm in the process of creating a new message and adding my first address in the To: field. I suspect that it has something to do with the AutoComplete feature as it appears to occur when the name is not automatically recognized or displayed even though it has been previously. Has anyone else encountered this bug and/or found a solution? I have the latest Outlook SPs installed. Haven't seen it, but it is more likely a corrupted profile than a corrup...

New Event Appears on Calendar
New events are appearing on my Outlook calendar that I have not created. They are blank execpt for the name "New Event" and the Start and End time are filled in. I did not create these events and don't know why they appear randomly on my calendar. I am using Outlook 2007, and the company uses Exchange Server 2007 SP2. -- Carolyn Do you use any addins? Sync with a device? Use a Dell laptop? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: ma...