adding a field using a query

[Guest]

I'm trying to add a field to a table using a make-table query. My original
table contains a field of codes for medical specialties, and I'm trying to
add a field next to it that contains the equivalent name, ie 100 is General
Surgery, 101 is Urology etc.

I'm using a look-up that I've joined on the relevant field. When I run the
query I get a message saying you are about to paste 82,000 rows, but the
original table that I'm adding the column to has about 85,000 rows. There are
no nulls, and there are no codes that aren't in the look-up. Where are my
rows going ??

Thanks.

 

[John Spencer]

Make table makes a new table. Why do you feel the need to do this?

You can add a new field to the existing table and use an update query, but I
would probably just join the lookup table to the main table and use the
value from the lookup table. There is almost always no good reason to store
data in two forms in a table 100 and General Surgery are the same value in
two forms.

In a query add your two tables and drag from the Speciality code in the main
table to the lookup table. Now double click on the join line and select
show all in the main table and only matches in the lookup table. Select the
fields you want to see.

As far as returning 82,000 of 85,000 records, my guess is there are 3,000
records where the code in the main table does not exist in the lookup table.
Probably typos.

To find those codes use the Unmatched query wizard to build a query that
shows records in the main table that don't exist in the lookup table (based
on the code)

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

 

[Guest]

I need both forms of the same information in the table because I'm sending it
to someone else who doesn't know the data well, and I don't know which form
of the information they will want to / be able to link on.

I've done the unmatched query and there are no records where the code in the
main table does not exist in the look-up.

 

[John Spencer]

Then did you check for NULLS in the table. That would be the only other
thing that I can think of that would cause the problem. By the way, posting
your SQL statement (Menu - View: SQL) might be a good idea. You might be
able to solve the problem by using a LEFT JOIN (or a right join) instead of
an INNER JOIN in the SELECT statement.

(Of course, you might already be doing that, but my ability to see the
actual query statement is limited by the speed of my psychic connection over
the internet.)

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

 

[Guest]

there's no nulls either. I just put the query together again just in case I
was making some silly mistake and I'm now getting a 'type mismatch' error
message

here's the SQL. (thanks for your help btw)

SELECT [data for sg2 v2].activity, [data for sg2 v2].[main specialty], [spec
look up].specialty, [data for sg2 v2].[treatment specialty], [data for sg2
v2].[hrg code], [data for sg2 v2].[primary diagnosis], [data for sg2
v2].[primary procedure], [data for sg2 v2].[secondary procedure], [data for
sg2 v2].PCT, [data for sg2 v2].[PCT Name], [data for sg2 v2].[PCT Name],
[data for sg2 v2].incode, [data for sg2 v2].sex, [data for sg2 v2].age, [data
for sg2 v2].volume, [data for sg2 v2].[Spell ALoS] INTO [data for sg2 v3]
FROM [data for sg2 v2] LEFT JOIN [spec look up] ON [data for sg2 v2].[main
specialty] = [spec look up].code
GROUP BY [data for sg2 v2].activity, [data for sg2 v2].[main specialty],
[spec look up].specialty, [data for sg2 v2].[treatment specialty], [data for
sg2 v2].[hrg code], [data for sg2 v2].[primary diagnosis], [data for sg2
v2].[primary procedure], [data for sg2 v2].[secondary procedure], [data for
sg2 v2].PCT, [data for sg2 v2].[PCT Name], [data for sg2 v2].[PCT Name],
[data for sg2 v2].incode, [data for sg2 v2].sex, [data for sg2 v2].age, [data
for sg2 v2].volume, [data for sg2 v2].[Spell ALoS];

 

[John Spencer]

The Group by clause will combine any records where your fields are
duplicated across the fields shown. So that might account for you having
less records in the table you are creating - hard to say since I don't know
your data and don't know if you have duplicates.

The data mismatch error would probably be caused by the ON clause since I
don't see anything else that could generate the error. Are the two
fields -
[data for sg2 v2].[main specialty] and [spec look up].code
of the same data type? Are they BOTH number fields or is one of them a text
field that contains number characters and the other a number field?

SELECT [data for sg2 v2].activity, [data for sg2 v2].[main specialty]
, [spec look up].specialty, [data for sg2 v2].[treatment specialty]
, [data for sg2 v2].[hrg code], [data for sg2 v2].[primary diagnosis]
, [data for sg2 v2].[primary procedure], [data for sg2 v2].[secondary
procedure]
, [data for sg2 v2].PCT, [data for sg2 v2].[PCT Name]
, [data for sg2 v2].[PCT Name]
, [data for sg2 v2].incode, [data for sg2 v2].sex, [data for sg2 v2].age
, [data for sg2 v2].volume, [data for sg2 v2].[Spell ALoS]
INTO [data for sg2 v3]
FROM [data for sg2 v2] LEFT JOIN [spec look up]
ON [data for sg2 v2].[main specialty] = [spec look up].code
GROUP BY [data for sg2 v2].activity, [data for sg2 v2].[main specialty]
, [spec look up].specialty, [data for sg2 v2].[treatment specialty]
, [data for sg2 v2].[hrg code], [data for sg2 v2].[primary diagnosis]
, [data for sg2 v2].[primary procedure], [data for sg2 v2].[secondary
procedure]
, [data for sg2 v2].PCT, [data for sg2 v2].[PCT Name]
, [data for sg2 v2].[PCT Name], [data for sg2 v2].incode
, [data for sg2 v2].sex, [data for sg2 v2].age, [data for sg2 v2].volume
, [data for sg2 v2].[Spell ALoS];

If you wanted to find out if you had duplicates based on the above you could
run this query.
SELECT Count(*) as RecordCount
,[data for sg2 v2].activity, [data for sg2 v2].[main specialty]
, [data for sg2 v2].[treatment specialty]
, [data for sg2 v2].[hrg code], [data for sg2 v2].[primary diagnosis]
, [data for sg2 v2].[primary procedure], [data for sg2 v2].[secondary
procedure]
, [data for sg2 v2].PCT, [data for sg2 v2].[PCT Name]
, [data for sg2 v2].[PCT Name]
, [data for sg2 v2].incode, [data for sg2 v2].sex, [data for sg2 v2].age
, [data for sg2 v2].volume, [data for sg2 v2].[Spell ALoS]
FROM [data for sg2 v2]
GROUP BY [data for sg2 v2].activity
, [data for sg2 v2].[main specialty]
,[data for sg2 v2].[treatment specialty]
, [data for sg2 v2].[hrg code], [data for sg2 v2].[primary diagnosis]
, [data for sg2 v2].[primary procedure], [data for sg2 v2].[secondary
procedure]
, [data for sg2 v2].PCT, [data for sg2 v2].[PCT Name]
, [data for sg2 v2].[PCT Name], [data for sg2 v2].incode
, [data for sg2 v2].sex, [data for sg2 v2].age, [data for sg2 v2].volume
, [data for sg2 v2].[Spell ALoS]
HAVING COUNT(*) > 1


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