How to start?

[bergjes]

Hi,

hope you can help me with following.

I have this table with data from a few years (and counting)

I need a query to select the data from the accountnumbers from the current year and the year before that, so I can calculate the difference between them.

For now I have 3 years, 2010, 2011 and 2012.
But in my query only the years 2011 and 2012 shows?


I need all three years.

SELECT Qry_omzet_week_rek.jaar, Qry_omzet_week_rek.Rekeningnr, Qry_omzet_week_rek.Weeknr, Qry_omzet_week_rek.omzet_week AS [Omzet week bj], Qry_omzet_week_rek_1.omzet_week AS [Omzet week vj]
FROM Qry_omzet_week_rek _
INNER JOIN Qry_omzet_week_rek AS Qry_omzet_week_rek_1 _
ON (Qry_omzet_week_rek.Rekeningnr = Qry_omzet_week_rek_1.Rekeningnr) AND (Qry_omzet_week_rek.Weeknr = Qry_omzet_week_rek_1.Weeknr)
WHERE (((Qry_omzet_week_rek_1.jaar)=[Qry_omzet_week_rek].[jaar]-1));

Hope someone can help me?
Perhaps I'm going all wrong about this, perhaps there is an other way to get the right results?

Thanks.
Erik

[hertfordkc]

Can't be certain, but it looks like the posted query is based on another query rather than a table. If this is the case, look at the underlying query to see if there is a WHERE clause limiting the years retrieved.

[bergjes]

Thanks,

you are right, it is based on an other query, but only because I only need a small part of the table data. And there is no WHERE clause in that query.

Gr,
Erik

[hertfordkc]

Thanks,

you are right, it is based on an other query, but only because I only need a small part of the table data. And there is no WHERE clause in that query.

Gr,
Erik

Can you identify the 'year' fields? When I look at the query, I guess that it is dealing with weekly events.
Having blanks in name and especially field names is not good.

[bergjes]

I'm not sure what you mean by "Can you identify the 'year' fields?"

Perhaps it would have been better if I made the query in "english"

year = jaar
week = Weeknr
accountnumber = Rekeningnr
amount = omzet_week

Hope this is what you mean?

[hertfordkc]

what the join between the two tables accomplishes. It is more a matter of my not having any feel for the data and your processes.
I say again, having blanks in your field names (following AS) is a bad idea. Either compact them or use underscores. However, I don't think that will solve your two years of data vs. three years desired. I'll think about it some more.

[bergjes]

Hi,
I re-write the query, so perhaps this makes things more clear?

The tabel has 9 records , but my query only 6 records (the ones from year 2010 are missing)


Table result

      sales    Id year accountnumber weeknumber amount 
     1079 2010 1000 1 1.011,38 
    1084 2010 1000 2 544,35 
    1090 2010 1000 3 557,31     
1597 2011 1000 1 766,05 
    1603 2011 1000 2 494,35     
1609 2011 1000 3 507,31     
2116 2012 1000 1 866,05     
2122 2012 1000 2 594,35     
2128 2012 1000 3 607,31

Query result

      weeksales    Year accountnumber weeknumber amount_1 amount_2 
     2011 1000 1 766,05 1.011,38 
    2011 1000 2 494,35 544,35     
2011 1000 3 507,31 557,31     
2012 1000 1 866,05 766,05     
2012 1000 2 594,35 494,35 
    2012 1000 3 607,31 507,31

SELECT sales.Year, sales.accountnumber, sales.weeknumber, sales.amount AS amount_1, sales_1.amount AS amount_2
FROM sales INNER JOIN sales AS sales_1 ON (sales.weeknumber =  sales_1.weeknumber) AND (sales.accountnumber = sales_1.accountnumber)
WHERE (([sales_1].[year]=[sales].[year]-1));

[hertfordkc]

I do queries in the QBE design view so I'm not much in SQL, but here's what I think needs to be changed

SELECT sales.Year, sales.accountnumber, sales.weeknumber, sales.amount AS amount_1, sales_1.amount AS amount_2, sales_2.amount AS amount_3

FROM sales INNER JOIN sales AS sales_1 ON (sales.weeknumber = sales_1.weeknumber) AND (sales.accountnumber = sales_1.accountnumber)
AND (sales.weeknumber = sales_2.weeknumber)

WHERE (([sales_1].[year]=[sales].[year]-1) AND ([sales_2].[year] = [sales].[year]-2) );

Good luck.

[bergjes]

Thanks.
It's much like a pivot table this way.

Problem Solved