how to test if table has certain record and if not insert that record 1

[avenuw]

Hello all,

I am trying to test if my table has certain values for each person (person is equavilant to several records in my table) and if not then insert a new record with that value. For example, in my db each person has several licenses 1-9. So ideally each person will have 9 rows ( each row with a different license number)

What I am trying to do is test if each person has all 9 licenses and if not, insert a record for the person with the appropriate license number. I am not sure how to go about doing that. Any suggestions would be greatly appreciated.

Thanks,
AV

 

[lewds]

Hi...

This might be a general outline of what you want..

Do Until UserList.EOF
VarUser = [User from UserList]
DO Until LicenseList.EOF
VarLicense = [License from LicenseList]
Use DLookup for a result of VarUser and VarLicense
If no result is found, insert into table VarUser and VarLicense
Loop
Loop

 

[PWise]

insert into Nameslicenses (Personid,licensid )
Select Personid,licensid 
from names,licenses 
left join Nameslicenses 
on names.Personid = Nameslicenses.Personid 
and  licenses.licensid =Nameslicenses.licensid 
where  Nameslicenses.Personid is null

1) names table with all names
2)licenses table with all 9 licenses
3)Nameslicenses table with list of people with their Licenses

 

[avenuw]

I would like to perform essentially a merge.

MERGE INTO table_name USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT column1 [, column2 ...] VALUES (value1 [, value2 ...])

but i am unsure if it is possible in access. The code below is what i have so far...

Public Sub insert_missing_lic_sub()
DoCmd.SetWarnings False

Dim num As Long
Dim sql As String

For num = 201 To 210
sql = "INSERT INTO temp_tbl( person,license, instructor, location,num1,num2,num3,num4)"

sql = sql + "SELECT person, " & num & ",instructor,location , 0 , 0 , 0 , 0 "
sql = sql + "FROM temp_tbl "
sql = sql + "WHERE (((temp_tbl.Person)='Doe,John') AND ((temp_tbl.[license] =" & num & ")))"

'Print to immediate window
Debug.Print sql
'Halt execution for testing purposes
'Stop
DoCmd.RunSQL sql

'Next num
DoCmd.SetWarnings True
Next
End Sub



But in my where clause, it is looking for that license record to insert the record. IF I change the where clause to <>&num& it then inserts x amount of records that ofcourse dont match the license number. But I just want to insert one record for each missing licsense.

I cant seem to think myself out of this one!

 

[avenuw]

Thanks lewds, i was not aware of Dlookup, but I think this is the best way to go. I am trying it now, but I keep getting the following error: "You cancelled the previous operation" it points to the following line:

Dim lic As String

lic = DLookup("[lic num]", "sqlSearch", "[lic num]=99876201 and person='smith,jim'")


As well I am trying to follow your logic of the code above lewds but should i have an array that loops to get me varUser and varLic?

Thanks,
AV

 

[danvlas]

I'd put my money on pwise's SQL...



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[avenuw]

Pwise post is helpful, but unforchantely it's not what i need, because of several reasons:
1) person and license number is from one table, and person and license number make up my primary key. ( I dont believe there is another way around this- i have 12 columns all dates and they are all functionally dependant on my composite key( person, license number)- to my knowledge this is the most it can be normalized for performance reasons.
2)the left join is assuming that license number is null/missing for that person. But each person might have license number 1-5, but not 6-9,etc.


Public Sub insert_missing_lic_sub()
DoCmd.SetWarnings False

Dim num As Long
Dim sql As String


For num = 201 To 210

sql = sql + "SELECT person, " & num & ",instructor,location , 0 , 0 , 0 , 0 "
sql = sql + "FROM temp_tbl "
sql = sql + "WHERE (((temp_tbl.Person)='Doe,John') AND ((temp_tbl.[license] =" & num & ")))"

'Print to immediate window
Debug.Print sql
'Halt execution for testing purposes
'Stop
DoCmd.RunSQL sql

'Next num
DoCmd.SetWarnings True
Next
End Sub

I want to insert a record based on record already avaliable for the person except with a different license number (num). The problem with sql query above that it only inserts a record when the license number is equal to num. I hope that makes sense.

