Adding records to a table

[Nona]

Is there a way to automatically add a record to a table for each person who
meets certain criteria?

The authorizations table I am working with has about 4,000 records. It holds
data about medical services for about 400 patients. The patient names and
other personal data are in a separate table.

For each of the patients who meet certain criteria (county, funding source,
etc.) I want to add a new record in the authorization table that has the same
information for each record - starting date of 1/1/09, ending date of
12/31/09, service code number, number of approved units of service.

It can't be an update query, because I want new records added to the table,
not update current records. It can't be an append query because I am not
appending records from another table or another database.

Is there a simple way to accomplish this?

I am using Access 2003 and will appreciate your help! My friends don't want
to have to enter each of these new records manually!

 

[tina]

It can't be an append query because I am not

appending records from another table or another database

well, wait a minute. you have the patients listed in a table, right? and you
have some method of identifying those patients who meet the certain criteria
you spoke of, right? and you have the service code(s) stored somewhere, or
know what it/they is/are, right? and you have the number of approved units
value(s) somewhere, right? whether all of this data is stored in one or more
tables in your database, or some of it is determined by the user according
to specific business rules (ex: patients who meet x criteria get 10 units,
while patients who meet y criteria get 20 units), you must have a way to
generate the data you need. and if you do, you can append or "insert" it to
a table.

with some more details of where your data is coming from, we can probably
help you write an Append/Insert query to do what you want.

hth

 

[Nona]

The answer to all your questions is Yes! I'll try to be more specific this
time.

The patient data is in the Consumer table. Data about the services is in the
Authorizations table. For every consumer who lives in County A or County B,
whose funding source is Plan B, and who is above age 17, I want to add a new
record (same record for everyone who meets that criteria).

The new record to be added will have the same fields already in the
Authorization table but will have new starting date, new ending date, the
number of authorizations approved, a service code number, and a status called
"Unmanaged. " (strange but true!)

I have a query that joins the two tables, and I can easily enter that data
one time (as in an update query), but I want to add the new record multiple
times (once for each of those patients who meet the criteria.)

I don't think I have heard of the "insert" you mentioned, but I would love
to learn about it!

Thank you, Tina!

 

[pietlinden]

The answer to all your questions is Yes! I'll try to be more specific this
time.

The patient data is in the Consumer table. Data about the services is in the
Authorizations table. For every consumer who lives in County A or County B,
whose funding source is Plan B, and who is above age 17, I want to add a new
record (same record for everyone who meets that criteria).

The new record to be added will have the same fields already in the
Authorization table but will have new starting date, new ending date, the
number of authorizations approved, a service code number, and a status called
"Unmanaged. " (strange but true!)

I have a query that joins the two tables, and I can easily enter that data
one time (as in an update query), but I want to add the new record multiple
times (once for each of those patients who meet the criteria.)

I don't think I have heard of the "insert" you mentioned, but I would love
to learn about it!

Thank you, Tina!

sounds like you want a deliberate cartesian product (created when you
add two tables to a query but don't join them - you end up with
tableA.RecordCount X tableB.RecordCount records). Create a query and
add both tables that you want to filter. Add the where clause/filters
to each table. Do not join the tables. Run the query. Once you have
it right, turn it into an append query...

Probably the best way of doing this is with a VERY SMALL dataset.
(like 3 records in one table and 2 in the other). Otherwise, it's a
nightmare figuring out if you have the logic right!) Once you have
the query working on the mini dataset, import the query into the
production database.

 

[Nona]

Thanks for your suggestions...I think I've almost figured out how to do this
but please advise on whether this is the way I should go.

I used a Make Table query to create a new table of all the consumers who met
the criteria for getting the new approved service units for 2009 (county,
Plan B, etc.). Then I used an update query for that new table to change those
records to the new data I want added - dates for 2009, approved number, and
the service code. Once those records in the newly made table are correct, I
can then append them to the database. Mission Accomplished...Almost. I think!

Will this work to update the approximate 1000 patients that meet the criteria?

But there's another hurdle to cross. The Make Table contains duplicate
records for many patients. That's because the Authorizations Query used to
create the Make Table contained several authorizations for the same patients,
as it is supposed to do. I don't know how to keep those from showing up in
the Make Table. I can go through and delete the duplicates manually, but
surely there's a way for Access to do that!

Thanks for your help and especially keeping me focused!



--
Nona

The answer to all your questions is Yes! I'll try to be more specific this
time.

The patient data is in the Consumer table. Data about the services is in the
Authorizations table. For every consumer who lives in County A or County B,
whose funding source is Plan B, and who is above age 17, I want to add a new
record (same record for everyone who meets that criteria).

The new record to be added will have the same fields already in the
Authorization table but will have new starting date, new ending date, the
number of authorizations approved, a service code number, and a status called
"Unmanaged. " (strange but true!)

I have a query that joins the two tables, and I can easily enter that data
one time (as in an update query), but I want to add the new record multiple
times (once for each of those patients who meet the criteria.)

I don't think I have heard of the "insert" you mentioned, but I would love
to learn about it!

Thank you, Tina!

--
Nona

It can't be an append query because I am not
appending records from another table or another database

well, wait a minute. you have the patients listed in a table, right? and you
have some method of identifying those patients who meet the certain criteria
you spoke of, right? and you have the service code(s) stored somewhere, or
know what it/they is/are, right? and you have the number of approved units
value(s) somewhere, right? whether all of this data is stored in one or more
tables in your database, or some of it is determined by the user according
to specific business rules (ex: patients who meet x criteria get 10 units,
while patients who meet y criteria get 20 units), you must have a way to
generate the data you need. and if you do, you can append or "insert" it to
a table.

with some more details of where your data is coming from, we can probably
help you write an Append/Insert query to do what you want.

Is there a way to automatically add a record to a table for each person
who
meets certain criteria?

The authorizations table I am working with has about 4,000 records. It
holds
data about medical services for about 400 patients. The patient names and
other personal data are in a separate table.

For each of the patients who meet certain criteria (county, funding
source,
etc.) I want to add a new record in the authorization table that has the
same
information for each record - starting date of 1/1/09, ending date of
12/31/09, service code number, number of approved units of service.

It can't be an update query, because I want new records added to the
table,
not update current records. It can't be an append query because I am not
appending records from another table or another database.

Is there a simple way to accomplish this?

I am using Access 2003 and will appreciate your help! My friends don't
want
to have to enter each of these new records manually!

sounds like you want a deliberate cartesian product (created when you
add two tables to a query but don't join them - you end up with
tableA.RecordCount X tableB.RecordCount records). Create a query and
add both tables that you want to filter. Add the where clause/filters
to each table. Do not join the tables. Run the query. Once you have
it right, turn it into an append query...

Probably the best way of doing this is with a VERY SMALL dataset.
(like 3 records in one table and 2 in the other). Otherwise, it's a
nightmare figuring out if you have the logic right!) Once you have
the query working on the mini dataset, import the query into the
production database.

 

[tina]

