[kaupmees]

Tere,
Ühendasin läbi Accessi serveris tabelid päringuga, aga kogused arvutab valesti. Lisan juurde pildi kus näha tabelite ühenduse ja lisan juurde päringu. Päringus read kus on sõna sum arvutab andmed suuremana kui tegelikus.

https://www.upload.ee/image/6261365/Hoovlitoode_ulevaatusleht.JPG

SELECT
job.order_stop,
job.order_id,
Sum(iif(g_stock.location="ProdG",g_stock.qty,0)) AS RawMatStockGlub,
Sum(f_reg.delivery_qty) AS delivery_qty,
Sum(rawmat.qty) AS UsedRawMat,
(IIf([job].[lamella_pieces_no1]=0,0,Round([job].[lamella_pieces_no1]/[job].[lamella_raw_material_no1]))-(Sum([rawmat].[qty])))*-1 AS RawMatMore,
IIf([job].[lamella_pieces_no1]=0,0,Round([job].[lamella_pieces_no1]/[job].[lamella_raw_material_no1])) AS RawMaterial,
Sum(rawmat.qty) AS RawMatSum,
Count(rawmat.qty) AS RawMatCount,
job.queue,
job.lamella_cross_section_no1,
job.lamella_cross_section_no2,
job.machine,
job.work_date,
job.comment,
job.mat_prodg,
job.product,
job.wood & "-" & job.raw_material_thickness & "-" & job.raw_material_width & "-" & job.quality & "-" & job.raw_material_length AS RawMatDimensions,
job.prodg_specification,
job.glue_area,
job.lamella_raw_material_no1,
job.lamella_raw_material_no2,
job.lamella_pieces_no1,
job.lamella_pieces_no2,
job.work_time,
job.customer,
f_orders.high_priority_order,
iif(Len(Nz([item_desc_suggestion],0))<1 And Len(Nz([item_no_suggestion],0))<1 And [request_time]=[request_time_suggestion],0,IIf(Nz([request_time_suggestion],0)=0,0,1)) AS Suggestion

FROM ((tbMolder_Job AS job
LEFT JOIN (tbl_job_done AS done
LEFT JOIN (tbl_job_raw_mat AS rawmat
LEFT JOIN tbl_job_fin_product AS finmat
ON finmat.id = rawmat.tbl_job_done_id)
ON done.id = rawmat.tbl_job_done_id)
ON job.order_id = done.order_no)

LEFT JOIN (forklift_orders_list AS f_orders
LEFT JOIN (forklift_registrations AS f_reg
LEFT JOIN glueboard_stock_rawmat AS g_stock
ON g_stock.forklift_registrations_id = f_reg.id)
ON f_orders.id = f_reg.forklift_orders_list_id)
ON job.order_id = f_orders.order_no)
LEFT JOIN comp_stock AS k_stock
ON k_stock.item_no = f_orders.item_no

WHERE
(job.order_stop = Nz([Tempvars]![OrdersStop],0))
AND (job.order_id Like "*" & nz([Tempvars]![OrdersNo],"*") & "*" )
AND (job.lamella_cross_section_no1 Like "*" & [Tempvars]![OrdersLamCrossSection1] & "*")
AND (job.lamella_cross_section_no2 Like "*" & [Tempvars]![OrdersLamCrossSection2] & "*")
AND (job.machine Like "*" & [Tempvars]![OrdersMachine] & "*")
AND (job.comment Like "*" & [Tempvars]![OrdersComment] & "*")
AND (job.mat_prodg Like "*" & [Tempvars]![OrdersMatProdG] & "*")
AND (job.product Like "*" & [Tempvars]![OrdersProduct] & "*")
AND (job.order_id<>"TL 000" Or (job.order_id)<>"Test")

GROUP BY job.order_stop,
job.queue,
job.lamella_cross_section_no1,
job.lamella_cross_section_no2,
job.machine,
job.work_date,
job.comment,
job.mat_prodg,
job.product,
job.wood & "-" & job.raw_material_thickness & "-" & job.raw_material_width & "-" & job.quality & "-" & job.raw_material_length,
job.prodg_specification,
job.glue_area,
job.lamella_raw_material_no1,
job.lamella_raw_material_no2,
job.lamella_pieces_no1,
job.lamella_pieces_no2,
job.work_time,
job.customer,
job.order_id,
f_orders.high_priority_order,
IIf(Len(Nz([item_desc_suggestion],0))<1 And Len(Nz([item_no_suggestion],0))<1 And [request_time]=[request_time_suggestion],0,IIf(Nz([request_time_suggestion],0)=0,0,1))
ORDER BY
f_orders.high_priority_order DESC ,
job.queue,
job.work_date,
job.work_time;

[Fukiku]

Kas oled proovinud Sum funktsioonid välja jätta, et näeksid kõiki ridu, mis summeerimisse lähevad?

Ei jaksa sisuliselt end sellest päringust läbi närida, aga spekuleerin, et kõigi nende joinide peale tekib sul olukord, kus mõnest tabelist tuleb mitu vastet, mis tõttu summeerimist vajav väli on ka väljundis mitu korda. Kui sa nüüd aru saad, mida ma mõtlen. Ühesõnaga, viska Sum'id välja ja vaata, mis read sealt päringust üldse välja tulevad.

NB! Alati ei peagi üritama kõike ühe megasuure päringuga lahendada. Mitu eraldi ja väiksemat päringut on okei lahendus, eriti kui see koodi loetavamaks teeb.

[kaupmees]

Tänud, sain asja vist korda.