473,233 Members | 1,559 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

Can't get a left join to work correctly

SP
I have two tables, A & B. Table B has adjustment factors of three
types, "COMM" being one of them. I want a query that generates all
rows of table A, and those where certain fields match, I want the
value of A.PD01 to be multiplied by the the "COMM" factor.

I have the following SQL, but it only generates the records where the
fields match, not all of them.

SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS, [b]![PD01]*[A]![PD01] AS
PD01
FROM A LEFT JOIN B ON (A.AR = [b].AR) AND (A.DV = [b].DV) AND (A.BU =
[b].BU) AND (A.MCLASS = [b].MCL)
WHERE (([b]![NAME]="COMM"));

Nov 13 '07 #1
2 1555
I A and B are aliases for tables then you need to include the table
names before the aliases. If A and B are the table names then never
mind about the above. Try the syntax below in the Query sql window
(change the names of the tables of course)
SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS,
B.PD01 * A.PD01 As PD01 FROM tableA A LEFT JOIN tableB B ON A.AR = B.AR
AND A.DV = B.DV AND A.BU = B.BU AND A.MCLASS = B.MCL
WHERE B.NAME='COMM';

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '07 #2
On Nov 13, 10:56 am, SP <s.pamur...@gmail.comwrote:
I have two tables, A & B. Table B has adjustment factors of three
types, "COMM" being one of them. I want a query that generates all
rows of table A, and those where certain fields match, I want the
value of A.PD01 to be multiplied by the the "COMM" factor.

I have the following SQL, but it only generates the records where the
fields match, not all of them.

SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS, [b]![PD01]*[A]![PD01] AS
PD01
FROM A LEFT JOIN B ON (A.AR = [b].AR) AND (A.DV = [b].DV) AND (A.BU =
[b].BU) AND (A.MCLASS = [b].MCL)
WHERE (([b]![NAME]="COMM"));
the WHERE clause is removing all the records which do not have a value
= "COMM", and since those which have no matching row cannot have a
"comm" entry, they will not show in your result set.

You can build a query on table b alone, filtered for name = "comm" and
use this query instead of the table, so you don't need the where
clause in the left join query.

You can also try changing the where clause to
WHERE [b]![NAME]="COMM" OR [b]![NAME] is null;
note that this sometimes has undesirable results.

Nov 13 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the...
1
by: Chris Thompson | last post by:
Here is the problem. I have a table called Locations with a field called Location which holds a list of peoples names. I have a query (query1 say)which has two fields :- 1st field is a person's...
1
by: Bob Alston | last post by:
I am trying to use an access sql statement in a module that does a left outer join on one table - joined to a table that has a selection criteria. The result is an inner join. If I do this in...
9
by: Mike Bridge | last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me the error "Join expression not supported". Apparently, Access "fixed" it for...
3
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and...
2
by: lelandhuang | last post by:
I am developing reporting service and using lots of 'LEFT OUTER JOIN', I am worried about the performance and want to use some subquery to improve the performance. Could I do that like below, ...
4
by: TGEAR | last post by:
Itemlookup table Field names : index_id (primary key), itemno, description. It has a child table, which is ItemPriceHistory table The relationship to the child table is one (parent table)-to-many...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
9
by: shanevanle | last post by:
I have two tables that are pretty big. I need about 10 rows in the left table and the right table is filtered to 5 rows as well. It seems when I join the tables in the FROM clause, I have to...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.