Create Query in VB?

Jim

How do I program visual basic to create a "Find Unmatched Query"?

I have two tables:

- TodaysImport
- YesterdaysImport

Both tables have the same 6 fields:

- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum

The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImport.

Any help would be appreciated!

Aug 8 '07 #1

Larry Linson

Is there some reason not to use the Find Unmatched Query Wizard to generate
the pattern and then use that as the basis for your VBA code?

Just an idle question: what is the definition of the "user curriculum"
field? If it is not a unique value for each record, you will have to
further clarify what makes the records different.

Larry Linson
Microsoft Access MVP

"Jim" <jl*******@gmail.comwrote in message
news:11**********************@x40g2000prg.googlegr oups.com...

How do I program visual basic to create a "Find Unmatched Query"?

I have two tables:

- TodaysImport
- YesterdaysImport

Both tables have the same 6 fields:

- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum

The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImport.

Any help would be appreciated!

Aug 8 '07 #2

John Winterbottom

something like this:

dim sql as string

sql = "create procedure UnmatchedRecords as " _
& "select * from TodaysImport as t " _
& "where not exists(" _
& "select * from YesterdaysImport as y " _
& "where y.UserCurriculum = t.UserCurriculum)"

currentproject.connection.execute sql , , adexecutenorecords

Jim wrote:

How do I program visual basic to create a "Find Unmatched Query"?

I have two tables:

- TodaysImport
- YesterdaysImport

Both tables have the same 6 fields:

- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum

The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImport.

Any help would be appreciated!

Aug 8 '07 #3

Jim

On Aug 8, 10:54 am, "Larry Linson" <boun...@localhost.notwrote:

Is there some reason not to use the Find Unmatched Query Wizard to generate
the pattern and then use that as the basis for your VBA code?

Just an idle question: what is the definition of the "user curriculum"
field? If it is not a unique value for each record, you will have to
further clarify what makes the records different.

Larry Linson
Microsoft Access MVP

"Jim" <jlrehm...@gmail.comwrote in message

news:11**********************@x40g2000prg.googlegr oups.com...

How do I program visual basic to create a "Find Unmatched Query"?

I have two tables:

- TodaysImport
- YesterdaysImport

Both tables have the same 6 fields:

- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum

The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImport.

Any help would be appreciated!- Hide quoted text -

- Show quoted text -

You're right Larry, I didn't think about it. Here's the SQL from the
query, so how do I get Access to create the query?

SELECT TodaysImport.User, TodaysImport.[User Name],
TodaysImport.Curriculum, TodaysImport.eMail, TodaysImport.OpGroup
FROM TodaysImport LEFT JOIN YesterdaysImport ON
TodaysImport.UserCurriculum = YesterdaysImport.UserCurriculum
WHERE (((YesterdaysImport.UserCurriculum) Is Null));

Aug 8 '07 #4

Tom van Stiphout

On Wed, 08 Aug 2007 11:57:05 -0400, John Winterbottom
<jo*****@rogers.cawrote:

Alternatively if you want to use the DAO methods:
'air code follows
dim q as DAO.Querydef
set q = CreateQuerydef("qryName", "select ...")
currentdb.querydefs.refresh

-Tom.

>something like this:

dim sql as string

sql = "create procedure UnmatchedRecords as " _
& "select * from TodaysImport as t " _
& "where not exists(" _
& "select * from YesterdaysImport as y " _
& "where y.UserCurriculum = t.UserCurriculum)"

currentproject.connection.execute sql , , adexecutenorecords

Jim wrote:

>How do I program visual basic to create a "Find Unmatched Query"?

I have two tables:

- TodaysImport
- YesterdaysImport

Both tables have the same 6 fields:

- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum

The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImport.

Any help would be appreciated!

Aug 9 '07 #5

Larry Linson

"Jim" <jl*******@gmail.comwrote

You're right Larry, I didn't think about it. Here's the SQL from the
query, so how do I get Access to create the query?

SELECT TodaysImport.User, TodaysImport.[User Name],
TodaysImport.Curriculum, TodaysImport.eMail, TodaysImport.OpGroup
FROM TodaysImport LEFT JOIN YesterdaysImport ON
TodaysImport.UserCurriculum = YesterdaysImport.UserCurriculum
WHERE (((YesterdaysImport.UserCurriculum) Is Null));

In Access 2003 or earlier, in the Database Window, Queries tab, click the
New button and Find Unmatched is one of the options. Should do what you
want... I didn't attempt to recreate your table structure to try it, though.

In Access 2007... somebody else will have to take the question. I'm not
using it yet.

Larry Linson
Microsoft Access MVP

Aug 10 '07 #6

Jens Schilling

Hi,

In Access 2003 or earlier, in the Database Window, Queries tab, click
the New button and Find Unmatched is one of the options. Should do
what you want... I didn't attempt to recreate your table structure to
try it, though.
In Access 2007... somebody else will have to take the question. I'm
not using it yet.

;-)

In A07 the Query Wizard is located on the Ribbon "Create" (Group Other)

Regards
Jens

Aug 10 '07 #7

Jim

Thanks everyone, the following code worked!

'Create query
Dim createSQL As String
createSQL = "create procedure TodaysNewRecords as SELECT
TodaysImport.User, TodaysImport.[User Name], TodaysImport.Curriculum ,
TodaysImport.eMail, TodaysImport.OpGroup FROM TodaysImport LEFT JOIN
YesterdaysImport ON TodaysImport.UserCurriculum =
YesterdaysImport.UserCurriculum WHERE
(((YesterdaysImport.UserCurriculum) Is Null));"
CurrentProject.Connection.Execute createSQL, , adExecuteNoRecords

Aug 10 '07 #8