Which keyword(s) do i use??

  • Thread starter Thread starter Hafeez Esmail
  • Start date Start date
H

Hafeez Esmail

I want my code to perfrom the following algorithim.

1 Select Last_Name from TblFHStaffList
2 Search TblPredef120 with Last_Name (from
TblFHStaffList) as a criteria
3 Compare results with file C1
if different:
overwrite C1 and go to "if same"
if same:
select next name from TblFHStaffList and repeat
(as new name is selected, a new file is selected)

I need help with using the Last_Name as a criterion,
comparing to a saved file and saving the results. I don't
know which class/property/method to use when.

Attached is my code. It creates queries that ask you to
manually input a last name (when you run it). The code
doesn't compare the search to anything as yet.

Any help (even suggesting which keywords to use) would be
greatly appreciated!

Public Function MakeRpts()

Dim dbs As DAO.Database
Dim rstrec As DAO.Recordset
Dim rstname As DAO.Recordset
Dim strLastName As String
Dim qdfTemp As DAO.QueryDef
Dim qdfNew As DAO.QueryDef


Set dbs = CurrentDb()
Set rstname = dbs.OpenRecordset("TblFHStaffList",
dbOpenSnapshot)
Set rstrec = dbs.OpenRecordset("TblPredef120",
dbOpenSnapshot)

If rstname.BOF = False And rstname.EOF = False Then
rstname.MoveFirst
Do While rstname.EOF = False
strLastName = rstname!Last_Name
****Set qdfNew = dbs.CreateQueryDef(strLastName
& "Predef", "SELECT * FROM TblPredef120 WHERE Last_Name =
strLastName")****
Let qdfNew.ReturnsRecords = True
qdfNew.SQL
qdfNew.Close
Set qdfNew = Nothing
rstname.MoveNext
'(Do some stuff here)
Loop
End If
rstname.Close
Set rstname = Nothing
rstrec.Close
Set rstrec = Nothing
dbs.Close
Set dbs = Nothing

End Function
 
Hafeez,
it is difficult to understand what you are doing here.
Thanks for posting the code - it might come in useful later.

Here's what would be helpful.
Try to explain what you are trying to achieve - just plain english
Explain TblFHStaffList , tblPreDef120 and C1 Table definitions
- Column names and datatypes.
A few lines of sample data would be very good.

HS
 
Hi,
For the line you have indicated, try this:

dbs.CreateQueryDef(strLastName & "Predef", "SELECT * FROM TblPredef120 WHERE Last_Name = '" &
strLastName & "'")
 
I have a table called tblPreDef120 that's linked to a
master table in ODBC and a local table called
TblFHStaffList. tblPreDef120 is a collection of info on
40 some employees.

I want to create one report, table or query (r/t/q) to be
saved within the database for each employee and email it
to them.

However, since this will be run everyday, I don't want to
send them the same r/t/q every day for a week (or whatever
the case maybe). So that's why I'd like to compare each
r/t/q to an existing r/t/q named C1 and then send them an
email (with the report attached) only if the data has
changed.

Field names:
tblPreDef120 - Last_Name, assignement_title,
assignment_code, system_code, start_date, due_date etc...

tblFHStaffList - Last_Name, emp_id

C1 would have all the fields tblPredef120 would have
except it would contain all the records for only one
employee. C2 would be the same as C1 but records for a
different employee.

C1 is actually named "report&&&&&" where &&&&& represents
the employee's last name (there are no duplicates in last
name)

Hope this clears some things up
Thanks for helping!!

Hafeez Esmail
 
First, you should not use one table for each employee. Instead, use one
table and add an employeeid column


determinimg what has changed is difficult, but here is a start

Select PD.* from tblPreDef120 PD
left Join C1 on PD.KeyCol1 = C1.KeyCol1 ... (which ever are the key columns)
Where c1.EmployeeID = "abc123" (this will filter the list for just the
employee you want)
AND (PD.LastName <> C1.LastName
OR PD.Assignment_title <> C1.Assignment_title (or other fields that
are expected to change)
OR c1.KeyCol is null) (This will identify new rows that are not in
C1)

This will list all new and changed rows for the employee specified.

HS
 
Thanks HS,

tblPreDef120 is linked to the local table (TblFHStaffList)
through the system_code field. system_code and
employee_id is a many to one relationship (each employe
may have several system_code numbers)

A few questions:
If I should use one table instead of one per employee,
then why is C1 being referenced?

A few questions:
What is PD.*?
Where would I keep the code you suggested?
How do I reference the entire set of records once it has
searched for any new or changed records?
(eg. If **** = Null Then NoNew/ChangedRecords Else
YesNew/ChangedRecords. What would I keep for the ****?

Thanks a million
Hafeez Esmail
 
Hafeez,
Do you have msn messenger?
HS

Hafeez Esmail said:
Thanks HS,

tblPreDef120 is linked to the local table (TblFHStaffList)
through the system_code field. system_code and
employee_id is a many to one relationship (each employe
may have several system_code numbers)

A few questions:
If I should use one table instead of one per employee,
then why is C1 being referenced?

A few questions:
What is PD.*?
Where would I keep the code you suggested?
How do I reference the entire set of records once it has
searched for any new or changed records?
(eg. If **** = Null Then NoNew/ChangedRecords Else
YesNew/ChangedRecords. What would I keep for the ****?

Thanks a million
Hafeez Esmail
Click to expand...
 
boy, where do I start?

PD is an alias for tblPreDef120 - this helps because you do not have to type
awkward names repeatedly

C1 represents the data that you are capturing at a point in time, right?
C1 is a copy of PD at a given date, let us say 10/1/03, correct?

Now, data is being added /modified in PD.
on 10/6 you compare the two tables, to determine what has changed.

After you complete your operations, you should replace C1 with the latest
data in PD

This is just a simple query, that returns the Changed/New records and you
can reference it anyway you want -
for example, you could just build a report based on this query.

HS
 
I'm not very familiar with the keywords in VB so for the
code you listed below, I'm wasn't sure if Select or left
join are words you're using to describe what I should do
or if they're keywords VB would understand. (I am familiar
with 'Where'). So I typed in Select and when VB asked for
a case clause (case 1, case2....) I knew I had the wrong
keyword. However, I don't know what you mean by join.

Thanks for your help!!!
Hafeez Esmail
 
that is not VB it is SQL
HS

Hafeez Esmail said:
I'm not very familiar with the keywords in VB so for the
code you listed below, I'm wasn't sure if Select or left
join are words you're using to describe what I should do
or if they're keywords VB would understand. (I am familiar
with 'Where'). So I typed in Select and when VB asked for
a case clause (case 1, case2....) I knew I had the wrong
keyword. However, I don't know what you mean by join.

Thanks for your help!!!
Hafeez Esmail
Click to expand...
 
Thanks a million HS!
You've been extremely helpful!!!

I stuck to the things you've suggested and then understood
how/why it worked.

Thanks again
Hafeez Esmail
 
Back
Top