I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalized. My inherited DB is not normalized and I find it
extremely irritating due to the workarounds needed.
I created tblUniqueSups by doing a select Distinct Supervisor Name.
Now I need to bring in the SSNs of the Unique Sups but I can't quite
get it.
I tried:
UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME =
tblNonNormalized.SupervisorName SET UniqueSups.SSN =
[tblNonNormalized].[SSN];
but the SSNs are not populating. I'm not quite up to speed on the
syntax for a union query.
TIA
Moe 6 1125
Try:
UPDATE UniqueSups
SET ssn =
(SELECT DISTINCT N.ssn
FROM tblNonNormalized AS N
WHERE N.supervisorname = UniqueSups.name
AND N.ssn IS NOT NULL)
This can only work if you have a single SSN for each unique name in
your non-normalized table. Otherwise you'll get an error and you'll
have to do some more data cleansing.
--
David Portas
SQL Server MVP
--
mo wrote: I need to bring the ssn's into UniqueSups (supervisors) from tblNonNormalized. My inherited DB is not normalized and I find it extremely irritating due to the workarounds needed. I created tblUniqueSups by doing a select Distinct Supervisor Name. Now I need to bring in the SSNs of the Unique Sups but I can't quite get it.
I tried: UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME = tblNonNormalized.SupervisorName SET UniqueSups.SSN = [tblNonNormalized].[SSN];
but the SSNs are not populating. I'm not quite up to speed on the syntax for a union query. TIA Moe
Huh? Post your table structure. field names? types? meanings?
union queries are about as hard as falling down. The *only* trick is
that you need union-compatible fields (generally of the same type).
Say you have two tables, tblA and tblB, with structures like this:
CREATE TABLE tblA(
SSN Text(9) PRIMARY KEY,
Firstname Text(20),
Lastname Text(25),
....
)
and
CREATE TABLE tblB(
SocSecNo Text(9) PRIMARY KEY,
FName Text(20),
LName Text(25),
....
)
Union is no big deal - you just have to alias the fields in one table
so they map right...
SELECT SSN, FirstName, LastName
FROM tblA
UNION ALL
SELECT SocSecNo as SSN, FName as FirstName, LName as LastName
FROM tblB
ORDER BY SSN;
(aliasing is the [FieldName] AS (alias) stuff.
IF the database really is not normalized, you *may* need to normalize
it to get it to work... so you might want to post the relevant parts of
the database structure and what you need to do with the data. Whether
you normalize will depend on several factors, and without knowing some
more about the thing, it's hard to tell what to advise. Could be a
huge undertaking for very little payoff... but then it might be worth
it or relatively painless...
Further clarification:
I am seeking to normalize the data.
tblNonNormalized: (Employees and supervisors)
Name SSN Supervisor
Jon 222 Ed
Sam 333 Ed
Ed 444 Tom
destination: UniqueSups (created from Create Table Distinct
SupervisorName)
Ed 444
Tom 555
They've (read: previous idiot designer) placed children and parents in
the same table! I am trying to extract the unique supervisors into a
new table. My new table would contain supervisorname Ed and his SSN
just once rather than one for every person he supervises. The plain
old falling off a log update query is not working, as it is copying all
the ssns rather than just those where it is a supervisor record.
(Sorry to obfuscate, I was trying to simplify the problem.) The
suggested query from David Portas, thank you btw, does not work in
Access "requires [UPDATE], [DELETE] etc."
It does not work in SQL Server because "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= , , >= or when the subquery is used as an expression.
The statement has been terminated." This syntax does work on other
normaized tables that I created.
UPDATE UniqueSups
SET ssn =
(SELECT DISTINCT N.ssn
FROM tblNonNormalized AS N
WHERE N.supervisorname = UniqueSups.name
AND N.ssn IS NOT NULL) pi********@hotmail.com wrote: mo wrote: I need to bring the ssn's into UniqueSups (supervisors) from tblNonNormalized. My inherited DB is not normalized and I find it extremely irritating due to the workarounds needed. I created tblUniqueSups by doing a select Distinct Supervisor Name. Now I need to bring in the SSNs of the Unique Sups but I can't
quite get it.
I tried: UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME = tblNonNormalized.SupervisorName SET UniqueSups.SSN = [tblNonNormalized].[SSN];
but the SSNs are not populating. I'm not quite up to speed on the syntax for a union query. TIA Moe Huh? Post your table structure. field names? types? meanings?
union queries are about as hard as falling down. The *only* trick is that you need union-compatible fields (generally of the same type).
Say you have two tables, tblA and tblB, with structures like this:
CREATE TABLE tblA( SSN Text(9) PRIMARY KEY, Firstname Text(20), Lastname Text(25), ... )
and
CREATE TABLE tblB( SocSecNo Text(9) PRIMARY KEY, FName Text(20), LName Text(25), ... )
Union is no big deal - you just have to alias the fields in one table so they map right...
SELECT SSN, FirstName, LastName FROM tblA UNION ALL SELECT SocSecNo as SSN, FName as FirstName, LName as LastName FROM tblB ORDER BY SSN;
(aliasing is the [FieldName] AS (alias) stuff.
IF the database really is not normalized, you *may* need to normalize it to get it to work... so you might want to post the relevant parts
of the database structure and what you need to do with the data.
Whether you normalize will depend on several factors, and without knowing
some more about the thing, it's hard to tell what to advise. Could be a huge undertaking for very little payoff... but then it might be worth it or relatively painless...
> It does not work in SQL Server because "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,
, >= or when the subquery is used as an expression.
That's because you have more than one matching SSN for a given name in
tblNonNormalized. In other words you need to clean up your data first.
Try this query:
SELECT *
FROM tblNonNormalized
WHERE supervisorname IN
(SELECT supervisorname
FROM tblNonNormalized
GROUP BY supervisorname
HAVING MIN(ssn)<MAX(ssn))
Then decide what you want to do about the duplicate names with
different SSNs. If you need more help, please post again with CREATE
TABLE statements for your tables and a few rows of sample data as
INSERT statements so that we can reproduce your problem.
--
David Portas
SQL Server MVP
--
Thank you very much David, cleaning the data is the goal.
This query actually succeeds in updating the appropriate SSNs in SQL
Server, but it doesn't work in Access, where I'm tasked to work. In
Access 2002, I'm still getting the 'must be an updateable query' even
though tblNonNormal is an attached SQL server table and the one I'm
writing to is a local Access one.
UPDATE UniqueSupervisors
SET UniqueSupervisors.SSN =
(Select Distinct [tblNonNormal].[SSN]
FROM tblNonNormal
WHERE UniqueSupervisors.SupervisorEmail = tblNonNormal.Email)
Any thoughts why it won't run? tia Moe
David Portas wrote: It does not work in SQL Server because "Subquery returned more than
1 value. This is not permitted when the subquery follows =, !=, <, <=
,, >= or when the subquery is used as an expression. That's because you have more than one matching SSN for a given name
in tblNonNormalized. In other words you need to clean up your data
first. Try this query:
SELECT * FROM tblNonNormalized WHERE supervisorname IN (SELECT supervisorname FROM tblNonNormalized GROUP BY supervisorname HAVING MIN(ssn)<MAX(ssn))
Then decide what you want to do about the duplicate names with different SSNs. If you need more help, please post again with CREATE TABLE statements for your tables and a few rows of sample data as INSERT statements so that we can reproduce your problem.
-- David Portas SQL Server MVP --
This is a SQL Server group . You'll probably get better help in an
Access group. Alternatively, run it as a pass-though query.
--
David Portas
SQL Server MVP
-- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Somerset Bob |
last post by:
I've posted my query at the phpbb forum, where I got half an answer and then
no more. I posted it again at another phpbb forum, where I got half an
answer and no more. I posted it in alt.php, where...
|
by: Somerset Bob |
last post by:
I've posted my query at the phpbb forum, where I got half an answer and then
no more. I posted it again at another phpbb forum, where I got half an
answer and no more. I posted it in alt.php, where...
|
by: John Baker |
last post by:
Hi:
As those who have looked at this newsgroup recently will realize, I am a neophyte with
Access, although I have experienced with Approach (the Lotus product). There are things I
could easily...
|
by: hope |
last post by:
Hi
Access 97
I'm lost on this code please can you help
=================================
Below is some simple code that will concatenate a single field's value from multiple records into a...
|
by: Randy Harris |
last post by:
I imported records into a table, later found out that many of them had
trailing spaces in one of the fields. If I'd caught it sooner, I could have
trimmed the spaces before the import.
This...
|
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all...
|
by: Takeadoe |
last post by:
Dear NG,
Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the...
|
by: so many sites so little time |
last post by:
all right so the script is pretty simple it goes it retrives what the
id of the post is and it lets you edit it well no it doesnt.
now if you go to www.kirewire.com/pp2/index/php you will see a...
|
by: dee |
last post by:
I have a table 'LeadHistory' which has among others, the following
fields.
Salesman(Text)
SalesmanAssmntDate(Date/Time)
Disposition(Text)
I also have a table 'LookUpSalesman' which has among...
|
by: pankajsingh5k |
last post by:
Hi guys,
These question is for all the experts...
Please help me before my brain explodes
The problem is again with the formview control..
I have a formview and i have to use it that...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |