Adding records to a table

> 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" wrote in message

> 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!
>
>
> --
> Nona
 >> Stay informed about: Adding records to a table 

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


"tina" wrote:

> > 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" wrote in message
>
> > 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!
> >
> >
> > --
> > Nona
>
>
>
 >> Stay informed about: Adding records to a table 

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


" " wrote:

> On Dec 29, 9:03 pm, Nona wrote:
> > 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
> >
> > "tina" wrote:
> > > > 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" wrote in message
> > >
> > > > 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!
> >
> > > > --
> > > > Nona
>
> 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.
>
 >> Stay informed about: Adding records to a table 

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" wrote in message

> 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
>
>
> "tina" wrote:
>
> > > 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" wrote in message
> >
> > > 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!
> > >
> > >
> > > --
> > > Nona
> >
> >
> >
 >> Stay informed about: Adding records to a table 

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!
--
Nona


"tina" wrote:

> 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" wrote in message
>
> > 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
> >
> >
> > "tina" wrote:
> >
> > > > 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" wrote in message
> > >
> > > > 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!
> > > >
> > > >
> > > > --
> > > > Nona
> > >
> > >
> > >
>
>
>
 >> Stay informed about: Adding records to a table 

On Wed, 31 Dec 2008 05:56:49 -0800, Nona
wrote:

>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.
--

John W. Vinson [MVP]
 >> Stay informed about: Adding records to a table 

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


"John W. Vinson" wrote:

> On Wed, 31 Dec 2008 05:56:49 -0800, Nona
> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
>
 >> Stay informed about: Adding records to a table 

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" wrote in message

> 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!
> --
> Nona
>
>
> "tina" wrote:
>
> > 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" wrote in message
> >
> > > 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
> > >
> > >
> > > "tina" wrote:
> > >
> > > > > 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" wrote in message
> > > >
> > > > > 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!
> > > > >
> > > > >
> > > > > --
> > > > > Nona
> > > >
> > > >
> > > >
> >
> >
> >
 >> Stay informed about: Adding records to a table 

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!




--
Nona


"tina" wrote:

> 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" wrote in message
>
> > 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!
> > --
> > Nona
> >
> >
> > "tina" wrote:
> >
> > > 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" wrote in message
> > >
> > > > 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
> > > >
> > > >
> > > > "tina" wrote:
> > > >
> > > > > > 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" wrote in message
> > > > >
> > > > > > 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!
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Nona
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>
 >> Stay informed about: Adding records to a table 

On Thu, 1 Jan 2009 16:07:01 -0800, Nona
wrote:

>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.
--

John W. Vinson [MVP]
 >> Stay informed about: Adding records to a table 

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" wrote in message

> 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!
>
>
>
>
> --
> Nona
>
>
> "tina" wrote:
>
> > 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" wrote in message
> >
> > > 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!
> > > --
> > > Nona
> > >
> > >
> > > "tina" wrote:
> > >
> > > > 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" wrote in message
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "tina" wrote:
> > > > >
> > > > > > > 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" wrote in message
> > > > > >
> > > > > > > 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!
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Nona
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >
 >> Stay informed about: Adding records to a table 

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.
--
Nona


"tina" wrote:

> 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" wrote in message
>
> > 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!
> >
> >
> >
> >
> > --
> > Nona
> >
> >
> > "tina" wrote:
> >
> > > 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" wrote in message
> > >
> > > > 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!
> > > > --
> > > > Nona
> > > >
> > > >
> > > > "tina" wrote:
> > > >
> > > > > 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" wrote in message
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "tina" wrote:
> > > > > >
> > > > > > > > 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
> > > > > > >
> > > > > > >
 >> Stay informed about: Adding records to a table 

On Thu, 1 Jan 2009 17:19:15 -0800, Nona
wrote:

>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!

--

John W. Vinson [MVP]
 >> Stay informed about: Adding records to a table