problem with multiple join table query on report

[sobeit via AccessMonster.com]

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.

how can i solve this



thank you in advance

 

[John W. Vinson]

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?

 

[sobeit via AccessMonster.com]

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]

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

"Doctor, I don't feel good, what should I take?"

"seems not right" isn't helpful. What is the [Output Report] table? How many
records does it have for each [SO#]? What direction are the relationships -
which table is the one, which is the many? Perhaps you're not aware that if
you join ten Transaction records that each record in [Output Report] will be
represented ten times in the query, and that the Sum may well be much larger
than you expect.

 

[sobeit via AccessMonster.com]

thanks mr. john

[quoted text clipped - 23 lines]

want but when i add the OUTPUT REPORT table everything seems not right as far
as data calculation is concern

"Doctor, I don't feel good, what should I take?"

"seems not right" isn't helpful. What is the [Output Report] table? How many
records does it have for each [SO#]? What direction are the relationships -
which table is the one, which is the many? Perhaps you're not aware that if
you join ten Transaction records that each record in [Output Report] will be
represented ten times in the query, and that the Sum may well be much larger
than you expect.

exactly sir many records on [OUTPUT REPORT] JOIN BY [SO#] from SOREPORT table
with [SONO] on [OUTPUT REPORT] yes i get a sum much larger than expected so
the wats shud i use type of join though i tried it all

 

[John W. Vinson]

exactly sir many records on [OUTPUT REPORT] JOIN BY [SO#] from SOREPORT table
with [SONO] on [OUTPUT REPORT] yes i get a sum much larger than expected so
the wats shud i use type of join though i tried it all

You may need to use a Totals query on OutputReport to calculate the sums,
grouping by SO#, and join *that query* to the other tables. Again... I don't
know your table structure so I can't be certain.

 

[sobeit via AccessMonster.com]

exactly sir many records on [OUTPUT REPORT] JOIN BY [SO#] from SOREPORT table
with [SONO] on [OUTPUT REPORT] yes i get a sum much larger than expected so
the wats shud i use type of join though i tried it all

You may need to use a Totals query on OutputReport to calculate the sums,
grouping by SO#, and join *that query* to the other tables. Again... I don't
know your table structure so I can't be certain.

[SOREPORT] [OUTPUT REPORT]

SO# SONO - JOINED
field ORAMOUNT - SUM(nz([ORMOUNT])
as DLTOTAL
field field
etc etc
etc etc
etc

 

[John W. Vinson]

You may need to use a Totals query on OutputReport to calculate the sums,
grouping by SO#, and join *that query* to the other tables. Again... I don't
know your table structure so I can't be certain.

[SOREPORT] [OUTPUT REPORT]

SO# SONO - JOINED
field ORAMOUNT - SUM(nz([ORMOUNT])
as DLTOTAL
field field
etc etc
etc etc
etc

Do you have a question or a comment there, sobeit? I cannot make head or tail
of what you're asking me.