how to solve this double query

[gunterhoflack]

Hello, in this first query I look for each month, how much money was made online.




Now I have an extra table, where i follow up new people and how many packs they buy.

Now I would like to have an extra colum for every month, how much packs I have in total for that month. FOr in stance in oktober ... how much packs were bought ? But people who started AFTER oktober, may not be count ....

I tried several things, but I'm not able to solve this puzzle ....;

Anyone can help???
tnx a lot

[alcapps]

i would like to help but I am confused by the column names.. If they were converted to english I might be able to help you.
one way to group by month is take the date column and Month(date field)

example in query : PackCountByMonth: Month(date field)
sql select Month(date field) as PackCountByMonth
Group by Month(date Field)

you can pivot as well.. once you have a column by Month in your query.

Hope this helps.

[gunterhoflack]

yes I know how to group by month or so but Let's take next example
I have X people, who buy a package .
Lets say, 2 people started at september, 3 others at oktober and 1 started in december
knowing as a fact that each person, has to buy every month a package
so the 2 people who started at september also buy a package in oktober, november ,.... and so on

I've put a group on the year and on the month ( because december 2012 is different than december 2013 )
Now, the question is :
How can I count the packages bought, let's say November
so the 2 people who started in september must be counted, and the people who started in oktober, but NOT thos in december.....

I hope this will be clearer as a question using the next table

ID : just tableID
TP_lidid : ID of the person who started ( details in another table)
TP_aantal : amount of packages the person bought
TP_datuma : date the people started to buy the packages.

[alcapps]

I would think that you can drop the personid and you can use format(tp_datuma,"mm/yyyy")

If u need people you will need to do a pivot table

[alcapps]

Where clause tp_datuma < #12/01/2012#

will restrict the records

[alcapps]

Are you trying to count new people who ordered or number of bags sold or both?

[gunterhoflack]

no I want to count the Packs in total , in future, people will buy more each month , for instance, people A will buy 2 people B 3 , People c 1, and s o on.

[gunterhoflack]

OK tnx, but I want to add this in an extra colum in the query I already Use :


where jaar = year and maand = month

So i want to use the values in this field to make the restriction you are talking about, but how do I put this in my formula .

Something like this ?
SELECT Year([tp_datumaankoop]) AS jaar, Month([tp_datumaankoop]) AS maand, Count(tbl_trafficpacks.tp_aantal) AS CountOftp_aantal
FROM tbl_trafficpacks
GROUP BY Year([tp_datumaankoop]), Month([tp_datumaankoop])
HAVING (((Year([tp_datumaankoop]))<=2012) AND ((Month([tp_datumaankoop]))<=12));

here trying to look how many packs there are bought in december 2012

but if i want to use this as an extra field in my query : the 2012 and 12 , must be replaced by the value I have in the query coming from the colum jaar ( 2012 )and the colum month ( 12 )


I know It's not easy..... but everyone learns from it, especially me ....

[alcapps]

ok. I

you can do it this way.

SELECT Format([tp_datuma],"mm/yyyy") AS d, Sum(tbl_trafficpacks.tp_aantal) AS MonthTotal
FROM tbl_trafficpacks
WHERE (((tbl_trafficpacks.tp_datuma) Between #1/10/2012# And #30/11/2012#))
GROUP BY Format([tp_datuma],"mm/yyyy")
ORDER BY Format([tp_datuma],"mm/yyyy") DESC;

or

SELECT Format([tp_datuma],"mm/yyyy") AS d, Sum(tbl_trafficpacks.tp_aantal) AS MonthTotal
FROM tbl_trafficpacks
GROUP BY Format([tp_datuma],"mm/yyyy")
HAVING (((Format([tp_datuma],"mm/yyyy")) Between #1/10/2012# And #30/11/2012#))
ORDER BY Format([tp_datuma],"mm/yyyy") DESC;

I think the issue was you were using the count instead of the sum values.

Hope this helps..

[gunterhoflack]

i tried your solution but i don't get the right results ,
for oktober I get : 3, an d for november I get 2 .
But this isn't right
Becuase I have 1 person who started in september with one packet
1 person in oktober, also with 1 packet
3 persons in december
and 2 in january

So , when I ask how many packets there will be bought in december, query must count al packet from the persons who started before december : so sep, oktober: so this should be 2
say I ask for january : that must be 5 ( 1 in sep with 1 packet, 1 in oct with 1 packet en 3 in december with 1 ppacket )

greetz

[alcapps]

ok, I think I got it.. Take this query as a base because we are counting what happened in each month but you are wanting a total count.
so create another query that sums the counts.

or

SELECT Sum(tbl_trafficpacks.tp_aantal) AS TotalPacket
FROM tbl_trafficpacks
WHERE tbl_trafficpacks.tp_datuma Between #1/10/2012# And #30/11/2012#

[gunterhoflack]

ok, if I would like to use this in a field on a continuous form, I could use Dsum function ?
Dsum( "tpaantal" , " tbl.trafficpacks" , "month(tbl_trafficpacks.tp_datumaankoop) < maand and year(tbl_trafficpacks.tp_datumaankoop) < jaar" )

where maand and jaar comes from the query

The result should be used in a continuous form, and it should give this result


For now i entered it through photoshop

every line is one line in a continuous form, and the value of the month and the year should be used to look in the tabel to see how many packs were bought since thath month


Is this clear ??