creating field with blank data for append query

[Guest]

using a2k.

i need to create a query to append old data to an existing table having
data. the source of my 'old data' does not have a field called 'Follow Up' (a
date field in it) although the existing one does, therefore i would like to
somehow instantiate this field in the append query and to have it contain
nothing so that when i go to append the table the query generates to the
existing one, fields line up -- this is what i think i'm supposed to be doing
unless i'm mistaken because of my unfamiliarity.

can some kind soul shed some light on whether this approach is sound?

thanks in advance,

-ted

 

[Marshall Barton]

using a2k.

i need to create a query to append old data to an existing table having
data. the source of my 'old data' does not have a field called 'Follow Up' (a
date field in it) although the existing one does, therefore i would like to
somehow instantiate this field in the append query and to have it contain
nothing so that when i go to append the table the query generates to the
existing one, fields line up -- this is what i think i'm supposed to be doing
unless i'm mistaken because of my unfamiliarity.

can some kind soul shed some light on whether this approach is sound?

Use an INSERT INTO query (Append). You can specify the
fields to copy from and to. If you just omit the the field
from both lists, the record will be left with a Null for the
nonexistent field. If you want to use a predefined value
for a field, include the field in the to list and use a
literal value instead of a field name in the from list.

 

[Guest]

hi marshall,

i've been noodling with this since i posted to the group and used the design
window. i have pasted the current manifestation of it expressed as SQL below:

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
[Pt Initials], [On-Study Date], FollowUp, Form, Months_X, Site, Comments )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].MR_Number, [Screening Log].[IRB Number], Left([First Name],1) &
Left([Last Name],1) AS Expr1, [Screening Log].RegisteredDate, " " AS Expr2, "
" AS Expr3, " " AS Expr4, [Screening Log].Campus AS Site, [Screening
Log].Comments
FROM [Screening Log];


and i see that it's 'complaining' when i attempted to run it over some kind
of violations and other issues. in some instances i am afforded the 'luxury'
of the ability to use the same name in both instances (since my target table
does not always contain the same field names as the query's source table). in
the instance of Expr1, the target table's name for that is "Pt Initials". in
the case of Expr2, the target table's name for that is "FollowUp" (a date
field) and we need it to be blank at this time, in Expr3's instance, the
target table's name for it happens to be "Form" (a text field -- which is
unknown at this time for the 455 records in the source table and will be
manually coded by a user, hence it needs to be left blank), in the case of
Expr4, its name in the source table is "Months" (a number fields) which we
will code later and needs to be left alone and allowed to be blank. Expr5
points to a memo field called "Comments" in the target table.

when i attempted to implement it, it paused and displayed the following
lengthy "informative" message:

"Microsoft Access can't append all the records in the append query:

Microsoft Access set 455 field(s) to Null due to a type conversion failure,
and it didn't add 2 record(s) to the table due to key violations, 0 record(s)
due to lock violations, and 0 record(s) due to validation rule violations. Do
you want to run the action query anyway? To ignore the error(s) and run the
query, click Yes."

as i think i mentioned there are 455 records in the source table of the
query's. i suspect i could research the issue of which records run afoul of
the PK question (the target table is using a composite PK comprising both
"Study #" AND "Reg #", but i'm sortof kindof totally unclear about the null
type conversion thing along with the point it's trying to make about lock and
validation rule violations.

with best regards,

-ted

 

[Guest]

i think i saw some areas which needed the benefit of a bit of work and i
submit the following (hopefully more polished) sql version

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
[Pt Initials], [On-Study Date], Site, Comments )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr] AS [Study #], [Screening
Log].MR_Number AS [Reg #], [Screening Log].[IRB Number], Left([First Name],1)
& Left([Last Name],1) AS [Pt Initials], [Screening Log].RegisteredDate AS
[On-Study Date], [Screening Log].Campus AS Site, [Screening Log].Comments
FROM [Screening Log];

i am still getting a rosetta stone's worth of messages, though somewhat
different (for better or worse):

it now seems to tell me it has set 0 field(s) to Null due to a type
conversion failure. and that it didn't add 2 record(s) tothe table due to key
violations, 0 record(s) due to lock violations and _229_ records due to
validation rule violations.

this last number 229 may have some big meaning (although what is beyond my
own comprehension) since 229 is the number of records in the target table!

any help's sure going to be appreciated.

-ted