Summary Query has Duplicates

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;
 
R

ryguy7272

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

MrRJ

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...
 
M

MrRJ

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question regarding Access Query 0
Links through multiple tables creates duplicates 4
query error 12
incorrect sum from query 2
Grouping/Deleting Dates/Times (HELP ANYONE!) 2
row which has TOTALS of coulmns? 5
Subquery not integrating to query to display previous sum of quantities 3
Seeking Import/Link/Query workaround #Num! and #Error responses 6

Top