Help with append query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

magicdds - 25 May 2008 06:40 GMT

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

Reply to this Message

Rick Brandt - 25 May 2008 12:51 GMT

> 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
[quoted text clipped - 21 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

magicdds - 25 May 2008 14:30 GMT

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 have an append query that takes data from Table1 and Table2 and
> > appends the data to Table3 for a mailmerge. Table1 and Table2 are
[quoted text clipped - 28 lines]
> simply uncheck the "Show" checkbox underneath it. That tells the query that
> the field is not to be included in the output.

Reply to this Message

Rick Brandt - 25 May 2008 14:41 GMT

> 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
[quoted text clipped - 6 lines]
> (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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

John Spencer - 25 May 2008 15:58 GMT

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
'====================================================

> 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
[quoted text clipped - 20 lines]
> Thanks,
> Mark

Reply to this Message