INSERT COMPOSIT PRIMARY KEY 1

[accessjack]

I have two tables: tblDeposits (App, Bank, Account) and tblMatrix (App, Bank, Account, Year, Amount)

The composite primary key in tblDeposits is App + Bank + Account. The composite primary key in tblMatrix is App + Bank + Account + Year.

I have a form where user chooses Year from a combo box (cboYear), then hits a button to add the composite primary key to the tblMatrix if it does not exist. My code is not working, am I missing parenthesis or is it something else? The multipe AND statements get me confused on both the JOIN and the WHERE clause.

sql = "INSERT INTO [tblMatrix]([App], [Bank], [Account], [Year]) " & _
"SELECT [tblDeposits].[App], [tblDeposits].[Bank], [tblDeposits].[Account], " & "'" & cboYear.Column(4) & "'" & _
"FROM [tblDeposits] " & _
"LEFT JOIN [tblMatrix] " & _
"ON [tblDeposits].[App] = [tblMatrix].[App] " & _
"AND [tblDeposits].[Bank] = [tblMatrix].[Bank] " & _
"AND [tblDeposits].[Account] = [tblMatrix].[Account] " & _
"WHERE [tblMatrix].App Is Null " & _
"AND [tblMatrix].Bank Is Null " & _
"AND [tblMatrix].Account Is Null " & _
"AND [tblMatrix].Year Is Null"

 

[randy700]

cboYear.Column(4) would be the 5th column in your combo box. The first column is Column(0). Perhaps you need cboYear.Column(3).


Randy

 

[accessjack]

Randy, I've double checked it in the debugger and the year appears to be correct. I'm pretty sure the where clause it missing parenthesis () but I'm not sure how to next them.

 

[vich]

You need a ; at the end of the sql stmt.

"AND [tblMatrix].Year Is Null;

 

[accessjack]

I was able to add the composite primary key by doing the following:
1) Turn warnings off
2) Remove the full WHERE clause above
3) Turn warnings back on

This way access by default will try to add duplicate primary key but the error message is suppressed.

Here's sample data:
App Bank Account Year
1 1 1 2008

Trying to add the same values with Year = 2009.

The above code works, but I'm not sure it's ideal. Normally I'd just write add record where primary key does not exist, but since this is a multi-field primary key I can't just say where all fields are null because they are not. Perhaps I could concatenate them together then check, but that might be overkill.

Any other suggestions?

 

[PHV]

My code is not working
What happens ? Any error message ? Computer crash ? Unexpected behaviour ? ... ?

Anyway, you may try the following:

sql = "INSERT INTO tblMatrix (App,Bank,Account,[Year]) " & _
      "SELECT D.App,D.Bank,D.Account,'" & cboYear.Column(4) & "'" & _
      "  FROM tblDeposits D LEFT JOIN tblMatrix M" & _
      "    ON D.App=M.App AND D.Bank=M.Bank AND D.Account=M.Account" & _
      " WHERE M.App Is Null " & _
      "   AND M.Bank Is Null " & _
      "   AND M.Account Is Null " & _        
      "   AND M.Year Is Null"
CurrentDb.Execute sql

BTW, If tblMatrix.Year is defined as numeric then get rid of the single quotes.

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

 

[accessjack]

PHV,
Thanks for your post, the table alias trick really improves the readability of the code. You asked what happened...I got no error, just appends 0 records.

Imagine this sample data already in tblMatrix:
App Bank Account Year
1 1 1 2008

Now I want to add:
App Bank Account Year
1 1 1 2009

Since App, Bank, Account & Year fields have data (from 2008), the code does not work. What I need is code that says where the new full primary key does not exist yet in the table.

My workaround is to just remove the where clause. The 1st time the code executes, the record is added. The second time the code executes, access just rejects the record because it is set up as a composite primary key. This is probably not optimal because this table has over 1/2 million records. Ideally, I'd like to only add the new records each week.

Does that make sense?

 

[PHV]

what about this ?

sql = "INSERT INTO tblMatrix (App,Bank,Account,[Year]) " & _
      "SELECT D.App,D.Bank,D.Account,'" & cboYear.Column(4) & "'" & _
      "  FROM tblDeposits D LEFT JOIN (SELECT * FROM tblMatrix WHERE [Year]='" & cboYear.Column(4) & "') M" & _
      "    ON D.App=M.App AND D.Bank=M.Bank AND D.Account=M.Account" & _
      " WHERE M.App Is Null " & _
      "   AND M.Bank Is Null " & _
      "   AND M.Account Is Null " & _        
      "   AND M.Year Is Null"

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

 

[accessjack]

Thanks PHV, this worked, great tip.

I have two follow up questions.
1) The tblMatrix also has 12 balance fields in it. For the part of the code above (Line 3) where you say "SELECT * FROM tblMatrix..." would I be better off specifying fields?

2) I'm not very experience with nested selects. This code worked like a champ on my sample data of 10,000 records, but production data has 600,000 records. Do you think the nested select will hurt performance/speed?

Thanks again,
Jack

 

[PHV]

You may replace this
SELECT *
with this
SELECT App,Bank,Account,[Year]

As for performance I'd create an index on tblMatrix([Year])


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