Help with append query

[magicdds]

I have an append query that takes data from Table1 and Table2 and appends the
data to Table3 for a mailmerge. Table1 and Table2 are joined by field
PatientID - list all records from Table1 and those in Table2 where the joined
fields are equal.

In the query builder, I brought in those fields from Table1 that need to be
appended into Table3. I also brought into the query builder Table2* . I
didn't specify field names for Table2 because the field names are constantly
changing (in other parts of the programming). So the field PATIENTID must
therefore come from Table2 (if brought down from Table1, I get an error that
there are two things trying to be appended to the PatientID field in Table3).

The problem comes in when there is no record in Table2 for a record in
Table1. Then the record that is appended into Table3 has no entry in the
PATIENTID field. This field must somehow get the PATIENTID from Table1 if
there is no record for that PATIENTID in Table2.

Is there a way to do this? Or the other option, is there a way, in the
query builder to bring in all fields from Table2 (using Table2*) but leave
out PATIENTID so that the field PATIENTID can be brought into the query
builder from Table1?

Thanks,
Mark

 

[Rick Brandt]

I have an append query that takes data from Table1 and Table2 and
appends the data to Table3 for a mailmerge. Table1 and Table2 are
joined by field PatientID - list all records from Table1 and those in
Table2 where the joined fields are equal.

In the query builder, I brought in those fields from Table1 that need
to be appended into Table3. I also brought into the query builder
Table2* . I didn't specify field names for Table2 because the field
names are constantly changing (in other parts of the programming). So
the field PATIENTID must therefore come from Table2 (if brought down
from Table1, I get an error that there are two things trying to be
appended to the PatientID field in Table3).

The problem comes in when there is no record in Table2 for a record in
Table1. Then the record that is appended into Table3 has no entry in
the PATIENTID field. This field must somehow get the PATIENTID from
Table1 if there is no record for that PATIENTID in Table2.

Is there a way to do this? Or the other option, is there a way, in
the query builder to bring in all fields from Table2 (using Table2*)
but leave out PATIENTID so that the field PATIENTID can be brought
into the query builder from Table1?

Thanks,
Mark

First off you should be able to do your merge with the query directly with
no need to take its output and place it into a third table.

Second, you should be able to add the field PATIENTID from table one and
simply uncheck the "Show" checkbox underneath it. That tells the query that
the field is not to be included in the output.

 

[magicdds]

I have the field PatientID from Table1 in the query. If the show box is not
checked, then there is no output to table3, when there is no record in
table2, and the field PatientID in table3 remains blank. This is the problem.
When there is no record in table2, I need the PATIeNTID field in table3 to
get the number from Table1. When there is a record in Table2, Table3 gets the
number for PATIENTID from Table2 (in this case, things work fine).

(I can't use the query for the mail merge - the reason is too lengthy to
describe)

 

[Rick Brandt]

I have the field PatientID from Table1 in the query. If the show box
is not checked, then there is no output to table3, when there is no
record in table2, and the field PatientID in table3 remains blank.
This is the problem. When there is no record in table2, I need the
PATIeNTID field in table3 to get the number from Table1. When there
is a record in Table2, Table3 gets the number for PATIENTID from
Table2 (in this case, things work fine).

(I can't use the query for the mail merge - the reason is too lengthy
to describe)

I can't think of a way to accomplish what you want while still allowing you
to use * for the field selection from Table2.

 

[John Spencer]

So is does Table3 always have the same structure? Or is this a
make-table query and not an append query.

How do you know that a record Table1 matches up to a record in Table2?

The only way I can see to handle this is to rename the field in Table1
(permanently by using a query against Table1 where you alias PatientID
to a different name.) Now using the query of Table1 joined to Table2
populate the records in Table3 which will need new field to hold the
renamed patientID from table1.

After you populate Table3 run an update query to set PatientID to the
value in NEW PatientID field where the PatientID has no value.


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