SQL - Checking for existing record in table

  • Thread starter Thread starter troubledstudent
  • Start date Start date
T

troubledstudent

It looks like it should work, and I have painstakenly looked over it for a
long time. It runs, but the result is not what I want (checking if record
exists): The primary key is the retort id & labor code & process_name &
employee_name

dim sqlcheck as string
dim rs as dao.recordset
dim db as dao.database
set db = currentdb

sqlcheck = "SELECT distinctrow Processing.Retort_id, Processing.labor_code,
Processing.process_name, Processing.employee_name " & _
"FROM Processing WHERE (((Processing.Retort_id)=""" &
Me.retortid.Value & """) AND ((Processing.labor_code)=""" &
Me.select_labor.Value & """) AND ((Processing.process_name)=""" &
Me.select_process.Value & """) " & _
"AND ((Processing.employee_name)=""" & Me.select_employee.Value &
"""))"

'checks database for current record
Set rs = db.OpenRecordset(sqlcheck)

If (rs.BOF And rs.EOF) Then
' file is found
Else
'file is not found
end if

This is not working, any thoughts or suggestions on how to check if record
exists based off of 4 user defined values?

Thanks
 
The primary key is the retort id & labor code & process_name &I sincerely hope you don't really mean all these fields collectively form
your table's primary key. I'm hoping you mean the primary key is [retort
id], and your search fields are [labor code], [process_name] and
[employee_name]. I'll work on that assumption!

In any case, you say your code doesn't work. Your approach looks roughly
fine to me. What about it doesn't work? What's it doing? Have you supplied
ALL the values in the form controls?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
No, this is a composite key. there are many processes per retort, and the
key design actually works great.

Graham R Seach said:
I sincerely hope you don't really mean all these fields collectively form
your table's primary key. I'm hoping you mean the primary key is [retort
id], and your search fields are [labor code], [process_name] and
[employee_name]. I'll work on that assumption!

In any case, you say your code doesn't work. Your approach looks roughly
fine to me. What about it doesn't work? What's it doing? Have you supplied
ALL the values in the form controls?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


troubledstudent said:
It looks like it should work, and I have painstakenly looked over it for a
long time. It runs, but the result is not what I want (checking if record
exists): The primary key is the retort id & labor code & process_name &
employee_name

dim sqlcheck as string
dim rs as dao.recordset
dim db as dao.database
set db = currentdb

sqlcheck = "SELECT distinctrow Processing.Retort_id,
Processing.labor_code,
Processing.process_name, Processing.employee_name " & _
"FROM Processing WHERE (((Processing.Retort_id)=""" &
Me.retortid.Value & """) AND ((Processing.labor_code)=""" &
Me.select_labor.Value & """) AND ((Processing.process_name)=""" &
Me.select_process.Value & """) " & _
"AND ((Processing.employee_name)=""" & Me.select_employee.Value &
"""))"

'checks database for current record
Set rs = db.OpenRecordset(sqlcheck)

If (rs.BOF And rs.EOF) Then
' file is found
Else
'file is not found
end if

This is not working, any thoughts or suggestions on how to check if record
exists based off of 4 user defined values?

Thanks
Click to expand...
Click to expand...
 
No, this is a composite key.
A composite key can be primary, which I suspect this is.
So far. What if you have 2 people with the same name? That situation is not
so rare.

A primary key, even a composite one, must be guaranteed to be unique. A
field can only be a candidate key if it is unique within itself.
Employee_name doesn't satisfy that criterion, because you can have more than
one person with the same name. In such a case, your (composite) primary key
fails; you might be able to save the data, but the data will be wrong.

Additionally (and there is debate about the merits of this), key fields
should ideally be numeric, because (a) numbers take up less space than text,
and (b) it takes longer to sort/search/join on text fields than it does
numeric ones. In a small database this doesn't make a helluva lot of
difference, but when you get into large datasets or VLDBs (very large
databases), it matters very much. The principles of good design remain the
same. This is the basis of normalisation.

Assuming all your other PK fields are unique, I would expect to see a
composite primary key composed of the following key fields:
* retort_id
* labor_code (but preferably, labor_code_id)
* process_id
* employee_id

Assuming all the PK fields are unique, such a key is guaranteed to be
unique, and the semantics will remain intact.

In any case, you still haven't answered my questions. What about it doesn't
work? What's it doing? Have you supplied
ALL the values in the form controls?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia



troubledstudent said:
No, this is a composite key. there are many processes per retort, and the
key design actually works great.

Graham R Seach said:
The primary key is the retort id & labor code & process_name &
employee_name
Click to expand...
I sincerely hope you don't really mean all these fields collectively form
your table's primary key. I'm hoping you mean the primary key is [retort
id], and your search fields are [labor code], [process_name] and
[employee_name]. I'll work on that assumption!

In any case, you say your code doesn't work. Your approach looks roughly
fine to me. What about it doesn't work? What's it doing? Have you
supplied
ALL the values in the form controls?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


troubledstudent said:
It looks like it should work, and I have painstakenly looked over it
for a
long time. It runs, but the result is not what I want (checking if
record
exists): The primary key is the retort id & labor code & process_name
&
employee_name

dim sqlcheck as string
dim rs as dao.recordset
dim db as dao.database
set db = currentdb

sqlcheck = "SELECT distinctrow Processing.Retort_id,
Processing.labor_code,
Processing.process_name, Processing.employee_name " & _
"FROM Processing WHERE (((Processing.Retort_id)=""" &
Me.retortid.Value & """) AND ((Processing.labor_code)=""" &
Me.select_labor.Value & """) AND ((Processing.process_name)=""" &
Me.select_process.Value & """) " & _
"AND ((Processing.employee_name)=""" & Me.select_employee.Value
&
"""))"

'checks database for current record
Set rs = db.OpenRecordset(sqlcheck)

If (rs.BOF And rs.EOF) Then
' file is found
Else
'file is not found
end if

This is not working, any thoughts or suggestions on how to check if
record
exists based off of 4 user defined values?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top