By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Create Query in VB?

P: n/a
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
Share this Question
Share on Google+
7 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.