473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Weird query, need to copy information...

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
4 2067

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: redneck_kiwi | last post by:
Hi all: I have a really weird problem. I am developing a customer catalog system for my company and as such have delved into sessions for authentication and access levels. So far, I have managed...
5
by: jason | last post by:
I could sure use some conceptualization and query help with a Page Watch System I am building in Access 2000 and Asp. I need to cycle through databae and generate a compiliation query email that...
13
by: Dmitry Tkach | last post by:
Hi, everybody! Here is a weird problem, I ran into... I have two huge (80 million rows each) tables (a and b), with id as a PK on both of them and also an FK from b referencing a. When I try to...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
2
by: jerrygarciuh | last post by:
Hi all, The following script is giving me weird problems. I have in this directory an index.php and hurricane.php. If the script gets $i = 'on' it is supposed to back up the current index...
3
by: zacaronius | last post by:
Hi, I am working on a database that was built for our operation, but the only problem is that it has almost 2.6 million records in a table, so it takes forever to run a report. I was wondering if...
10
by: Ben | last post by:
Hi, i have a weird problem and i don't know who is responsible for this: IIS, excel or asp.net. My problem: we use an asp.net 2.0 application under IIS 6.0 (server 2003 sp2) which must write...
6
by: DBMonitor | last post by:
I have a table on a database that contains 18million records. I need to design a system that queries this table to produce fast counts. I have got counts for multiple criteria down to only a few...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.