GROUP BY - INNER JOIN

FILIPA 11/10/2012 04:47:11
#411784

If objSQLacs.Provider = 1 Or objSQLacs.Provider = 2 Then


BD.RecOpen recDocs, [Ô]SELECT * FROM Fct, FctLn WHERE (Fct.FctData) >=#[Ô] & Format(
DataIni.Value, [Ô]m/d/yyyy[Ô]) & _
[Ô]# And (Fct.FctData) <=#[Ô] & Format(DataFim.Value, [Ô]m/d/yyyy[Ô]) & _
[Ô]# And (FctLn.FctLnData) >=#[Ô] & Format(DataIni.Value, [Ô]m/d/yyyy[Ô]) & _
[Ô]# And (FctLn.FctLnData) <=#[Ô] & Format(DataFim.Value, [Ô]m/d/yyyy[Ô]) & [Ô]# ORDER By
FCT.FCTID, FCTLN.FCTID, FCTLN.FCTLNID, Fct.FctNr, Fct.FctData, FctLn.FctLnData [Ô]

End If


If BD.RecEmpty(recDocs) = False Then

If BD.GetFieldValue(recDocs, [Ô]FCT[Ô]) <> 0 Then
ContFct = 1
Do Until recDocs.EOF
ContFct = 1
[ô]recDocs.MoveNext
ContFct = ContFct + 1
recDocs.MoveNext


[ô] isto é o cabeçalho que se imprime no ficheiro txt,

[ô]r1
Print #f, [Ô]R1[Ô] & Format(BD.GetFieldValue(recDocs, [Ô]FctData[Ô]), [Ô]yyyy[Ô]) &
Format(BD.GetFieldValue(recDocs, [Ô]FctData[Ô]), [Ô]mm[Ô]) & Format(BD.GetFieldValue(recDocs,
[Ô]FctData[Ô]), [Ô]dd[Ô]) _
& [Ô]0010[Ô] & [Ô]F-[Ô] & Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctNr[Ô])), [Ô]000000[Ô]) & [Ô]00[Ô] _
& Format(BD.GetFieldValue(recDocs, [Ô]FctEntCd[Ô]), [Ô]000000[Ô]) & [Ô]0000[Ô] & [Ô]0000[Ô] & [Ô]0000[Ô] &
[Ô]0010[Ô] _
& [Ô]EUR[Ô] & [Ô]0000[Ô] & [Ô]G-000000[Ô] & [Ô]00000000[Ô] & Space(106) & [Ô]N[Ô]
[ô]If BD.GetFieldValue(recDocs, [Ô]FCTln[Ô]) <> 0 Then
[ô]
[ô] ContFct = 1
[ô] Do
[ô]
[ô]ContFct = ContFct + 1
[ô]recDocs.MoveNext

[ô] R2

[ô] este é o r2 que é o [Ô]meio [Ô] todas as linhas de artigos são representadas aki

