Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Crystal Reports Left Join


I have an Employee table and an Employees Trained table. Can someone show me how to get a report (In Crystal Reports 8.5) of the employees that were not trained this year? I have fields "Hours" and "TrainingDate" in the Employees Trained table. So I need those employees in Employee that are either not in Employees Trained or are in Employees Trained, but their Training Date was not this year.....




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Differance Between Left Join && Left Outer Join?
I come across one question in recent inter view
can any one tell me the differance between Left join & Left outer join?
Thanxs in Advance

CRYSTAL REPORTS WITH RIGHT OUTER JOIN IS NOT FUNTIONING!!! VERY URGENT
HAI! IN MY APPLICATION RIGHT OUTERJOIN IS NOT WORKING. I WANT TO KNOW THAT WHETHER THE RIGHT OUTERJOIN WORKS FINELY WITH CRYSTAL REPORT OR NOT?

HERE IS THE CODE

SELECT
    lp.OperatorName,
    tztview.Operatorid, tztview.Clientcode, tztview.PreKey, tztview.CompleteKey, tztview.Resolution, tztview.Points
FROM
    tzt.prog.lp lp right outer join tzt.prog.tztview tztview
on
    lp.OperatorId = tztview.Operatorid
ORDER BY
    tztview.Operatorid ASC


I AM ATTACHING THIS QUERY IN CRYSTAL REPORT SHOW SQLQUERY.
BUT I AM NOT GETTING THE RIGHT RESULTS.


REPLY IMMD..

BY,

VEEDEVELOPERS

edit by johnminkjan -> PLEASE DON'T SHOUT IT WILL NOT MAKE US ANSWER ANY FASTER!!!!!!



Edited by - johnminkjan on 5/11/2005 1:02:39 AM