hmm, okay. we're getting somewhere. i'm assuming that the Consumers table is
linked to the Authorizations table (though you didn't make that clear) in a
one-to-many relationship, as "one consumer may have many authorization
records, but each authorization record belongs to only one consumer." so you
want to add records to the authorization table for 2009, each new record
linked back to one specific consumer record.

if the above is correct, you need to start first by building a query that
returns the consumer records that meet the criteria you cited. if you need
to build a multi-table query to return those records, no problem - you're
not going to be updating the query dataset anyway. set the query to *show*
only those fields that you will want to write into the Authorizations table
(you can set criteria on a field without "showing" the field in the dataset,
just uncheck the field's Show checkbox in query Design view).

next review the list of required data below from your previous post:

The new record to be added will have the same fields already in the
Authorization table but will have new starting date, new ending date, the
number of authorizations approved, a service code number, and a status called
"Unmanaged. "

since the status is identical for every new record, you can hard-code that
value into the query. just add a calculated field to the query, as

NewStatus: "Unmanaged"

in query Design view, add the above expression into the Field row of the
first empty column. when you change to View mode, you'll see the NewStatus
column (calculated field) at the right of the datasheet, with "Unmanaged" -
without the quotes - in that field in every record.

if every new record will have the same starting and ending dates, the same
number of approved authorizations, and same service code number, then you
can hard-code that data into the query as well, same as you did for the
status. if those values may vary depending on certain criteria, you may be
able to assign the appropriate values by making use of DLookup() function,
and/or IIf() function, to name a couple of options. or you may be able to
link more tables into the query as needed to pick up the appropriate values.

once you can successfully display the correct records in the query, then go
back to Design view and change the query to an Append query. note: if you
find that you're not able to return *all* the correct data for the specific
records you want, still another option may be to append some basic
identifying data into the Authorizations table, then use one or more Update
queries to "finish" setting up the data in the new records.

i recommend that you make a copy of your live database, and work in the copy
until you succeed in setting up the new records correctly. then you can
export the query(s) you used into your live database and run the setup "for
real". and you'll be ready to do it again next year, without having to
reinvent the wheel.

hth

 

[Nona]

Thank you so much, Tina.
I worked on this almost all day yesterday and essentially did exactly what
you prescribed! I apologize for not describing fully what I was doing, but
you inferred everything exactly right. It's really nice to hear from an
expert that my work was not in vain and couldn't have been done in two
minutes!

I used a one to many query (patient to many authorizations) to make a new
table. Then I deleted the columns of the new table that would not be part of
the auth table. Then I manually deleted the duplicate patients in the new
MakeTable (couldn't figure any other way to do that!) Then I created an
update query and updated the records on the MakeTable with the new 2009 data.
Then I created a new query from this table and changed it to an append query,
then appended the new records from the MakeTableto the database.

And yes, I did all this experimenting on a copy. I won't actually make these
updates until January 1, and even then, I will have a back-up in case I screw
up along the way!

Again, it's good to know that my process was correct. If there is an Access
procedure for eliminating those duplicate names in the new MakeTable, I would
love to hear about that. That would save loads of time.

Thanks for your help! I really really appreciate it. Happy New Year!

 

[John W. Vinson]

Again, it's good to know that my process was correct. If there is an Access
procedure for eliminating those duplicate names in the new MakeTable, I would
love to hear about that. That would save loads of time.

You can set the "Unique Values" property of the query. If it contains only
fields pertaining to patients' biographical data (i.e. no fields from the
"many" side), right mouseclick the background of the tables in the query
design window; select Properties; and set the Unique Values property to Yes.

It would probably be a good safety check to do the maketable query, but it's
not actually required; you can base the Append query directly on the unique
Select query and save a step.

In any case, keep a backup!!!, and compact and repair the database after
you're done with the temporary tables; otherwise Access won't recover the
space that they occupied.

 

[Nona]

Wonderful! Thank You!!!! This is a great help.

 

[tina]

well, it happens that you did hear from an expert in this thread, hon, it
just wasn't me. but thanks for the "temporary field promotion", anyway!
and you're welcome for the help, glad it worked out for you.

 

[Nona]

I updated the 2009 records today and all went well. The only problem was that
I had to manually delete the duplicate patient records from the MakeTable.
That took at least an hour to go through about 2500 records and eliminating
all but about 687. The query I used to create the MakeTable was from the
patient table (which has no duplicates) and also from the auth table (which
does have duplicates). So John's suggestion to check Unique Values property
wouldn't work because there were multiple authorizations for most patients in
the new query.

Everything else worked right on target! Thanks for your help!

 

[John W. Vinson]

I updated the 2009 records today and all went well. The only problem was that
I had to manually delete the duplicate patient records from the MakeTable.
That took at least an hour to go through about 2500 records and eliminating
all but about 687. The query I used to create the MakeTable was from the
patient table (which has no duplicates) and also from the auth table (which
does have duplicates). So John's suggestion to check Unique Values property
wouldn't work because there were multiple authorizations for most patients in
the new query.

Everything else worked right on target! Thanks for your help!

gnnnnnnn....

Sorry you didn't ask for amplification, Nona. A Delete query based on an
appropriate join would have deleted the duplicates; the Unique Values property
WILL work if it's done correctly; and the MakeTable query is not necessary.

It's done for this year but just for the record, the query *could have been
adapted* to do what you want.

 

[tina]

John is the expert you can be happy to have in this thread, Nona, and i have
no doubt that he is right. if you were deleting *duplicate* records from the
MakeTable, then the original Select query was not built appropriately to
suit your needs. a properly built Select query would have pulled only the
dataset you needed, and could have been changed to an Append query to add
the new records directly into the Authorizations table, without additional
work.

you've got the job done for 2009, which is good. but you might want to
continue working on the concept, not only for next year, but also because
everything you learn about manipulating data with SQL statements (queries)
will increase your ability to harness the power of Access in other
situations as well.

hth

 

[Nona]

Thanks for this additional information. I'll definitely work on this. I've
never done a "delete" query, but I'll figure it out! John, I know you said
the Maketable Query was an unnecessary step, but I really felt safer updating
the data in the new table before appending.

This is precisely why I love working with Access - I never get tired of the
challenges, and there's always something new to learn. I especially
appreciate the help I've gotten from this discussion group site. No where
else could I have gotten such good training on so many Access issues. Many
Thanks.

 

[John W. Vinson]

Thanks for this additional information. I'll definitely work on this. I've
never done a "delete" query, but I'll figure it out! John, I know you said
the Maketable Query was an unnecessary step, but I really felt safer updating
the data in the new table before appending.

This is precisely why I love working with Access - I never get tired of the
challenges, and there's always something new to learn. I especially
appreciate the help I've gotten from this discussion group site. No where
else could I have gotten such good training on so many Access issues. Many
Thanks.

If you'ld like help for future reference, please post the SQL view of the
MakeTable query; I'd be glad to show you how to tweak it to be unique.

Thanks, Tina, for taking care of Nona and so many others here - and apologies
for jumping in on your thread!

 

[Nona]

I can't believe in my good fortune in getting so much help!

Here is the MakeTable query statement I used.
SELECT qryConsumers.CLN, qryConsumers.CFN, qryConsumers.IsAge,
qryConsumers.County, qryAuthRecords.ConsumerNo2, qryAuthRecords.AuthStart,
qryAuthRecords.AuthEnd, qryAuthRecords.ServiceCode,
qryAuthRecords.AuthsStatus, qryAuthRecords.CostCtr, qryAuthRecords.Source
INTO tblToAppend
FROM qryConsumers LEFT JOIN qryAuthRecords ON qryConsumers.ConsumerNo1 =
qryAuthRecords.ConsumerNo2
WHERE (((qryConsumers.County)="Buncombe") AND
((qryAuthRecords.Source)="IPRS"));

This includes the fields that are to be updated in the next step. The
underlying consumers query has already selected active consumers and computes
the patients' ages.

I would really like to learn how to eliminate the duplicate records because
it's very likely this exercise will have to be repeated with another service
code. If this isn't enough information, please let me know.

Thank you very very much.

 

[John W. Vinson]

I can't believe in my good fortune in getting so much help!

Here is the MakeTable query statement I used.
SELECT qryConsumers.CLN, qryConsumers.CFN, qryConsumers.IsAge,
qryConsumers.County, qryAuthRecords.ConsumerNo2, qryAuthRecords.AuthStart,
qryAuthRecords.AuthEnd, qryAuthRecords.ServiceCode,
qryAuthRecords.AuthsStatus, qryAuthRecords.CostCtr, qryAuthRecords.Source
INTO tblToAppend
FROM qryConsumers LEFT JOIN qryAuthRecords ON qryConsumers.ConsumerNo1 =
qryAuthRecords.ConsumerNo2
WHERE (((qryConsumers.County)="Buncombe") AND
((qryAuthRecords.Source)="IPRS"));

This includes the fields that are to be updated in the next step. The
underlying consumers query has already selected active consumers and computes
the patients' ages.

I would really like to learn how to eliminate the duplicate records because
it's very likely this exercise will have to be repeated with another service
code. If this isn't enough information, please let me know.

Thank you very very much.

What's the ultimate goal, in terms of which fields update which table?

For that matter... if you already have consumers in the Consumers table, why
do you need to create a new consumers table?

Let's take a step back: where is the information coming from, and where is it
going? And why do new records need to be added rather than just accumulating
records in two (or more?) related tables?

 

[tina]

apologies

for jumping in on your thread!

are you kidding? an op can use all the help s/he gets, in any thread, and i
always learn something from your posts, John, as well as the other MVPs, and
some of the other skilled folks here - even when y'all don't have to
actually correct something i did wrong!

 

[aratony]

Is there a way to automatically add a record to a table for each person
who
meets certain criteria?

The authorizations table I am working with has about 4,000 records. It
holds
data about medical services for about 400 patients. The patient names and
other personal data are in a separate table.

For each of the patients who meet certain criteria (county, funding
source,
etc.) I want to add a new record in the authorization table that has the
same
information for each record - starting date of 1/1/09, ending date of
12/31/09, service code number, number of approved units of service.

It can't be an update query, because I want new records added to the
table,
not update current records. It can't be an append query because I am not
appending records from another table or another database.

Is there a simple way to accomplish this?

I am using Access 2003 and will appreciate your help! My friends don't
want
to have to enter each of these new records manually!