Odd Grouping?

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Thanks for taking the time to read my question.
I wrote some vb script that goes through our server and finds any files that
are larger than size n and writes it to a .csv file. I'm doing this because
our server is running out of space. The server holds users backed up files.
I've imported the data into Access 2000, and now need to make a report to
show the data logically.

My first report is to show duplicate files. So that means either one user
has backed up the same file twice in 2 locations, or different users (2 or
more) have the same file saved in different locations.

Right now I have a report that groups on User, the problem is that when 2
different users are listed as having the same file, only the grouped name
shows up in the detail section and not the other user that also has the file.

My data looks like this:
FilePath FileName FileSize User
G:\UserData\Bob\My Documents\ Test.txt 22.56 Bob
G:\UserData\Fred\My Documents\ Test.txt 22.56 Fred

and so on.

So when I group on User, Bob comes up first and the report lists Bob as
having Test.txt in the detail section but it doesn't show Fred as having it
under the Bob group, which I need so that Bob can see that Fred has a copy
too, and then they can talk and see who should delete their copy.

What do I need to do so that Fred will show up under the Bob section?

Thanks
Brad
 
Brad said:
I wrote some vb script that goes through our server and finds any files that
are larger than size n and writes it to a .csv file. I'm doing this because
our server is running out of space. The server holds users backed up files.
I've imported the data into Access 2000, and now need to make a report to
show the data logically.

My first report is to show duplicate files. So that means either one user
has backed up the same file twice in 2 locations, or different users (2 or
more) have the same file saved in different locations.

Right now I have a report that groups on User, the problem is that when 2
different users are listed as having the same file, only the grouped name
shows up in the detail section and not the other user that also has the file.

My data looks like this:
FilePath FileName FileSize User
G:\UserData\Bob\My Documents\ Test.txt 22.56 Bob
G:\UserData\Fred\My Documents\ Test.txt 22.56 Fred

and so on.

So when I group on User, Bob comes up first and the report lists Bob as
having Test.txt in the detail section but it doesn't show Fred as having it
under the Bob group, which I need so that Bob can see that Fred has a copy
too, and then they can talk and see who should delete their copy.

What do I need to do so that Fred will show up under the Bob section?
Click to expand...


Reports can not do that by themselves.

First, get the Concatenate function from
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Once you include the function in your project, you can then
add a text box to the detail section and set its expression
to use the function. Read the info in the download for
details about how to use the function.
 
Thanks for your reply Marshall.

Do I need multiple queries then, multiple reports? What's my solution?

My head hurts from banging it on the keyboard.

Brad
 
Try these two queries --
FileDupList --
SELECT FileName
FROM YourData
WHERE (FileSize > 10) AND (Count([FileName]) >1)
GROUP ON FileName;

SELECT FilePath, FileName, FileSize, User
FROM YourData INNER JOIN FileDupList ON YourData.FileName =
FileDupList.FileName;
 
Use your existing report with the same table you are already
using. Add a text box to the detail section and set its
expression to something like:

=Concatenate("SELECT User FROM [same table] WHERE FileName =
""" & Me.FileName & """ ")
 
Brad

It appears you are assuming that two files named "NewDocument.txt" are, in
fact, the same file.

Or that two individuals would never use the same file name for different
files.

Is that a safe assumption in your environment?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks everyone for your replies.

What I did was I created a key field which is the FileName and the FileSize
concatenated. I use that to link the main report to the sub report. The main
report and the sub report have the same data source. The main report has just
the user and is grouped by User. The sub report is joined to the main report
via the Key, and has all the detail info, no header or grouping info.

Thanks,

Brad
 
Great comment Jeff.

In this case many of the duplicated files come from users that have left the
company, but their data is still backed up just in case. Then someone has
copied a file to thier PC or backup folder just in case.... so now we have 2
of the same file. Do we still need the old users data??? That's the big
question, and do we need duplicate and triplicate of the same file?

I will leave it up to the users to determine if the files are in fact the
same or different.

Thanks,

Brad
 
Back
Top