Print #f, [Ô]R2[Ô] & Format(BD.GetFieldValue(recDocs, [Ô]FctData[Ô]), [Ô]yyyy[Ô]) &
Format(BD.GetFieldValue(recDocs, [Ô]FctData[Ô]), [Ô]mm[Ô]) & Format(BD.GetFieldValue(recDocs,
[Ô]FctData[Ô]), [Ô]dd[Ô]) _
& [Ô]0010[Ô] & [Ô]F-[Ô] & Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctNr[Ô])), [Ô]000000[Ô]) _
& Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctLnCD[Ô])), [Ô]000000000000000[Ô]), [Ô],[Ô],
[Ô][Ô]) & [Ô]00000000[Ô] & [Ô]00000000[Ô] & [Ô]00000000[Ô] & [Ô]UN[Ô] & [Ô]0001[Ô] _
& Format(BD.GetFieldValue(recDocs, [Ô]FctLnTxIva[Ô]), [Ô]00[Ô]) & [Ô]00[Ô] & Replace(Format(CStr(
BD.GetFieldValue(recDocs, [Ô]FctLnQtd[Ô])), [Ô]00000000.000[Ô]), [Ô],[Ô], [Ô][Ô]) _
& [Ô] [Ô] & Format(BD.GetFieldValue(recDocs, [Ô]FctLnTxDesc[Ô]), [Ô]00[Ô]) & [Ô]00[Ô] & [Ô]0000[Ô] &
[Ô]0000[Ô] & [Ô]U[Ô] & Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctLnVlrDef[Ô])),
[Ô]00000000.000[Ô]), [Ô],[Ô], [Ô][Ô]) & [Ô] [Ô] _
& Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctLnDesc[Ô])), [Ô]00000000.00[Ô]), [Ô],[Ô], [Ô][Ô]
) & [Ô] [Ô] & [Ô]0000000000[Ô] & [Ô] [Ô] _
& Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctLnVlr[Ô])), [Ô]00000000.00[Ô]), [Ô],[Ô], [Ô][Ô])
& [Ô] [Ô] & Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctLnValorCIva[Ô])),
[Ô]00000000.00[Ô]), [Ô],[Ô], [Ô][Ô]) & [Ô] [Ô] _
& Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctLnIva[Ô])), [Ô]00000000.00[Ô]), [Ô],[Ô], [Ô][Ô])
& [Ô] [Ô] & Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctLnValorCIva[Ô])),
[Ô]00000000.00[Ô]), [Ô],[Ô], [Ô][Ô]) & [Ô] [Ô] & [Ô]N[Ô] & [Ô]N[Ô]
[ô]recDocs.MoveNext
[ô]
[ô]Loop
[ô]End If

[ô] R3

[ô]r3 é os totais da fatura

Print #f, [Ô]R3[Ô] & Format(BD.GetFieldValue(recDocs, [Ô]FctData[Ô]), [Ô]yyyy[Ô]) &
Format(BD.GetFieldValue(recDocs, [Ô]FctData[Ô]), [Ô]mm[Ô]) &
Format(BD.GetFieldValue(recDocs, [Ô]FctData[Ô]), [Ô]dd[Ô]) _
& [Ô]0010[Ô] & [Ô]F-[Ô] & Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctNr[Ô])), [Ô]000000[Ô]) _
& Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctTotalDesc[Ô])), [Ô]00000000.00[Ô]), [Ô],[Ô],
[Ô][Ô]) & [Ô] [Ô] & [Ô]0000000000[Ô] & [Ô] [Ô] _
& Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctTotalSIVA[Ô])), [Ô]00000000.00[Ô]), [Ô],[Ô],
[Ô][Ô]) & [Ô] [Ô] & Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctTotal[Ô])), [Ô]00000000.00[Ô]
), [Ô],[Ô], [Ô][Ô]) & [Ô] [Ô] _
& Replace(Format(CStr(BD.GetFieldValue(recDocs, [Ô]FctTotalIVA[Ô])), [Ô]00000000.00[Ô]), [Ô],[Ô],
[Ô][Ô]) & [Ô] [Ô] & [Ô]0000000000[Ô] & [Ô] [Ô] & Replace(Format(CStr(BD.GetFieldValue(recDocs,
[Ô]FctTotal[Ô])), [Ô]00000000.00[Ô]), [Ô],[Ô], [Ô][Ô]) & [Ô] [Ô] & Space(76) & [Ô]N[Ô]
recDocs.MoveNext
Loop
End If

Sal:
PROXIMO:
BD.RecClose recDocs, True
End If


o meu problema é que tenho 2 tabelas na base dados.
- Fct --> onde tenho os totais e o numero de faturas
- FctLn --> onde tenho os produtos da fatura por linhas ( podem ser 3 linhas na base dados de 1 fatura

o meu problema e que neste momento ao imprimir a linha do artigo nao vem certo para o numero de faturas..

sugeriram me fazer 2 select. e colocar inner join ou group by e fazer um for para imprimir o 2 print #f.

ajudem-me

em anexo mostra como deverá imprimir