Simple Select Query

I am trying to perform a select query to select all records that are NOT in 
both tables.  So I have tblPROJHIST and tblWMSNEW and they both have project 
# fields.   What I am trying to do is find all the new project # records in 
tblWMSNEW that are not in tblPROJHIST and put them in tblPROJHIST.


Thanks
0
Utf
12/21/2007 7:17:00 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
673 Views

Similar Articles

[PageSpeed] 22

If the tblPROJHIST project # field is a unique index or primary key field, 
just do an append query from tblWMSNEW into tblPROJHIST. The unique index or 
primary key will prevent duplicate project #'s from being added to the table. 
You should get an error message saying how many records were not appended due 
to constraint errors.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"THINKINGWAY" wrote:

> I am trying to perform a select query to select all records that are NOT in 
> both tables.  So I have tblPROJHIST and tblWMSNEW and they both have project 
> # fields.   What I am trying to do is find all the new project # records in 
> tblWMSNEW that are not in tblPROJHIST and put them in tblPROJHIST.
> 
> 
> Thanks
0
Utf
12/21/2007 7:33:00 PM
Is this a one-time cleanup effort, or part of a larger strategy of putting 
records in one table, then moving/copying them into another table?  If the 
latter, that's a very spreadsheetly way of approaching this.  (hint:  that's 
not considered a compliment in these newsgroups...<g>)

Regards

Jeff Boyce
Microsoft Office/Access MVP

"THINKINGWAY" <THINKINGWAY@discussions.microsoft.com> wrote in message 
news:0DFEA625-23DE-4BD5-952C-2226969B9F18@microsoft.com...
>I am trying to perform a select query to select all records that are NOT in
> both tables.  So I have tblPROJHIST and tblWMSNEW and they both have 
> project
> # fields.   What I am trying to do is find all the new project # records 
> in
> tblWMSNEW that are not in tblPROJHIST and put them in tblPROJHIST.
>
>
> Thanks 


0
Jeff
12/21/2007 7:34:55 PM
It is a part of a larger strategy of importing data from spreadsheet that is 
the only source for the data (read produced by another business entity using 
another enterprise database solution).   Additionally, I am exceedingly (or 
excruciatingly) new at this and you may feel free (I would say in my case 
obligated) to point out any inefficiencies with associated recommendations.   
Prefer not to recieve criticisms without alternatives/suggestions though 
because they're like driving down a dead end alley at 90mph (read, doesn't 
ever result in anything good).  

Thank you,

"Jeff Boyce" wrote:

> Is this a one-time cleanup effort, or part of a larger strategy of putting 
> records in one table, then moving/copying them into another table?  If the 
> latter, that's a very spreadsheetly way of approaching this.  (hint:  that's 
> not considered a compliment in these newsgroups...<g>)
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "THINKINGWAY" <THINKINGWAY@discussions.microsoft.com> wrote in message 
> news:0DFEA625-23DE-4BD5-952C-2226969B9F18@microsoft.com...
> >I am trying to perform a select query to select all records that are NOT in
> > both tables.  So I have tblPROJHIST and tblWMSNEW and they both have 
> > project
> > # fields.   What I am trying to do is find all the new project # records 
> > in
> > tblWMSNEW that are not in tblPROJHIST and put them in tblPROJHIST.
> >
> >
> > Thanks 
> 
> 
> 
0
Utf
12/21/2007 8:48:00 PM
To return all records in WMSNEW that are not in ProjHist (based on the 
field Project #) you would use a query that looked like the following.

SELECT tblWMSNEW.*
FROM tblWMSNEW LEFT JOIN tblProjHist
ON tblWMSNEW.[Project #] = tblProjHist.[Project #]
WHERE tblProjHist.[Project #] is null

You should be able to use the UNMATCHED query wizard to build this query.

You can then use this query as the basis of an insert query to add the 
records from tblWMSNew.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


THINKINGWAY wrote:
> I am trying to perform a select query to select all records that are NOT in 
> both tables.  So I have tblPROJHIST and tblWMSNEW and they both have project 
> # fields.   What I am trying to do is find all the new project # records in 
> tblWMSNEW that are not in tblPROJHIST and put them in tblPROJHIST.
> 
> 
> Thanks
0
John
12/21/2007 10:18:12 PM
So, part of an ongoing process, rather than one-time...

In that case, consider spending the time to familiarize yourself with 
normalization and with relational database design.

Typically, spreadsheets are not (and usually cannot be) normalized, so the 
tools are designed for that.

Access' relationally-oriented features/functions work best with 
well-normalized data.

Just because your data comes from spreadsheets doesn't mean you need to use 
that same structure to store the data in Access.

And rather than using two tables to "stage" a process, you could use a 
single table, and add a field to indicate that the step had been taken.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

"THINKINGWAY" <THINKINGWAY@discussions.microsoft.com> wrote in message 
news:77F36B66-19C6-4B8E-8B5B-16A8E72D2FD7@microsoft.com...
> It is a part of a larger strategy of importing data from spreadsheet that 
> is
> the only source for the data (read produced by another business entity 
> using
> another enterprise database solution).   Additionally, I am exceedingly 
> (or
> excruciatingly) new at this and you may feel free (I would say in my case
> obligated) to point out any inefficiencies with associated 
> recommendations.
> Prefer not to recieve criticisms without alternatives/suggestions though
> because they're like driving down a dead end alley at 90mph (read, doesn't
> ever result in anything good).
>
> Thank you,
>
> "Jeff Boyce" wrote:
>
>> Is this a one-time cleanup effort, or part of a larger strategy of 
>> putting
>> records in one table, then moving/copying them into another table?  If 
>> the
>> latter, that's a very spreadsheetly way of approaching this.  (hint: 
>> that's
>> not considered a compliment in these newsgroups...<g>)
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "THINKINGWAY" <THINKINGWAY@discussions.microsoft.com> wrote in message
>> news:0DFEA625-23DE-4BD5-952C-2226969B9F18@microsoft.com...
>> >I am trying to perform a select query to select all records that are NOT 
>> >in
>> > both tables.  So I have tblPROJHIST and tblWMSNEW and they both have
>> > project
>> > # fields.   What I am trying to do is find all the new project # 
>> > records
>> > in
>> > tblWMSNEW that are not in tblPROJHIST and put them in tblPROJHIST.
>> >
>> >
>> > Thanks
>>
>>
>> 


0
Jeff
12/21/2007 11:10:49 PM
Thank both for your input.  I do appreciate.  This will get me going on the 
correct track.

"John Spencer" wrote:

> To return all records in WMSNEW that are not in ProjHist (based on the 
> field Project #) you would use a query that looked like the following.
> 
> SELECT tblWMSNEW.*
> FROM tblWMSNEW LEFT JOIN tblProjHist
> ON tblWMSNEW.[Project #] = tblProjHist.[Project #]
> WHERE tblProjHist.[Project #] is null
> 
> You should be able to use the UNMATCHED query wizard to build this query.
> 
> You can then use this query as the basis of an insert query to add the 
> records from tblWMSNew.
> 
> '====================================================
>   John Spencer
>   Access MVP 2002-2005, 2007
>   Center for Health Program Development and Management
>   University of Maryland Baltimore County
> '====================================================
> 
> 
> THINKINGWAY wrote:
> > I am trying to perform a select query to select all records that are NOT in 
> > both tables.  So I have tblPROJHIST and tblWMSNEW and they both have project 
> > # fields.   What I am trying to do is find all the new project # records in 
> > tblWMSNEW that are not in tblPROJHIST and put them in tblPROJHIST.
> > 
> > 
> > Thanks
> 
0
Utf
12/24/2007 3:12:01 PM
Reply:

Similar Artilces:

Select Specific Data From Cells For A Chart
I have a spreadsheet that has a row that I want to use as a data range in a chart. The cells in the row contain three numbers on the left and three letters on the right. I can select just the three numbers by using “LEFT(A1:Z1,3)”. How do I use that formula to select just the three numbers for the data range in the chart? Note that your formula is incorrect in what you think it does, the LEFT function cannot reference a range of cells. To reference the first 3 cells in a row: =OFFSET(A1,,,,3) To reference cells C1:E1 would be: =OFFSET(A1,,2,,3) -- Best Regards, Luke...

Need VBA Code to Select text boxes in spreadsheet
I have a spreadsheet (Excel 2003) of data copied from a web page that has critical data contained in a column of 400 text boxes. I need to remove the text boxes from the spreadsheet, but retain the data. So far, I have been able to address the text boxes one at a time from VBA and extract the data, but I need to know how to address them from within the code. The text boxes are named HTMLText1 through HTMLText400. I can generate the names using a For loop, but using them to address the text boxes stumps me. Dim strThisBoxName As String Dim intValue As Integer ...

Simple graph
I have a chart with zip codes and a population rate. ZIP RATE 80001 2.34 80002 1.23 80003 1.23 I want to make a chart that has the rate on the x axis and the number of times that rate occurs on the y axis. | | | * | | * |_________________________________ | | 1.23 2.34 Thank you. I will assume the ZIP and RATE stuff is in A1:B200 (labels in row 1) Label in G1 to read RATE (but leave it empty for now), label in H2 to read COUNT Make a list of rates in G2:G20 (say) In H2 =COUNTIF($B$2:$B$200,G2) Copy down the row Select H1:G20 and make a...

Simple help with implementing Outlook-like GUI
Hi All. I am an experienced unix programmer who sometimes has to do something in the windows world, and always has newbie-questions. Basically I want to write an application program that looks like outlook in that it has nice icons down the left hand side that choose the content of the main area in the right hand side. So I started the MFC App wizard, and got me a SDI program with a CLeftFrame (CTreeView) and a CMainFrame (CFrameWnd). Then I prepared the following snippet of code to draw the buttons: // Create a pushbutton CBitmapButton* pmyButton; pmyButton = new CB...

Simple Simple Simple
Just started Excel (again) and need to make an easy speadsheet fo calculating square inches. Column A: Height Column B: Legnth Column C: Total Tags per 16 x 24 Column D: Cost per tag Figures I know: each sheet cost me $9.98. each sheet is 16 x 24 inches What I want to do is enter the Height, enter the Length and have th total (sum) be entered into columns C and D. I can't believe I forgot how to enter formulas. Any help will b appreciated. Thank you, Crai -- Message posted from http://www.ExcelForum.com Hi Craig, One Way, might be better ways but this'll work: List the colou...

Cannot select paper size in MS word
I have a problem with MS word when I am working offline from the office network. I do not have the selection to choose which paper size I want to use. But when I plug back into the office network all the selection will return. Is there any setting that can resolve this as other users do not have this issue. The paper sizes you can set are determined by the current printer driver. Ensure that the printer driver is installed locally on your laptop. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> G...

Help! Selecting data according to date range
I'm attempting to setup a worksheet for reminding employees to rene their licenses. I have input the data where the data range is from ro 3 - 84 (this could increase or decrease with hiring/firing, etc.). Th columns range from A - K with column H being *date*. I would like to start a new sheet (sheet2) in this workbook with th range of months in a year. In each monthly section it would search th data range in sheet 1 and return the records with the correspondin dates for that month. For example: If a employee's license expires i January, the entire record for that employee would...

How to preserve conditional formatting on a web query table result
I have an external database that Excel queries and returns two columns of dates. I can set up a conditional format (in one colum) so that the dates in each row of the column change colour if the corresponding columns date is different. My problem is how to COPY and PASTE the conditional formatting across all dates in the one column (so that each cell looks at the date in the corresponding cell next to it)? Any ideas or suggestions? You can just use the format painter to copy and paste formats -- Regards, Peo Sjoblom "Simon L" <Simon L@discussions.microsoft.com>...

Simple hack to get $500 to your home. 06-05-10
Simple hack to get $500 to your home at http://uknews.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. ...

Simple Query Wizard hangs
I am using Access 2003 When I initiate the "Simple Query Wizard", I am presented with the first screen - "What fields do you want in your query" When I click on the pulldown list to select a table, I get the following message: "The expression On Get Focus you entered as the event property setting produced the following error: the text you entered isn't an item in the list * the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. * There may have been an error evaluating the function, event, or macro&q...

List Box Selection to Run report
Hi I have a list box in which there are 10 fields shown. I wish for the user to make selections (multiple if required) and then for them to run a report. I have the following code (mainly extracted from Martin Greens website) which is allowing the report to run but my criteria is not being used in the record selection. There is no other criteria in the reports query so I am looking for help as to why I am not able to run the report correctly displaying the users selections Private Sub cmdRunReport_Click() Dim db As DAO.Database Dim varItem As Variant Dim strCriteria As Str...

Query of a subform
I'm looking to create a query that will pull subform data into it. The subform (four of them) have data that gets manually entered. All the boxes in the subform have dropdown boxes except one. This box is used to enter amounts. Will it be possible to pull this data? I can't seem to find it when I use the Query Wizard or Query Design. Thanks for any help. The data that is entered in the subform should be being stored in the table that is the source of the subform. Query that table. -- Milton Purdy ACCESS State of Arkansas "Gdesrosiers" wrote: >...

I Need a Simple Sales Software
Hello, I am looking for software to keep track of my prospects, customers, and the notes of the sales process. I spend about one hour a day on sales and marketing, so I prefer a simple and inexpensive software. Maximizer, Outlook, Act, and Goldmine seem made for the full-time professional salesperson. Please let me know if what I am looking for exists. Best Regards, T.I. ...

Query-Based Distros for Sender Restriction
Can I use a query based distribution list as a Sender Restriction? i.e., if I have a query based distribution list which is "All Email Users", can I add that to the list of allowed senders on a different distribution list to effectively block external senders from sending mail to that other distribution list? The reason I ask is that I'm tryig to write a vbscript to go through a number of distribution lists and change this but recieve an error if I attempt to set it to a query based distribution list. A regular distribution list works fine. The odd thing is that if I go into t...

Make it more simple or intuitive to do simple things
I appreciate the fact that applications are becoming more versatile and able to do things that we hardly thought possible in the past but I feel that in this added complexity you are losing sight of the need to do simple things easily without resorting to trial and error or consulting "help" which often anyhow doesn't lead one straight to the solution! An example is how to produce a chart with a series of months i.e Jan Feb Mar etc appearing on the X axis. This is no doubt something that resulted naturally in the first versions of Excel charts or in a competitor's ea...

Update query
Are you aloud to qualify criteria’s in an UPDATE Query? I’m trying do a UPDATE Query to import information from one table to another but I’m having trouble getting it to work. If I run the query as followed it works find. UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID = "1893" WHERE (((tblIncomeDetail.LocationID)=6) AND ((tblIncomeDetail.Date)=#1/4/2008#)); But when I qualify my criteria’s and SET , it doesn’t UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID = [Tables]![tblIncomeSum]![IncomeID] WHERE (((tblIncomeDetail.LocationID)=[Tables]![tblIncomeSum...

Curious Database Query Question
When creating a "New Database Query" from another Excel workbook, when you get to the "Select Workbook" dialog, there is a small checkbox labelled "Read Only". According to the help file, it has this to say: "To prohibit updates to this file, select the Read Only check box." I don't understand why this is an issue. In my understanding, database query is a one-way transfer of data from the external source to your excel workbook...if you edit the data in your workbook, that *doesn't* change the data back in the external source...right? Am I wr...

Simple public folder permission problem
I have a public task list folder. However even though I have given everyone "Author" priviledges and full control of the directory, users cannot update the tasks (although they can create new ones). The changes to tasks get reset to their original values. Where should I look to fix this problem? Brian How did you grant Author Access, via Outlook or ESM? What do you meanyou gave them full control of the directory? "Brian Taylor" <taylorb@newsgroups.nospam> wrote in message news:e2hwumGrEHA.1160@tk2msftngp13.phx.gbl... > I have a public task list folder. Ho...

Clear Unbound Text box used as input parameter to query
I've looked at everything I can find, tried all the suggestions but still can't get this to work. I have a form that opens blank. You input your parameter into an unbound text box then press a command button that performs a requery of the database and returns the results in a continuous form. When the results are returned, the parameter is listed as part of the detail in the form so I want the parameter box to be cleared out and ready for the next one. My command on the Search button looks like this: Private Sub SearchEPICode_Click() Me.Requery End Sub ...

Code for Criteria in a Pass Through Query
I have read all of the posts on this forum about Pass Through queries and I just do not get it. Does anyone have any sample code of how they used data on a form as criteria in a Pass Through query. Particularly for date ranges. I have a Pass Through query that looks like this. select [detail].[sd_term_cntry], [detail].[sd_datekey], [detail].[md_tran_amt1], [detail].[sd_key], [detail].[sd_resp_cde], [detail].[sd_mbr_num] from [detail] (nolock) where ((([detail].[sd_term_cntry]) <> '999') and (([detail].[sd_term_cntry]) <> ' ') and (([detail].[sd_...

dropdownlist selected index remains zero.
Hi, I have a ModalPopupExtender in my page. And In popup I am showing a gridview. Inside Gridview a dropdownlist is there. In pop there is a submitt button. But problem is in dropdownlist selected index remains zero irespective of my selection.Even after I select second,third or fourth Item ti will show first item of the dropdown as the selected Item. Please help. Please let me know why dropdownlist selected item is not getting changed ? Also all my code in Page_load is inside if (!IsPostBack). My Code is as below. binding gridview in the popup:- # protected void b...

Rules queries
Hello All I currently use Outlook Express for emailing, with Office 97, but I think I need to move to Outlook97 now because ... I regularly receive 3-5 important emails per week from each of around 110 (specified) people - so around 330-550 emails per week (plus about the same number again of spams and other miscellaneous emails). For all the emails from the 110 specified people I want to do two things: 1) Print them 2) Maintain an electronic copy of the email in a specific folder (different folder for each person - so 110 folders will be set up). I need these two things to happen automati...

What is simple text?
When creating a custom list that includes a column of accounting formated data, I get an error message that 'fields without simple text will be ignored'. The result is all other data except the accounting formatted data. Help!!!! I suppose it means without the currency symbol and thousands delimiters. On Fri, 8 Apr 2005 11:01:04 -0700, TexMas <TexMas@discussions.microsoft.com> wrote: >When creating a custom list that includes a column of accounting formated >data, I get an error message that 'fields without simple text will be >ignored'. The result is ...

Question on anatomy of a query
I was looking at an article by Plamen Ratchev (which I had printed a while ago but can't find it online anymore) where he states that the first thing a query does if there are 2 tables involved is create a Cartesian Product (cross Join) between each table. Then the ON filter is applied. If Table A has 20,000,000 rows and Table B has 30,000,000 rows - the system would read all the rows and put all the combinations together to get 600,000,000,000,000 rows? Then apply the ON filter??? Where would it put the data it just read? Does it read all this into a temporary table in...

Contacts not updating on select machines
Good day - We are using CRM 3.0 Rollup 2 desktop clients and server. I am having difficulties with a couple of users who were part of a test CRM group. We did a test install with test data that would mimic our production install. When we were ready for a production install we wiped out all information and recreated the install with production data. For some reason some of the employees who were in the test group are still seeing test contacts in addition to production contacts. Is there a cache on the local machine? All production users except these few test users are not seeing these contac...