Thanks

 

[danvlas]

A left join on the basis of your composite key where any required field in the table on the right is null will give you EXACTLY the records that do not exist and need to be inserted.

An Inner Join would work for the update.

I still keep my money on pwise's post.

If you post the exact table structures, you may get the exact SQL.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[lewds]

Hi....

Assuming a Table for Users and another for Lic

Table Users (tblUsers)
USER
bob
carol
ted
alice

Table for Lic (tblLic)
LIC
L1
L2
L3
L4

Table for Info (tblInfo)
USER LIC

'DLookup returns a Variant


Dim db As DAO.Database
Dim rsUsers As DAO.Recordset
Dim rsLic As DAO.Recordset
Dim VarUser As String
Dim VarLic As String
Dim VarResult As Variant

Set db = CurrentDb()
Set rsUsers = db.OpenRecordset("tblUsers", dbOpenSnapshot)
Set rsLic = db.OpenRecordset("tblLic", dbOpenSnapshot)

rsUsers.MoveLast
rsUsers.MoveFirst

Do Until rsUsers.EOF
VarUser = rsUsers(0)
rsLic.MoveLast
rsLic.MoveFirst
Do Until rsLic.EOF
VarLic = rsLic(0)
VarResult = DLookup("[Lic]", "tblInfo", "[User] = '" & VarUser & "' AND [Lic] = '" & VarLic & "'")
If IsNull(VarResult) Then
DoCmd.RunSQL "INSERT INTO tblInfo " & "(User,Lic) VALUES " & "('" & VarUser & "','" & VarLic & "');"
End If
rsLic.MoveNext
Loop
rsUsers.MoveNext
Loop

Set rsUsers = Nothing
Set rsLic = Nothing
Set db = Nothing

Tested...........

Table for Info (tblInfo)
USER LIC
bob L1
bob L2
bob L3
bob L4
ECT...

The external Loop goes through each User and the internal loop checks each Lic.

 

[PWise]

lewds:

My Insert statement does exactly as your double loop triple recordset multiple dlookup function with one insert statemet

dim SqlStr as string
SqlStr="insert into Nameslicenses (Personid,licensid ) " & _
"Select Personid,licensid from names,licenses left " & _
"join Nameslicenses on names.Personid=" & _
"Nameslicenses.Personid and  licenses.licensid = " & _
"Nameslicenses.licensid where  Nameslicenses.Personid " & _
"is null"

docmd.runsql SqlStr

 

[danvlas]

Well, there are only 2 recordsets, which doesn't actually make any difference...
To my knowledge, no recordset/looping can compete with a well written SQL statement. When a DLookup is involved for each loop, it's time to reconsider...
Another thing, ignored so far: one key, 12 date fields. Doesn't look too normalized to me. Of course, I may be mistaken, but it looks like a classic many-to-many relationhip is needed there.
Those dates relate to the composite key. But I'm almost sure they
also relate to something else - maybe the month of the year?




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[lewds]

Hi...

pwise:

My submission should no way infer that your solution was incorrect or not elegant.

My submission is only another way to skin the cat.

This is just a Recordset Method as oppose to an Array mentioned by avenuw

If I have offended you in some way, Please let me apologize.

 

[avenuw]

Thanks Lewds for the code, I figured it out and havent checked the forum since.My code is very similar to yours.
But Pwise if you can help me normalize it further that would be great.

Let me show you my table structure

tbl_person consists of the following fields:
person
assistant
location
license #
Jun
Jul
Aug
Sep
Oct
nov
Dec
Jan
Feb
Mar
Apr
May

each of the month fields contain the number received for each person-license pair.I hope that makes sense. The assistant will differ each time for a person and so will the license number and location. So for each persons, I will have several license numbers, and different/ random location ( that is not linked to a particular license # or person or location). And the same for the other fields.


I think if I normalize any further i will be doing to many joins , but like you said a clean sql statment is always more efficient.

Thanks Pwise and Lewds.
AV

 

[avenuw]

OOPS...didnt read carefully.
Thanks danvlas for pointing out pwise's code.