top 4 records 1

[robertsquestion]

Hi,

I'm using MS Access 2003 on Windows XP and I've got a question about selecting specific records from my database.

The tables are defined as follows:
Table 1 : T_Customer
Customernr (primary key)
Customername

Table 2 : T_Sales
Customernr
Periodnr
Article_group
Sales_euro
(primary key is combination of Customernr/Periodnr/Article_group)

I've create a relationship between the 2 Customernr-fields.

Now what I need is the "Sales_euro" per customernr per article_group per periodnr from the last 4 available(!) periodnrs. The last periodnr per customernr could be different, not all customers buy in all periods. There are only 3 different article_groups (A, B and C). If there are sales in one period, the period should be selected (if it is in the last 4 periods within that customer).

Now I've tried to solve this with "select top 4" (on periodnr) in my query. I had to create a few different (sub)queries to get a result. But the final query is quite slow, even in my test-database that has got only 4.000 records. There will be around 100.000 records in the actual database.

Some example records in table T_Sales:
1247;200701;A;80
1247;200701;B;100
1247;200701;C;60
1247;200702;A;800
1285;200701;A;60
etc.

Does anyone know a nice and clean solution to solve this? (without having to create too many different queries which makes the final query quite slow)

Thanks in advance for your help!

Regards,
Robert
The Netherlands

 

[PHV]

A starting point:
SELECT A.Customernr, A.Periodnr
FROM T_Sales AS A INNER JOIN T_Sales AS B ON A.Customernr=B.Customernr AND A.Periodnr<=B.Periodnr
GROUP BY A.Customernr, A.Periodnr
HAVING Count(*)<=4

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robertsquestion]

Hi PH,

Thanks for your quick reply! I've tried your suggestion, but unfortunately it didn't work. I do get a result out of the query, but the result is not correct.

This is my SQL (field names are a bit different than above, but the idea is the same):
SELECT A.BPID, A.StartPeriod, A.ReportGroup, A.Pcs
FROM NHMREP AS A INNER JOIN NHMREP AS B ON (A.StartPeriod <= B.StartPeriod) AND (A.BPID = B.BPID)
GROUP BY A.BPID, A.StartPeriod, A.ReportGroup, A.Pcs
HAVING (((Count(*))<=4));

Result of this query for for example BPID nr 1 is:
(BPID, StartPeriod, ReportGroup, Pcs)
1;200603;A;5
1;200603;B;100
1;200603;B;19790

200603 is the last available period for this customer so these records should indeed be included, but this BPID also had records for period 200602 which should have been in the query result.
I guess something's going wrong with selecting the last 4 available periods (so maximize on period).
Do you have any other suggestions?

Thanks,
Robert

 

[PHV]

You didn't follow my suggestion.
The following (sub-)query should retrieve the last 4 available periods per BPID:
SELECT A.BPID, A.StartPeriod
FROM NHMREP AS A INNER JOIN NHMREP AS B ON (A.StartPeriod <= B.StartPeriod) AND (A.BPID = B.BPID)
GROUP BY A.BPID, A.StartPeriod
HAVING Count(*)<=4


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

You didn't follow my suggestion.
The following (sub-)query should retrieve the last 4 available periods per BPID:
SELECT A.BPID, A.StartPeriod
FROM NHMREP AS A INNER JOIN NHMREP AS B ON (A.StartPeriod <= B.StartPeriod) AND (A.BPID = B.BPID)
GROUP BY A.BPID, A.StartPeriod
HAVING Count(*)<=4

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robertsquestion]

Hi PH,

I've done some more testing and I finally got it running!
Thanks a lot for your help!

Regards,
Robert