Pin number code issue, 2

[enigmatic1]

Hello,
I am writing an access script that takes account information from one DB and uses a portion to create PIN numbers.
There are 2 seperate sides to this DB as well. I am also attempting to have it verify the users from one and when duplicates are there use the same PINs for both.
Although I have a portion of the script done it seems slightly buggie and I was hoping to maybe get some input on where mine is wrong.

Thanks in advance,

 

[randy700]

Now that you've explained what your program does, tell us what it doesn't do.


Randy

 

[enigmatic1]

Ok,
here is the situation, it is supposed to create a 6 digit pin, then in the next macro it is supposed to take that 6 digit pin and match it to SSNs that are duplicates in the second table.
If they are dupliacte SSNs in both table then they should have duplicate PINs. If not they should have their own PINs.
However it creates dupliactes sometimes and sometimes not. Also once I do a copy to move the PINs to the second tables it erases what exists in the 2nd table and creates new ones.
In other words its creating the PINs just not making sure the duplicates are correct. If you want to see the script let me know for security reasons I cannot publish it however.

 

[fneily]

Can you post your table structures? FieldNames, primary keys. If you have SSN's in two different tables, your tables are not normalized/database is design incorrectly. Have you read such articles as:
Fundamentals of Relational Database Design

http://r937.com/relational.html

And it would seem we would need to see your code to see what your doing. The data you can change.

 

[enigmatic1]

fneily,
i can post the script however not the tables. Although I truly prefer not to post the script because of the sensitive nature of the script. If you would like to see the script I will gladly email you the basic codeline.

Thank you,

 

[enigmatic1]

Ok,
I got an OK to post since you are all tech and programmers anyway,

=======================================================================================
Copy Lenders to Loans

UPDATE [TDS Loans] INNER JOIN [TDS Lenders] ON [TDS Loans].SSN = [TDS Lenders].SSN SET [TDS Loans].WPC_PIN = [TDS Lenders].[WPC_Pin]
WHERE ((([TDS Lenders].SSN)=[TDS Loans].[SSN]) AND (([TDS Loans].SSN)<>''));


Copy Loans to Lenders

UPDATE [TDS Lenders] INNER JOIN [TDS Loans] ON [TDS Lenders].SSN = [TDS Loans].SSN SET [TDS Lenders].WPC_PIN = [TDS Loans].[WPC_Pin]
WHERE ((([TDS Loans].SSN)=[TDS Lenders].[SSN]) AND (([TDS Lenders].SSN)<>''));

=======================================================================================

Lender PIN Gen

UPDATE [TDS Lenders] SET [TDS Lenders].WPC_PIN = Int(Rnd(Now())+Rnd(Right([TDS Lenders].[SSN],4))*"800000"+"120000")
WHERE ((([TDS Lenders].WPC_PIN) Is Null));
=======================================================================================


Loan PIN Gen

UPDATE [TDS Loans] SET [TDS Loans].WPC_PIN = Int(Rnd(Now())+Rnd(Right([TDS Loans].[SSN],4))*"800000"+"120000")WHERE ((([TDS Loans].WPC_PIN) Is Null));

=======================================================================================


I appreciate the effort and feedback as well.

 

[fneily]

A little confused. You have two tables - TDS_Loans and TDS_Lenders. Is the SSN in the TDS_Loans the SSN of the person acquiring the loan or the SSN of the Lender? If it is the SSN of the person acquiring the loan, then it should not be in the Lenders table.
If it's the Lenders SSN, then it should not be in the Loan table. Since you didn't post your table structures. there should be a primary key of the Lender table, like LenderID, that should be used for the connection to the Loan table.
Another question, a lender can make many loans, can a loan belong to many lenders? I've seen this in real estate tranactions. Let's go with that. Then you have a many-to-many relationship. You must create a junction table then. So have a
TDS_Transaction table with
TransID Primary Key
LenderID
LoanID
Date
WPC_Pin
other common fields.

Now you'd have your function generate a PIN and its placed in only one place, the table that, by its nature, has Lender and Loan with the same SSN.

But you give little for me to go on. Maybe someone else out here sees it differently.

 

[ProgramError]

You're doing a fine job Fneily.

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[enigmatic1]

Ok,
The users in both loans and lenders have have SSNs, some may be both lender and or loan. Others may be just a lender or the loan account.
Although many are loaned accounts many are also serviced house lender accounts as well.
So you may have a lender that is both lender and loan.

I know its a little confusing...

 

[enigmatic1]

It works fine for my application. However what is happening is it is taking data that already exists in a tabled format from another program. This data is scanned and creates random PINs for the accounts. Now remembering also these PINs must be the same for the user no matter whether he is the Loan or Lender.
Then this populates 2 seperate sides of the DB, on side is lenders and one side is loans. However is the SSN is the same in loans and lenders the PIN must also be the same.
After this is all done I have to make a daily update on PINs for new accounts without it changing the current PINs.
This is then exported to a MYSQL DB for further deployment.

