By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Weird query, need to copy information...

P: n/a
I have two tables... one contains all the loan officer information
(name/address/phone/email/etc) the other is a NH license table which
lists has 3 columns - txtLOName, dtDateHireSent, dtDateTermSent and
the tables are joined on the LO's name.

I need to create two queries for this.. one for when I tell NH the LO
is hired, and one for when I tell them they're fired. It's the hiring
one I can't get straight.

Basically, I need listed on a continuous form all the LO's that exist
in the LoanOfficers table and not in the NHLOLicense table. (If
they're listed in the NHLOLicense table, that means I sent their
information to NH already). Then, I need it to automatically put the
date in the dtDateHireSent field.

I have the query set up as:
SELECT tblNHLOLicense.txtLOName, tblNHLOLicense.dtDateHireSent,
LoanOfficers.Name, LoanOfficers.Address, LoanOfficers.City,
LoanOfficers.State, LoanOfficers.Zip, LoanOfficers.Phone,
LoanOfficers.[SS#], LoanOfficers.HireDate, LoanOfficers.Inactive
FROM LoanOfficers LEFT JOIN tblNHLOLicense ON LoanOfficers.Name =
tblNHLOLicense.txtLOName
WHERE (((LoanOfficers.Inactive) Is Null))
WITH OWNERACCESS OPTION;

Which does exactly what I want, with one possible exception. I can't
get the names from LoanOfficers.Name to copy over into
tblNHLOLicense.txtLOName.

Someone a few days ago gave me this loop to use for a different
purpose:
(keep in mind, I modified this loop to what I thought was right, but I
accidentally deleted that. So this is the loop pre-modification)
Dim rst As Object
Set rst = Me.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
Me.Bookmark = rst.Bookmark
Me.DateTermStatementSent = Date
rst.MoveNext
Loop
Set rst = Nothing

Which, when I tried to modify it for what I needed, it added the
form's name to both LOName fields instead of copying the name from
LoanOfficers.Name to tblNHLOLicense.txtLOName.

I hope that makes sense... this has burned me out
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

On 26 Sep 2003 09:05:50 -0700, tu*****@hotmail.com (Andromeda) wrote
in comp.databases.ms-access:
SELECT tblNHLOLicense.txtLOName, tblNHLOLicense.dtDateHireSent,
LoanOfficers.Name, LoanOfficers.Address, LoanOfficers.City,
LoanOfficers.State, LoanOfficers.Zip, LoanOfficers.Phone,
LoanOfficers.[SS#], LoanOfficers.HireDate, LoanOfficers.Inactive
FROM LoanOfficers LEFT JOIN tblNHLOLicense ON LoanOfficers.Name =
tblNHLOLicense.txtLOName
WHERE (((LoanOfficers.Inactive) Is Null))


should be (warning: air-code):

insert into tblNHLOLicense (txtLOName, dtDateHireSent)
select L.Name, now()
FROM LoanOfficers L LEFT JOIN tblNHLOLicense N
ON L.Name = N.txtLOName
WHERE L.Inactive Is Null and n.txtLOName is null

Two things to note:

1) I used now() to timestamp the dtDateHireSent field, but you may
want to just grab the current date and not time too, or use a
parameter to enter something other than todeay's date.

2) I'm not sure why you use 'L.Inactive Is Null' in your where clause,
but left it in in case you have some undeclared reason for it. The
'n.txtLOName is null' part of the where clause does the work of
checking for entries in one table that are not in the other table.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.800.987.7716 1.619.839.3900
Nov 12 '05 #2

P: n/a
TC

Andromeda <tu*****@hotmail.com> wrote in message
news:c3**************************@posting.google.c om...

(snip)
I have two tables... one contains all the loan officer information
(name/address/phone/email/etc) the other is a NH license table which
lists has 3 columns - txtLOName, dtDateHireSent, dtDateTermSent and
the tables are joined on the LO's name.

What happens when the second John Smith joins the LO team?

TC

Nov 12 '05 #3

P: n/a
I will try the query and let you know how it goes.

As for why I have LOName as my primary key, I know it will become a
problem at some point. Considered using an auto number, which would
allow me to have more than one John Smith, but it doesn't stop users
from entering in two of the same John Smith which is a huge problem and
would pretty much make the database worthless for tracking. Considered
changing the field to not allow duplicates, but then it might as well be
a primary key. Considered then doing a combo primary key of both the
name and address which would, in theory, block the same John Smith from
being entered twice, but that leads to two problems.. the first being if
someone types the person's address as 123 Main Street and someone else
types it as 123 Main St I'm back at square one. The other is I was
ripped apart in another forum and told that using a combo key is just
"bad programming". So I'm more than happy and actually looking forward
to changing that if you can point out another way to avoid duplicates of
the same person.

- And
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
TC
It's a "six of one, half a dozen of the other" situation.

You simply can not afford to link tables by person name in any serious
application. However, you then run into the problem that you descriube,
namely: if the user enters two John Smiths, are they the same person (eg. a
duplicate record), or two different people?

This is what some of my systems do. First, they ensure that each unique
person has a unique ID. If a user enters some data using that ID, it goes to
that specific person, regardless of any other people with the same name. So
if there are three John Smiths, with person IDs 123, 456 & 789, and a user
enteres a record for person ID 456, it is quite clear which John Smith, that
is.

But if a user chooses to enter against a person's *name* - perhaps because
they can't remember the person's ID - then, the system does a quick check
for "similar" names already on file. If any similar names are found, a list
is displayed, & the user is asked to say whether the person who they are
entering, is really one of the people shown on that list. So if there were 3
John Smiths already on file, & the user enters a new record for John Smith,
the system would say: "Er, is *that* John Smith, actually one of *these*
John Smiths? John Smith, mechanic, 1 blah st; John Smith, painter, 2 Ocean
boulevard, ..."

By that means, I give maximum flexibility to my data entry users. But there
is lots & lots of code behind this.

HTH,
TC
"Andromeda Weiss" <tu*****@hotmail.com> wrote in message
news:3f***********************@news.frii.net...
I will try the query and let you know how it goes.

As for why I have LOName as my primary key, I know it will become a
problem at some point. Considered using an auto number, which would
allow me to have more than one John Smith, but it doesn't stop users
from entering in two of the same John Smith which is a huge problem and
would pretty much make the database worthless for tracking. Considered
changing the field to not allow duplicates, but then it might as well be
a primary key. Considered then doing a combo primary key of both the
name and address which would, in theory, block the same John Smith from
being entered twice, but that leads to two problems.. the first being if
someone types the person's address as 123 Main Street and someone else
types it as 123 Main St I'm back at square one. The other is I was
ripped apart in another forum and told that using a combo key is just
"bad programming". So I'm more than happy and actually looking forward
to changing that if you can point out another way to avoid duplicates of
the same person.

- And
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.