Summary query from 2 tables

Question

Post reply

Summary query from 2 tables

b.storm

Valued Member

Points: 57
More actions
April 23, 2010 at 6:45 am

#219516

Can you help me constructing a query for the following?
I have 2 tables
ProductsPurchasedPerMonth
Column Name
idDetailsPerPayment
idPaymentsPerCustomer
idCustomer
idProduct
ProductName
PricePerProduct
Quantity
TotalPerProduct
Remarks
PartialPaymentsPerProductPerMonth
Column Name
idPartialPayment
idPaymentsPerCustomer
AmountPaid
DatePaid
idPaymentMethod
Remarks
idProduct
idDetailsPerPayment
The result set should contain the following fields:
ProductName, Sum of Quantity, Sum of TotalPerProduct
The following conditions must be fulfilled:
-The month and year of PartialPaymentsPerProductPerMonth.DatePaid are parameters. This is not a problem for me.
-The sum of PartialPaymentsPerProductPerMonth. AmountPaid per product must be equal to or higher than ProductsPurchasedPerMonth. TotalPerProduct.
-The product may only be included in the result when the final payment has been made in the specified month.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What have you tried so far?

We're here to help you, not to substitute you...

Also, please read and follow the first link in my signature on how to ask for help (providing ready to use sample data).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

b.storm Valued Member Points: 57 More actions · Answer 2

Lutz,

you clearly noticed that I am a newbie on this forum.

My apologies if I am not yet aware of the etiquette.

I can give you the latest version of the query that produces some result but not the correct values.

The fieldnames are in Dutch. I translated them previously to make them easier to understand for anyone who doesn't understand Dutch (which is about 99.999...% of the world population).

I hope however that the structure explains itself.

The query I constructed is shown below:

SELECT T1.NaamProduct AS Product, Sum(T1.MaxVanAantal) AS Stuks, Sum(T1.SomVanBetaaldBedrag) AS Bedrag

FROM qryVolBetaald AS T1 INNER JOIN dbo_DeelBetalingen ON (T1.idProduct=dbo_DeelBetalingen.idProduct) AND (T1.idBetalingenPerKlant=dbo_DeelBetalingen.idBetalingenPerKlant)

GROUP BY T1.NaamProduct, dbo_DeelBetalingen.idBetalingswijze, Month([DatumVoldaan]), Year([DatumVoldaan])

HAVING (((dbo_DeelBetalingen.idBetalingswijze)=16) AND ((Month([DatumVoldaan]))=[@Maand]) AND ((Year([DatumVoldaan]))=[@Jaar]))

I wil read your suggested link.

Thanks,

Berend Storm

b.storm Valued Member Points: 57 More actions · Answer 3

Lutz,

thank you for your primary response.

I placed the same question also on another Forum.

Here I received my answer this morning.

This means we can close this thread.

How do I do that?

Berend Storm