Padding Zeros On Left In Crystal Reports 8
Suppose I have some Integer field. it is currently showing 1.00 in the report. i want it to show as 0001(extra zeros shown on the left side just as in VB's format command). How to do that. any ideas ??????


Kind Regards

Ali

Sql Left Join/inner Join Error
Hi ..I keep getting err.number -2147217900 syntax error(missing operator)....

Im using vb to interface w/an access 2k db through Ado.

I started off using inner joins then I realized as I broke up my db(perhaps too much to avoid nulls and such) that my pk [Employee Id] may ot be in all tbales and would cause an error. So I moved to a left join statement:

FullRsQuery = "SELECT * FROM Employees LEFT JOIN WorkInfo ON " & _
"Employees.[Employee ID] = WorkInfo.[Employee ID] LEFT JOIN " & _
"Employees ON Emergency.[Employee ID] = Employees.[Employee ID] " & _
"Left JOIN Notes ON Employee.[Employee ID] = Notes.[Employee ID] " & _
"WHERE Employees.[Employee ID] = '" & EmpID & _
"' Order by Employees.[Employee ID] Asc"


now I realize vb/sql/access converion may be screwy at times do I treid to pass my empIs variable with & without the ' ' around it. made no difference.

Query analyzer I used at work told me this code was working but my db is at home..


I Even took out everyting from the where clause on so the error is genereated b4 my where clause..

any ideas?

..oh here's my immediate window output:

SELECT * FROM Employees LEFT JOIN WorkInfo ON Employees.[Employee ID] = WorkInfo.[Employee ID] LEFT JOIN Employees ON Emergency.[Employee ID] = Employees.[Employee ID] Left JOIN Notes ON Emergency.[Employee ID] = Notes.[Employee ID] WHERE Employees.[Employee ID] = '1' Order by Employees.[Employee ID] Asc

Left Join
could someone give me a run of the mil sql example for a multiple jeft join expression? thanks

Sql + Left Join
I searched the forum and although my specific question was not answered it seemed that posting SQL specific questions were acceptable. So here goes...

select t.*, v.vwap, a.moniker
from accounts a, trades t, stocks s LEFT JOIN vwaps v on (s.stockID = v.stockID)
where a.accountNumber = t.accountNumber AND t.symbol = s.symbol
AND a.moniker not like '%kb001%' and a.moniker like '%kba%' AND t.timeVWAP is not NULL
order by t.[date]

That is my SQL code. It's almost there except that I don't want to do the LEFT JOIN vwaps v unless the s.stockID=v.stockID AND t.[date] = v.dateTime.

So unless to conditions are met I don't want the join, I just want it to return NULL values v.vwap and attach that to trades.

I can't get it to recognize that t or trades is a valid table.

ANy idea?

Sql Left Join
I have two tables.
tbl1 has the fields id,name,address,position
tbl2 has the fields user_id,details
each id in tbl1 exists only once, in tbl2 there can be many entries with same user_id. I want to print all info from tbl1 and if there is that user_id in tbl2 I want to add true to that info. So this is my select statement.


Code:
Select * from tbl1 left join tbl2 on tbl1.id = tbl2.user_id
When I loop through the recordsets and print them, then when a certain id exists twice in tbl2 it prints the info from tbl1 twice. I want it to print only once, I don't care how many times it exists in tbl2 I just need to know if it does or doesn't exist in tbl2. Can you help me?

Left Join In Sql
I changed my inner join to left outer join and it still gives me only the records that have a match in both tables, what could be the reason for this?

Left Join
I use 'left join' in SQL to join two tables. Can I join two tables on the fields that have different data type? If not , anyway to fix it execpt changing the datebase?

Left Join
Hi all !!!

I have a question. How can I make a Left Join query with two or more fields on the key?

In the help, I see how to make a left join with only one field... but with more than one?


Code:
SELECT [Category Name],
ProductName
FROM Categories LEFT JOIN Products
ON Categories.CategoryID = Products.CategoryID;


Here we have just one key(Categories.CategoryID = Products.CategoryID)

Any ideas???
Thanks

Inner/Left/Right Join
VB 6.0 Data reports W/ Access 2003. I had a problem ealier and Wsh2 helped me solve it by writing my sql to use an inner join for two tables.

Well, I wanted to pull more info but this time it included adding another table.

Here is what I would like to do: I have 3 tables Status, Customer, and Bill

Status = statusID, desc
Customer = accountID, statusID
Bill = accountID, finance

I want to display all the status ID's and descriptions, and then I want to display the sum of the finance that is affliated w/ the status ID.

example

Status Table
StatusID Desc
1 Debits
2 Assests
3 Loan    
4 Buyer

Customoer Table
AccountID StatusID
1     1
2     2
3     3    

Bill Table
AccountID Finance
1         10
1         20
2         15



My report would show this:

ID Desc Finance
1 Debits 30
2 Assests 15
3 Loan 0
4 Buyer     0


Here is my original code which doesnt work How ever I did some research about the different types of joins. Since I need to display every Status ID and Description I believe I need to use the Left Join, and since I need to display the sum of the finances reguardless the amount, I believe I need to use the right join again?

Note on my code below for some reason when I take out the "ON .... " Line then I get a error on the From clause... if I keep those two lines then I get a error basicly telling me my sql is wrong.

Code:
strSQL = _
   "SELECT T1.StatusID, T1.Desc, " & _
   " (T3.MaxFin) as MaxFin" & _
   " FROM [Status] AS T1 " & _
   " LEFT JOIN " & _
   " ( SELECT AccountID, " & _
   " statusID " & _
   " FROM customer " & _
   " ) AS T2 " & _
   " ON T1.statusID = T2.statusID " & _
   " RIGHT JOIN " & _
   " ( SELECT AccountID, " & _
   " SUM(financecharge) as MaxFin " & _
   " FROM bill " & _
   " ) AS T3) " & _
   " ON T2.accountID = T3.accountID "



Not very good at all with sql




Edited by - epatterson78 on 12/28/2004 8:18:40 AM

Left JOIN
Hi

I'm trying to do a Left join but i have always an error message saying that i have to few parameters.

Here what i'm doing

[vbcode]

rst.Open "select sum(fdtDetail.heure) as Totalheure from fdtDetail LEFT JOIN fdtEntete ON fdtDetail.nojob = fdtEntete.nojob and " & _
"fdtDetail.idsection = fdtEntete.idsection and " & _
"fdtDetail.idcouleur = fdtEntete.idcouleur and " & _
"fdtDetail.idregion = fdtEntete.idregion and " & _
"fdtDetail.idequipe = fdtEntete.idequipe and " & _
"fdtDetail.date = fdtEntete.date and " & _
"fdtDetail.heure = fdtEntete.heure " & _
"where fdtDetail.Date >= '" & txtDebut.Text & "' and " & _
"fdtDetail.Date <= '" & txtFin.Text & "' and " & _
"fdtDetail.IdEquipement = '" & txtPresse & "' ", _
"DSN=Central", adOpenDynamic, adLockOptimistic
[/vbcode}

is there some body who can help me?

thanks a lot

Left Join In Access/sql
i have the following cod eand it throws me error:syntax error..but there is none..is it the problem with left join???

FROM dbo_u_master_pl a INNER JOIN dbo_u_MTM_Output b ON a.rec_date = b.rec_date AND a.tnum = b.tnum AND a.block_num = b.block_num AND a.bucket = b.bucket
LEFT JOIN (dbo_vPrices v ON a.bucket = v.MatureDate AND a.trade_date = v.EntryDate AND v.Zainet_Mkt = 'POWER' AND v.Zainet_Comp = 'PL_ON')
LEFT JOIN ( dbo_vPrices x ON a.bucket = x.MatureDate AND a.trade_date = x.EntryDate AND x.Zainet_Mkt = 'POWER' AND x.Zainet_Comp = 'PL_OF')
LEFT JOIN ( dbo_vPrices y ON a.bucket = y.MatureDate AND a.trade_date = y.EntryDate AND y.Zainet_Mkt = 'POWER' AND y.Zainet_Comp = 'PL_FL' )
WHERE a.BUCKET BETWEEN (SELECT MIN(bucket) FROM dbo_u_master_pl WHERE rec_date = #" & dtmDate & "# ) AND (SELECT MAX(bucket) FROM dbo_u_master_pl) AND a.book IN ('SE10','SE06') AND a.rec_date = #" & dtmDate & "#
ORDER BY a.book, a.bucket, a.trade_date, a.tnum

Double Left Join
I really need help on doing a double left join, and i need it really quickly, because i can't move on my project till i do it. Would someone please give me an example of a double left join?
Thank you

Left Join Problems
MS Access is the DB. I will simplify my real tables (Q represents Question in the field names):

Table: Q1
-----------
Study
QType
QNum

Table: Q2
-----------
Study
PersonID
QType
QNum
Answer1

Table Q1 has 3 records for QType "C". Table Q2 has a PersonID that only answered 2 of the 3 questions in Q1 for type "C". I am trying to get ALL of the QType and QNum from table Q1 and matching records/nulls by joining on table Q2. I can't seem to get the nulls. Assume in table Q2 PersonID 123 answered QNums 1 and 2, but there are QNums 1,2, and 3 in table Q1. I want output like this:


Code:
Study PersonID QType QNum Answer1
---------------------------------------------------------
0001 123 C 1 555
0001 123 C 2 777
0001 123 C 3 (null)
But I can only get a recordset with the records for QNums 1 and 2, not the null for QNum 3.

Here is the SQL:


Code:
SELECT Q1.QNum,
Q1.QType,
Q2.PersonID,
Q2.QNum,
Q2.QType, Q2.Answer
FROM Q1 LEFT JOIN Q2 ON (Q1.QType = Q2.QType)
AND (Q1.QNum = Q2.QNum)
AND (Q1.Study = Q2.Study)
WHERE (((Q1.QType)="C"))
ORDER BY Q2.PersonID;
I know that I am selecting both Q1 and Q2's QNum and QType in the query, it is just for testing.

Left Join Problem
SELECT lefft1.*, x.Price AS [ELPSJ Trade Date] INTO lefft2
FROM lefft1 LEFT JOIN dbo_vPrices AS x ON (lefft1.Bucket = x.MatureDate) AND (lefft1.[Trade Date] = x.EntryDate)
WHERE (((x.Zainet_Mkt)="NATGAS") AND ((x.Zainet_Comp)="ELP SJ"));

in this query the lefft1 table has 820 rows of data but after this query is executed lefft2 table has only 728..why is this happening??any help will be great...

Thanks

LEFT JOIN Problem
I'm using MS Access. This is probably obvious, but I don't see the problem. I can't get this query to work...

SELECT Market.MarketID AS Market
FROM Market
LEFT JOIN Market_ProjectTypes ON Market.MarketID = Market_ProjectTypes.MarketID
LEFT JOIN Market_Genres ON Market.MarketID = Market_Genres.MarketID
GROUP BY Market.MarketID;

What I find odd, is that removing either of the LEFT JOINs will make it work. So these work great...

SELECT Market.MarketID AS Market
FROM Market
LEFT JOIN Market_ProjectTypes ON Market.MarketID = Market_ProjectTypes.MarketID
GROUP BY Market.MarketID;

SELECT Market.MarketID AS Market
FROM Market
LEFT JOIN Market_Genres ON Market.MarketID = Market_Genres.MarketID
GROUP BY Market.MarketID;

But I need it to work for both LEFT JOINs in the same query. What am I missing? (Error message is attached below.)

Sql-query ---> Left Join
sql = "Select tabell1.knr,tabell1.knavn,tabell2.stk " & _
"From tabell1 " & _
"Left Join tabell2 ON tabell1.knr = tabell2.knr " & _
"Left Join tabell3 ON tabell1.knr = tabell3.knr "

What's wrong with this code ?. I get the following errormessage :

"Syntax error (missing operator) in query expression 'tabell1.knr = tabell2.knr
Left Join tabell3 ON tabell1.knr = tabell3.knr'."

SQL Statement - LEFT, RIGHT JOIN?
I am trying to setup a SQL statement to grab records from my database and allow them to be filtered by tacking on a WHERE clause at the end. Here is the massive SQL statement that Access gives me when I specify the tables I want.


Code:
SELECT Parts.*
FROM Reqs INNER JOIN ((Customers INNER JOIN Quotes ON
Customers.Cust_ID = Quotes.Cust_ID) INNER JOIN (Parts INNER JOIN
PartReqs ON Parts.Part_ID = PartReqs.Part_ID) ON Quotes.Quote_ID =
Parts.Quote_ID) ON Reqs.Req_ID = PartReqs.Req_ID;
The problem is that not ALL Parts have PartReqs but some do, therefore I think I need do a LEFT or RIGHT Join on the PartReqs and Reqs tables do get it to grab all records regardless if they exist in the PartReqs table, but I can't seem to get it to work correctly.

ANy help would be appreciated.

LEFT JOIN In MS ACCESS
Hi
What is wrong in select below ?


Code:
SELECT TB02_PK, TB02_NOME,TB02_NICK,TB05S_DESCR
FROM TB02
LEFT JOIN TB05S ON TB02.TB02_FUNCAOID = TB05S.TB05S_PK ORDER BY 2


When I execute in VB return me Two rows , but in MS ACCESS return 3 Rows, In the Access Work fine

What is Wrong ?

Left Join With Too Coincidences
Hi, I've the following SELECT with a WHERE clause which throws a result with no NULLs for B.idPersonal.
How do I get all A.idPersonal=B.idPersonal though B.idPersonal=Null?

SELECT C.Descripcion as TipoCF, B.Nombre, B.Apellido, E.Descripcion as Provincia, D.Partido, D.Localidad, D.Direccion, A.idCF, A.ComputadoraTipo, A.Marca, A.Modelo, A.Serie, B.Nombre, B.Apellido, A.MonitorPulgadas
FROM tblCFComputadora A, tblPersonal B, tblCFTipo C, tblCentroFisico D, tblProvincia E
WHERE A.idcf=D.idCF and D.idTipoCF=C.idTipoCF and D.idProvincia=E.idProvincia and A.idPersonal=B.idPersonal

Thanks in advance,

Marcelo.

Left Join - Urgent
Dear all,

How to use more than one LEFT JOIN function from
VB to execute selection query from MS-ACCESS ?

For example we have 3 tables:

Table Name : Master
Fields : Name, AnimalCode, ColorCode

Table Name : Animal
Fields : Code, Descr

Table Name : Color
Fields : Code, Descr

I want to link Master.AnimalCode = Animal.Code and Master.ColorCode = Color.Code
to get Master.Name, Animal.Descr, Color.Descr
(to get all Master regardless there isn't any link to Animal and/or Color)

How to do that ?
The code below doesn't work

----
s = s & "Select "
s = s & " m.Name, c.Descr as [ColorDesc],"
s = s & " a.Descr as [AnimalDesc] "
s = s & "From "
s = s & " Master m LEFT JOIN Color c ON m.ColorCode = c.Code, "
s = s & " Master m LEFT JOIN Animal a ON m.AnimalCode = a.Code "
----

Thanks in advance
Keiko

Left Outer Join To Left Outer Join
I have this Query In oracle wher a table1 is left outer Joined with table ,then another table2 is again left outer joined with table1.Doing this in oracle is quiet simple by specifying all the tables once in "from" clause of query and then giving relationship in "where" clause.
But how can this be done in MSSQL using "left outer join" and "on" clause

My query is

select A.CoCode, A.DOCTYPECODE RetTYPE, A.DOCNO RetNO,A.DOCDATE RetDATE, B.DOCTYPECODE MrnType,B.DOCNO MrnNo, B.DOCDATE MrnDate, f.doctypecode Potype,f.docno PONO,e.Doctypecode Plantype,e.Docno PlanNo,A.DEPCODE, b.SuppCODE SuppCode,d.SuppName,c.ProductCode,
c.ProductDesc ProductName,c.Productcatcode ProductCat,
A.REMARKS,A.DOCSTatuscode RetStatus, A.VALIDFLAG,A.EntryBy,
A.EntryDate,A.AUTHORBY,A.ACTIONCODE,A.MODIFIEDUSERCODE,
a.MODIFIEDUSERDATE,a.MODIFIEDCODE,a.AUTHORREMARKS, A.Transkey,a.LinkTranskey
from P15storerettrans A,Mrntrans B, ProductMaster c,SuppMaster d,
plantrans e,Potrans f where
A.CoCode = 'PRV' and A.DocTypeCode = 'GR' and A.DocDate between '01-Apr-2000' and '31-Mar-2004' and a.linktranskey = b.transkey(+) and f.linktranskey=e.transkey(+)and b.linktranskey = f.transkey(+) and e.productcode=c.productcode(+) and b.Suppcode=d.suppcode
Order By a.DocDate desc,a.CoCode,a.DocTypeCode ,a.DocNo desc

can this be written in MSSQL

regards
vipresh



Edited by - vipreshjha on 3/16/2004 3:18:04 AM

How To Use The Left Join With The Where Conditon !
Hai All,

 I have a problem in the query where I use Left Join and Date Condition in the Where Clause.

Actually I need to retrieve datas from two tables and shown in a MSFlexGrid.

 Stock - this the Mastertable for ItemId
Itemid , Description, StockQty are the fields taken from Stock Table

 Sales is the Salestable will have details for only the SoldItem
 ItemId, SoldQty, SoldDate

So I Need the Output as below, I have total 5 items in the Stock Table. Assume the Pipe symbol separates the Fields, because in Preview the Table is not Clear.

ItemId //     Description // StockQty // SoldQty

C1453 //        CompaqMonitore // 100 // 0

HP2500 // HPPrinter // 150 // 30

McCafee //    VirusProtectionS/w // 1000 // 450

NAV //    NortronAntiVirus // 0 // 50

Ms12985 //    MicrosoftOutlook // 0     // 0        
 
What I am getting is only the item Sold.for the user given date if there is no sales for that item, then its not shown

HP2500 // HPPrinter // 150 // 30

McCafee // VirusProtectionS/w // 1000 // 450

NAV // NortronAntiVirus // 0 // 50


All the ItemId's from Stock Master should be shown, whether the StockBalance is 0 or greater and the SoldQunatity Should be show according to the user's input. (i.e) the date

I think it has to be done with Leftouter join, I tried, but what happens is when i include the date condition, the ItemId's Sold between the date is only shown as the output, the rest didn't shown.

rst.Open "select distinct ItemId,sum(StkQty),slsItemId,sum(SoldQty) from Stock stk Left Join Sales sls on stk.ItemId = sls.ItemId where sls.SoldDate >=’” & txtFromdt & ”’ And sls.SoldDate <=’” & “’ group by stk.ItemId,sls.ItemId order by stk.ItemId", cnn, adOpenStatic, adLockReadOnly, adCmdText

the Where condition is executing first is the query is correct
How can i modify and write this query. Kindly suggest me your idea.

Thank you very much,
Chock.




Edited by - chs on 8/20/2003 6:16:35 AM

Left Join Question For SQL: HELP!
Hey,

I can't seem to figure this one out. Here's the deal.

I have a table full of Courses and information pertinent to them (like school courses). I have another table full of "Requirements"-- there is a specific field in the requirements table which tells how often the person must take the course. The two are linked together by a "CourseCode". Say I want to select just the requirements of one individual... i would use a left join as follows:

SELECT tblCourses.CourseCode, tblCourses.Title, tblRequirements.Frequency
FROM tblCourses LEFT JOIN tblRequirements ON tblCourses.CourseCode = tblRequirements.CourseCode
WHERE (((tblRequirements.PersonnelID)=1))
ORDER BY tblCourses.Title;

My problem is... I want to select ALL the courses in the table regardless of whether a person is required to take it. Therefor, the Frequency column would hold a value if it were present for the person... and be null if they weren't required to take it. I can't seem to figure out how to do this. Please tell me there's a way to do this!

Thanks in advance.

Multiple LEFT JOIN Help Please
Hi Everyone

I am using an ADODB Connection to query tables in access.
Have to use left joins as fields may be empty in the right hand table.

Just cant seem to get the syntax correct for mutiple joins (got it working for one join though)

Some help would be great, as I dont want to waste vast amounts of time on this.

My code is below:

Code: locQuerySQL = "SELECT * FROM Solicitor " & _
"LEFT JOIN Addresses ON Solicitor.solicitorAddressID = Addresses.addressID " & _
"LEFT JOIN TelephoneNumbers ON Solicitor.solicitorPhoneID = TelephoneNumbers.telephoneID " & _
"WHERE solicitorChildID = " & locChildID & ""

'query the database
DBRecordset.Open locQuerySQL, DBConnection


thanks in advance for any help

cheers

Daniel

Confusing Error From Left Join
I am pulling my hair out over this one, i'd really appreciate some help.

I currently have the following relational database structure:


Code:
Table: Employers

id name
1 Macrosoft
2 Mikes Mechanics
3 Peters Plumbing Co.

-----------------------

Table: Placements

id employer_id title start_date end_date
1 2 Engine Tuning 11/03/05 18/03/05
2 1 Software Design 15/11/04 23/11/04
3 1 Product Support 20/11/04 21/11/04
4 2 Exhaust Fitting 13/04/04 16/04/04
5 2 Wheel Balancing 29/01/05 03/03/05
6 3 Bathroom Plumbing 15/12/04 15/12/04
7 1 Database Design 11/02/05 18/02/05


-----------------------

Table: Users

id first_name last_name
1 Bob Bloggs
2 Claire Peaches
3 Toby Braithwaite

-----------------------

Table: Users_Placements

user_id placement_id outcome
1 1 Success
2 2 [ongoing]
3 3 Success
3 4 Success
1 5 Fail
3 6 [ongoing]
2 7 Success
The information i'm trying to get is all the placements by a specific employer, and the details of the client who attended the placement, if the client is present. By this i understand i need a left join.

I have written the query that retrieves all the requested employers placements, and the id of the client attending:


Code:
sql = "select placements.[id], placements.[placement_work_type], " & _
"placements.[start_date], placements.[end_date], placements.[leaving_reason], " & _
"placements.[payrate], placements.[hrs_per_week], placements.[placement_description], " & _
"placements.[program_type], placements.[placement_title], " & _
"client_placements.[client_id] " & _
"from placements left join client_placements " & _
"on placements.[id] = client_placements.[placement_id] " & _
"where " & _
"placements.[employer_id] = " & employer_id
This works ok. But i need to get the details (name, surname) of the client who attended the placement too. So i though by simply adding the clients table to the from statement, this should work. I ended up with the following query:


Code:
sql = "select placements.*, clients.* " & _
"from clients, placements left join client_placements " & _
"on placements.[id] = client_placements.[placement_id] " & _
"where " & _
"placements.[employer_id] = " & employer_id & " and " & _
"client_placements.[client_id] = clients.[id]"
I'm sure the above syntax is correct, but i keep on getting the "Join expression not supported" error message.

Is my query ok?
(i'm using vb with ado and access 2000)

Left Join Problem In MYSQL
As I can't use subqueries in MYSQL I have to resort to outer joins. I have a problem. The code below is code I have obtained that should work if modified a little.

Example data:

tbl_lm_modules
iModID sName
1 "Module 1"
2 "Module 2"

tbl_lm_usermodules (which users have completed what modules)
iUserID iModID
100 1
100 2
101 1
102 2
103 1
103 3

What I need to obtain are the modules that a specified user has not yet completed (e.g. user 101 - module 2). The following code doesn't work because I have not included a reference to iUserID and therefore it will allways match somewhere and give me no results. What do I need to do to add in the iUserID element to the query?


Code:
SELECT tbl_lm_modules.*
FROM tbl_lm_modules
left JOIN tbl_lm_usermodules
ON tbl_lm_modules.imodid = tbl_lm_usermodules.imodid
WHERE tbl_lm_usermodules.imodid IS NULL;

Left Join Problem Using Access
I've got a strange problem with a SQL query.
I want my app to import records from a database into the database used by my application. I have this working perfect for importing from a MySQL db to the MySQL db from my app. No I'm working on Importing data from a MS Access db to my MySQL db from my app.
My app shows the records from the original db by using a grid and the imported records show up in another grid. To prevent records to be double imported I use a Left join to remove the imported records from the grid which shows the records the orginal grid.
Below you can find the querys which are used in two situations. The first is using two MySQL databases, this works perfect. The second one is the query used for MySQL and MS Access. This query just shows all records and doesn't hide the records which are already imported.

Code:

"SELECT playersrank.firstname,playersrank.lastname,playersrank.id FROM (jbrs_ranking.playersrank LEFT JOIN CoCo.t_players ON playersrank.id=t_players.ext_id) WHERE t_players.ext_id IS NULL"

"SELECT players.firstname,players.lastname,players.id FROM (players.players LEFT JOIN CoCo.t_players ON players.id=t_players.ext_id) WHERE t_players.ext_id IS NULL"
If it is possible I need a solution which works for both MySQL and MS Access. Because I want to prevent using too much "if.. then.. else..."

thanks

Left Outer Join On Access
Why this is not working on Access ? It works fine on SqlServer
How i should write this on access connect ?


VB Code:
select MyNumber1, MyNumber2  from MyTable1 left outer join MyTable2 on MyNumber2= MyNumber1

Ado, Odbc, And Left Join Problems
Hi,

I have 4 tables, 1 primary table, and 3 others linked to it using left joins, so that data will always appear in the primary table, but there may not be a related entry in the other 3 tables.

I'm linking to my access back end db using the MS Access ODBC driver. The problem is that although using the sql string below in Access brings all the records from the primary, and any related records from the other 3, when I use the same sql in VB using ODBC and ADO, it will only return those records that have a matching entry in all 4 tables - in other words, the left join is being ignored. Any suggestions?

Thanks

Frank

  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=MSDASQL;dsn=datamatchlink;uid=;pwd=;"

  Set adoPrimaryRS = New Recordset
  Set adoRSStatus = New Recordset

 sqlstring = "SELECT huggydata.*, huggydecision.*, huggyprevadd.*, " _
  & "frauddata.* FROM ((huggydata LEFT JOIN frauddata ON huggydata." _
  & "APPLICATION_NUMBER = frauddata.APP_NUMBER) LEFT JOIN " _
  & "huggydecision ON huggydata.APPLICATION_NUMBER = huggydecision." _
  & "APP_NUMBER) LEFT JOIN huggyprevadd ON huggydata.APPLICATION_NUMBER" _
  & "= huggyprevadd.APP_NUMBER"
  
  
  adoPrimaryRS.Open sqlstring, db, adOpenStatic, adLockBatchOptimistic
        
   Set adoPrimaryRS.ActiveConnection = Nothing
   'Set db = Nothing

SQL Syntax Help For Left Outer Join
Dear All,

I have problem in connecting two tables.

pohead is a table in a database pmsdata.mdb from which records need to be fetched. It has a field called prty_code (Party Code). While displaying the records I need to get the name of the party for each record. The Party master is lying in another database called pmsmast.mdb.

when I am using following syntax for populating a ADODC, it gives error

"select po_no, name, ref_no from pohead left outer join s:pmspmsmast.prtymast on pohead.prty_code = s:pmspmsmast.prtymast.prty_code"

The error is
syntax error [missing operator] in query expression 'pohead.prty_code = s: pmspmsmast.prtymast.prty_code'

Can someone help ?

Thanks & Regards,

Vishal Badle

Left Outer Join In MS-ACCESS (Solved)
Hi Friends,
I am using my Database in Ms-Access.
How to get the -Left Outer Join- Query in Ms-Access?
Its not supporting *=

Please give me the solution

Manesh

Multiple Left Outer Join With Where Clause
This seems simple and basic, but I can't find samples on any VB books or tutorial.

Table A has fields a1, a2, a3
Table B has fields b1, b2, b3
Table C has fields c1, c2, c3

What's wrong with

Select A.*, B.*, c.* from A where A.a3 = 'Y' ((left outer join B on A.a1 = B.b1) left outer join C on A.a1 = C.c1 and A.a2 = C.c2) order by A.a1, A.a2

How should I put the brackets?

Thanks.

Converting Left Inner Join Into Access Query
Hey Peeps,

I've got a query working in SQL server, but I now need to convert it to Access syntax to use on my Access database. The Access database has the exact table structure as the SQL server DB (obviously some fields datatypes had to changed but field names and values are all the same).

This is what the query looks like for SQL Server :

Code:SELECT a1.Address_1, a1.Address_2, a1.Address_3, a1.Area_Code, a1.Phone, b1.First_Name, b1.Last_Name, b1.LS_MyLS_ID_Number, b1.Company_Name, b1.Job_Title
FROM Company a1, Contact b1 , Company a2, Company a3, Company a4, Company a5, Company a6, Company a7, Company a8, Contact b2, Contact b3, Contact b4, Contact b5, Contact b6
WHERE b1.Reports_To_Id *= b2.Contact_Id AND a1.Parent_Company_Id *= a2.Company_Id AND b1.Company_Id *= a3.Company_Id AND b1.Delta_Company_Id *= a4.Company_Id AND a1.Partner_Company_Id *= a5.Company_Id AND a1.LS_Med_Aid_Administrator_Id *= a6.Company_Id AND a1.LS_Med_Aid_Scheme_Id *= a7.Company_Id AND b1.LS_Fi_Med_Aid_Scheme_CompanyId *= a8.Company_Id AND a1.Supplier_Account_Manager_Id *= b3.Contact_Id AND a1.Reseller_Key_Contact_Id *= b4.Contact_Id AND a1.Partner_Contact_Id *= b5.Contact_Id AND a1.Referred_By_Id *= b6.Contact_Id AND b1.Company_Id = a1.Company_Id AND b1.Contact_Id = '0x0000000000000628'

Can anyone help me convert this syntax for access? I'm not sure of how it would look or work so any help would be nice.

Thanks for any effort.

HH

Some folks wear their halos much too tight...

 

Left Outer Join Problem In CR9 Through ODBC
Hi,

I have a problem with a left outer join in the database expert.
I join two tables with a left outer join and it appears like it is like an inner join.

My database is pervasive2000. I have a connection through ODBC.

I found a document on the "Cr9_left_outer_join.pdf" and I added my driver to a registry key. But left outer join works like a inner join.

How can I solve this problem.

Thanks in advance !!

Syntax Error With Left Outer Join On Statement
Good day Sirs & Madams

I have a little problem. I have the following sql statement that refuses to run perfectly:

Code:
select * from invoice a
left outer join inv_numbersl b
on
b.doc_no = a.doc_no
where
doc_date between '2005-01-01' and '2005-09-30'
and
manual_price > '0'
on (select distinct c.sap_no from sap_address c
where
sales_org = '1850'
and
dist_channel = '01'
and division = '01')
c.sap_no = a.cust_no
The syntax error occurs when I add the lines in blue. I would really appreciate your help as my dumbnecity has reach new hights.

Kind Regards

Left Outer Join Error In The SQL Builder(Datareport)
Hi: Please help!!!!!!! I was trying to do modify an SQL that was created using the data environment. I used the SQL builder that is in the data environment. But when I test it it shows an error in the from clause.
SELECT DISTINCT
    tblOrder.O_Dept_ID, tblDepartment.DEPT_Description
FROM tblDepartment, { oj tblOrder LEFT OUTER JOIN
    tblProduct ON
    tblOrder.O_ItemCode = tblProduct.ItemNoLong }
WHERE tblDepartment.DEPT_ID = tblOrder.O_Dept_ID AND
    (tblOrder.O_Dept_ID = ?)

 

Crsytal Reports Join Or Link?
Can I do something like this in crystal? join two tables where the keys match and show them in the report? they are set in code since the table names change.

Code:strTempName = "Show" & CStr(CLng(Timer))

Set db = OpenDatabase(DBFILE)

'Create a table def for a linked table
Set tb = db.CreateTableDef(strTempName)
tb.Connect = ";DATABASE=" & SHOWFILE
tb.SourceTableName = "Show"

SQL = "SELECT * FROM [General] INNER JOIN [" & strTempName & "] ON [" & strTempName & "].[Key] = [Show].[Key]"

db.TableDefs.Append tb
Set rs = db.OpenRecordset(SQL)

'Call the report
Load frmClassSheet

'delete the linked tabledef
db.TableDefs.Delete strTempName

frmClassSheet.Visible = True

Crytal Reports - Outer Join
We have a requirement to create a report by outer joining the tables in two different databases. But CR8.5 is allowing only equi joins? Any pointers?

Creating Data Reports From Join Of Three Tables
hello all,
please suggest how to create data report where data is bein fetched from join of 3 tables.
As i am creating reports for the first time in , i m facing a lo of trouble...with data environment, i dont know how to mentio data source in terms of " app.path" and hence the defaul c:/desktop/mydatabase.mdb is being accepted as the path by th connection.

Moreover can anyone plz suggest if i cn use PL/SQL 4 data repor creation...as the table/sql statement doesn't serve m purpose...or can i use the stored procedure option to call procedure to display...n if so how??

i am pretty hopeful of ur cooperation n suggestions...
thanking u in advance....
Tanu

Right To Left Property In Reports
Hi,

VB 6.0 built-in report designer has Right To Left property for fields, but the designer is very primitive without flexibility. I'm using Crystal Reports instead, but it doesn't have Right to Left property. Does anyone know a good report designer like Crystal Reports with Right to Left property? I also try Active Report but it is the same as Crystal Reports.

Thank

Right To Left Cryatal Reports
How can I make a CrossTab Table That Supports Arabic Language And Direction

Data Reports Active Reports Or Crystal Reports
Hi all

I am really stuck here and i need help urgently
I have to call three tables in a row to print a single invoice entry
noe the problem is if the do it by calling single tables then there is a problem that it is nopt going to print the last order which is filled.Is there is any way it can be done through using sql command or something.
Secondly if i use max order in sql then i would not be in a position to print order after searching for it


Thanx

MySQL Inner Join W/ Crystal Report
hi,

i am trying to link two tables for my reports in crystal report 8.5, which i successfully did using MS Access. But using MySQL as my database it gives me this error:

ODBC error: You have an error in your SQL syntax near ''database_name'.'table1' INNER JOIN 'database_name'.'table2' ON table1.'field1'=' at line 1

i search for some solutions but with no luck.

thanks for the help in advance!!

In Crystal Reports, How To Display Financial Reports (P & L, Balance Sheet Etc.) In Columnar Format
Dear All,

I am developing a Financial application using VB6, MS-Access & Crystal Reports 8.5. I am facing problems in certain reports. Some of the reports like Profit & Loss and Balance Sheet require the output to be printed in columnar format.

I have tried giving subreports for each section, i.e.; Assets & Liabilities, but then, the output is not coming in order as per the groups. The problem is even more where there are sub-sections in the right side column & left side column for example in Profit & Loss, where there are sub-sections for Gross Profit/Loss and Net Profit/Loss. In such cases, how to bring the total amount fields at the same horizontal level.
I hope someone can help me how to do the above.

Thanks in advance.

Best Regards
Mahesh

Change The Database Location In Crystal Reports Sub Reports From VB During Runtime
I have several reports that were designed in Crystal Reports that have the DB location changed during runtime. When I move my database, or choose another copy of the DB that has a lot of records in it, my reports don't fire. I need to be able to Verify the database during runtime. I'm usiung VB6.0 and CrystalReports8.5 I've tried using Crystal's "Verify On Every Print" function, but I haven't had any luck. Here is the code I am using.

MyLocation = "C:mydatabase.mdb"
CRPE321.ReportFileName = "c:aptcom
eports eninfo.rpt"
CRPE321.DataFiles(0) = MyLocation
CRPE321.ParameterFields(0) = "ThisKey;" & Who.Text & ";true"
CRPE321.PrintReport

Also, does anyone know if changing the DataFiles location during runtime also effects the subreports. I don't think it does, but any input would be appreciative.

Need To Understand Data Reports - Heard That Crystal Reports Were Slow
I have two questions:

1.) I was going to take a step forward to learn how to use crystal reports but I heard that on a website they are viciously slow. Is that true or is there a way around that with coding?

2.) I have been using Access reports through Visual Basic 6.0 with the following code - thanks for the tremondous help of everyone on this website:

Code:
appAccess.OpenCurrentDatabase SERVER & "disp-1_be.mdb"
appAccess.DoCmd.Maximize
appAccess.DoCmd.OpenReport "oalog", acPreview
appAccess.Visible = True

The reports work great UNTIL I came accross a situation where the report is based off of the data entry of the user. oalog is a monthly report and in the query it automatically pulls the current month with the following query:

Code:
SELECT oalogallmonths.ctsbranch, Count(oalogallmonths.Amount) AS [Total Of Amount], oalogallmonths.GivebyName, oalogallmonths.United, oalogallmonths.Mayflower, oalogallmonths.Other, oalogallmonths.Tord, oalogallmonths.CURRENTDATE, oalogallmonths.Amount, oalogallmonths.Month1, oalogallmonths.ORDER
FROM months, oalogallmonths
WHERE (((months.monthID)=[oalogallmonths].[Amount]))
GROUP BY oalogallmonths.ctsbranch, oalogallmonths.GivebyName, oalogallmonths.United, oalogallmonths.Mayflower, oalogallmonths.Other, oalogallmonths.Tord, oalogallmonths.CURRENTDATE, oalogallmonths.Amount, oalogallmonths.Month1, oalogallmonths.ORDER
HAVING (((oalogallmonths.Amount)=11;

If you notice the records are pulled HAVING the 11th month in there.

If I want the user to ENTER the month I know that I must change the last line to:


Code:
HAVING (((oalogallmonths.Amount)=[ENTER MONTH]));

But a user might type January or 12/2003, etc...

so I made a combobox named ListMonth of the 12 months.
Then I made the change from:


Code:
HAVING (((oalogallmonths.Amount)=11;
to

Code:
HAVING (((oalogallmonths.Amount)=ListMonth.ListIndex + 1));

The only problem is that I keep getting a message box asking for ListMonth.ListIndex + 1.

My question is in the above code that defines appAccess.DoCmd.OpenReport "oalog"...
is there a way to change the RecordSource of the report to pull from my own query instead of the Access Query?

Thanks,
Stephen

Crystal Reports - Upgraded To 8.5, Now Reports Written In 8.0 Wont't Work!!!
My reports are crashing when I run them on a machine with CR8.5, but they work fine on cr8.0. They crash inside the .DiscardSavedData Routine.


Does anyone know why or what I can do...

I call the report.Printout routine and right after I call the
.DiscardSavedData. The report is still printing out in debug mode.


Please help. Is my syntax wrong. The help file says I can only use Printout and DislcardSavedData in "formatting idle" mode. I don't know what this this???? The .PrintOut routine works but not the discardsaveddata.


Thanks,

Any help is appreciated.


M

Copyright 2005-08 www.BigResource.com, All rights reserved