PC Review


Reply
Thread Tools Rate Thread

Inner Join In VBA

 
 
KostasP via AccessMonster.com
Guest
Posts: n/a
 
      22nd Nov 2007
Hi Everyone!
This is my first post and i need your help,
I have two tables with a one-to-many relationship and i need to make a query
in VBA using SQL (INNER JOIN) statement, in order to get my preferred result.

Here is the example of my recent code that doesn't work by the way:
The related Tables are KAM and KAM Port.
sr = "SELECT Count(CFR) AS [URV],"
sr = sr + "Avg(KAM.Loa) AS [MLREF]"
sr = sr + "FROM KAM Port INNER JOIN KAM ON KAM Port.Port Name]=" + "
KAM.[Port Name]"
sr = sr + "AND (KAM Port.[Port Code]=" + "KAM.[Port Code] WHERE "
sr = sr + "ComYear<=" + Str(nb) + " AND EventEndDate>=" + Str(nb) +
"9999"
sr = sr + " AND " + sport
sr = sr + " AND (KAM.[Ton Ref])=" + "0"

What am i doing wrong?
Thanks in advance!
KostaP

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...dules/200711/1

 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      22nd Nov 2007
Your 2nd table name contains a space, so you must use square brackets around
it, i.e.:
[KAM Port]

If you do get stuck, you can use the graphic query designer to design the
query you want, using any literal criteia that can act as samples. Then
switch the query to SQL View (View menu, in query design) to see what your
query statement should look like.

That's what I do, and then use this utility to create the SQL string in VBA
code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"KostasP via AccessMonster.com" <u39264@uwe> wrote in message
news:7b9622fb527f0@uwe...
> Hi Everyone!
> This is my first post and i need your help,
> I have two tables with a one-to-many relationship and i need to make a
> query
> in VBA using SQL (INNER JOIN) statement, in order to get my preferred
> result.
>
> Here is the example of my recent code that doesn't work by the way:
> The related Tables are KAM and KAM Port.
> sr = "SELECT Count(CFR) AS [URV],"
> sr = sr + "Avg(KAM.Loa) AS [MLREF]"
> sr = sr + "FROM KAM Port INNER JOIN KAM ON KAM Port.Port Name]=" +
> "
> KAM.[Port Name]"
> sr = sr + "AND (KAM Port.[Port Code]=" + "KAM.[Port Code] WHERE "
> sr = sr + "ComYear<=" + Str(nb) + " AND EventEndDate>=" + Str(nb) +
> "9999"
> sr = sr + " AND " + sport
> sr = sr + " AND (KAM.[Ton Ref])=" + "0"
>
> What am i doing wrong?
> Thanks in advance!
> KostaP


 
Reply With Quote
 
 
 
 
KostasP via AccessMonster.com
Guest
Posts: n/a
 
      27th Nov 2007
Many Thanks Allen Browne!
Did work!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...dules/200711/1

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
An Inner Join on an Inner Join David Wetmore Microsoft Access Queries 1 1st May 2008 12:10 PM
Two INNER JOIN, WHERE balks on first INNER JOIN =?Utf-8?B?QmFuYXRpY3Vz?= Microsoft Access ADP SQL Server 5 7th Feb 2006 08:24 PM
Left Join acting like inner join??? =?Utf-8?B?VmFjYXRpb24ncyBPdmVy?= Microsoft Access VBA Modules 7 10th Jun 2005 08:35 PM
Left Join with Inner Join not supported? Mark Microsoft Access Queries 2 15th Nov 2003 11:48 AM
Access Not Showing Up Records for Rt Join or Inner Join Ker Microsoft Access Queries 2 18th Oct 2003 03:57 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:39 AM.