Re: problem with multiple join table query on report

thanks mr. john

heres my sql.. im on deadlock on this dont know how i cud solve this


SELECT SOREPORT.INVDATE, SOREPORT.[SO DATE], SOREPORT.TRANSTYPE, SOREPORT.REF,
SOREPORT.SALESMAN, SOREPORT.[SO#], SOREPORT.[CLIENT NAME], SOREPORT.AMOUNT,
SOREPORT.ADJUSTMENT, SOREPORT.FURNITURE, SOREPORT.SFURN, SOREPORT.SERVICES,
SOREPORT.TRADING, SOREPORT.DELIVERY, SOREPORT.DISCOUNT, SOREPORT.VAT,
SOREPORT.INVINFULL, SOREPORT.CARPENTRY, SOREPORT.UPHOLSTERY, SOREPORT.
FINISHING, SOREPORT.METALWORK, SOREPORT.SEWING, SOREPORT.SUBCON, SOREPORT.
PAYMENT, Sum([OUTPUT REPORT].ORAMOUNT) AS DLABOR, Sum(TRANSACTION.TPRICE) AS
MATERIALS
FROM [OUTPUT REPORT] LEFT JOIN (SOREPORT LEFT JOIN [TRANSACTION] ON SOREPORT.
[SO#] = TRANSACTION.SONO) ON [OUTPUT REPORT].SONO = SOREPORT.[SO#]
WHERE (((SOREPORT.INVDATE)>=[forms]![Report Date Range]![BeginDate] And
(SOREPORT.INVDATE)<=[forms]![Report Date Range]![EndDate]))
GROUP BY SOREPORT.INVDATE, SOREPORT.[SO DATE], SOREPORT.TRANSTYPE, SOREPORT.
REF, SOREPORT.SALESMAN, SOREPORT.[SO#], SOREPORT.[CLIENT NAME], SOREPORT.
AMOUNT, SOREPORT.ADJUSTMENT, SOREPORT.FURNITURE, SOREPORT.SFURN, SOREPORT.
SERVICES, SOREPORT.TRADING, SOREPORT.DELIVERY, SOREPORT.DISCOUNT, SOREPORT.
VAT, SOREPORT.INVINFULL, SOREPORT.CARPENTRY, SOREPORT.UPHOLSTERY, SOREPORT.
FINISHING, SOREPORT.METALWORK, SOREPORT.SEWING, SOREPORT.SUBCON, SOREPORT.
PAYMENT;

if only the SOREPORT AND TRANSACTION table is joined i do get the data that i
want but when i add the OUTPUT REPORT table everything seems not right as far
as data calculation is concern


thanks for help sir


John W. Vinson wrote:
if i join my TWO table using left join the data i want to extract is doing
what exactly i want but when i add another table to join in, its doesNt show
the right data that i want , ive tried all the types of join but its always
inaccurate data.

We'd be glad to help if we could but since you have not posted the SQL of the
query, the data you expect, or the data you're actually getting, it's
impossible to determine what the problem might be or how to fix it.

Give us a hand here please?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200806/1

.

Relevant Pages

  • Re: embedded SQL in COBOL
    ... I still have 'Learner' plates up for SQL - fortunately with N/E, having defined your DB, it allows you to select Query Type and it generates from EXEC to END-EXEC including the troublesome NULLs. ... DBI Main. ... Not shown in the above flowchart is a template for Dialogs to which I pass creation parametersand subsequently send data or receive data back - using the class Edit Materials as the Controller, ). ... MakeCollection - CURSOR SELECT DISTINCT ...
    (comp.lang.cobol)
  • Re: Trigger not updating in all conditions
    ... INSERT INTO MATERIALS ... TRIGGER FOR INSERT,UPDATE,DELETE IN MATERIALS TABLE ... row in Stock, there can only be one row in INSERTED with the ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: c++ and database
    ... > languages using ODBC and SQL. ... > materials which make me work with this databases (I use Borland free ...
    (comp.lang.cpp)
  • Re: Joining every row from 1 table to every row from another
    ... I need to join all garments to all materials,! ... This can be done in SQL simply by omitting any join condition. ... This should work in Oracle, MySQL, or any other system that supports the most rudimentary features of SQL. ...
    (comp.lang.java.databases)