Simple Select Query

  • Thread starter Thread starter THINKINGWAY
  • Start date Start date
T

THINKINGWAY

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
 
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.
 
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
 
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,
 
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
'====================================================
 
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
 
Thank both for your input. I do appreciate. This will get me going on the
correct track.

John Spencer said:
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
'====================================================

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
Click to expand...
Click to expand...
 
Back
Top