Append query?

  • Thread starter Thread starter CAD Fiend
  • Start date Start date
C

CAD Fiend

Hello,

I have a table (tbl_Owner_Info) with fields "First_Name" and "Last_Name"
that are linked via combo box to 2 other tables (lul_First_Names and
lul_Last_Names), which now have about 450 first & 300 last names. The
reason that I'm doing it this way is to assist the user in getting the
first (and last names more so) spelled correctly for legal purposes.

What will inevitably happen, is that a new name will come up, and the
user will obviously type it in. How do I write a query such that at the
end of their input session, the user can run a query that will take any
of the names that are now in the either the tbl_Owner_Info.First_Name or
tbl_Owner_Info.Last_Name and copy them to the tables
lul_First_Names.First_Name or lul_Last_Names.Last_Name?

And finally, so that I can have someone check the new entries into the
name tables to be sure that they are correct, is there a query (or
report?) that I can run to show how many new records have been inserted?

Or are these topics VBA issues and should be posted accordingly to the
VBA NG?

TIA.

Phil.
 
Phil

Are you saying you are recording the same fact in multiple tables? If so,
this violates one of the tenants of relational database design.

It sounds like you want to be able to verify/validate names. If this is
your objective, there are ways to do this that don't require the creation of
multiple duplicative tables & fields.

Good luck

Jeff Boyce
<Access MVP>
 
Hi -

When you put a combo box directly on a table, you have the option to either
"Limit" to the list, or to prevent items not on the list from being selected.

If you use a Form for your table input, and the ComboBox is linked to the
name field, there are some additional possibilities. For example, if an item
is not on the list, it can trigger a method that adds the item to the list
(and perhaps adds a date field to the same record, if you store the dates
along with the list items).

This IS a common thing, normally done with Access's native Visual Basic (VBA).

Thus, I suggest placing your table's fields on a form, adding a date field
or some other sort of flag to the table with the names, and asking further at
the Access Group: access.forms.

Phil Freihofner
 
Hello,

I have a table (tbl_Owner_Info) with fields "First_Name" and "Last_Name"
that are linked via combo box to 2 other tables (lul_First_Names and
lul_Last_Names), which now have about 450 first & 300 last names. The
reason that I'm doing it this way is to assist the user in getting the
first (and last names more so) spelled correctly for legal purposes.

What will inevitably happen, is that a new name will come up, and the
user will obviously type it in. How do I write a query such that at the
end of their input session, the user can run a query that will take any
of the names that are now in the either the tbl_Owner_Info.First_Name or
tbl_Owner_Info.Last_Name and copy them to the tables
lul_First_Names.First_Name or lul_Last_Names.Last_Name?
Click to expand...

Well... don't do it that way.

Instead, use the COmbo Box's NotInList event to add the value to the
tables, at the time that it's being entered.
And finally, so that I can have someone check the new entries into the
name tables to be sure that they are correct, is there a query (or
report?) that I can run to show how many new records have been inserted?
Click to expand...

Only by putting a timestamp (date/time field with a default of Now())
into the name tables, and running a query to search for new entries
since the last time they were checked.
Or are these topics VBA issues and should be posted accordingly to the
VBA NG?
Click to expand...

Yep.

John W. Vinson[MVP]
 
John,

See my comments below.

John said:
Well... don't do it that way.

Instead, use the COmbo Box's NotInList event to add the value to the
tables, at the time that it's being entered.
Click to expand...

Can you please put the code in here? Or can you point me to a website that
has a good explanation of this?
Only by putting a timestamp (date/time field with a default of Now())
into the name tables, and running a query to search for new entries
since the last time they were checked.
Click to expand...

Also here, John, if you could please provide me with some code or point me in
the direction of a good website to do this action.
 
Jeff,

See my comments below.

Jeff said:
Phil

Are you saying you are recording the same fact in multiple tables? If so,
this violates one of the tenants of relational database design.
Click to expand...

No. I don't want multiple tables with the same info.
It sounds like you want to be able to verify/validate names.
Click to expand...

Well, more like a compare action. I have one table called lul_First_Name with
one field called First_Name. That is my source table for the First_Name entry
field in the form. That field, First_Name belongs to the table tbl_Owner_Info.
When the user clicks in the First_Name field in the form, they can either begin
typing or select a name from the list box. If the name they type is ALREADY in
the table lul_First_Name, then it will automactically spell it out and they can
hit enter or tab and move on to the next field, but if the name they are typing
in IS NOT in the table lul_First_Name, then the user will continue typing it in,
thereby creating a "New Name", that should get added to the table
lul_First_Name.

Basically, all I want is to be able to look the two tables, COMPARE them to each
other, find the unique name in the table tbl_Owner_Info.First_Name field, then
APPEND it to the field in the look up list table lul_First_Names.First_Name.

Does this make sense? If so, what would be the code for that type of COMPARE
between the two tables?
 
Back
Top