I hope this is a little more help for understanding the layout if not just yell back and I will try to clarify more.


Thanks,

 

[randy700]

It works fine for my application

Apparently not, or you wouldn't be having these problems.

The thing that jumps out at me right away is the fact that you have SSNs in both tables, that sometimes refer to the same individual.

I would consider creating a separate table that identifies the individuals, storing the SSNs in this table only. Make sure to include a primary key that can be used in the other tables to identify the loan and/or lender.


Randy

 

[enigmatic1]

Here is the problem I seem to be having. Also the issue is we cannot create other tables due to the fact we are using 3rd party. And this is an edit on the fly.
My problem is yes it creates my PINs as I want however it is sporadic on the duplicational factor for a lender with many accounts. It seems to sometimes create the same PIN for the lenders or loans and sometimes it creates different ones. Now also after it creates the initial PINs lets say in the Lenders Tables those have to be checked against the SSN in the Loans Tables. If they are the same SSN then the PIN should be the ssame. And if not then a new PIN generated for them.
One issue I have noticed is when I run the copy from Lenders to Loans if there are any existing PINs it clears them and makes new ones. Also once I copy back it erases existing ones also.

There are several little bugs that create one major issue.

 

[PHV]

Copy Lenders to Loans

UPDATE [TDS Loans] INNER JOIN [TDS Lenders] ON [TDS Loans].SSN = [TDS Lenders].SSN
SET [TDS Loans].WPC_PIN = [TDS Lenders].[WPC_Pin]
WHERE NOT ([TDS Lenders].[WPC_Pin] Is Null);

Copy Loans to Lenders

UPDATE [TDS Lenders] INNER JOIN [TDS Loans] ON [TDS Lenders].SSN = [TDS Loans].SSN
SET [TDS Lenders].WPC_PIN = [TDS Loans].[WPC_Pin]
WHERE NOT ([TDS Loans].[WPC_Pin] Is Null);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[enigmatic1]

The initial creation seems to be one of the biggest buggers. It makes no sense why in some duplicate SSN accounts it will assign the same PIN however in other duplicate SSN accounts it will not assign duplicate PINs.

 

[PHV]

As you use Now() in two different queries it seems logical to me to NOT get the same value ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[enigmatic1]

So then would this script be correct or not? Im a little confused LOL. Would these as shown create PIN that would be the same for the same SSN and yet dif for dif SSNs.

Lender PIN Gen

UPDATE [TDS Lenders] SET [TDS Lenders].WPC_PIN = Int(Rnd(Now())+Rnd(Right([TDS Lenders].[SSN],4))*"850000"+"120000")
WHERE ((([TDS Lenders].WPC_PIN) Is Null));
=======================================================================================


Loan PIN Gen

UPDATE [TDS Loans] SET [TDS Loans].WPC_PIN = Int(Rnd(Now())+Rnd(Right([TDS Loans].[SSN],4))*"850000"+"120000")WHERE ((([TDS Loans].WPC_PIN) Is Null));

=======================================================================================

 

[PHV]

I'd use this sequence:
1) Lender PIN Gen
UPDATE [TDS Lenders]
SET WPC_PIN = Int(Rnd(Now())+Rnd(Right([SSN],4))*850000+120000)
WHERE WPC_PIN Is Null;
2) Copy Lenders to Loans
UPDATE [TDS Loans] INNER JOIN [TDS Lenders] ON [TDS Loans].SSN = [TDS Lenders].SSN
SET [TDS Loans].WPC_PIN = [TDS Lenders].WPC_Pin
WHERE Nz([TDS Loans].WPC_PIN,0) <> [TDS Lenders].[WPC_Pin]);
3) Loan PIN Gen
UPDATE [TDS Loans]
SET WPC_PIN = Int(Rnd(Now())+Rnd(Right([SSN],4))*850000+120000)
WHERE WPC_PIN Is Null;
4) Copy Loans to Lenders
UPDATE [TDS Lenders] INNER JOIN [TDS Loans] ON [TDS Lenders].SSN = [TDS Loans].SSN
SET [TDS Lenders].WPC_PIN = [TDS Loans].WPC_PIN
WHERE Nz([TDS Lenders].WPC_PIN,0) <> [TDS Loans].[WPC_Pin]);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[enigmatic1]

it still seems to not generate identicle PINs in that initial generation for the first set of tables.
Really makes no sense however it theoretically should generate the same PIN if the SSN is the same.

 

[PHV]

it theoretically should generate the same PIN if the SSN is the same
You really think so ?
So, why using Rnd() 2 times and Now() ?
Your PIN generation formula is far away from deterministic ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[enigmatic1]

Yah Im kinda stumped, it should generate the same PINs for the same SSN, however it seems not to be doing that. Im looking for other issues that may be creating this. I just noticed that yes it creates the copies correctly dup the PINs to the same SSNs. However on the Create Buttons it is not creating the same PIN based on duplicate SSNs.