Append Query

[Red via AccessMonster.com]

Good Afternoon all,
I am using an append query to appends data in a table. The query runs, but
replicates the same rows each time it is ran. I'm trying to figure out why
it is creating duplicates. Once the query appends the table once, I dont
want it to do it each and every time the query is ran. I have included my
SQL. Can anyone please point out where I'm going wrong.

NSERT INTO table1 ( Name, sex, Age, [UCA Code], Ward, Prefix, SSN, Service,
Surgeon, Assistant, [Preop Dx], [Procedure], [ASA Class], [X-Ray], comments )
SELECT tblRequest.Name, tblRequest.Sex, tblRequest.Age, tblRequest.[UCA Code],
tblRequest.Ward, tblRequest.Prefix, tblRequest.SSAN, tblRequest.Service,
tblRequest.Surgeon, tblRequest.Assistant, tblRequest.[Pre Op DX], tblRequest.
Procedure, tblRequest.Anestetic, tblRequest.[X-Ray], tblRequest.Comments
FROM tblRequest LEFT JOIN Table1 ON tblRequest.SSAN = Table1.SSN
WHERE (((tblRequest.Approved) Like 2));


As always, your help is truely appreciated.

VR,

Red

 

[Guest]

What field or combination of fields constitutes a duplicate in Table1? Make
that field or combination of fields the primary key and Access will not let
those records in Table1. You will get an error message then the non-dupes
will be let in. Of course you will need to clean up the existing dupes before
creating the PK.

 

[John Spencer]

Assumption:
If the SSN exists in Table1 then you do not want to add a new record to
Table1 for that SSN

If that is correct then try modifying your query's WHERE clause to reflect
that condition.

INSERT INTO table1 ( Name, sex, Age, [UCA Code], Ward, Prefix, SSN, Service,
Surgeon, Assistant, [Preop Dx], [Procedure], [ASA Class], [X-Ray],
comments )
SELECT tblRequest.Name, tblRequest.Sex, tblRequest.Age, tblRequest.[UCA
Code],
tblRequest.Ward, tblRequest.Prefix, tblRequest.SSAN, tblRequest.Service,
tblRequest.Surgeon, tblRequest.Assistant, tblRequest.[Pre Op DX]
, tblRequest.Procedure, tblRequest.Anestetic, tblRequest.[X-Ray],
tblRequest.Comments
FROM tblRequest LEFT JOIN Table1 ON tblRequest.SSAN = Table1.SSN
WHERE tblRequest.Approved =2 and Table1.SSN is Null

 

[Red via AccessMonster.com]

Thanks Jerry for your reply,
All fields are duplicated in Table1. I thought about using a primary key,
but there isn't any fields that is unique enough. I could use my ID field
but this field is an auto number and when the query runs, the auto number
just increases with the amount of records. Name, SSN, UCA Code, Surgeon all
are used mitiple times in records. Is this where I'm going to have problems
the fact I dont have a PK?

Thanks,

Red

What field or combination of fields constitutes a duplicate in Table1? Make
that field or combination of fields the primary key and Access will not let
those records in Table1. You will get an error message then the non-dupes
will be let in. Of course you will need to clean up the existing dupes before
creating the PK.

Good Afternoon all,
I am using an append query to appends data in a table. The query runs, but

[quoted text clipped - 17 lines]

Red

 

[Red via AccessMonster.com]

Thanks for your reply John,
SSN (Social Security Number) is not a unique number. If a patient has mutiple
visits, there will be mutiple records with SSN as one of the fields in the
table. I cant think of any unique fields that I can use. Even if I use an
ID#, it is auto number and when I run the query, the auto number just
increases accordingly. I appreciate any advice you can provide.
VR,
Red

Assumption:
If the SSN exists in Table1 then you do not want to add a new record to
Table1 for that SSN

If that is correct then try modifying your query's WHERE clause to reflect
that condition.

INSERT INTO table1 ( Name, sex, Age, [UCA Code], Ward, Prefix, SSN, Service,
Surgeon, Assistant, [Preop Dx], [Procedure], [ASA Class], [X-Ray],
comments )
SELECT tblRequest.Name, tblRequest.Sex, tblRequest.Age, tblRequest.[UCA
Code],
tblRequest.Ward, tblRequest.Prefix, tblRequest.SSAN, tblRequest.Service,
tblRequest.Surgeon, tblRequest.Assistant, tblRequest.[Pre Op DX]
, tblRequest.Procedure, tblRequest.Anestetic, tblRequest.[X-Ray],
tblRequest.Comments
FROM tblRequest LEFT JOIN Table1 ON tblRequest.SSAN = Table1.SSN
WHERE tblRequest.Approved =2 and Table1.SSN is Null

Good Afternoon all,
I am using an append query to appends data in a table. The query runs,

[quoted text clipped - 23 lines]

Red

 

[John Spencer]

Do you have a selection of fields that would make the result unique. For
instance is SSN, Ward, Service, and Procedure enough to make this unique?
You can have up to ten fields (if I recall correctly) in the join or in an
index. If you can't define a record as unique with ten fields then I think
you are not going to be able to do what you want.

