only show items which are downloaded twice or more and only if last download was ...

[TheReneAccess]

Hi all,

normally I do everything in excel but now I have to change to access as I have to create more often Reports.

My Situation:

I have a excel list of all downloads from one client, there are more than 10.000 entries in it.

for example:

FileID Size Premium Price Date
1012866 XSmall No 1 8/23/2010
1012866 Medium No 6 10/30/2010
1012866 XSmall No 2 9/18/2011
1012866 Medium Yes 135 7/27/2012


1018171 Medium Yes 155 7/31/2012


1020987 Medium No 10 11/4/2011
1020987 Medium Yes 135 2/2/2012
1020987 Medium Yes 135 7/17/2012




1034127 Medium Yes 145 5/8/2011
1034127 Medium Yes 153 3/19/2012
1034127 Medium Yes 155 7/1/2012



I need the query to show me only files which have been downloaded(in the past month) but twice or more (in total) , and all historical downloads of the same file as well.

It's Access 2007 if you need any information let me know!

Thanks,
Rene

[June7]

What do you mean by 'past month'? This is October, do you want October or September data? If report run on November 1, do you want November or October data? Or do you want the past 30 days regardless of the month?

[TheReneAccess]

What do you mean by 'past month'? This is October, do you want October or September data? If report run on November 1, do you want November or October data? Or do you want the past 30 days regardless of the month?

Hi I have to create the report for august september and then for every month on the 1st for the past month

[June7]

Try:

SELECT Table1.*, DCount("FileID","Table1","FileID='" & [FileID] & "'") AS CountID, Format(DMax("[Date]","Table1","FileID='" & [FileID] & "'"),"mmyyyy") AS MaxDate
FROM Table1
WHERE (((DCount("FileID","Table1","FileID='" & [FileID] & "'"))>1) AND ((Format(DMax("[Date]","Table1","FileID='" & [FileID] & "'"),"mmyyyy"))=[Enter MMYYYY]));

[TheReneAccess]

Try:

SELECT Table1.*, DCount("FileID","Table1","FileID='" & [FileID] & "'") AS CountID, Format(DMax("[Date]","Table1","FileID='" & [FileID] & "'"),"mmyyyy") AS MaxDate
FROM Table1
WHERE (((DCount("FileID","Table1","FileID='" & [FileID] & "'"))>1) AND ((Format(DMax("[Date]","Table1","FileID='" & [FileID] & "'"),"mmyyyy"))=[Enter MMYYYY]));

Hi I'm getting a Missmatch error, I attached a quick setup of your query with my table can you have a look why it's not working? I have to force Quit acces as soon as I run your query.

Report_2012-10-28.zip

Thank you!
Rene

[June7]

My suggestion assumed all fields were text type. If number type don't need the apostrophe delimiters. The query will work but is slow. Domain aggregate functions can be very slow.

Try this alternative.

This query will return the latest date and the records count for each FileID that has more than 2 records and for the specified mmyyyy.

SELECT Table1.FileID, Count(Table1.FileID) AS CountOfFileID, Max(Format([Date],"mmyyyy")) AS MaxMoYr
FROM Table1
GROUP BY Table1.FileID;
HAVING (((Count(Table1.FileID))>1) AND ((Max(Format([Date],"mmyyyy")))=[enter mmyyyy]));

However, if you need to see the other data from the table then join the first query to the table.
SELECT Table1.* FROM Query2 INNER JOIN Table1 ON Query2.FileID = Table1.FileID;

Unfortunately, the two methods don't return the same set of records. Not sure why.