Summary Query has Duplicates

  • Thread starter Thread starter MrRJ
  • Start date Start date
M

MrRJ

Hi,

I need your help. Here is my SQL as I understand that it may be helpful to
you to understand my problem. I have a table and mulitple queries in which I
am trying to create a summary report which lists Vendor and Types on the left
with Volume and Price listing across for each Period. It looks like the data
is a duplicate. How do I resolve this?

SELECT DISTINCTROW [Vendor / Types].[GP Vendor], [Vendor / Types].Types,
Sum([P 12 PET Rebate Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty], Sum([P
12 PET Rebate Calc].[Rebate Rate]) AS [SumOfRebate Rate], Sum([P 11 PET
Rebate Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty1], Sum([P 11 PET
Rebate Calc].[Rebate Rate]) AS [SumOfRebate Rate1], Sum([P 10 PET Rebate
Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty2], Sum([P 10 PET Rebate
Calc].[Rebate Rate]) AS [SumOfRebate Rate2]
FROM (([Vendor / Types] LEFT JOIN [P 12 PET Rebate Calc] ON ([Vendor /
Types].[GP Vendor]=[P 12 PET Rebate Calc].[GP Vendor]) AND ([Vendor /
Types].Types=[P 12 PET Rebate Calc].Type)) LEFT JOIN [P 10 PET Rebate Calc]
ON ([Vendor / Types].[GP Vendor]=[P 10 PET Rebate Calc].[GP Vendor]) AND
([Vendor / Types].Types=[P 10 PET Rebate Calc].Type)) LEFT JOIN [P 11 PET
Rebate Calc] ON ([Vendor / Types].[GP Vendor]=[P 11 PET Rebate Calc].[GP
Vendor]) AND ([Vendor / Types].Types=[P 11 PET Rebate Calc].Type)
GROUP BY [Vendor / Types].[GP Vendor], [Vendor / Types].Types;
 
Can you try an Inner Join? Just a guess; I'm trying to learn this stuff too!!
HTH,
Ryan--
 
I did the inner join and it appears that some of the Types are ok accross all
periods, but some types have abnormally large values. It doesn't make sense?

Rich

ryguy7272 said:
Can you try an Inner Join? Just a guess; I'm trying to learn this stuff too!!
HTH,
Ryan--

--
RyGuy


MrRJ said:
Hi,

I need your help. Here is my SQL as I understand that it may be helpful to
you to understand my problem. I have a table and mulitple queries in which I
am trying to create a summary report which lists Vendor and Types on the left
with Volume and Price listing across for each Period. It looks like the data
is a duplicate. How do I resolve this?

SELECT DISTINCTROW [Vendor / Types].[GP Vendor], [Vendor / Types].Types,
Sum([P 12 PET Rebate Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty], Sum([P
12 PET Rebate Calc].[Rebate Rate]) AS [SumOfRebate Rate], Sum([P 11 PET
Rebate Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty1], Sum([P 11 PET
Rebate Calc].[Rebate Rate]) AS [SumOfRebate Rate1], Sum([P 10 PET Rebate
Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty2], Sum([P 10 PET Rebate
Calc].[Rebate Rate]) AS [SumOfRebate Rate2]
FROM (([Vendor / Types] LEFT JOIN [P 12 PET Rebate Calc] ON ([Vendor /
Types].[GP Vendor]=[P 12 PET Rebate Calc].[GP Vendor]) AND ([Vendor /
Types].Types=[P 12 PET Rebate Calc].Type)) LEFT JOIN [P 10 PET Rebate Calc]
ON ([Vendor / Types].[GP Vendor]=[P 10 PET Rebate Calc].[GP Vendor]) AND
([Vendor / Types].Types=[P 10 PET Rebate Calc].Type)) LEFT JOIN [P 11 PET
Rebate Calc] ON ([Vendor / Types].[GP Vendor]=[P 11 PET Rebate Calc].[GP
Vendor]) AND ([Vendor / Types].Types=[P 11 PET Rebate Calc].Type)
GROUP BY [Vendor / Types].[GP Vendor], [Vendor / Types].Types;
Click to expand...
Click to expand...
 
I think that I found the source, but still needs help rectifying it. When I
did it for one period, it is okay, however, when I add another period, it
doesn't. WHY?

MrRJ said:
I did the inner join and it appears that some of the Types are ok accross all
periods, but some types have abnormally large values. It doesn't make sense?

Rich

ryguy7272 said:
Can you try an Inner Join? Just a guess; I'm trying to learn this stuff too!!
HTH,
Ryan--

--
RyGuy


MrRJ said:
Hi,

I need your help. Here is my SQL as I understand that it may be helpful to
you to understand my problem. I have a table and mulitple queries in which I
am trying to create a summary report which lists Vendor and Types on the left
with Volume and Price listing across for each Period. It looks like the data
is a duplicate. How do I resolve this?

SELECT DISTINCTROW [Vendor / Types].[GP Vendor], [Vendor / Types].Types,
Sum([P 12 PET Rebate Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty], Sum([P
12 PET Rebate Calc].[Rebate Rate]) AS [SumOfRebate Rate], Sum([P 11 PET
Rebate Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty1], Sum([P 11 PET
Rebate Calc].[Rebate Rate]) AS [SumOfRebate Rate1], Sum([P 10 PET Rebate
Calc].[SumOfAccpt Qty]) AS [SumOfSumOfAccpt Qty2], Sum([P 10 PET Rebate
Calc].[Rebate Rate]) AS [SumOfRebate Rate2]
FROM (([Vendor / Types] LEFT JOIN [P 12 PET Rebate Calc] ON ([Vendor /
Types].[GP Vendor]=[P 12 PET Rebate Calc].[GP Vendor]) AND ([Vendor /
Types].Types=[P 12 PET Rebate Calc].Type)) LEFT JOIN [P 10 PET Rebate Calc]
ON ([Vendor / Types].[GP Vendor]=[P 10 PET Rebate Calc].[GP Vendor]) AND
([Vendor / Types].Types=[P 10 PET Rebate Calc].Type)) LEFT JOIN [P 11 PET
Rebate Calc] ON ([Vendor / Types].[GP Vendor]=[P 11 PET Rebate Calc].[GP
Vendor]) AND ([Vendor / Types].Types=[P 11 PET Rebate Calc].Type)
GROUP BY [Vendor / Types].[GP Vendor], [Vendor / Types].Types;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top