An example with the four fields I named above.
INSERT INTO table1 ( Name, sex, Age, [UCA Code], Ward, Prefix, SSN, Service,
Surgeon, Assistant, [Preop Dx], [Procedure], [ASA Class], [X-Ray],
comments )
SELECT R.Name, R.Sex, R.Age, R.[UCA Code],
R.Ward, R.Prefix, R.SSAN, R.Service,
R.Surgeon, R.Assistant, R.[Pre Op DX]
, R.Procedure, R.Anestetic, R.[X-Ray],
R.Comments
FROM tblRequest AS R LEFT JOIN Table1 As T
ON R.SSAN = T.SSN
AND R.Ward = T.Ward
AND R.Service=T.Service
AND R.Procedure = T.Procedure
WHERE R.Approved =2
AND T.SSN is Null
AND T.Ward is Null
AND T.Service is Null
AND T.Procedure is Null

Assumption:
If the SSN exists in Table1 then you do not want to add a new record to
Table1 for that SSN

If that is correct then try modifying your query's WHERE clause to reflect
that condition.

INSERT INTO table1 ( Name, sex, Age, [UCA Code], Ward, Prefix, SSN,
Service,
Surgeon, Assistant, [Preop Dx], [Procedure], [ASA Class], [X-Ray],
comments )
SELECT tblRequest.Name, tblRequest.Sex, tblRequest.Age, tblRequest.[UCA
Code],
tblRequest.Ward, tblRequest.Prefix, tblRequest.SSAN, tblRequest.Service,
tblRequest.Surgeon, tblRequest.Assistant, tblRequest.[Pre Op DX]
, tblRequest.Procedure, tblRequest.Anestetic, tblRequest.[X-Ray],
tblRequest.Comments
FROM tblRequest LEFT JOIN Table1 ON tblRequest.SSAN = Table1.SSN
WHERE tblRequest.Approved =2 and Table1.SSN is Null

Good Afternoon all,
I am using an append query to appends data in a table. The query runs,
but
replicates the same rows each time it is ran. I'm trying to figure out
why
it is creating duplicates. Once the query appends the table once, I dont
want it to do it each and every time the query is ran. I have included
my
SQL. Can anyone please point out where I'm going wrong.

NSERT INTO table1 ( Name, sex, Age, [UCA Code], Ward, Prefix, SSN,
Service,
Surgeon, Assistant, [Preop Dx], [Procedure], [ASA Class], [X-Ray],
comments )
SELECT tblRequest.Name, tblRequest.Sex, tblRequest.Age, tblRequest.[UCA
Code],
tblRequest.Ward, tblRequest.Prefix, tblRequest.SSAN, tblRequest.Service,
tblRequest.Surgeon, tblRequest.Assistant, tblRequest.[Pre Op DX],
tblRequest.
Procedure, tblRequest.Anestetic, tblRequest.[X-Ray], tblRequest.Comments
FROM tblRequest LEFT JOIN Table1 ON tblRequest.SSAN = Table1.SSN
WHERE (((tblRequest.Approved) Like 2));


As always, your help is truely appreciated.

VR,

Red

 

[John Vinson]

Thanks for your reply John,
SSN (Social Security Number) is not a unique number. If a patient has mutiple
visits, there will be mutiple records with SSN as one of the fields in the
table. I cant think of any unique fields that I can use. Even if I use an
ID#, it is auto number and when I run the query, the auto number just
increases accordingly. I appreciate any advice you can provide.
VR,

You should then CERTAINLY modify your table structure. If you're
storing the same patient's SSN, name, and other such information
repeatedly for every visit, then you're misusing Access!

You need (at least) two separate tables, Patients (with a unique
patient ID such as the SSN, though that may have problems in that not
all patients have SSNs and it's still not legal to require them),
related one to many to a Visits table which would record information
about each visit.

John W. Vinson[MVP]

 

[Red via AccessMonster.com]

Thanks John,
I have been working with my append query using your suggestions below and it
works perfectly! I do appreciate your help and all others that help out
answering questions and I hope to be good enough one day to be one of the
"experts" answering postings offering advice. It is truely appreciated.
Thanks!

VR<
Red

Do you have a selection of fields that would make the result unique. For
instance is SSN, Ward, Service, and Procedure enough to make this unique?
You can have up to ten fields (if I recall correctly) in the join or in an
index. If you can't define a record as unique with ten fields then I think
you are not going to be able to do what you want.

An example with the four fields I named above.
INSERT INTO table1 ( Name, sex, Age, [UCA Code], Ward, Prefix, SSN, Service,
Surgeon, Assistant, [Preop Dx], [Procedure], [ASA Class], [X-Ray],
comments )
SELECT R.Name, R.Sex, R.Age, R.[UCA Code],
R.Ward, R.Prefix, R.SSAN, R.Service,
R.Surgeon, R.Assistant, R.[Pre Op DX]
, R.Procedure, R.Anestetic, R.[X-Ray],
R.Comments
FROM tblRequest AS R LEFT JOIN Table1 As T
ON R.SSAN = T.SSN
AND R.Ward = T.Ward
AND R.Service=T.Service
AND R.Procedure = T.Procedure
WHERE R.Approved =2
AND T.SSN is Null
AND T.Ward is Null
AND T.Service is Null
AND T.Procedure is Null

Assumption:
If the SSN exists in Table1 then you do not want to add a new record to

[quoted text clipped - 44 lines]